cell a2, cell a1, combinations: Cleopoatra I don t believe you need a macro, just some formulas to convert your data. First I would use a formula to put the data in the format that you want. For example assume your data is in sheet 1 beginning on row 1 column A thru D. So in column...

Excel: macros, aidan, brackets

aidan, brackets, combinations: Do you want the output in the form show (with brackets) and are we limited to 4 columns or may it get longer? my email (if it helps) is aidan.heritage@virgin.net - we are looking here at a fairly simple set of for...next loops, but I d need to be sure of...

Excel: multiple linked lists, excel, dependent lists

excel, dependent lists: I use to recommend the tutorial on this page for dependent lists: http://www.contextures.com/xlDataVal02.html It has a section for adding a third dependent list, that can be expanded as needed. If you have any issues following the tutorial please let me...

Excel: "If, Then" with multiple numbers for equal, input number, ogilvy

input number, ogilvy, thanks in advance: Mike, As I understand you description, I would see it like this: =if(or(B1=2,B1=4,B1=6,B1=8,B1=10),B1*A1,(B1+0.1875)*A1) or =if(And(b1 =2,B1 =10,Mod(B1,2)=0),B1*A1,(B1+0.1875)*A1) and to fancy it up =if(or(B1= ,A1= ), ,if(or(B1=2,B1=4,B1=6,B1=8,B1=10),B1*A1,(B1+0.1875)*A1))...

Excel: #NAME$ error with UDF function, active project, ogilvy

active project, ogilvy, vbe: Ray, You need to put it in a general/standard module in the same workbook - not in a sheet module or other type of class module. In the Visual Basic Editor (VBE), with your workbook as the active project (selected in the object explorer), in the menu...

Excel: Names and Ranges, rapid reply, material specs

rapid reply, material specs, h beam: Mike, thanks for the positive comments. Private Sub Workbook_Open() Dim sName As String, bk As Workbook Dim nm As Name see if the child can see the parent sName = Dir( S:MyfolderMaster.xls ) If sName Then On Error Resume Next Set bk...

Excel: Need help with Excel Formula, copier company, excel formula

copier company, excel formula, time id: Keith, use the IF function, and simply calculate the price for each tier. Here s a similar answer I gave to this before: http://599cd.com/tips/excel/complex-if-example?key=AllExperts Let me know if you have any other questions or comments. Also,...

Excel: Nested IF(AND(OR #VALUE! error, value error, correct result

value error, correct result, linda linda: Linda, It looks like you don t have the parentheses correct. At the end of your combined IF, there needs to be one closing parenthesis for each embedded IF. I only see ONE parenthesis, where you need four. I m surprised Excel doesn t complain...

Excel: Null Values shown in cEll, union operator, null values

union operator, null values, null value: David The #NULL! is the result of not using the comma when referring to two areas in a formula that don t intersect. The following is from excel help To refer to two areas that don t intersect, use the union operator, the comma (,). For example, if...

Excel: Number rows by name column, cell b2, number rows

cell b2, number rows, thanks in advance: James Assume the first Jim is in cell B2. In cell A2 put in this formula: =countif($B$2:$B2,$B2) then drag fill down column A. (or copy and paste) This does NOT require that the names are adjacent as you show. If they are adjacent as you...

Excel: name of person with highest score, conditional formatting, highest score

conditional formatting, highest score, column c: =INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0),1) will do it for you - slightly more efficient if you change it from the entire column to the actual size of data you are working with, but this should do it - ONE proviso - this will return ONE name, so if you have...

Excel: offset, excel, sumif

excel, sumif: You could use the SUMIF formula for that. For example: =SUMIF(B1:B100, Corn ,C1:C100) This will add up all the values on range C1:C100 that correspond to Corn on range B1:B100. You can change the hard coded word for a cell reference if you want to retrieve...

Excel: open textfile in delimited and save, array array, excel workbooks

array array, excel workbooks, excel2003: mike, I would see it as something like this: Private Sub CommandButton2_Click() Dim strLoad As String Dim bk as workbook Dim sPath as String sPath = C:Data strLoad = Dir( C:data*.txt ) Do While strLoad vbNullString code goes...

Excel: open textfile in local drive and save it as a delimited file, run time error, array array

run time error, array array, excel workbooks: The OPEN command will open ONE file, but you ve tried to run a directory listing as far as I can tell - I d use the FileSearch command instead Set fs = Application.FileSearch With fs .lookin = C:Data LookIn should be changed to the location...

Excel: PAYROLL FORMULA, future proof, tax bands

future proof, tax bands, many thanks: Alex, sorry for the delay in responding If it was me I would make the sheet future proof by collating these values in a table that could be referenced by the formula (so if the bands or rates change you only need to change the values in that table rather...

Excel: Page break, abwe, page break

abwe, page break, cheers: Hi ABWE How are you? This depends on your Excel version. You have to explore the menu Windows. In Excel 2007 there is a menu Windows with a submenu panels and then a release panels command. Sorry I cannot tell you exactly because I am not working...

Excel: Pasting Conditional Formatting, format painter, cell references

format painter, cell references, absolute references: The format painter tool does not, to my knowledge, paint the conditional formatting rules . When you copy and paste that cell the conditional formatting does get applied. Or you can preselect the range of cells that you need to apply conditional...

Excel: Percentage Formula, percentage formula, variable formula

percentage formula, variable formula, completion dates: My only SLIGHT problem is getting excel to know you want to deal with 4 cells - ideally you d have another column with (say) the project name in it which would mean the whole thing could be a variable formula - but =counta(a1:a4)/4 will do it as long...

Excel: Pivot tables not refreshing when buried in vba, case differences, true field

case differences, true field, pivot table: The code looks fine, but there isn t anything in this code seting the ItemName string value - and also there is nothing in it to handle case differences (so A won t equal a for example) if it helps, you can email me with sample files at aidan.heritage@virgin.ne...

Excel: is it Possible to Expire Excel Sheet on Specific Time ?, excel sheet, allexperts

excel sheet, allexperts, aidan: This is a question I get asked fairly regularly - it s not something I would normally do as any protection within Excel can be gotten round by someone who knows what they are doing but see http://en.allexperts.com/q/Excel-1059/2009/3/expire-Excel-file-certain-1.htm...

Excel: Print Macro, busplan, true copies

busplan, true copies, separate pages: This is not a valid printarea: .PrintArea = ( DPBusPlanPg1,DPMonthRevPt1,DPMonthRevPt2,DPWSpaceSy,DPSkills ) You need .PrintArea = Range( DPBusPlanPg1,DPMonthRevPt1,DPMonthRevPt2, DPWSpaceSy,DPSkills ) Besides that, tho, it d be best if you simply...

Excel: Printing out of Excel, page layout view, free microsoft excel

page layout view, free microsoft excel, excel tips tricks: Use Page Layout view and set an appropriate Zoom ratio. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Problems using "Replace" function iwithin VBA in Excel, input text, vba

input text, vba, dear mr: Fabricio, i can t see anything wrong with your code. i wonder if your numbers actually have got a (comma) in them though or if they are just displayed in that way by Excel using a number format (for example with the number format set to #,### then the...

Excel: Program Guide, fishing show, time slots

fishing show, time slots, column c: Something I m not understanding -- it seems that all you need in C is a reference to E, like in C3 have =E3 as the formula. If you re listing a program in E, like Bobs Fishing Show, how would the program name in C be anything else? I don t get it (yet)...

Excel: Program Guide, e mail address, time in china

e mail address, time in china, dynamic properties: David, Clearly, you need at least two major things: a database of all the programs and a display page. But I bet you already knew that! So I d create a standard-looking database of the programs. This would be a typical Excel table (and if...

Excel: Protected sheets in Excel

Catherine, it certainly does make the difference. In order to edit the protected status of the workbook or sheets within it you must open it exclusively and turn off sharing first. Once you have made your edits you can then turn sharing back on Hope...

Excel: Getting part of an Excel-Sheet in the body of an E-mail, mail question, session accounts

mail question, session accounts, outlook account: Your assumption is correct and like I said you will need to create new workbook, copy b to that new workbook, and send the new workbook. You can record a separate macro that will do that and then put that code ahead of your. You can record a macro that...

Excel: passing values to and from procedures, onerous tasks, logical tests

onerous tasks, logical tests, candidate names: You d need a function if you want a value returned, or if you need more than one value returned, make sure you pass the variables you want returned to the sub. Demo: Sub DemoOfFunctionCalls() Dim sArg1 As String sArg1 = Test MsgBox Function1(sArg1)...

Excel: percentage help in excel, k4

k4: The formula you need is =D4/SUM($D$4:$K$4) The cell should be formatted as percentage to display as a percentage. NOTE that the value is actually 0.263157895 so if you are going to use this in a calculation you may want to use =round(D4/SUM($D$4:$K$4),2)...

Excel: postion of a value in a list, conditional tests, conditional test

conditional tests, conditional test, weird results: Greg, A nested IF is indeed the answer. Perhaps you need a tutorial on this topic, so I will include one at the end of this answer. Maybe you don t know about the LARGE(range,k) and SMALL(range,k) functions. These allow you to find the kth...

Excel: printing highes value on the chart, type curve, s curve

type curve, s curve, internal values: Shuaib, This is a very difficult problem. I m not sure I know an answer! The problem (as you know) is that you don t have any numbers for that part of the curve. Excel can t look at a chart and read off a number! Your eye can do this so easily,...

Excel: Question, logical values, logical value

logical values, logical value, sum function: Jim, A Logical Value refers to the values TRUE and FALSE. In certain cases, these have the numerical values 1 and 0 , but not in the case of SUM(). However, in the case of cell+cell+cell...+cell, they add (or subtract) as 1 and 0, and many other...

Excel: Question on excel working, alphabetical format, numerical format

alphabetical format, numerical format, minus sign: (1) nothing built into Excel I m afraid but see http://www.ozgrid.com/VBA/ValueToWords.htm for a way of handling this (2) if it s just the days between the 2 dates then the function is the minus sign - as in =Date2-Date1 - just ensure the cell is formatted...

Excel: Question about Random Numbers, excel, random

excel, random: For the first part of the question you can use the RANDBETWEEN function, something like: =RANDBETWEEN(17,37) If this function returns the #NAME? error, install and load the Analysis ToolPak add-in on the menu Tools- Add-ins For the second part, I would...

Excel: Question about Random Numbers, random number generations, cell a1

random number generations, cell a1, changing all the time: I don t quite follow, but it sounds as though you want to run a series of random number generations with 5, 12, and 13 all falling together? If so, there is no formula to do this, but a macro could be used - but as there could be any number of combinations...

Excel: Question about Random Numbers, excel, random

excel, random: If they are random numbers there is no formula to force them into that series without losing the randomness. You can check if the series is present on a given series with an array formula like: =SUM((A1:A50=5)*(A2:A51=12)*(A3:A52=13)) Enter it with Ctrl+Shift+Enter....

Excel: Question about Random Numbers, cell a1, random series

cell a1, random series, changing all the time: Sahil, To prevent the random numbers from changing all the time, set the workbook calculation method to MANUAL. This is found under OPTIONS. If you want to generate the random series 5, 12, 13, this is not a random series. Type 5, 12, and 13...

Excel: Question about Random Numbers, cell a1, random numbers

cell a1, random numbers, final result: Sahit, If you are using Excel 2007 you can use RANDBETWEEN to generate the numbers. if you are using 2003, you ll have to create an equation that translates 0 through 1 into 17 to 37. I won t spend time telling you how-to because you might have 2007...

Excel: Random Assignment, random assignment, allexperts

random assignment, allexperts, hotel rooms: try http://en.allexperts.com/q/Excel-1059/Excel-Random-Assignment.htm which has a solution to a similar problem - HOPEFULLY this will help you do what you want - sorry to refer you to another answer, but I ve had a huge volume of requests today so I...

Excel: Random Question Generation in MS Excel, question option, random generation

question option, random generation, drive option: Mangesh, Your problem is with getting 20 random questions, correct? Here s what I suggest. Number your 500 questions from 1 to 500. Then on the questions page, create a column of 20 random number that range between 1 and 500. Use a MATCH...

Excel: Re: Calculating quartely costs, pinky, 31 dec

pinky, 31 dec, q3: Pinky, I was afraid you d say that! This is becoming a complex problem, but of course I can solve it! The real question is, can I explain the solution! But I don t know the solution yet, because I have another question. Do you...

Excel: Rearranging text data in a cell, time in the uk, complete sentence

time in the uk, complete sentence, holiday time: I m sure it should be possible to do this - unfortunately, AllExperts allows no more than 3 days to provide an answer and with this being a holiday time in the UK I am spending time with the family so cannot easily do a macro to do this work at the moment...

Excel: Recursive formula, cell d1, recursive formula

cell d1, recursive formula, d3: I don t know about a formula to do this, but you can use goal seek. If you put some x value in D2, say, and put a 1 in D1, and this formula in D3: =PMT(6%/12,D1*12,-D2) Then you can use Goal Seek to set D3 to 1000 by changing cell D1, and D1 will then be...

Excel: Reference in Excel, d8, quick reply

d8, quick reply, kelp: Kelp, for the example you gave (if I didn t get confused on what is where). in D8 of sheet1 put in the formula =Indirect( Sheet2!B &A2) so if A2 hold the number 5, D8 will display the value in Sheet1!B5 and if A2 is changed, the value for the...

Excel: Reference Trendline Equation in Cell, linest, allexperts

linest, allexperts, polynomial: If your data is in A1:A15, select 1 row x 7 columns and ctrl+shift+enter this: =LINEST(A1:A15,ROW(1:15)^COLUMN(A:F)) if your data is in C3:C50, then use this (ctrl+shift+enter) =LINEST(C3:C50,ROW(1:48)^COLUMN(A:F)) the value in the ROW(...) is the...

Excel: Referencing a tab name based upon a cell, excel sheet, e7

excel sheet, e7, cells: Marc, This is easier than you think! Take a look at the ADDRESS function. It should do just what you want. When you embed the ADDRESS function inside an INDIRECT function, it will retrieve the value from the cell specified by ADDRESS. If you...

Excel: Regression Macro, curve data, selection formula

curve data, selection formula, sheet1: You are referencing the sheet directly - eg Sheets( Sheet1 ).Select try for each sh in activeworkbook.sheets your code in here but reference the sheet as sh.select next ALSO you don t always need to select the sheet - for instance Sheets(...

Excel: Regression Macro, curve data, selection formula

curve data, selection formula, sheet1: If I understand you right you want to still select ENTER CURVE DATA sheet to do the work on that sheet then beck to whatever the first sheet was when you started the macro? If so (and yes, this does everything your macro does): Sub MUCHMUCHShorter() Set...

Excel: Remove Duplicates in Database on Excel, unique records, mac versions

unique records, mac versions, contest entries: Not familiar with Mac versions, so I ll give two possible answers - one data, filter, advanced filter - unique records - this would need to be done on the EMAIL column alone. Other is a method I often do which is a decreasing countif - take the email column...

Excel: Removing middle initial - with a twist..., excel, text

excel, text: Here goes one, with a few nested ifs, that should cover the three conditions: =IF(ISERROR(SEARCH( ,A1)),A1,IF(LEN(TRIM(A1))=3,A1,IF(SEARCH( ,A1)=2,MID(A1,3,100),LEFT(A1,SEARCH( ,A1))))) It should leave the standard names untouched. Hope this helps,...

Excel: Repeating formula, countif, column c

countif, column c, massive amounts: You seem to be saying that you have data for hour 1 in rows 2-61, data for hour 2 in rows 62-121, etc etc. For each hour, you want the average value in col B for those rows in which the col C value is NREM. In E2, enter =E1+1 In F2, enter =SUMIF(OFFSET($B$2:$B$61,(E2-1)*60,0),...

Excel: Repeting code, false selection, a3

false selection, a3, a5: Sheets( Temp ).Select Sheets( Sheet1 ).Range( A3:AU3 ).Copy Range( A1 ) Sheets( Sheet1 ).Range( A4:AU4 ).Copy Range( AV1 ) Sheets( Sheet1 ).Range( A5:AU5 ).Copy Range( A2 ) Sort alphabeticaly Rows( 1:50 ).Select ActiveSheet.Sort.SortFields.Clear...

Excel: Replacing cells with test based on dating, trying to find a way, begining

trying to find a way, begining, term contract: Bill I can t get your copy of the page to be large enough for me to read it, but--- If the date you are testing is always in a different location it will be very difficult to write a formula that will work for you, If the date is always in the same row...

Excel: Resolving cell contents to a list, transpose operation, cell contents

transpose operation, cell contents, changing all the time: Kevin, If this isn t a constantly changing list, you can do this with the TRANSPOSE operation, a subset of PASTE SPECIAL. Then you can number the rows 1 through whatever, in an additional column. Next SORT by the column containing the TRUE...

Excel: Retrieve Data to New Sheet, ross bob, database account

ross bob, database account, jane doe: Tracey, Send me a sample workbook with the source data and where you want the results - also where you will specify the critera. Also, what columns you want returned. I can show you the formulas you need to do what you want. send it to twogilvy@msn.com...

Excel: Reverse Lookup?, row headings, array formula

row headings, array formula, array formulas: It s do-able but I m not sure how easy it s going to be to describe - still, I ll do my best - it will need array formulas - for this example, I m assuming the Large value is in F12, and my data is in F8:H10 - amend as necessary =SUM(IF(F8:H10=F12,ROW(F8:H10),0))...

Excel: Rounding to nearest 10 in excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: The normal rounding in Excel should be to round a number UP if it ends in a decimal 5 or higher. If you want to round to the nearest 10, then multiply your number by 10, take the INTeger value of it which would remove the decimal component, and then divide...

Excel: Row Hide MAcro, r1, best wishes

r1, best wishes, len: Ann, The following will do what you need. Sub Hide() Dim i As Integer Dim j As Integer For i = 1 To 5 For j = 1 To 14 With Sheets( Sheet & i) If Trim(.Cells(j, 1)) = Then .Cells(j, 1).EntireRow.Hidden =...

Excel: Run-time error '13': Type mismatch, run time error 13 type mismatch, run time error

run time error 13 type mismatch, run time error, run time error 13: WHEN, within the loop, are you getting the error? At the beginning, in the middle, or at the end/last record? A type mismatch error means that you are passing data to a variable that the variable can not accept, like passing a date to a string variable....

Excel: Runtime Error 13 type mismatch, runtime error 13 type mismatch, type mismatch error

runtime error 13 type mismatch, type mismatch error, data cells: If the error is in this chunk ERROR STARTS HERE--- If Sheets( Document ).Range( A3 ).Offset(0, PCLoop).Value = True And Sheets( Document ).Range( A4 ).Offset(0, PCLoop).Value 0 Then - ---ERROR ENDS HERE then it s that chunk that is causing the problem...

Excel: recipe costing worksheet, recipe table, recipe name

recipe table, recipe name, component parts: It sounds a great idea, but I ve spent some time thinking about this and haven t managed to come up with anything that would resolve it - if it s another recipe, you d have to input that in a named range area and reference it - which could get tricky. Possibly...

Excel: regression macro to work on multiple sheets, excel, vba

excel, vba: You can change the instances where the Sheet1 is hard coded to the sheet where you want to run the macro. Alternatively, you could trim the code of all the extra Sheets( Sheet1 ).Select statements, and use only one when needed. It would make the macro...

Excel: replace function question, state zip code, paragraph mark

state zip code, paragraph mark, city state zip: try the data menu, then text to colums - choose delimited, with commas - this will THEN give you a field per value - it s going to be the easiest way to deal with it as otherwise you will need a macro (I think) that replaces all but the last comma with a return...

Excel: return multiple data from dropdown list, vlookup function, database column

vlookup function, database column, database note: Terry, I m not entirely sure what you mean, but it sounds like you want to look up data from a table (in L4:L43) based on the entry from a drop down in H1. However, in order to perform a lookup, the table containing the data MUST be more than one column...

Excel: runtime error '1004', array array, 43881

array array, 43881, vba code: I m not going to look at the details of this longish macro, but if you ve reached a limit for excel, a macro isn t going to go beyond it. My thought would be to change Selection.SpecialCells(xlCellTypeVisible).Select to d it in pieces, like Range( A1:E40000...

Excel: runtime error '1004', array array, 43881

array array, 43881, vba code: In the line Selection.SpecialCells(xlCellTypeVisible).Select I don t believe that the SpecialCells is necessary. Please try Instead of Range( A:E ).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy try this: Range(...

Excel: runtime error '1004', array array, 43881

array array, 43881, vba code: Al, I correctly diagnosed your problem and gave you what I consider to be the correct answer for this problem and you seem to have ignored it as I see you responding positively to the incorrect answer (or at least kludgy answer that Bob gave you). Well,...

Excel: Save a file with format and values (not formulas), master spreadsheet, one day at a time

master spreadsheet, one day at a time, day at a time: Lynette If you are saving one day at a time the easiest option would be to copy the entire sheet and then go to edit paste special Values which will remove the formulas but leave the numbers/text. Depending on the version you have you may also have...

Excel: Scanning ID Barcodes into Excel and have them show Names, barcode scanners, scanning id

barcode scanners, scanning id, input cells: Matthew, All you have to do is make a database that has everyone s name in it, with their barcode value as the left most column. Then, for each barcode you scan in, you can have a VLOOKUP that matches the barcode with any of the other data in...

Excel: Scatter graph, scatter graph, northwest technical college

scatter graph, northwest technical college, numeric values: for a nxn array, excel will plot the series in rows. It will plot it in columns if there are more rows chosen. so if row 3 is empty, include it in the selection (scatter isn t affected by blank rows). that is, select A1:B3 then make your scatter. There shouldn...

Excel: Scheduling, column widths, gridlines

column widths, gridlines, thursdays: Agnes, Just make your own calendar! It can look any way you choose. Change the row heights and column widths, or draw an outline around multiple cells and hide the gridlines if you need more than one place per-day for an entry. You could also use...

Excel: Screen Split (excel 2007), free microsoft excel, split screens

free microsoft excel, split screens, excel tips tricks: I don t believe you can split it twice, sorry. You could use ACCESS instead of Excel and use multiple subforms for a header and footer. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute...

Excel: SET DUEDATE IN EXCEL, date math

date math: Hi Tran, Your formula would be: =A1+1 Dates are really integer values. Change cell the A1 format to General to see it. Today is 39925, tomorrow is 39926. Time is a fraction of a day. Example: Noon is 0.5 This allows date and time math to be...

Excel: Selecting Date & Time, data validation, speadsheet

data validation, speadsheet, protection features: Debbie What you probably have is data validation protection which can be found at toolbar/data/validation. You can check out this feature by going to the help index and typing in data validation. Also check out the other protection features by going to...

Excel: Selecting a range for the autofill destination, c12, cells

c12, cells, error message: HI Buddhika, What could be causing trouble is when you run that line of code, the Selection is not a range of cells. Another possible cause is when the selected cell is NOT cell C12. If you always want to do the autofill starting from cell C12, change...

Excel: Setting a range of cells in Excel, a3, thanks in advance

a3, thanks in advance, cells: You can either have the entire column OR a given range, but unfortunately cannot mix them - I tend to use EITHER dynamic named ranges - which work along the lines of =A3:OFFSET(A3,COUNTA(A:A)-1,0) OR simply setting a range that is bigger than any range...

Excel: Simple VBA Code, code question, control toolbox

code question, control toolbox, commandbutton: Lisa, Sub Commandbutton1_click() Dim r As Range, r1 As Range Set sh1 = ActiveSheet For Each sh In Worksheets Set r = sh.Range( H2:H2821 ) Set r1 = r.Find(What:=sh1.Range( R6 ).Text, _ After:=r(r.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows,...

Excel: Sorting in excel office 2007, cell reference, thanks john

cell reference, thanks john, 2c: Numbers get sorted first, then text - so what you are seeing is by design - to get round it, create a blank column as a helper column and (assuming we are talking single digits here) use =left(YourCell,1) as the formula where YourCell is the cell reference...

Excel: Sorting in excel office 2007, excel, sort

excel, sort: The problem is probably on the format of the cells, it is considering the numbers as number and the numbers with letters as text. One way to make it work the way you like is to format the cells as text before you enter the values, and then they should sort...

Excel: If/Then Statement, sumproduct, addressing modes

sumproduct, addressing modes, excel function: George, The images that the site allows are usually too fuzzy to read in any detail. Plus of course I cannot see the actual equation your trying. So here s a few hints: #NAME means you ve mistyped the name of a function, or somehow made Excel...

Excel: If Statement using a tab, cell reference, month today

cell reference, month today, text string: Aaron, It MIGHT be possible if you could predict the name of the most recent tab in the other workbook. If, for example, the tabs are named with the current date, or some variation of it, then you would know the tab name from which to get the numbers...

Excel: Stretching a set of data, interpolation schemes, linear interpolation

interpolation schemes, linear interpolation, mathematical procedure: Adrian, When you say stretch these numbers to 100 , it sounds as if you wish you had 100 numbers (on the X-Axis) instead of 45. You first say you have a SET of numbers and then you refer to both sets of numbers . I suspect what you have are...

Excel: SUM Function, excel, multiple sheets

excel, multiple sheets: I am not sure on what can be causing your issue, as your method should work. One suggestion that can simplify the process, if you are adding the same cell from every sheet, is to use a formula like: =SUM(Sheet1:Sheet12!A1) This will add the cell A1 of all...

Excel: SUM(Start:End!), many thanks, inclusion

many thanks, inclusion: sorry Mikael, it isn t something I ve come across the way you describe it. You could set up a little macro to carry out the calculation for you and then edit it to add in conditions that determine when to include which sheets (possibly with a user form so...

Excel: Subtraction of year in MS Excel, Date functions

Date functions: Hi sweety, You can take apart and reassemble dates in Excel using the YEAR, MONTH, DAY and DATE functions: YEAR returns the year of the date MONTH returns the month number (1-12) of the date DAY returns the day number (1-31) of the date DATE creates...

Excel: Sum Numbers only in cell array, sum numbers, 4 digits

sum numbers, 4 digits, cell array: Larry, I must say I don t understand your equation relating to CHAR(1). That is neither the Line Feed (10) or Carriage return (13), so how d it get there? But anyway. Whatever you did apparently works for you! To get the number out...

Excel: Sumif - array?, gender code, sumproduct

gender code, sumproduct, database functions: Dave, You said: I am curious why you can t enter the text/etc itself versus a cell number for the criteria. You CAN enter the criteria itself. But if you do, then to change it, you have to edit the formulas. I NEVER enter fixed constants...

Excel: Summing up, sumproduct, excel function

sumproduct, excel function, record selection: Joe, What you want is called a multi-criteria record counter . If you have Excel 2007, COUNTIFS() will work nicely [read about this in the function reference]; if NOT, then you have to use oe of the two following methods. I hope this helps and...

Excel: Sumproduct Question, case check, good answer

case check, good answer, full attention: Anibtu Sorry-- I can t seem to get a good answer for you very quickly, and I can t give it my full attention right now. I suggest you reask the question of another allexperts person and I am sure you can get a good answer much quicker than waiting on me....

Excel: script in VBA, iloc, ff2

iloc, ff2, rr2: Sub ABC1() Dim lastrow As Long, i As Long Dim iloc As Long, s As String Dim cell As Range lastrow = Cells(Rows.Count, A ).End(xlUp).Row For i = 1 To lastrow Set cell = Cells(i, 1) iloc = InStr(1, Trim(cell), circuit: , vbTextCompare) If iloc ...

Excel: selecting date range in excel, regional settings, ogilvy

regional settings, ogilvy, bets: Bill, In the US, using a version of Excel with US regional settings, then this worked for me: Private Sub CommandButton1_Click() With ActiveSheet If Not .AutoFilterMode Then .[H2].AutoFilter .Range( H2 ).Select If .FilterMode Then .ShowAllData...

Excel: setting a control limit/alert?, conditional formatting, font choice

conditional formatting, font choice, cell values: John, The ability to change cell appearance based on cell values is so important that Excel has dedicated a huge new improvement in the 2007 edition to expand the capabilities of this. Excel 2003 has this capability also but it is much more powerful...

Excel: sheet group name, sheet names, cell values

sheet names, cell values, attis: Attis, While I think it is fine, just to be safe, I would add parentheses around the conditions to insure the correct precedence is followed in evaluating the conditions: Sub Black_circle_and_X() Dim sh As Object For Each sh In Worksheets...

Excel: significant figures (significant digits), significant digits, significant figures

significant digits, significant figures, decimal places: Steve, The closest I could recommend is to use SCIENTIFIC format, like 1.23E04 or 1.256E-04. By setting the number of decimal places, you can control the number of significant figures. People who are not used to this format might have a hard time...

Excel: IF statement, first approach, months of the year

first approach, months of the year, january february: No need. If the 1 or 2... is in cell A1, use this formula: =DATEVALUE(A1& /1/2000 ) OR =DATE(2000,A1,1) and format that cell as mmmm. Alternately, you can use =CHOOSE(A1, January , February ,..., December ) but the first approach is certainly simpler....

Excel: static date based on cell value, target value, sheet tab

target value, sheet tab, dot uk: Tom, right click on the sheet tab of that sheet and select view code. Then in the resulting module paste in code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.Count = 1 Then If InStr(1, Target.Value,...

Excel: How to sum cells based on conditions, theresa chan, d2 d2

theresa chan, d2 d2, jane doe 1: Tracey, In E2 put in the formula =Sumproduct(--($A$2:$A2=$A2),--($D$2:$D2=$D2)) In F2 put in the formula: =IF(E2=1,SUMPRODUCT(--($A$2:$A$5000=$A2),--($B$2:$B$5000=$D2),$C$2:$C$5000), ) Now select E2:F2 and drag fill down column E:F (Change...

Excel: sumif and index at the same time, excel, sumif

excel, sumif: Sorry for the late, I didn t connect during the weekend. In this case, I have assumed that the sheet2 table sits on top of the page, and the sheet1 is located as in the image. With that, you can use a formula like this on C2 on sheet2: =SUMIF(Sheet1!$A$4:$A$11,$B2,OFFSET(Sheet1!$A$4:$A$11,0,MATCH(C$1,Sheet1!$B$3:$D$3,0)))...

Excel: summarizing multiple ranges, conditional formatting, rgs

conditional formatting, rgs, num num: it s called a User-Defined function. You actually should add one line to it at the beginning to ensure it recalculates when the worksheet changes: Option Base 1 Function NoBlanks(ParamArray rgs()) As Variant Dim v() As Variant Application.Volatile True...

Excel: Text boxes, text boxes, tickler

text boxes, tickler, hth: Can be done with VBA: Write this function (alt/F11, Insert/Module): Function TextBox() Application.Volatile True If Len(ActiveSheet.TextBoxes(1).Characters.Text) 0 Then TextBox = See Text Box below Else TextBox = End Function Assuming...

Excel: Text boxes, text boxes, statusbar

text boxes, statusbar, tickler: You are right that no formulas can do this (at least, not as far as I know) but VBA could - I d be INCLINED to do this via VBA, using the StatusBar (the BOTTOM of the screen) to display whatever message you wanted - or EVEN simply a pop up message box when...

Excel: Time Calculations, time math

time math: Hi Troy, Sorry for the delay. The problem probably is that you are not really entering the quitting time as the next day . Excel is interpreting 6am as being on the same day as the 10pm. One way to fix this is to enter both the date and time in the...

Excel: Time intervals, hh mm ss, free microsoft excel

hh mm ss, free microsoft excel, excel tips tricks: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Time keeping in excel, excel sheet, subtraction

excel sheet, subtraction: Misty, Your question has two parts but both parts seem to ask the same thing. All you have to do is subtract the two times. The result will be in HH:MM. Be sure to format it as TIME if you get a result that doesn t look like HH:MM. If...

Excel: Time stamps in excel, target value, target address

target value, target address, time stamps: Lots of ways are possible, depending on what you want. Here s one way (may not be the easiest, but you didn t specify the criteria): Private Sub Worksheet_SelectionChange(ByVal Target As Range) Select case Target.Address Case $A$3 , $B$4 , $G$20...

Excel: Timecard Hours worked, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, time example: I would break this up into two separate columns, or put a zero in that field as a default. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to...

Excel: Totaling hours based on project number from multiple worksheets, project numbers, selected records

project numbers, selected records, number project: Sharon, Yes this is quite possible. The answer I d use is based on the SUMPRODUCT() function, whose advanced form can be used to SUM any field in selected records. You ll need to create one SUMPRODUCT function for each day on each sheet, and...

Excel: Tracking Membership Durations, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel tutorials: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Transpose and Delete Units Macro, false application, cell1

false application, cell1, true application: Nick, this produced what you show: Sub CreateParameterImport() Dim cell As Range, r As Range Dim cell1 As Range, r1 As Range Dim s As String, s1 As String Dim iloc As Long, iloc2 As Long Dim NewWBName As String Dim wbDest As Workbook, _ wbSource...

Excel: Trying to calculate what each technician gets paid per job, v26, w22

v26, w22, q21: Josh I am thinking that you could apporoach the problem totally different. but I may be wrong not knowing exactly how you are going to use the data, etc. How about if you create a table that will reflect all of the criteria and based on all of those...

Excel: Trying to identify incorrect entries in excel, len value, conditional formatting

len value, conditional formatting, incorrect entries: Anthea, The answer depends on if these are actual NUMBERS or are they TEXT. It sounds like they are TEXT because numbers can t have a ? sign in them. So I ll assume they are TEXT. Just take the LEN(of-the-text). Add a column for this. Then...

Excel: Turn Around Time Calculation (TAT), turn around time, hello bill

turn around time, hello bill, time calculation: Sandeep, When writing a question to an Expert, please keep in mind that we don t know anything at all about your particular industry. I have no idea what a turnaround time is, or what numbers from your tables it is based on. So I can t tell...

Excel: "*text*" & AND in an array, array formula, sumproduct

array formula, sumproduct, column c: =SUMPRODUCT(--ISNUMBER(FIND( KR ,A9:A13)),--(B9:B13 CLOSED )) worked for me. you could increase the number of rows: =SUMPRODUCT(--ISNUMBER(FIND( KR ,A9:A10000)),--(B9:B10000 CLOSED )) If using xl2003 and earlier you can t do =SUMPRODUCT(--ISNUMBER(FIND(...

Excel: text sequence - days of the week, sep oct nov, text sequence

sep oct nov, text sequence, input number: Doerhe, I assume what you mean is the you want to type MON in the lead cell, and have the rest of then show as TUE, WED, etc. The if you type FRI in the lead cell, you want to see SAT, SUN, MON, etc in the following cells. If that is a correct...

Excel: transferring data, copy and paste, a1

copy and paste, a1, target: In just one specific and also always the same cell or in ANY cell? And if in any cell, where would the other cell be in the other sheet? You can add a lot of formulas in the other sheet that would be: =IF(Sheet1!A1= , ,Sheet1!A1) and then copy...

Excel: I am having trouble out of #N/A., conditional formatting, format button

conditional formatting, format button, isna: You select all the cells which MAY possibly contain the error. You use the conditional formatting formula on ONE cell and it will apply it to all the cells. If the ACTIVE cell is E3, for example, then yuo d simply use this: =ISERROR(E3) then click the format...

Excel: Uncheck Select Multiple Items in Pivottable with VBA, cube olap, pivot table in excel

cube olap, pivot table in excel, question pool: Jill, tested in xl2007 You didn t ask this question of me, but I found this question in the Question Pool. I set up a pivot Table in Excel 2007 using data in the same workbook (so no OLAP cube). The workbook was an xlsm extension (macro enabled workbook)....

Excel: Updating a spreadsheet automatically with forms, qab, menu command

qab, menu command, dialogs: Amber, You can apply a FORM menu command to your database which will automate this somewhat. In 2003, this command is readily available, I think on the DATA menu. Look for FORM.... In Excel 2007, you MUST add the FORM command to the quick access...

Excel: Use of Excel 'Text to Columns' wizard, excel, text to columns

excel, text to columns: As far as I know, there is no way to change the default separator of the text to columns. You may still use the feature of Excel remembering the delimiters used on the last run of the wizard, and start with an statement like this on the immediate window:...

Excel: User Interface - Scroll Bar or Graph to drive split of year into months, line graph, interactive line

line graph, interactive line, line charts: Steve, Excel line charts have always had to capability to drag any individual data point on the chart itself, and have the data in the corresponding cell change to match. All you have to do is provide a line chart for your percentage row and the...

Excel: Using Cell Values as Address / Auto-Move Window to Show New Address, bmp files, visible portion

bmp files, visible portion, auto move: Dan: You will need to write a macro to do what you want to do and sorry I don t write macros for free for people who post questions who need one. My standard reply is: Sorry, as politely as possible, I don t write macros for people on this site who need...

Excel: Using time-related function, target price, cell a1

target price, cell a1, excel function: Raffi, I apologize, I hadn t seen your followup question. I think you must have missed a most important part of my answer! You MUST turn the Calculation Method to an iteration of 1 to prevent the circular reference error! That should do it......

Excel: How to use Vlookup, column height, countif

column height, countif, detailed explanation: Viabhav, This is really very difficult to do. Instead of VLOOKUP, you have to use a combination of MATCH & INDEX. The MATCH function has the same problem as VLOOKUP: it only finds the first occurrence of the search item. BUT, if you use an...

Excel: V-Look Up, vlookup formula, excel spreadsheet

vlookup formula, excel spreadsheet, database column: Norma, Since there is very little information in your question, all I can do is give you my VLOOKUP tutorial and let you decide if it applies to your situation, or not. So here it is. Bill =================== VLOOKUP TUTORIAL ====================...

Excel: Validation Order, odd behavior, drop down list

odd behavior, drop down list, vba: Joe, I was going to press the this is outside my area of expertise button but thought I would at least try and answer you. Unfortunately I haven t managed to cure this problem either without using VBA to create my drop down list in the first place.......

Excel: Variable Data, ah6, ac6

ah6, ac6, cs3: Derren Here is what I would suggest First type in your names in one column (for example lets use column E) once the 1300 names are typed in do one copy and paste of the entire column across from column E to column GV (this is 200 columns). Then create...

Excel: VB Macro, target range, sheet tab

target range, sheet tab, vb program: Nate, Right click on your sheet tab and select view code. Put in this code in the resulting module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range( A1:Z26 )) Is Nothing...

Excel: VB macro issue, additon, cecilia

additon, cecilia, cheers: Hi Cecilia How are you? Please see a demonstration of the use of selection end Sub insertformula() Dim R As Range, F As Range, ntrows As Integer Set R = Selection.End(xlDown) Set F = R.Offset(1, 0) F.FormulaR1C1 = =Sum(R1C1:R5C1) End Sub ...

Excel: IF AND THEN VBA, data manipulation, logical statements

data manipulation, logical statements, menu functions: Jon, sure, I can do this with VBA. You need to tell me all the conditions and what actions you want taken specifically. Also how are the values in I12 and J12 changed - by manual editing or formulas. So I need I12 J12 1.6 Closed K12: =SUM(B12:H12)...

Excel: VBA, dt1, documents and settings

dt1, documents and settings, test sets: rouble, Let filesearch pick up all the files and then when you loop through, check the date of the file dim s as string, dt as date dim dt1 as Date dt1 = date(year(date)-1,month(date),day(date)) If .Execute() 0 Then For i = 1 To .FoundFiles.Count...

Excel: VBA, command button, jpg file

command button, jpg file, textfile: Manesh, Firstly it would be more useful to see your text file than the spreadsheet, secondly have you used the Excel s built in converters? Try opening the text file from excel directly and use the wizard. Assuming you want to do this on a regular basis...

Excel: VBA Coding issue, max column, count column

max column, count column, macro code: Craig, see if this is what you want. Sub DAvg() IMPORTANT: you NEED to add a count column which extends the full data set Column a (1)- The numbering I put in the file Column b (2)- Date Column c (3)- Time Column D (4)- battery Column...

Excel: VBA cde to add textbox to userform, textboxes, cde

textboxes, cde, many thanks: HI Brad, It probably fails because there already is a control named MyTextbox8 on your form, adding another will cause an error indeed. Change this: Set x = Application.VBE.ActiveVBProject.VBComponents. _ Item( frm11 ).Designer. _...

Excel: VBA code for concatenate, b cells, concatenate

b cells, concatenate, b cell: Maria, Here is a general approach I would take. You will have to tweak it to get the output exactly as you want it (should A precede B, should there be a space between the two values, if B is empty, what then - right now it concatenates so only A would...

Excel: VBA code to delete #DIV/0!, vba macro, macro code

vba macro, macro code, div: Hi Jim, Like this: Sub RemoveDivZeroErrors() Dim oCell As Range For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors) If oCell.Value = CVErr(xlErrDiv0) Then oCell.ClearContents End...

Excel: VBA code: Inserting a row, max column, blank row

max column, blank row, seperate sheet: Sub Inserter() Set currentcell = Worksheets( Sheet1 ).Range( A1 ) Do While Not IsEmpty(currentcell) Set nextCell = currentcell.Offset(1, 0) If currentcell.Value 10 Then currentcell.EntireRow.Insert End If Set currentcell...

Excel: VBA in Excel, dynamic charts, excel

dynamic charts, excel: Ashley... If the reason for doing this is to update charts then you should look into Dynamic Charting . With dynamic charting all of your charts data (and axis) will update automatically as data is entered. It is fairly complex to explain here, so check...

Excel: VBA excel beep, audible beep, vba excel

audible beep, vba excel, target value: dan, I had put 6.1, but should have been 6.2. also is that inclusive - I assume it is. Revised code below. right click on the sheet tab, select view code, and paste in code like this in the resulting module: Private Sub Worksheet_Change(ByVal...

Excel: VBA excel beep, excel, validation

excel, validation: In order to emit only a beep you would need VBA. If you don t mind that it shows also a message you can use the menu Data- Validation. You can set there the validation criteria to be a decimal between 4 and 6.2, and show an error alert when the values are...

Excel: VBA for Moving Data Between Workbooks, state assumption, blank rows

state assumption, blank rows, blank row: Alex, given that this state assumption is met: Assumes that your data starts in range A1 and there are no blank rows or columns within the data. It shouldn t make any difference if it has multiple columns. I use currentregion. To see what...

Excel: VBA in personal.xls, input boxes, sheet names

input boxes, sheet names, blank sheets: Take a look at the code I provided, and notice the references to ThisWorkbook . ThisWorkbook refers to the workbook in which the code resides. So when you moved it to personal.xls, and ran it, the macro then operated on personal.xls, NOT the workbook from...

Excel: VBA script, bj2, ff2

bj2, ff2, fl1: Hi Solay, From the example you provided I am a bit unsure whether the sample result shown applies to the exactly to the list of data you provided. In particular, I am not sure why it shows the second row to be 2.BJ2.1-BJ3.1 STM1 1-2-3-6 Circuit: ...

Excel: VBA script, stm 1, cybercity 1

stm 1, cybercity 1, candos: Another way to copy it from the allexperts interface is to go here http://www.allexperts.com/expert.cgi?m=1&catID=1059&expID=2697 right now my answer is shown as the second entry down toward the bottom in the list of answered questions. If you click...

Excel: Video Clip, xls file, personal camera

xls file, personal camera, excel spreadsheet: You can insert objects into spreadsheets, so a video clip would be fine - it would have to be double clicked to play - it could be linked OR embedded (linked is the external version, embedded will be part of the sheet) - use Insert Object to get the object...

Excel: Visual Basic Excel, visual basic excel, text functions

visual basic excel, text functions, increments: Bob, My approach to this would be to use the CELL( filename ) function to return the name of the sheet. Then use the text functions (FIND, RIGHT, LEN, MID, etc) to extract the sheet name and ultimately the sheet number. After turning the extracted...

Excel: VLOOK UP, abhay mehta, format dialog

abhay mehta, format dialog, cell a1: Abhay, Your solution is almost correct. All you have to do is convert the date in Cell A1 to TEXT with the TEXT(A1,format) function inside the VLOOKUP reference. Replace your call to A1 with the TEXT() function. You can look up the appropriate...

Excel: VLOOKUP, inventory worksheet, laudatory comments

inventory worksheet, laudatory comments, warehouse location: Jon, Thanks for the laudatory comments and advertising . Hopefully this answer will live up to expectations. in column E of Inventory for example E2: =sumproduct(--( All Inventory !$A$1:$A$10000=$A2),--( All Inventory !$B$1:$B$10000=$D2), All...

Excel: VLOOKUP formula combinations, excel, match

excel, match: I am assuming in my answer that you have created the structure on your destination sheet in a way that all the information from a hotel is put together and in the same order. With that, you could use an additional column on the destination file (column A,...

Excel: VLookup, 4 digits, budget category

4 digits, budget category, project numbers: two options - one, select the column and do a search and replace - replace all spaces with NOTHING. Two, use the TRIM worksheet function = eg =trim( Aidan Heritage ) would return Aidan Heritage as it trims the leading and/or trailing spaces....

Excel: VLookup value, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: You can use ISERROR to return a True or False, or even bundle that with an IF and then put whatever you want in there. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Vlookup, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup function: Well, I can t figure it out either without seeing your sheet. Perhaps it would help you to know how the VLOOKUP function works: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up...

Excel: Vlookup with Codes and Rates, vlookup, countif

vlookup, countif, index function: see the other reply (!) - you could also use a countif =if(countif(SA2_Col1,m12)+countif(M12_col1,m12)=0,0,IF(OR(M12=65,51,54,55,58),(VLOOKUP(M12,SA2,2)),VLOOKUP(M12,Comm,2))) in this instance, it s only using the first column of the named ranges (so...

Excel: Vlookup with codes and rates, free microsoft excel, vlookup

free microsoft excel, vlookup, excel tips tricks: Sure, VLOOKUP can use text. You re just going to have to perform another VLOOKUP based on the city (I assume it s in the table) or use an IF statement to manually code it for the options. Let me know if you have any other questions or comments. Also,...

Excel: Vlookup multiple values, list of roman numerals, roman numeral

list of roman numerals, roman numeral, list of names: Are you looking to find the 2nd, 3rd, etc roman numeral for the same name or are you wanting to look up the combination of name & roman# and based on that combination, find the result from another column? (Both can be done... need to know which you re looking...

Excel: Vlookup/If statement, conditional formatting, cell references

conditional formatting, cell references, cell reference: Eldy, Without having any idea of what your data values are, how they are organized, or what this timeline looks like, it is quite impossible for me to write a formula for you. The way you wrote your question, 4 of the worksheets is a timeline , using...

Excel: vba checkboxes, speadsheet, value retail

speadsheet, value retail, listindex: When placing checkboxes etc on a worksheet, don t use the Forms toolbar. Instead, use the Control Toolbox to place your checkboxes on the sheet as OLEobjects. You can name these checkboxes e.g. CheckBox1 through CheckBox26. Then your vba code can control them...

Excel: vba checkboxes followup question, obj object, target range

obj object, target range, value retail: Tony, that wasn t my understanding of the requirement, so naturally my code doesn t do that. Hopefully this will work. The revised code is untested, but should point the way. Private Sub Combobox10_Click() Dim cb As MSForms.ComboBox Dim cbx As...

Excel: vba code to sort list, run time error, command button

run time error, command button, ascending order: Yes. And, assuming you want to know and really meant to ask HOW to do this and not just wanted to knoe if is there something, (in which case the answer is Yes) then: type On Error Resume Next above ActiveSheet.[B3:S150].Sort ActiveSheet.[B3:S150],...

Excel: vba combobox, listindex, combobox

listindex, combobox, msgbox: tony, If it is an activeX combobox from the control toolbox toolbar, then go into design mode, right click on the control and select view code. This should take you to the sheet module associated with the sheet on which the combobox is located. Put in...

Excel: vba - excel to pdf, cutepdf, pdf printer

cutepdf, pdf printer, default printer: Tony, yes there is ... sort of... In terms of printing to PDF .. you will need a PDF printer.. something like cutepdf is good but if you have money to spend then the adobe software is very good and gives you a lot more scope. The user will also need...

Excel: vba script, blank cells, data situation

blank cells, data situation, blank rows: Solay, that is a very general question. In your previous questions you only had data in column A. so for this situation of data only in column A and blank rows, it narrows down to cells in column A that are blank (actually blank - don t contain formulas...

Excel: vba script, pointe aux sables, stm 1

pointe aux sables, stm 1, ring2: Solay, Sorry - missed that detail. Hard to remember everything g Sub abc1() Dim lastrow As Long, i As Long Dim sh1 As Worksheet, sh2 As Worksheet Dim rw As Long Set sh1 = Worksheets( sheet1 ) Set sh2 = Worksheets( sheet2 ) rw = 1 lastrow = sh1.Cells(Rows.Count,...

Excel: vba script, unique records, sheet1

unique records, sheet1, cells: Hi Solay, If you record a macro whilst setting up the advanced filter you can see what VBA code is involved in doing so. After the filter has been applied you can use the specialcells method of the range object to extract the visible (and hence unique)...

Excel: vba script, ubound, sh1

ubound, sh1, ogilvy: Solay, You didn t say where you wanted the results, so I put them in column B next to the original data. Assumes the data is on Sheet1. Sub abc() Dim v As Variant, i As Long, j As Long Dim sh1 As Worksheet, cell As Range, s As String Set sh1 = Worksheets(...

Excel: vba script, iloc, rr2

iloc, rr2, instrrev: solay, Assuming the data starts in cell A1, this macro produced exactly the results you show: Sub EFG() Dim r As Range, cell As Range, s As String Dim iloc As Long Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) For Each cell In r If...

Excel: vlook up query, debits and credits, vlookup

debits and credits, vlookup, web site sales: I m not sure what you are actually trying to produce, but a lookup table doesn t sound like the right solution - this will only return one value at a time - it LOOKS as though a Pivot Table might be the way to go - this would consolidate data - so that you...

Excel: vlookup issue, free microsoft excel, cursor arrow

free microsoft excel, cursor arrow, excel tips tricks: Ignore my previous answer if you received it. It was wrong. I m sorry, but I don t know what the problem is. Cordially, Richard Rost 599CD Computer Training http://www.599cd.com P.S. Also, don t forget to visit my FREE Microsoft Excel Tips & Tricks...

Excel: vlookup - pivot table & VB Macro - returning all unique values, table vb, x cell

table vb, x cell, pivot table: Rick, I would check if it is already included in the string. Public Function FindSeries(TRange As Range, MatchWith As String) For Each cell In TRange If cell.Value = MatchWith Then if instr(1,x,cell.offset(0,1) & / ,vbTextcompare)...

Excel: Weekday functions, cell a1, b1

cell a1, b1, thursdays: In your example, you use 4/2/2009 (in cell A1), which is a Thursday. I ll assume that the value in A1 will always be a Tues or Thurs, and that you want B1 to be the next Tues or Thurs after A1, and C1 to be the next Tues or Thurs after B1, etc etc. In cell...

Excel: Work Date variations, k20, workdays

k20, workdays, test one: Chris, You can arrange is to that the difference is always positive. Just embed both versions of DATEDIF inside an IF statement, and test one date greater than the other. depending on the test, execute one of the other DATEDIFS. It will...

Excel: Workbook Links, cheque account, window option

cheque account, window option, spreadsheet: to create a link, open both sheets then type the = sign to start the formula, use the window option to switch to the other workbook, click on the worksheet and cell you want - link is then created automatically - it will look something like =[chequeAccount.xls]May2009!$B$12...

Excel: Workbook protection using VBA Code, code question, guidence

code question, guidence, using tools: The code I gave would go into the Thisworkbook, which is what s shown if you get to it via right-click the excel LOGO and select View Code. When you say it doesn t work, what are you seeing that verifies this? The code will run before you close it and it will...

Excel: Worksheet auto-population from Master Sheet redux, business intelligence application, exact record

business intelligence application, exact record, application report: Peter, I ve read this over (and over) and I don t quite see what you re driving at. The only NON-MACRO way to populate N additional worksheets, using data from a master worksheet (or from anywhere else) is to have EQUATIONS in the N worksheets...

Excel: Writing a formula that occurence of finacial values between two dates., excel, date

excel, date, sumproduct: You can try with a formula like: =SUMPRODUCT(--(A1:A42 =DATE(2008,4,1)),--(A1:A42 =DATE(2008,4,7)),(C1:C42=34)+(C1:C42=16.5)) I am assuming that you have the dates to compare on column A, and the values on column C, you can change the ranges on the formula...

Excel: two wosksheets with one common column--need to merge them, excel, vlookup

excel, vlookup: You can use the VLOOKUP function for this, something like this on cell B1: =VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,FALSE) You can adjust the range on sheet2 to reflect exactly the number of rows that you have on that sheet. Copy down the formula on column B for...

Excel: XY scatter chart: adding more than one line to a graph, statistical difference, column c

statistical difference, column c, add button: Once you have your xy scatter from A1:B3, click on the chart, use the Design tab from the Chart contextual menu, click Select Data from the Data group, click the Add button, give a series name, drag across C1:C6 for the X-values, clear the ={1} from the...

Excel: xl 2007 - conditional formatting - Icon set, absolute reference, b20

absolute reference, b20, checkbox: Icon sets work only for the cell(s) they re in, so you need a helper column. Say your values are in B1:B20. In cell C2, enter this: =IF(B1 B2,-1,IF(B1 B2,1,0)) and fill down to C20. Now use Icon sets on C2:C20, but include the checkbox to Show Icon only...

Excel: xlEdgeBottom Continuous on Change in Column Data, time programmer, b cells

time programmer, b cells, column data: Hello Dave, Mabye something like this: I use column B to check for the change in value Sub DrawBorder() Dim r As Range, cell As Range Set r = Range(Cells(2, B ), Cells(2, B ).End(xlDown)) For Each cell In r If cell.Value cell.Offset(1,...

Excel: xls to image, screen key, clipboard

screen key, clipboard, bearing: Not sure what you wanted to use the image for, as that could have a bearing on the way to do what you want, but the quickest way to get any screen into an image is via the print screen key which takes a copy of the screen to the clipboard - you can then paste...

Excel: 2 Users reading from and writing updates to a single spreadhseet, database spreadsheet, double bookings

database spreadsheet, double bookings, vlookup: Rich, Could you manage this with code - sure. this article provides sample code to see if a workbook is not available because it is open: http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open You...

Excel: 2003 excel macro is not working in 2007 excel, excel macros, excel macro

excel macros, excel macro, srv: WHAT line of code produces the error? You do not say. Workbooks.Open Filename:=srv_loc & src_rpt_name should never work on any version since you are trying to open the following workbook named: Workbooks(wbn).Worksheets( SETTINGS ).Range( B1 )Workbooks(wbn).Worksheets(...

Excel: Absolute formula in a large formula, negative signs, column table

negative signs, column table, absolute reference: Ann-Marie, This shouldn t be too difficult! It sounds like what you are talking about is the magnitude of the absolute values of the numbers. Its as if you want to erase the negative signs and look at the remaining numbers, as if all numbers...

Excel: Add Year to date total at a given month, cell b1, column c

cell b1, column c, account nr: Hanno, Do you still need help with this? if so, here it is! But actually, I have a number of questions before I can provide a valid answer. 1) do you want to do this for a whole bunch of account numbers at once? I suspect your summary...

Excel: Adding Values For Same Code, excel, sumif

excel, sumif: SUMIF can be a good solution for this. I suppose that you are using a separate column for the results, you may try with something like this on column C: =SUMIF($A$1:$A1,A1,$B$1:$B1) And copy it down. It will add the values from column B based on the previous...

Excel: Allexpertsq, excel, references

excel, references: The exact formula will depend on the layout and the complexity of the data. It can range from a simple reference: =Sheet2!A1 To a lookup formula: =VLOOKUP(A1,Sheet2!A1:B100,2,FALSE) Or other options like pivot tables. If you send more information on...

Excel: Assigned hours to pay codes, c cells, column c

c cells, column c, sumif: There are lots of solutions. In the example you supplied, you could use this formula in F11: =SUMIF(B$1:B$100,E11,C$1:C$100) and fill down. But if you don t yet have the values in E11:E17, you can create them by selecting column B, use advanced filter and...

Excel: AUTOPOPULATE DATA, column index, g300

column index, g300, a100: Assuming all the data is already in the workbook somewhere, say Sheet1!A1:G300, and your pick list refers to A1:A100 on sheet2, then in the cells to the right of the pick list you can enter this: =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)) and in the next column:...

Excel: Auto copy data from sheet 1 to sheet 2, client signs, auto copy

client signs, auto copy, assignment sheet: Sorry, not clear - I m pretty sure you need VBA using the change event of the worksheet, but I cannot follow the logic - if you can clarify I d be happy to help further - I m not clear what exists on sheet 2 =-= if it helps , you can email me at aidan.heritage@virgin.net...

Excel: Auto filter, custom option, blank line

custom option, blank line, spreadsheet: To be honest, I m not 100% sure - there do seem to be bugs in auto-filter - we have a sheet in the office that has long descriptions, and NONE of these are visible - we can only use the custom option from the autofilter - MIGHT be worth taking the filter off,...

Excel: Auto filtering, blank lines, help file

blank lines, help file, spreadsheet: Debbie, If your database is LARGE you might be running into the autofilter line number limit. AUTOFILTER in 2007 can only handle 10,000 different entires in a database. I am not sure what the limit for 2003 is, but if using 2003, you can check the...

Excel: Auto-Print Macro, vbcrlf, colons

vbcrlf, colons, ogilvy: Ricky, I assume you are using that dialog just to select a printer. If so, then use this instead: application.Dialogs(xlDialogPrinterSetup).Show if not, then I am not sure what you are trying to achieve, but when you click OK on the xlDialogPrint...

Excel: Auto Transfer of Data, minor typos, bk2

minor typos, bk2, sh2: Denny, Sub ABC() Dim bk1 As Workbook, sh1 As Worksheet, r1 As Range Dim bk2 As Workbook, sh2 As Worksheet, r2 As Range Set bk1 = Workbooks( Data Collection.xls ) change to the correct sheet Set sh1 = bk1.Worksheets( 1st PC.PRINT ) Set r1 = sh1.Range(...

Excel: Auto update a date after a certain time, incorrect dates, time format

incorrect dates, time format, retail store: Kurt, Use the NOW() function instead of TODAY(). NOW() includes both the date and the time. All you have to do is subtract 2:00 from NOW to create a date-time combo which is 2 hours earlier than the real date & time. Format NOW()-2:00 to only...

Excel: Autofilling cells, vlookup, data validation

vlookup, data validation, location column: Yes. Create a table of the info that lists each person and his relevant info. X Y Z Bill President 123-45-6789 Rob VP 9876-54-321 etc Assume this in X1:Z10 In B1, assuming the name was typed in A1 and appears...

Excel: Automate copying of records from the main table onto separate sheets, q2 q3, q1 q2

q2 q3, q1 q2, cell b3: CBETA, I misread your question initially and it didn t appear I had all the information I needed. I have since gone back and re-read it and I believe this is what you want. Sub ABC() Dim sh as Worksheet, r as Range Dim cell as Range, sh1 as Worksheet...

Excel: Automatic import of photos, layout location, automatic import

layout location, automatic import, cell a1: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Automatic Updating of Pivot Page Values in Excel, visual basic for applications, pivottables

visual basic for applications, pivottables, pivot tables: To do anything automatically in Excel, you use Visual Basic for Applications (vba) coding, sometimes called macros. If you re not familiar with vba, then you can t solve this problem automatically, and you should start by getting yourself a basic understanding...

Excel: Average IF, tyre, howto

tyre, howto, mixture: steve You can use an Average and an If function to get what you want but you will also need to use the function as an Array (ie finishing the function with Ctrl + Shift + Enter). Here is an example that you can look at that I just uploaded onto my website....

Excel: adding times, excel, times

excel, times: You can use the standard sum formula for this, something like: =A1+B1+C1 or =A1+B1*2+C1*3 And then format the result cell with the h:mm:ss. If this is not working for you, please let me know, and send details on what may be missing. Hope this helps,...

Excel: advanced filter with criteria area, cell a1, dsum

cell a1, dsum, h100: Hannah I usually don t refer people to the help menu but I think in this case it will help you with your criteria. Go to the online help and type in complex criteria, select the examples of complex criteria and I believe you will see the answers to your...

Excel: What If analysis with multiple sheets, vlookup function, screen directions

vlookup function, screen directions, whatif: Luiz, There are two ways in Excel off the top of my head to do this. 1. Use the vlookup function. Go to the Insert drop down menu, select Function. In the Select a Function section select vlookup. Follow the on screen directions from there....

Excel: automate pulling data from 1 xls to another, 6 cells, control tab

6 cells, control tab, paste link: Hi Dan, Does the weekly total always reside in the same cells in the first sheet? If so, then you could do this: - open both workbooks in Excel - select the weekly total cell and hit copy - hit control+tab to switch to the other sheet - select...

Excel: automatically increment numbers every time you open the same template, increment numbers, st petersburg florida

increment numbers, st petersburg florida, b6 c6: Lisa The procedure for making the invoice number increment is a bit more that a formula. It is a combination of formula and a Macro that copies and pastes the new invoice number. What I use is a formula that says something like this =IF(A6 B6,+C6+1)...

Excel: Basic scripting (if / elseif / case), data cells, slot 2

data cells, slot 2, slot one: Mrwho, I have a script that will update a timestamp every time data changes that you could use I think but you will need to set up a series of them. If you send me the worksheet I can have a look for you.. perhaps with some dummy data if you have anything...

Excel: how to block a column, format cells, protectin

format cells, protectin, checkbox: Select all cells (by clicking upper-left corner). Choose Format, Cells, Protectin and uncheck Locked, OK. Then select the column(s) to be protected, and use the same process to CHECK the Locked checkbox. Then protect the sheet (Tools, Protection, Protect...

Excel: button to sort data, lawn bowling club, followup questions

lawn bowling club, followup questions, macro recorder: Stephen, Ordinarily I don t offer macro advice on this website, but this one is so easy I ll give it a try! Don t ask too many followup questions, though, or I ll have to refer you to a different Expert. Basically, you PROGRAM the button by creating...

Excel: Calculating daily and weekly OT, excel timesheets, overtime rate

excel timesheets, overtime rate, red herring: shouldn t be too hard - I IMAGINE the 40 hours in a week is a red-herring, as to work more than 40 hours in a week you d have to have done more than 8 hours in at least one of the days - so it s simply a sum of the daily overtime rate? As it sounds like you...

Excel: Calculating interest, interest formula, calculating interest

interest formula, calculating interest, question thanks: Jeroen, You can use an IF statement to check the present value of the bank account. if it is greater than X, give yourself fixed amount Y. if it is less than X, give yourself the result of your computation. =IF(balance X,Y,your-equation-goes-here)...

Excel: Calculating if X, then copy Y formula., week 1, aidan

week 1, aidan, len: Having stared at the question a number of times it LOOKS to me as though you are simply after a count of the number of times 100% appears, and the sum of the values that appear next to it - iF as it reads, the value in D would be 100% without any other text,...

Excel: Calculation of Days, Time difference, excel, date

excel, date, format: The problem that you have there is with the date format. That spreadsheet is probably using the dates as mm/dd/yyyy, so when a day is greater than the first 12, it won t recognize it as a date because there are no months over 12, and the formulas associated...

Excel: Cell Changes in other spreadsheets., speadsheet, b24

speadsheet, b24, cell changes: Hi Ken How are you? Yes, you can do that. If, for instance, you want B24 of sheet2 reflect any change of A25 of Sheet1, please Type in B24 of Sheet2 =Sheet1!A25 On the other hand, if you want B24 of sheet2 of Workbook2 reflect any change of A25...

Excel: Cell color in macros, chip pearson, color index

chip pearson, color index, elseif: David, The is no property you can check. The easiest is to check the condition itself. If Range( A1 ) 100 then elseif Range( A1 ) = 100 then end if Chip Pearson has written a general routine that pulls the conditions out of a conditionally...

Excel: Cell Referencing, intersection operator, indirect address

intersection operator, indirect address, sheet names: Craig, There are several methods that Excel provides which allow you to compute an address. The best one is the aptly-named ADDRESS() function. By calculating the various parameters of the ADDRESS function you can create any address you want,...

Excel: Cell reference, current sheet, cell reference

current sheet, cell reference, cheers: Hi Trevor How are you? Please try the following: 1. Assume your data is in D6 of Sheet1 2. Type in DD57 of Projects Sheet1!D6 The intermediate cell has to contain a full address, i.e., the sheet and the cell which contain the data Cheers Adelaide...

Excel: Changing the colour of the cell which old more than a month, cell b2, format button

cell b2, format button, spread sheet: Hi Lilly, Suppose you want to color the cells that contain the Yes/No s and that those cells are in column B (B2, B3, B4, ...). Also assume the dates are in column A (A2, A3, A4, ...). 1. Select all cells with the y/n, starting with cell B2 2. Choose...

Excel: Changing Week Ranges to Months, excel, consolidate

excel, consolidate: My suggestion for this one is the following: use an additional column and write the corresponding month there. You could use a formula like this to get the first word on the cell in order to build the column: =LEFT(B1,SEARCH( ,B1)-1) With this column...

Excel: Chaning spreadsheet link from range reference, spreadsheet link, vba code

spreadsheet link, vba code, text path: Say that in your active workbook, active sheet, you have links to a closed, external workbook, for which the path/filename is in F64. E.g., the value in F64 might be: C: empBook1.xls Similarly, in F58, you have the path/filename of the external workbook...

Excel: Chart question (actual vs. budget), e mail address, pop up menu

e mail address, pop up menu, help file: Mart, This is easy to do so you don t need a sample. Besides, use Experts don t get to see your e-mail address so we can t send you anything, anyway. All you need to do is create a chart of your data, bar or line. Then select one of the data...

Excel: Check if a cell is highlight in yellow, yellow cells, conditional formatting

yellow cells, conditional formatting, colorindex: Can you clarify what form of highlighting we are talking about - IF it s the cell format, then the use of cell.interior.colorindex will pick this up. If it s conditional formatting, then you would have to test the condition itself within the macro. If...

Excel: Check if a cell is highlight in yellow, colored cells, yellow cells

colored cells, yellow cells, cell a1: Sudha What you want is difficult because the condition is within the cell that you want to change. The formula in the cell would have to say something like this===Assume the formul is in cell A1--- If A1 is yellow then add 100 to A1. As you can see the...

Excel: CheckBoxes selection->Hide, target range, case range

target range, case range, true range: Marwan, If you have checkboxes from the Control Toolbox toolbar (ActiveX) checkboxes, then go into design mode and right click on them. Select format control and then under the Properties tab, select Move and Size with cells. Forms controls checkboxes...

Excel: Cleaning spreadsheet of addresses, consecutive rows, isna

consecutive rows, isna, error check: Angus, This is a little tricky (as you know) but I think I see a method. You ll need to add some columns for a set of logical equations. The first thing to do is add a column called NUM which you will use to number the database from 1 to 5,000....

Excel: Clear contents in specific cells across multiple workbooks, spath, sname

spath, sname, command button: Carol, You can try this. since you are saving in the middle of the loop, this can disrupt the behavior of the DIR command - but try it and see if it works. If not, post back and I can redesign it to get and array of filenames first and then process the...

Excel: COUNTIF, judicious placement, sumproduct

judicious placement, sumproduct, m2500: Simon, If you have Excel 2007, look at the COUNTIFS() function. It does exactly this. But if you have Excel 2003, you can use the advanced (and undocumented) form of SUMPRODUCT() to do this. There is an excellent (though containing too...

Excel: Code for question's response, excel sheet, aidan

excel sheet, aidan, spreadsheet: I m not at all clear what you want to do here - it sounds like you might have a series of questions and responses in a spreadsheet and want to count the instances of both values? Can you clarify - if it helps, my email is aidan.heritage@virgin.net - you can...

Excel: Color fill tow cells from one value., spreadsheet cell, logical formula

spreadsheet cell, logical formula, highest score: Ron, Evidently you have applied Conditional Formatting [CF] to the score cell to make the color change occur. You do essentially the same thing to make the name change... you can actually make the entire row change color if you want. You ve...

Excel: Color a text cell from data of a value cell., cell b5, golf score

cell b5, golf score, odd question: You say color and also text - and it s not totally clear what needs to happen - my GUESS is that you need a formula to look up the winner of the game, so perhaps some form of lookup function - or that you need to use the SAME formula you have already to highlight...

Excel: Colored rows, tool cribs, conditional formatting

tool cribs, conditional formatting, odd text: Kendra, There is some odd text before your question: is this directed to me??? , or is there an error on your part? It may be something you accidentally copied from elsewhere that you didn t mean to include. The odd text I am referring to is: ...

Excel: Column Graphs In Excel 2003, column graphs, column graph

column graphs, column graph, chart dialog: Jarrod, I can t give you a complete graphing tutorial on this website, but I can get you started. If I knew HOW you were failing or what was going wrong, I could correct the specific problem. If you are using Excel 2007, the graphing routines have been...

Excel: Closest match, column number, cell reference

column number, cell reference, mode bit: Petac, Here s an answer which gives the closest match from the table, which is LESS THAN the number you re looking for. If you want the number that is literally closest to, I don t have an answer yet: but write back and I ll look some more....

Excel: Combination of numbers, mathematical algorithm, number ie

mathematical algorithm, number ie, ogilvy: Cat, Not only isn t there an easy way, there isn t even a fairly difficult way. There is an impossible way, however! The impossible way involves writing out every possible combination of numbers and adding them up.... which isn t really the solution...

Excel: Two Combo box hyperlink, proper punctuation, page2 page3

proper punctuation, page2 page3, sheet names: KK, Your question is a bit self-contradictory, for in the first part you say that combo box 1 contains WEBSITE links and in he second part you say (or imply) that Combo box 1 contains SHEET NAMES. But in any event, my answer should work either...

Excel: Compare then Copy, vlookup, column c

vlookup, column c, e10: it sounds like a vlookup, which is a function that looks up a value in one column and returns information from another - so =vlookup(a1,$A$2:$E$200,3,false) would return the value from column C (the third column in the range A2:E200) on the row that...

Excel: Compare information between 2 sheets and copy if a match, countif, ogilvy

countif, ogilvy, havoc: Tim, The code worked perfectly for me. I don t see anything that would make it not work; behave as you describe. I set up two worksheets as you describe in the beginning of your post and then ran the code against those sheets and as I say, it did exactly...

Excel: Comparing Workbooks, conditional formatting, excel worksheet

conditional formatting, excel worksheet, code reference: Anthony, Your predecessor has left you a huge intractable MESS. Whatever you do is going to be tedious and time consuming. What I would do is this, but keep in mind that I can t see ANY of the mess, and so this might not work well. But if it...

Excel: Comparison in excel, cell b1, cell a1

cell b1, cell a1, continuous manner: RAVI You can use a formula like this =IF(RANK(A1,$A1:$F1,2)=1, L1 ,+IF(RANK(A1,$A1:$F1,2)=2,+ L2 ,+IF(RANK(A1,$A1:$F1,2)=3,+ L3 ,+IF(RANK(A1,$A1:$F1,2)=4,+ L4 ,+IF(RANK(A1,$A1:$F1,2)=5,+ L5 ,+IF(RANK(A1,$A1:$F1,2)=6,+ L6 , )))))) This formula compares...

Excel: Complex Mail-Merge, assignements, excel workbook

assignements, excel workbook, progess: VBA could be used to pick up specific changes and then generate documents based on this - OR if there is a field containing date complete then that date could be used to control the merge. My email is aidan.heritage@virgin.net - if you can email me a sample...

Excel: Complicated Lookup, vlookup tutorial, cell a1

vlookup tutorial, cell a1, excel function: Thomas, All you need to do is apply my favorite function, VLOOKUP(). For each value you want returned, you need one VLOOKUP(). You will see how to use VLOOKUP when you read the Excel function guide. I ve also included a short VLOOKUP tutorial....

Excel: Conditional Formatting, conditional formatting, comparison check

conditional formatting, comparison check, blank cell: Janet, The formulas used for conditional formatting (in the conditional formatting dialog box itself) MUST be logical equations whose answer can ONLY be TRUE or FALSE. The equation you gave =IF(TODAY()-E106 30,TODAY()-F106, ) can...

Excel: Conditional Formatting, conditional formatting, format button

conditional formatting, format button, complete post: Julie, If I select the cell and do Format= Conditional formatting, then the dialog displays Cell Value Is and a text box. I would type Complete in the text box then click the format button, select the formatting to apply (color green). then...

Excel: Conditional Formatting More Than One Cell, cell c5, conditional formatting

cell c5, conditional formatting, c cells: Sure. Select ALL the cells in the NEW column, apply the conditional formats you want using the active cell as the sample . So if you say for the first CF =AND(C1 A1,C1 B1) and give it the color you want, this will apply it for ALL the rows selected. To check,...

Excel: Conditional formatting, conditional formatting, cell b5

conditional formatting, cell b5, exact details: John, Well its not quite THAT simple, but its not too difficult either. However, you said CONDITIONAL FORMAT and then your question has nothing to do with color changes or appearance. You seem to want an IF statement instead! So here...

Excel: Conditional formatting question, cell d1, cell d2

cell d1, cell d2, royal mail: You only need to check if cells contain WEU, not if the VLOOKUP(...) returns WEU. You mention D1 -- is D1 the ONLY cell that would be blank? And highlight WHAT cell as red? I need a bit more information. Where is the VLOOKUP formula... D1? Column D? Does...

Excel: Conditional formatting question, cell d1, question question

cell d1, question question, single quote: Brent, =or(And(len(trim($A$1)) 0,len(trim($D$1))=0),And(len(trim($D$1)) 0,Not(isText($D$1)))) worked for me. The only thing you have to be careful of is that D1 is not formatted as Text or you somehow otherwise get a number in D1, but it is stored as...

Excel: Conditional formatting for reminders., conditional formatting, blank cells

conditional formatting, blank cells, ogilvy: Rams, I think you have your comparisons backwards. If I followed you general pattern, I would use: =IF(B2= , ,IF(B2 TODAY(), COMPLETED ,IF(B2=TODAY(), CRITICAL ,IF(B2 =TODAY()+1, TOP URGENT ,IF(B2 =TODAY()+3, URGENT ,IF(B2 =TODAY()+7, IMMEDIATE ,...

Excel: Connecting calculations to a cell, pivot table, header row

pivot table, header row, pivottable: I suspected that that may be the case - I THINK that a pivot table is probably going to be the solution you need - this is data, pivot table and chart report (unless you are on office 2007) as this would PROBABLY do the work for you by dropping data in place...

Excel: Connecting two excel columns, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Consolidating Multiple Company Data, general ledger account, nancy nancy

general ledger account, nancy nancy, microsoft query: Nancy, I believe that you need to use a multiple-criterion lookup. It would be best if you d advise me as to what version of Excel you use, so I could pick the correct function. The possibilities are SUMIFS, SUMPRODUCT, and DSUM. Bill ...

Excel: Converting numbers in one cell into a table, ogilvy, delimited

ogilvy, delimited, separator: Mert, So all the data is in cell A1, then: sub convertData() Dim r as Range, v as Variant Dim rw as Long, col as Long, i as Long set r = Range( A1 ) v = split(r.value, , ) rw = 2 col = 1 for i = lbound(v) to Ubound(v) cells(rw,col) = v(i)...

Excel: Converting text to number format, tithes, r13

tithes, r13, r11: James, Try this approach: Private Sub Input_figuresin1_OK_Click() Dim r As Range, res As Variant, ipos As Long Dim r1 As Range, cc As Currency, i As Long Dim s(1 To 15) As String, ss As String s(2) = Input_figuresin1_Tithes s(3) = Input_figuresin1_Project...

Excel: Converting text to number format, format cells, pound symbol

format cells, pound symbol, textboxes: James, I don t know how exactly you formatted to currency so first try this. 1. Select the cell you want to show in pounds 2. Select the Format drop down menu 3. Select Cells... This will show a pop up menu labled Format Cells 4. Select...

Excel: Copy data to new sheet if specific criteria met, autofilter, workloads

autofilter, workloads, several thousand: Do you want this copied the moment he enters his name? What if he spells his name a little differently than the name on the tab? What kind of error do you want? If they delete their name, how is it determined which row on their sheet should be deleted.. won...

Excel: Copy every other row and randbetween function, chemistry classes, excel sheet

chemistry classes, excel sheet, random numbers: Put the following formula in a cell and copy and paste as far down as you need. =MOD(CELL( row ,A1),2) Then sort and then copy. Maybe this will help. If not then you will need to write a macro. Concerning the RANDBETWEEN question, I don t think...

Excel: Copy & Paste Data in Transpose Mode (follow Up), empty cells, paste data

empty cells, paste data, data layout: Try this (untested): Sub Combine3() Dim Area As Range, Files As Variant Files = Application.GetOpenFilename(MultiSelect:=True) Application.DisplayAlerts = False On Error Resume Next For i = LBound(Files) To UBound(Files) Workbooks.Open Files(i),...

Excel: Copy and paste, excel, references

excel, references: I think I understand. You may try using absolute references in the formulas that you want to move. There is a good chapter about them on the Excel help, but basically you just have to put a dollar sign in front of the row or column that you want to fix....

Excel: Copy rows based on Column value, value question, select sort

value question, select sort, pivot tables: To do this with a pivot table first make sure you have a heading for each column, otherwise Excel will use the first row as the headers. To do this with a pivot table: 1. Highlight all of the columns that have data you want to transfer. They must be...

Excel: To Copy Sheet In A New Book, active one, adnan

active one, adnan, tnaf: Adnan, As far as I know using code to copy sheets they don t need to be visible. In addition the command to copy to a new workbook by default will make the new workbook the active one and as it will only contain one worksheet called TNAF that will be the...

Excel: To Copy Sheet In A New Book, copy windows, dear bill

copy windows, dear bill, adnan: Sub Macro2() Dim ThisWB as Workbook Set ThisWB = ActiveWorkbook Sheets( TNAF ).Visible = True Sheets( TNAF ).Select Sheets( TNAF ).Copy Windows( Book2 ).Activate Sheets( TNAF ).Select ActiveWindow.SelectedSheets.Visible =...

Excel: To Copy Sheet in a New Workbook, false application, code question

false application, code question, ammendments: Sorry for my failure to answer the full question - it s rather too easy sometimes to see what you think is the problem and answer that. You will see that I ve applied the ActiveWorkbook to a variable - this makes it possible to reference the book...

Excel: To Copy Sheet in a New Workbook, false application, nomination form

false application, nomination form, copy windows: sorry - that should have been Sub Macro1() Dim MyBk As Workbook Set MyBk = ActiveWorkbook Application.ScreenUpdating = False Sheets( TNAF ).Visible = True Sheets( TNAF ).Copy MyBk.Activate Sheets( TNAF ).Visible = False Application.ScreenUpdating...

Excel: CopyFilter, run time error, subscript out of range

run time error, subscript out of range, filter selections: Phil, When I have run the code, I get the following message: Run-time error 9 : You have said a lot, but from what I can tell, your question concerns the above error: the only thing I can see in that code that would cause subscript out of range...

Excel: Copying Conditional Formatting, conditional formatting, cell reference

conditional formatting, cell reference, a1: You didn t specify the formatting conditions you had used, but basically you are going to need a mixed cell reference - I m assuming you have something like =min(a1:f1) this needs to be =min($a1:$f1) which locks the columns in place but allows...

Excel: Copying rows, current sheet, a14

current sheet, a14, mail: copying rows Not sure how much of this you have already done - it would be easier to apply the code to a workbook and mail that to you - if you can email me a sample file - aidan.heritage@virgin.net - I can build something for you. NOT certain that it needs...

Excel: Copying one worksheet into another worksheet, code breaks, oddity

code breaks, oddity, quirk: A little quirk or oddity, in my opinion, is that you can t select the cells and the sheet the cells are in with the same line of code. Seems logical that you could do it the way you are trying to do it but it won t work. With the way you are doing this,...

Excel: Copying through worksheets, labels, january february

labels, january february, sumif function: Example: you have a sheet named January , a sheet named February , etc. On each sheet, col A has labels and col B has values. On a separate sheet, you want to perform a SUMIF for each of these monthly sheets: you want the sum of all values in col B for which...

Excel: Count, effor, excel help

effor, excel help, raghu: Raghu, you need an IF statement.. to compile the statement for you I would need to know the full criteria.. what would you like displayed if the number of steps is zero or more than 30 etc? Alternatively you can use the excel Help to find the syntax for...

Excel: Count Dates, countif, relative reference

countif, relative reference, dollar signs: the result three equates to the number of unique dates presumably? There is no inbuilt function to do this directly - options are (1) a pivot table which would group the entries together. (2) use a formula to show a reducing count, and then sum those values...

Excel: Count the number of times an entry appears within a date range, database range, sumproduct

database range, sumproduct, correct response: Carolyn, Okay, I have received your workbook. Now I need some explanation. Please tell me which sheets to look at, which columns you are trying to operate on, and where your criteria ranges and DCOUNT functions are. Your guidance will save me lots...

Excel: Count a occurence of a word in 'n' number of columns, external criterion, excel function

external criterion, excel function, word bill: Jeff, If you wish to do a COUNT on a database using multiple criteria, the [2007-only] function COUNTIFS() is the way to go! If you don t have Excel 2007, you can use the advanced form of SUMPRODUCT() to count records selected by more than one...

Excel: Count specific dates in cell range for current month, computer clock, running errands

computer clock, running errands, spread sheet: Ron: I apologize for the slightly slow response (I usually like to reply within a few hours; however, I was out of the office today running errands and I just got home). You were VERY close with your formula - you definitely understood what needed to...

Excel: Counting and comparing date, cell b1, countif

cell b1, countif, brilliant works: Hi John, Yes you can do that, by using a relatively unknow function called SUMPRODUCT: This example sums cells B1,D1,F1,H1: =SUMPRODUCT((A1:J1)*(MOD(COLUMN(A1:J1),2)=0)) To sum A1,C1,E1,F1,G1: =SUMPRODUCT((A1:J1)*(MOD(COLUMN(A1:J1),2)=1)...

Excel: To Create Hyperlink, myfolder, program titles

myfolder, program titles, broucher: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Create a horizontal list from vertical array, vertical array, horizontal array

vertical array, horizontal array, order of succession: Dilip, Nice of you to attach an image, but you didn t make any reference to it in your text. There are sets of numbers all over the place, vertical and in tables, so I don t know which numbers want to end up where. However, there are several steps...

Excel: Create Outlook 2003 appointment from EXCEL, dim myitem, vba excel

dim myitem, vba excel, subject sheets: The macro copy looks fine - he hasn t referenced activecell.offset etc because that was my suggestion as it seems from your question that you don t always want to use A1, A2 etc. Is it possible to see your sheet - then I can run it against my copy of office...

Excel: Creating a special column, sum formula, richard florida

sum formula, richard florida, cells: Philip I am confused-- I am not sure if we are picking an average of six or 14 or???? Here are some suggestions--Excel has an average formula that looks like this =AVERAGE() So if you want to average cells A1 to A55 the formula would look like this....

Excel: CSV File saving, free microsoft excel, csv file

free microsoft excel, csv file, excel tips tricks: If you format the column as TEXT (right-click, format, TEXT) then export as CSV, it will retain the zeros. I just tested it with Excel 2007 and it worked fine for me. Let me know if you have any other questions or comments. Also, be sure to visit my...

Excel: Cummulative sales, hi friends, year 1

hi friends, year 1, several ways: There are several ways to do this. To me the easiest is to pull it apart and let Excle do the work. First I d set up a grid showing the years and the related production and sales like below: Year 1 2 3 4 1 ...

Excel: Cumulative totals in excel, random number generator, cricket game

random number generator, cricket game, array formula: Robert, Enter this in C1. Enter it with Ctrl+Shift+enter rather than enter since it is an array formula. It worked fine for me: =IF(B1= , ,SUMPRODUCT(IF(ISERROR(MID(B1,ROW(INDIRECT( 1: &LEN(B1))),1)*1),0,MID(B1,ROW(INDIRECT( 1: &LEN(B1))),1)*1)))...

Excel: Current Data for A Stock Graph, stock graph, dynamic name

stock graph, dynamic name, total value: Dave, This can be done but it may not be easy if you aren t very experienced with Excel. Briefly, the way to do it is to create a DYNAMIC NAME for your data range. The NAME you create is based upon an OFFSET function, instead of specifying the...

Excel: Custom Formatting, serial number, remainder

serial number, remainder, dddd: If it s really a date and FORMATTED as you said, then no, it can t be done, since what s REALLY in the cell is a serial number, not Saturday May 16 2009 . If what was in the cell were text, and Saturday May 16 2009 , then you can select the Saturday, make...

Excel: How to change color of "found" cell when using find/replace, bottom section

bottom section: Not exactly -- you can use the Find All (without replacing yet) and in the resulting bottom section of the results you can press ctrl/A which will select all the found items, then you can close the find dialog and they will still be selected; Then you can...

Excel: changing times base on drop down option, current time zone, pacific standard time

current time zone, pacific standard time, eastern standard time: Marco for you first time in each day, you can put in a base number - say zero and then have it sum in the value in a single cell that will specify the amount of hours added or subtracted from that base time. You dropdown would show the time zone, then...

Excel: check for highlight cells, target address, left hand side

target address, left hand side, msexcel: Hi Sudha How are you? I wonder how familiar you are with VBA. Anyway, please have a look at 1. Run the VBA editor from the Excel 1.1. Run the Excel 1.2. Press the office button 1.3. Press Excel Options 1.4. Select Popular from the menu on the left-hand...

Excel: checkbox control, excel objects, control question

excel objects, control question, target: If you don t want the code to run when you click one of the checkboxes, what event do you want to use to trigger the enabling/disabling? And what would make the first checkbox become enabled or disabled and what would make the 2nd checkbox become enabled or...

Excel: checkbox control, excel objects, sheet1

excel objects, sheet1, excel sheet: I ll assume that when you say tick box, you mean a CheckBox that s been placed on Sheet1. When placing CheckBoxes, you should never use the Forms toolbar. You should choose from the main Excel menu View, Toolbars, Control Toolbox. Then from the Control Toolbox,...

Excel: choosing specific cells, a6 a8, indirect address

a6 a8, indirect address, initial numbers: Maki, When I come across a problem like this, the first thing I check is the help file. It will clearly tell you, in a way better than I can, how to use any function in Excel. Since you are new to Excel, you should learn to use this incredibly valuable...

Excel: choosing specific cells, excel, filter

excel, filter: You can use the following formula on C2: =ISEVEN(ROW()) Copy down the formula all the way to C1000, go to menu Data- Filter- Autofilter and filter for TRUE values. Select the full filtered range of data on column B starting on B2, and enter this formula...

Excel: code for copy & paste multiple Tables from diff word files to excel at different sheets, excel vb, word application

excel vb, word application, word macros: the selection of entire table by table number means the number of rows doesn t matter, BUT it s not clear if the table would always be the only table in the file - it s easy enough to modify the code to open one file, take what it wants, then close that file...

Excel: code for different txt files contents to copy & paste it at different sheets in a workbook, spath, cstr

spath, cstr, chdir: Bhavana, I expect this is what you d need: Sub ImportTextFiles() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = c:windows emp ChDrive sPath ChDir sPath vFilename...

Excel: code to finish a macro, vba macro, message boxes

vba macro, message boxes, macro code: Jim, Dim oCell As Range Dim r as Range on Error Resume Next set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,xlErrors) On Error goto 0 if not r is nothing then for each oCell in r If oCell.Value = CVErr(xlErrDiv0) Then oCell.ClearContents...

Excel: colorcoding data in spreadsheet, conditional formatting, format cells

conditional formatting, format cells, menu choices: sam, I thought I gave you a formula to get the 1, 2, and 3rd values. I guess I don t understand what you want to do. Send me a sample workbook explaining exactly what you want and I will try to show you how. twogilvy@msn.com -- Regards,...

Excel: column formatting into one page, excel, transpose

excel, transpose: You could use a formula like this on B2: =OFFSET($A$1,47*(COLUMN()-1)+ROW()-1,0) And copy it to the range B2:O47. It will bring you the first 705 numbers, but that is all you can fit on that range. If you want to tweak the formula to a different number...

Excel: combining two macros in MS Excel 2007, false selection, copy windows

false selection, copy windows, copy application: not clear where you want to add it but (1) copy the code and paste it into the area you want - thus extending the first macro (2) type Macro1 where you want to call macro 1 - on its own line so something like activewindow.close macro1 end sub ...

Excel: conditional formatting??, conditional formatting, vlookup

conditional formatting, vlookup, value values: Thomas Nested If statements only go up to 7 Nested if statements. Your best option would be to put the information into another sheet and use a vlookup to gather the correct information. Have a look at this link for Vlookup http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446...

Excel: conditional formatting, conditional formatting, ogilvy

conditional formatting, ogilvy, value values: Thomas, Your description has some ambiguity. You say if the value is 45 assign a value of 1 and assign a value of 2. Similar for each of the boundary values. this function does 0 - 44 1 45 - 89 2 90 - 134 3 and so forth =IF(A1= , ,IF(A1=0,1,IF(A1=360,8,TRUNC(A1/45)+1)))...

Excel: conditional formatting, excel, vlookup

excel, vlookup: You may use the VLOOKUP formula here. Check the help for the details, but the basic usage is that you will build a table with the ranges, and then use a formula like: =VLOOKUP(A1,Sheet2!A1:B8,2) It is a simpler formula than nested IFs, and easier to maintain....

Excel: conditional formatting, conditional formatting, value values

conditional formatting, value values, aidan: It s not conditional formatting, it s simply an IF statement - or a lookup - I d probably do it with a vlookup table, but with if statements =if(a1 45,1,if(a1 90,2,if(etc In your example, you had the values as both top of one range and bottom of the...

Excel: conditional formatting?, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: conditional macro, operator inputs, excel sheet

operator inputs, excel sheet, myprint: Hoi Katrien, Volgens mij moet het zo zijn: Sub MyPrint() If Range( AF2 ).Value = DEFECT Then MsgBox Vul in reden minor!!!! Exit Sub End If Save ActivePrinter Name curPrinter = Application.ActivePrinter ...

Excel: control tick box from VBA, excel objects, excel sheet

excel objects, excel sheet, microsoft excel: Hi Lukasz, I m not sure what why you don t want to use the Private Sub CheckBox1_Click() event. This is virtually the only event that can trap a checkbox Click event. However, this can be done at the workbook event level or the worksheet event level, and...

Excel: copy cells in aspecified form, even rows, odd rows

even rows, odd rows, autofilter: Ahmed, Still need an answer? You can TRY this, though I can t say for absolute sure if it will work. Add a new column to your data, on the left side. Fill this with alternating 0 & 1 values, so the 1 s are in the even-numbered rows. Apply...

Excel: copy and paste multiple worksheets into one, paste routine, workbook sheets

paste routine, workbook sheets, autofit: Sub CopyPaste() CopyPaste Macro this macro copies tables from different worksheets into a single (new) worksheet of the same workbook. for i=1 to 4579 Sheets( Table & i).Select Range( A1:G9 ).Copy Sheets( Sheet1 ).Paste...

Excel: copy record from one sheet to another, macro code, volunteer

macro code, volunteer: Well, that all depends on HOW the particular record is determined or WHAT determines the particular record ,and also WHERE in the other sheet the particular record is to be copied. Since you don t give me any useful or enough information then sorry I...

Excel: copying & pasting a specific range, chg, text numbers

chg, text numbers, petc: ANAND I hope I am understanding your question-- I would handle the problem with two formulas First in one column I would put the formula =IF(AND(A4 0,B4 0),A4, ) This would bring over the value in column A which is the text value for example...

Excel: countif formula?, excel expert, blank cell

excel expert, blank cell, jennifer jennifer: Jennifer You can use the sumproduct function using two extra cells for your dates (easier than constantly chaning the formula with date changes) Insert todays date =Today() in a blank cell (we will say J1), then below type in =Today()-184 and for...

Excel: Not counting cells that have a formula in them, relevant formula, blank cell

relevant formula, blank cell, incorect: Hi John, So the cells that pull in values from a blank cell show zero, correct? If so, you could just subtract the count of zero cells from the total count in each relevant formula: =COUNTIF(N3:N41, = &TODAY())+(COUNTIF(N3:N41, R ))+(COUNTIF(N3:N41,...

Excel: creating macros, rental cottages, excel worksheet

rental cottages, excel worksheet, peltier: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: creating unique id, receipt number, arvind

receipt number, arvind, ogilvy: Arvind, This is a somewhat difficult problem, which I believe can best be solved with a macro. Unfortunately I don t offer macro.VBA advice on this site, so I suggest you re-direct your question (if you still need an answer) to Tom Ogilvy or one of...

Excel: creating worksheet, information transfer, peltier

information transfer, peltier, atlantic city new jersey: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: cumulative addition, cursor keys, aidan

cursor keys, aidan, notepad: You won t be able to do this in notepad, so there are two options - 1, copy directly to excel and then use the data, text to columns option to split the data out. Two, put it into Word first, then press Ctrl shift F8 to turn on column edit - use the cursor...

Excel: Daily time Extract, countif, exception report

countif, exception report, sheet1: pankaj say you had a master list of people in column A of Sheet2 say your extraction has the names in column B of sheet1 in column B of sheet2 you could put B2: =if(countif(Sheet1!B:B,A2)=0, Missing , ) then drag fill that down column B....

Excel: Data in excel is only recognaized as a number when cell is activated, format cells, cell reference

format cells, cell reference, staff id: Iman, There are many things that could be wrong. Your number might be TEXT. Perhaps you are trying to find TEXT in a column of numbers. To test if the number is text or not, write an =ISTEXT() function in a cell somewhere, using the suspect...

Excel: Data Validation, data validation, destination area

data validation, destination area, vlookup: Gary, I just tried this and it works perfectly. 1) Create a column of the 15 numbers. My example uses C4:C18 2) Create the destination area where the numbers will be selected from the drop-down. My example uses E4:E18. 3) Create a column...

Excel: Data Validation, validation criteria, data validation

validation criteria, data validation, conditional formatting: Dolores, What you really want is to use the Whole Number Only model of data validation. You don t need to get so fancy and put an ISNUMBER equation in the dialog box. data validation has a Whole Number Only option that allows you to limit the range...

Excel: Data validation list, target value, target address

target value, target address, data validation: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Database, address info, thanks in advance

address info, thanks in advance, cells: Rosa, A messy database can be a big problem. There are ways to avoid this and give your users guided info as the move from cell to cell. But that s not what your question is about. It seems that you now have EQUATIONS in cells where you...

Excel: Date Calculation Code does not work, set target, target parent

set target, target parent, target value: I don t see an easy way to add it to the Worksheet_Change code because it would be recursive -- that it, it would run in a loop if each time it runs once you replace all / with / because EACH change of / would rerun the macro which wants to replace all...

Excel: If Between Date Range Return a certain Value, cell d2, spread sheet

cell d2, spread sheet, u18: Hi Teresa, Create a separate table like this (assume this table is on Sheet2, range A2 to B11: 07/31/2001 1 07/31/2002 2 07/31/2003 3 07/31/2004 4 07/31/2005 5 07/31/2006 6 07/31/2007 7 07/31/2008 8 07/31/2009 9 07/31/2010 10 (the numbers...

Excel: Date & Time Calculation Question., time calculation, negative numbers

time calculation, negative numbers, minus sign: Nikmat Subtract the two dates with a minus sign, just like you would subtract any two numbers. The answer you get is the NUMBER OF DAYS between the dates. Format the result as a GENERAL number to avoid seeing a funny result like 00-01-10. You MUST...

Excel: Dates count, error correction, aidan

error correction, aidan, cells: question not totally clear - is this to be one cell counting dates in other cells, or a count of the number of times a cell is changed - the latter needs VBA to achieve, and although possible is not something I d usually recommend (error correction counts...

Excel: Days and Times spanning midnight, time time time, whole number

time time time, whole number, decimal point: Alan, The key to understanding how to calculate with dates and times is to understand the structure of the SERIAL NUMBER, which is the format in which dates and times are stored. This is actually easy to understand. The serial number is just...

Excel: Delete rows based on comboBox and offset values, excel userform, portfolio database

excel userform, portfolio database, developing a portfolio: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Dependent Filtering, custom filter, filter feature

custom filter, filter feature, information organization: http://www.mcgimpsey.com/excel/lookuppics.html may give some ideas for the image - as to the filter, VBA would do it BUT the user would have to enable that - so POSSIBLY some form of array filter MIGHT be the way to go? see http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls...

Excel: Don't know how to configure formula to calculate, column headings, jan dec

column headings, jan dec, taxable income: Jennifer, I don t see a workbook from you, but your question is clear enough without it. What you need is a way to select all the records that have SALE in the Purchase or Sale column and add the taxable amount for those together, and to...

Excel: Drop Down Boxes Disappearance, data validation, drop down boxes

data validation, drop down boxes, first timer: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Drop down boxes & check boxes, drop down boxes, check boxes

drop down boxes, check boxes, constants: The results ARE constants so I don t understand your question. And you don t give me very much information. Politely, please understand and remember that I can not see what you see, know what you know about your data, know how your data is arranged, know...

Excel: Drop-Down Macro, target value, mail phone

target value, mail phone, fax mail: I did make the comment that you might need to extend this to add the right hand side as well! Target.Value =left(oldval,instr(oldval,newval)-1) as you can see pulls in the LEFT hand edge - so Target.Value =left(oldval,instr(oldval,newval)-1) & right(oldval,len(oldval)-len(left(oldval,instr(oldval,newval)-1))-len(newval))...

Excel: Drop Down menus, menu boxes, original costs

menu boxes, original costs, drop down menus: David, Instead of copying the Data worksheet to another workbook intact, try doing a COPY on the sheet, and then a PASTE-SPECIAL VALUES to a new sheet in a new workbook. This process eliminates all the equations in the pasted copy, and makes this copy...

Excel: Due Amounts by week, sumif, excel help

sumif, excel help, excel sheet: Chris, Of course it is possible! But I think I have confused you (because I missed something). I read your equation as a SUMIFS and it was clearly a SUMIF. That S makes a big difference. Maybe you missed the same thing when you read my reply. ...

Excel: Dynamic range for Spin button, bb8, time sep

bb8, time sep, max values: Sep, Sorry, but I have been unable to assign anything but a given number to the min and max values for the spin button. I d hoped that you could use an equation, like MAX(of-column) for the MAX value, but the dialog box won t accept anything but whole...

Excel: data time range difference, time intervals, column c

time intervals, column c, time range: Stefano, I m a little bit confused, because if the times are a few seconds out side the limits, then they are outside the limits and the FALSE indications are correct! You said ... while it is not completely false... , but it IS completely false,...

Excel: data transfer between worksheets, cell reference, cell references

cell reference, cell references, sheet tab: Amber, This should be simple enough, as you said. If you are hand-typing the cell reference, you might have the syntax of the reference incorrect. [I NEVER hand type ANY cell references if it can be avoided.] Instead of typing the sheet name,...

Excel: data validation with "mixed criteria", data validation, error alert

data validation, error alert, combo box: I think it depends a little on the date - if this is a range of a small-ish number of dates, then you can use a table as your validation range - this would allow a drop-down list. Otherwise you could use a formula to check for the value being either between...

Excel: How to get data from web in Excel, import data options, login credentials

import data options, login credentials, vba script: It sounds like the log in screen is a form of security for the web page. There might be a VBA script you could use to entert the login on the web site, but I am not that well versed on VBA. Contact Aidan Heritage who volunteers on this board. He is very...

Excel: How to get data from web in Excel, import data options, google co uk

import data options, google co uk, http www google: I ve not done this myself, but it should be possible - my only involvement with web data has been via web querys or adapting sheets others have written - this http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4ACAW_enGB326GB326&q=automate+get+data+excel+from+web+secured+location...

Excel: date calculations in excel, date feature, amount of money

date feature, amount of money, aidan: This should be easy, but a key part (I think) of your question is that you want this to work with the data you already have - and I don t know what that looks like - is it possible to see a sample (or the actual file) - my email is aidan.heritage@virgin.ne...

Excel: date to text, concatenate, ogilvy

concatenate, ogilvy, format string: Sunny for purposes of giving an answer, I will assume the following A1: tom A2: 12/28/2008 (so month/day/year are not ambiguous - stored as a date Serial - in otherwords stored as a date) in A2 =A1&text(A2, mmddyy ) So you use the text...

Excel: default value in a listbox created via Cell data validation, worksheet menu bar, validation method

worksheet menu bar, validation method, data validation: Hi Srikanth, Yes, this is possible, but how depends on what you mean by default value , in particular, when it is that you want the cells to assume their respective default values. For example, do you want the cells to assume the default values when the...

Excel: is there a delete command?, last names, tho

last names, tho: Yes, but it s unclear what A2 looks like: Barry X Smith or Smith Barry X or other? Once you have the results of your formula, tho, you d need to copy/paste special values or the removal of the name from A would make the formulas no longer be accurate....

Excel: How to delete rows with zero values with VBA, zero values, error condition

zero values, error condition, zero value: This is the standard method I use in this situations - this version tests both for a zero value and an error condition Sub Deleter() Set currentcell = Worksheets( Sheet1 ).Range( A1 ) Do While Not IsEmpty(currentcell) Set nextCell = currentcell.Offset(1,...

Excel: deleting conditional formatting, many tables, c range

many tables, c range, nothing works: If range( F6:GW10 ) has 2 Conditional Formatting conditions, and you want to delete the 2nd one but not the first, the code is: Range( F6:GW10 ).FormatConditions(2).Delete If you don t know if the 2nd format condition exists, but want to delete it if...

Excel: display list, drop down list, aidan

drop down list, aidan, xls: version shouldn t overly matter - see http://www.aidanheritage.byethost3.com/excel/example%20choices.xls for an example of what I think you are asking for - please email me if you need clarification and I can try to make something more related to what...

Excel: drop down box, dropdown arrow, sunny yang

dropdown arrow, sunny yang, data validation: Sunny, First, I don t know of a site or pages on a site that deal specifically with this. It isn t something I have personnally needed to look for and I haven t run across one. Debra Dalgleish does show using an activeX combobox so simulate a data validation...

Excel: drop down box & check box coversion, coversion, ribbon

coversion, ribbon, cells: Eric, You can convert any result that is showing as the answer to a formula to a solid value, independent of the formula, by COPYing the values, and then using PASTE-SPECIAL VALUES to some other cells, or even on top of itself. Copying and pasting...

Excel: Enter rows on multiple sheets at different rows, ms excel 2000, lct

ms excel 2000, lct, pieterse: Dave, it does that already - so there is no change to make Here is a simplified extract of the code. Run this so you will see: Sub Tester1() Dim v(1 to 3) as String v(1) = Shipment Rqst v(2) = CI v(3) = Packing List For lCt...

Excel: Entering time returns error, colon separator, entering time

colon separator, entering time, correct time: Since 15:00 is a very valid time, something else is going on. Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference...

Excel: Error in Excel "Unable to Read file", pivot table, excel 2000

pivot table, excel 2000, maximum length: There is no difference in file format, so there is something odd about the file or it s location - the full path and filename has a maximum length (I forget exactly what ) so if a file is nested deep down in directories it MAY cause this error. Otherwise,...

Excel: Error in Excel "Unable to Read file", pivot table, excel 2000

pivot table, excel 2000, peltier: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: EXCEL 2003, error message states, change history

error message states, change history, multiple users: Shared workbooks are a BAD idea - it s an idea Microsoft had that really doesn t work - sooner or later problems will be encountered - such as you have done - do you have ANY machines that can open the file - if so, open it, take off sharing, then (if you...

Excel: EXCEL MARCO for find/Replace - surrounding text, excel marco, sun data

excel marco, sun data, current sheet: Chris, I can t swear that this suggestion will work, but it may get you started. First, I would use excel s FIND/REPLACE to replace the leading equal-sign with = ( single-quote-equal sign). This will convert all your equations to TEXT. This...

Excel: EXTENDED "IF" FUNCTION ?, test ct, conditional test

test ct, conditional test, logical question: Dave, What you want to do is called a NESTED or COMPOUND IF statement. This is one of the more common and basic formulas which you should understand through and through if you want to get proficient at Excel. Hopefully you will read the help-file (function...

Excel: Exacting the minimum values for each day, g 5000, relative reference

g 5000, relative reference, cell blocks: Jiwei, You can do this by applying the DMIN function to your data set. The DMIN function (Database MINimum) allows you to enter a criterion, and then will find the minimum for all values that meet the criterion. In your case, the criterion is...

Excel: Excel 0227 macro, cell a2, indeterminate length

cell a2, indeterminate length, cell contents: Dick, I just wrote the code to your specification: I am trying to write a macro to delete the first 3 or 4 characters (including spaces) from text in each cell of column A in a spreadsheet. and 1. putting the cursor in cell A1 You can have...

Excel: Excel 2000 dynamic range, proper punctuation, column letter

proper punctuation, column letter, dynamic ranges: Frank, It s a known behavior that named dynamic ranges created using the OFFSET function don t appear in the NAME box. Maybe I should have mentioned that, but I didn t think Excel 2000 had the OFFSET function. You did an amazing job by persevering...

Excel: Excel 2000 dynamic range, numeric constants, proper punctuation

numeric constants, proper punctuation, excel 2000: What 4 THE numeric constants are you referring to? I don t see anything like that. Politely, please understand and remember that I can not see what you see, know what you know about your data, know how your data is arranged, know anything about your...

Excel: Excel 2003, cell b1, o zero

cell b1, o zero, information thanks: Victorino I am guessing that the Unit you are speaking of is an assigned value of the course and is already indicated somewhere on your spreadsheet. In this example the value is in cell B1. =IF(B2= Pass ,+B1,+IF(B2= Fail ,0,+IF(B2= Inc ,+ - ,+IF(B2=...

Excel: Excel 2003, brute force approach, computational functions

brute force approach, computational functions, holiday name: Melissa, An upgrade to Excel 2007 would immediately solve your problem because holiday compensation is now built-in to several of the data-spanning computational functions. If you can t upgrade, then you have to use a somewhat complex system to...

Excel: Excel 2003, victorino, vitorino

victorino, vitorino, b6: Vitorino To extend the units to the desired column you can use an if formula For example in cell A5 +IF(A5 0,+B5, ) or +IF(A5= ENG ,+B5, ) In cell L6 +IF(A5=the criteria,+B5, ) Etc, etc. The formula will put the units into the proper...

Excel: Excel 2003, alternate ideas, dear bill

alternate ideas, dear bill, input number: Victorino, According to your IF statement, every single value of A26, no matter what it is, will result in the value of whatever is in cell I26. So I don t understand why you have an IF statement at all. The only thing I see is that if the value in...

Excel: Excel 2003, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: I believe the limit for any function (or cell data, for that matter) is 255 characters long. If you need a longer IF function, then use VLOOKUP instead: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel...

Excel: Excel 2003 - formular question on populating information automatically into another worksheet?, surname data, vlookup function

surname data, vlookup function, excel function: Keith, I don t see the difference between your original question and your re-stated question. Both seem to say that: .... when I physically type in my reference no. on the destination worksheet, .. it then automatically populate it [some cells, presumably]...

Excel: Excel 2003 IF statements, logical tests, conditional test

logical tests, conditional test, result column: Jason, Without knowing the precise details of your logic, I can t write the equation for you (and I probably wouldn t anyway: I d rather teach you how!). What you need to do is create in your mind (better if on paper OR Best if in Excel) a little...

Excel: Excel 2007 conditional formatting, conditional formatting, aidan

conditional formatting, aidan, heritage: I don t quite follow, but I m not sure what the rules you are applying are- it SOUNDS though as though you simply need to modify the formula such that it returns false if an actual date is entered - but I don t follow what you meant by that. My email if...

Excel: Excel 2007 macros, using activex controls, option buttons

using activex controls, option buttons, xlon: Arthi, I am trying to assign macro to option buttons and here is what I have. Two option buttons X, Y in a form group A submit button outside the form group If x is selected I want data in cell E36 to be captured from another worksheet called assumptions...

Excel: Excel 2007 macros, risk quantification, b9

risk quantification, b9, ogilvy: arthi, try this: assume yes or no appears in cell B9 of the sheet named Top Risk Quantification and you want to hide or unhide the rows on the activesheet which is not Top Risk Qantification Dim sh as worksheet Dim cell as Range set sh = Worksheets(...

Excel: Excel, g1, e7

g1, e7, d5: Your question is a little unclear, but if you want to find the row of the largest value: =MATCH(LARGE(F3:F11,1),F3:F11,0)+2 the +2 is because this starts in row 3, so if the largest were in F3, this would return 3. Now you can use this to get the values...

Excel: Excel, dear bill, peltier

dear bill, peltier, last names: I could help a lot better if I saw the actual file. Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18...

Excel: Excel, conditional formatting, information functions

conditional formatting, information functions, subject modules: Melvin, I am not sure what you re asking, a picture would be helpful, and ESPECIALLY a clear statement of what you are trying to find would help. For now the best I can do is suggest that you look at the cell information functions, but there is...

Excel: Excel, conditional formatting, colorindex

conditional formatting, colorindex, cell interior: It depends how the cell is formatted - there is no direct formula to count formatted cells, but if the cells are formatted using conditional formatting you could use the condition IN THE FORMATTING to create a formula. IF the cells are hard formatted...

Excel: Excel, product code numbers, aidan

product code numbers, aidan, spreadsheet: it doesnt really make sense I m afraid, though it s probably just me - are you checking that the numbers in A and G match each other, or do you need to organise one list into the order of the other - and are you going to end up with data in all columns A,...

Excel: Excel, cells

cells: Jambo, It sounds as though you ve applied some odd modification of the percent format to all cells. Apparently no percent symbol appears, though, or you would have mentioned this! Try selecting the whole sheet and choosing the general number format...

Excel: Excel Autogenerating blocking chart, integrated marketing campaign, colored cells

integrated marketing campaign, colored cells, conditional formatting: Steffanie, This is a REALLY cool and interesting problem that I would LOVE to work on for you (through my professional service). Its complex to describe how-to, but I have done similar things before, so maybe I can explain an outline of what you...

Excel: Excel accessing data behind a secure login; for Automated Machine Failure Notification System, failure notification system, machine failure

failure notification system, machine failure, google: I ve not done web access via secure logins before, but I found http://answers.google.com/answers/threadview/id/770960.html which I think looks promising - it seems more sensible to point you to this site and let you investigate, but let me know if I...

Excel: Excel - automating the update to closed workbooks, relevant cells, cell a1

relevant cells, cell a1, text segments: Martin, You MIGHT be able to make this work if every link to a cell in a closed workbook used a computed cell address inside the INDIRECT function. I cannot say for sure, since I have never done this directly myself. What I mean by...

Excel: Excel Column to Row, transpose operation, thanks in advance

transpose operation, thanks in advance, rectangle: Michelle, I may have finally gotten an idea on this, though you MUST have solved it by now! I ll tell you my thoughts anyway! When I encounter the need to change the shape (organization) of one set of data into another shape, I usually turn to...

Excel: Excel Copy and Paste, absolute references, cell references

absolute references, cell references, single quote: Elizabeth, It sounds like you want to have the formula in A3 be =A1+B1, and the formula in A4 to also be =A1+B1, etc. If I have read this correctly, the answer is to convert the formula to TEXT and then copy it anywhere you want. Then change...

Excel: Excel Data Validation, excel data validation, dropdown arrow

excel data validation, dropdown arrow, peltier: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Excel Date Q, seperate cells, thanks in advance

seperate cells, thanks in advance, current date: What happens to 5-9 days old? 21-30 days old? And what do you mean to pull the other information -- do you only want to see 1-4, 10-20 and 30+ and ignore the others? Send a sample workbook to me at bobumlas@yahoo.com, use subject of AllExpertsQ and repeat...

Excel: Excel DBA - Please Post Code, run time error 13 type mismatch, run time error

run time error 13 type mismatch, run time error, run time error 13: Jason, Here is the code - and the discussion. Again, you essentially have to remove the validation aspects of the validation dropdown. I, as an alternative, you wanted to add those choices to the bottom of your list(s), you can, but they will be visible...

Excel: Excel Formula, j29, excel formula

j29, excel formula, p16: P16 is already set with a drop down choice - but you want to adjust this value based on other choices - this seems counter-intuitive. I would EXPECT that a user would choose a set of values and these would be reflected in the outcome rather than adjusting...

Excel: Excel Formula Question, excel formula, subtraction

excel formula, subtraction, len: I m GUESSING that we cannot rely on the order number being 4 characters, and that we aren t allowed to do text to columns to split out the numbers (!) but I m HOPING we can rely on a single space - if so =VALUE(RIGHT(A1,LEN(A1)-SEARCH( ,A1))) where...

Excel: Excel formuas in sorting data, dominic dominic, drop down boxes

dominic dominic, drop down boxes, autofilter: Dominic, I never received a file named PETS from you. But I can answer your question anyway. The first thing I d ask is WHY do you want to copy your database to separate sheets! I think this is a mistake....once you have two copies of...

Excel: Excel formula, countif, excel formula

countif, excel formula, pivot table: At face value, a countif of sumif function would be the way to go, but I would GUESS that you might want the total sum for EACH type of entry- at which point, I d use a pivot table - you haven t specified version, so I cannot be specific about how to insert...

Excel: Excel formula, excel formula, cells

excel formula, cells, apartments: Kim, There isn t a way to sum a column based directly upon the cell colors in the column. However, there are MANY (well, at last 3) other ways to do so, based upon the values in other cells in the yellow rows. There must be something in common...

Excel: Excel If function, d8, c8

d8, c8, ogilvy: bernard, both of these worked for me as I understood the clarification of your rules. =if(And(F8= T ,C8 0,D8 0,C8+D8=C2),C8+D8,If(And(F8 T ,C8 0,D8 0,C8+D8=C3),C8+D8,0)) =if(And(C8 0,D8 0),if(or(and(F8= T ,C8+D8=C2),And(F8 T ,C8+D8=C3)),C8+D8,0),0)...

Excel: Excel HELP, data validation, dc16

data validation, dc16, custumers: You need a master list somewhere with all the customer names. Say this list is in K1:K50. Next to this list is the list of bililng numbers for each item in K. Select all of column A (or as much as you would ever use for this), then use the data/Validation,...

Excel: Excel Macro, excel macro, ogilvy

excel macro, ogilvy, substring: Andrea, I only looped through worksheets. I would need to expand it to include all sheets (I overlooked your comment about Pivot Chart sheet in the original post - sorry about that). Sub Hidesheets() Dim sh as Object for each sh in ActiveWorkbook.Sheets...

Excel: Excel Macro, answer change, excel macro

answer change, excel macro, peltier: Change xlSheetHidden to xlSheetVisible Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of ~3 hours each led by Jon Peltier and yours truly. Check here for details -- http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html...

Excel: Excel Macro Edit, aa aa, false application

aa aa, false application, retrieving records: Hi Lindsay, I guess like this: Private Sub Workbook_Open() Sheets( Sheet1 ).Activate Application.ScreenUpdating = False Columns( AA:AA ).Select Selection.EntireColumn.Hidden = True Dim rng As Range Set rng = Range( H1: ...

Excel: Excel - Match and capture, cell a2, index tab

cell a2, index tab, excel workbook: Shirfan Talyn, A2 in Tab#3 should be =IF(ISNUMBER(MATCH( Tab#1 !$A2, Tab#2 !$C:$C,0)),INDEX( Tab#2 !$A:$A,MATCH( Tab#1 !$A2, Tab#2 !$C:$C,0),1), ) In My version of Excel, the sheet name must be enclosed in the single quotes because it contains the...

Excel: Excel macro, paste routine, workbook sheets

paste routine, workbook sheets, programming macros: You must be using version 2007 if you have 4,579 worksheets in one workbook. That is a LOT of sheets. Any macro that would need to manipulate that many sheets will probably freeze your PC at some point. I would suggest rethinking the design of your project...

Excel: Excel macro, word application, application question

word application, application question, sorry bill: In theory this is an OK idea at best if I may offer my opinion. If I were reviewing your resume I think I would have somewhat of a difficult time navigating it. But more importantly, in my opinion, your resume may never even get read because it will require...

Excel: Excel macro to copy sheet from workbook and paste into new workbook and then protect and close, excel macro, cells

excel macro, cells, gm: Without testing it, it seems this will help. First, the line: For Each ws In ActiveWorkbook.Worksheets is not needed because the activeworkbook contains only one sheet -- it s the one from the Sheets(i).Copy. Also, to ensure you re working on the right...

Excel: Excel macro to edit all text in a worksheet, layout location, voice of experience

layout location, voice of experience, excel macro: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Excel macro help, value application, excel macro

value application, excel macro, comma: Hi Jorge, This macro does the trick: Option Explicit Sub SplitAndTranspose() Dim oCell As Range Dim vValues As Variant Dim lCount As Long For Each oCell In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range( B:B )) vValues...

Excel: Excel Nested If statement with Dates, aidan, heritage

aidan, heritage, virgin: Not sure why you don t want to use the year function, and not clear what the criteria is - 12/31 doesn t evaluate to anything other than a string - so possibly using the DateValue worksheet function MIGHT be the way you want to go? email me with a sample...

Excel: Excel Novice needs help with a formula, learner, novice

learner, novice, bonus: The formula is vlookup - or at least, the easiest way to do it is that formula Create a data table with the MINIMUM value in the first column and the bonus in the second then type =vlookup(E5,YourTable,2,true) Replace YourTable with the range that...

Excel: Excel - Pasting Special, peltier, basic knowledge

peltier, basic knowledge, spreadsheet: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Excel if problem, date system, info time

date system, info time, sum function: HI Lee, Excel is able to handle negative times only when you set your workbook to work with the 1904 date format. You must also select the proper time format to actually see the minus sign: [h]:mm . Note however, that all dates currently in your file...

Excel: Excel Query, excel query, sheet tab

excel query, sheet tab, target: Not clear -- you want to change say row 1,3, and 7, for example, then when you save the workbook you want rows 1,3 and 7 to turn red? If that s the case, the VBA would have to keep the history of all the rows you changed (you could change several thousands...

Excel: Excel question, gridlines, exact method

gridlines, exact method, checkbox: Keshav, You can turn off the gridlines but the exact method depends on the version of Excel you have. In 2003, look under TOOLS-- OPTIONS... I m sorry but I don t have a copy of 2003 running on my computer any more so I cannot be more specific....

Excel: Excel question, timevalue, follow ups

timevalue, follow ups, sumproduct: Your formula doesn t make sense to excel, it would need the AND keyword =if(and(f2 =timevalue( 08:00 ),f2 =timevalue( 20:00 )), B ,etc but easier would be =if(and(hour(f2) =8,etc As to the count, once you have the letters, a COUNTIF function would...

Excel: Excel question for three conditions and three results, dollar rates, cell references

dollar rates, cell references, dear bill: Luis, A simple nested IF is the answer you want. =IF(weight 18,358,IF(weight 8,290,250)) Where weight is the cell containing the weight number of the actual package. Instead of actually typing the numbers 8,18,358,290, and 250 in this...

Excel: Excel question for three conditions, vlookup formula, ogilvy

vlookup formula, ogilvy: Luis, You could avoid using the table: Assume B9 contains the weight then in the cell to show the charge: =IF(ISNUMBER(B9),IF(B9 =0, ,IF(B9 =8,250,IF(B9 =18,290,358))), ) If you want to use the table, then look at the Vlookup formula in Excel...

Excel: Excel/SUMIF, excel help file, excel sumif

excel help file, excel sumif, array formula: Kate, The expert who answered your question gave you an ARRAY formula as a solution! I never use array formulas, because they are slow, hard to enter, and hard to modify. Just as you have observed! They are especially slow (and clunky) if you have...

Excel: Excel small macro, cell b1, blank cells

cell b1, blank cells, macro in excel: Raducu, Sub CheckData() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, r1 As Range, cell As Range Dim bBlank As Boolean Set sh = Worksheets( Sheet2 ) Set sh1 = Worksheets( Sheet73 ) Set r = sh.Range( A1:A4 ) Set r1 = sh1.Range( B1 ) bBlank...

Excel: Excel, minute increments, answer time

minute increments, answer time, table time: Either: =IF(C3 TIMEVALUE( 0:30 ), U30 ,0) or =IF(C3*24 30/60, U30 ,0) First is comparing time against time. 0:30 is the text string zero-colon-three-zero and has nothing to do with time. Passing this to TIMEVALUE converts it to a true time, sot he...

Excel: Excel symbols, aidan, xls

aidan, xls, directory listing: Not sure I know what you need to know (you ve already covered a lot of the common ones!), but http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls has a lot of useful information in it regarding formulas link should work fine - I copied it from...

Excel: Excel Table, table tables, missing something

table tables, missing something, copy paste: I need more detail. So far, it sounds like all you ened to is insert a row above the table, so I m sure I m missing something. Also, how does one number a table ? Tables are ranges and ranges can have names, not numbers, as their reference. How is a table...

Excel: Excel Time Converstion, time ie, duty time

time ie, duty time, h3: Jared, This seems really easy, am I missing something? Put 08:00 in cell K3. The you want =(H3-D3)-K3. My answer assumes that there will ALWAYS be hours over 8 hours; your question didn t say otherwise. If that s NOT...

Excel: Excel Tranpose Error, 00000000000000000000000000000000, correct solution

00000000000000000000000000000000, correct solution, ogilvy: Michelle, I could write a macro or I could write worksheet formulas that will do it, but I don t know what I am looking at or where the data is or where you want the data. the above could be in multiple columns or it could be in one column (you said in...

Excel: Excel V-lookup question, product model number, vlookup

product model number, vlookup, excel sheet: I THINK it s probably more an automated filter you need, which can be done with formulas - I have problems seeing the images that come on here, but if you can email me an excel file I can attempt to come up with something that will work for you - my email...

Excel: Excel and VB, word answer, macros

word answer, macros, free time: Please try another volunteer on this site since I don t write macros for people for free on my own free time. FYI - Your post goes to a specific person, in this case me, and not to a team as you seem to think this site works. I am sure someone else who will...

Excel: Excel VBA, target 3000, target value

target 3000, target value, target address: Jason, Depends on the range: Here are several ways column C If Target.Column = 3 Then row 5 If Target.Row = 5 specific list of cells If not Intersect(Target,Range( C5,C9:D10,C17,C20,D30 )) is nothing Then The list can be fairly...

Excel: Excel VBA, target value, excel vba

target value, excel vba, uninitialized variables: Jason, the top if this routine shows how to handle the multiple cell selection or a cell that is blank. I have entered code to show how to ignore certain entries - although the code doesn t guarantee a correct entry. Also, for this to work, you would...

Excel: Excel VBA & Loops, master spreadsheet, excel vba

master spreadsheet, excel vba, excel functions: A for next loop will work like this for variable=lowerbound to higherbound optional step value next lowerbound being the start of the loop, higherbound the end and the optional step value being the increment value - BUT it wouldn t help here as there...

Excel: Excel Workbooks!!!, sumif, excel workbooks

sumif, excel workbooks, tab two: Christopher, This isn t too difficult, but perhaps you have found an answer elsewhere by now. All you need is a simple SUMIF that uses the word Completed as the criterion. I presume that you have a status column of some sort that has either...

Excel: Excell formula SUMIF function, array formula, joint session

array formula, joint session, performance report: Deborah, the formula worked fine for me when I entered it with Ctrl+Shift+Enter rather than just enter since it is an array formula. Did you remember to enter it as an array formula? As I said, when entered that way, I received the correct sum in the...

Excel: Excell-Time Sheet, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Extract basic info, season thanks, info question

season thanks, info question, single file: Assuming you mean workbooks(files) when you say worksheets then just open each one of the workbooks(files) and copy and paste the data. If you don t want to do this manually, and since you did not say then I did not know, then you will have to write a macro...

Excel: Extracting Acronyms, acronym list, excel code

acronym list, excel code, source word: David, try this - it worked for me. Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, cell As Range Dim s As String, s1 As String Dim schr As String, v As Variant Dim i As Long, rw As Long Set sh = Worksheets( Main ) Set sh1 = Worksheets(...

Excel: Extracting Acronyms, microsoft word documents, microsoft office 97

microsoft word documents, microsoft office 97, acronym list: David, I guess I am a bit confused here. You first said you extracted acronyms from word and copied them to excel. then you said you wanted to do everything in one step and you describe getting acronyms from a worksheet and copying them to another worksheet....

Excel: excel 2003 - data range, right arrow, blank areas

right arrow, blank areas, d17: It s not really a recordable thing - but you COULD define a name and refer to that - the name would be a formula something like =a4:offset(d4,counta(a4:a4000)-1,0) with any name you like -then refer to that name in the macro (IF I was doing this I MIGHT...

Excel: excel, blank rows, dynamic ranges

blank rows, dynamic ranges, different numbers: Drew, Yes, there is. You can use MIN() and MAX() of the column to continuously display the largest and smallest values. To encompass adding more values day-by-day, you can just declare the range over which MIN() and MAX() operate to be a huge...

Excel: excel, front desk clerk, receptionist hotel

front desk clerk, receptionist hotel, administrative asst: As I haven t worked in these situations, I have no idea- however, worth a download is the file http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls which I came across some time back which has examples of lots of functions in Excel - using this...

Excel: excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information on how to use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: excel autosum, autosum, aidan

autosum, aidan, spreadsheet: One of two things - either you are on manual calculation at which point it says calculate at the bottom of the working area OR the formula itself doesn t contain enough cells - cannot be certain which without seeing the sheet - my email is aidan.heritage@virgin.ne...

Excel: excel form, data validation, employee names

data validation, employee names, question data: Sue, Your brilliant application of a named dynamic range should work perfectly. What makes you say it didn t work? Perhaps you think it failed because the names of dynamic ranges created this way don t appear in the NAME box list. But they work perfectly...

Excel: excel formula, judgement call, excel formula

judgement call, excel formula: Pramit, Sounds like a simple IF statement is all you need here. lets say the number you care about is in cell B10. Try this: =IF(B10 =6500,541,B10*0.0833) If the number is greater than [OR EQUAL TO] 6500, you ll get 541, otherwise you...

Excel: excel formula, input cells, data validation

input cells, data validation, input cell: Tatavarty, You can write a formula which prompts for user input. Use an IF staement that tests to see if the input cell is blank. If the cell is blank, it means the user has not input anything yet, so display a prompting message. Once the...

Excel: excel formula find file name, numeric digits, dash character

numeric digits, dash character, search cell: Tony, In order to answer this followup, I d need to know what the PATTERN for all possible filenames is. Do they all end in 000? Do they all end in three numeric digits? Do they all end in three characters? If they DO all end in 3 characters,...

Excel: excel formula needed, compound conditions, excel function

compound conditions, excel function, question statement: Dennis, This is easy to solve and REALLY easy if you have Excel 2007. In 2007, just apply the IFERROR function. You can read about the IFERROR syntax in the Excel function guide and will find it easy to use. In 2003, embed your desired...

Excel: My excel Speed is very very slow and cannot be run., vlookup function, peltier

vlookup function, peltier, acnj: I d have to see the file, but you can certainly strip it down to way less than 50000 lines & 10 worksheets for me to examine the formulas you re using & possibly suggest an alternative. Send a sample (smaller) wb to me at bobumlas@yahoo.com, use subject...

Excel: excel time sheet, excel time sheet, conditional test

excel time sheet, conditional test, time example: Raymond, You can embed your entire time calculation inside an IF staement, whose conditional test is based on the value in the end time cell. something like this: =IF(ISBLANK(end-time-cell), ,your-calculation-here) Bil...

Excel: excel validation problem, validation problem, mouse pointer

validation problem, mouse pointer, aidan: using the mouse to select a cell and then select another shouldn t stop the validation kicking in - the only way I know of to stop validation working is to have data entered prior to the validation (or POSSIBLY via vba) - I m happy to look at the rogue sheet...

Excel: excell, nice day, stack

nice day, stack, long time: Vijay, Do you still need an answer to this question? It s been in my stack for a long time, so I suspect you may have solved it by now. If you still need an answer, please resubmit your question to me and I ll see what I can come up with. Otherwise,...

Excel: expiration date formula, pivottable field, pivot table

pivottable field, pivot table, john doe: Stephanie, The auto date is simple as was in your original quesiton. If the date is in cell A2, then in the expiration date column the formula: =B2+10 and this will have the expiration date here. You can then copy this formula down on the rest of...

Excel: Factoring, array formula, curly brackets

array formula, curly brackets, vba module: Brad, If you used =GetFactors(54), then the -8 means that there are 8 factors and you need to array enter the formula in 8 cells in the same row - at least that is what the directions say. When I do that I get 1 54 2 27 3 18 9 6 so it definitely...

Excel: FHA 203k Max Mortgage Work Sheet, fha 203k, mortgage worksheet

fha 203k, mortgage worksheet: Rick, I m not a mortgage analyst or broker, so I have no idea what you want. A 203K mas mortgage worksheet is Greek to me. If you have an incorrect worksheet that is password protected, (and you don t know the password), I can t help you break the...

Excel: Fill color in cell when adding text, conditional formatting, data validation

conditional formatting, data validation, cell reference: Ron, I am SO sorry! I made a mistake on your answer and typed the totally wrong thing! Instead of Data validation, I should have referred you to Conditional Formatting! Whups! What was I thinking??? So, its Conditional Formatting that...

Excel: Filter & Copy Row Macro, false application, auto filters

false application, auto filters, report application: Worksheets( import ).Select Range( S2 ).Select Selection.AutoFilter Field:=19, Criteria1:= = Dim myrange As Range Sheets( import ).Select criteria = Cust Svc Rel Mgmt Set myrange = Sheets( import ).Range( A1:A50 ) Range( A1 ).Select For Each C...

Excel: Filter & Protection, filter protection, check boxes

filter protection, check boxes, proper combination: Deb, I received your workbook. I m not sure which is the area that you are having troubles filtering, but, I made it work okay... it is a simple thing, really. I picked one of your tables and applied a filter, did the trick (see below) and it worked...

Excel: Filtering Data Using Combo box, combo box, business email

combo box, business email, dear bill: Adnan, You requested a professional quote for this. Please send a more detailed description of your question, along with a sample of your workbook data, to my business email at ExpertAtExcel@AOL.COM and we can get down to business! Bil...

Excel: Filtering The Data Using a Combo Box, combo boxes, query method

combo boxes, query method, combo box: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Filtering Data Using Combo box, combo box, excel sheet

combo box, excel sheet, input range: Hi Adnan, Follow these steps: 1. Insert a dropdown from the Forms toolbar 2. Rightclick it and select Format control . 3. Select the range of data for the Input range 4. Click OK 5. Rightclick on the dropdwon again, select Assign Macro. 6. Click...

Excel: Find & Delete Record in VBA Macro, vba macro, layout location

vba macro, layout location, exact data: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Find Macro Function, sirajuddin, khaja

sirajuddin, khaja, sheet1: Hi Mohammed, Like this? Sub CheckSheet() Dim oCell As Range On Error Resume Next Set oCell = Worksheets( Sheet2 ).Cells.Find( _ What:= incompatibility , _ After:=Worksheets( Sheet2 ).Range( A1 ), _ ...

Excel: Find Macro Function, sheet1, sirajuddin

sheet1, sirajuddin, khaja: Sub CompatCheck() dim Fnd as Range On Error Resume Next Err.Clear Set Fnd = Sheets( Sheet2 ).Cells.Find( incompatibility ) NRow = Sheets( Sheet1 ).Range( E50000 ).end(xlup).Row+1 If Err.Number 0 then Sheets( Sheet1 ).Cells(NRow,5).Value...

Excel: Find Macro Function, incompatibility issue, cell b5

incompatibility issue, cell b5, sheet1: Sirajuddin, In the previous, you wanted existing data moved down. Here you have changed that to write to the next available cell - at least that is what I understand you to say: Sub Find_Incompatibility() Dim r As Range, r1 As Range, s As String Dim...

Excel: Find Macro Function, sheet1, sirajuddin

sheet1, sirajuddin, khaja: what wasn t clear was if the error found should actually have any reference to WHERE the error was found? I m GUESSING that you actually want to return one value per data line - so I would need some clarification before writing a macro for you - are...

Excel: Find Macro Function, khaja, sheet1

khaja, sheet1, macro function: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Finding averages using calculations, set of numbers, missing something

set of numbers, missing something, begining: Ben, I must be missing something because it seems like you could just take the average of the second set of numbers (the line begining with 156,128, and ending just before the 2328). What s wrong with using excels Average function on this set...

Excel: Follow up question, hello bill, time bill

hello bill, time bill: Dilip, This question has been unfortunately neglected too long. Now that I see it, there is no actual question here, just your image. If you still need an answer, please start over with the question, again, and I ll see if I can do better this time....

Excel: Formating Cell Equation, cell reference, spread sheet

cell reference, spread sheet, integers: Zach, Use =IF(MOD(N,1)=0, integer , not integer ) to determine if a number is an integer or not. N is the number you re testing, or a reference to the cell containing the number. Instead of using an IF statement, you...

Excel: Formatting, conditional formatting, cell contents

conditional formatting, cell contents, cpearson: Kendra: This can be done with conditional formatting. The following are websites which explains how to change an entire row color based on the row number. You can make a small modification to change color based on the cell contents: http://www.cpearson.com/excel/cformatting.htm...

Excel: Formatting in Excel, conditional formatting, price column

conditional formatting, price column, initial selection: Joey, All you need to do is apply a Conditional Formatting (CF) rule to each entire row. So select the ENTIRE set of ALL the rows, including the column with the minimum prices. Then open the CF dialog (I will type CF so I don t have to spell...

Excel: Formatting validation drop down list, area of geometric figures, arial unicode ms

area of geometric figures, arial unicode ms, data validation: Nada, The only way I could see this working is if you used a custom font that had a 2 superscript in it. Then the superscript two would not require special formatting. This shouldn t be as far fetched as it may sound. In xl2002 and later, I believe excel...

Excel: If Formula, e12, minus sign

e12, minus sign, text string: Heidi, The reason you can t do it with an IF statement is that your number isn t a number at all, its a TEXT STRING. You can prove this to yourself by writing an =ISTEXT(cell) function in a cell, and for (cell) refer to the cell that contains the non-number....

Excel: Formula, excel, average

excel, average: Without more information on the layout, I will assume that the values out of 55 are in range A2:A7, and the values out of 100 are in range A8:A15. With that you can do something like: =(SUM(A2:A7)/55+SUM(A8:A15)/100)/14 If the layout is different, the formula...

Excel: Formula to calcuate time range, numerical functions, input time

numerical functions, input time, time range: Nagesh, If you want to output a time RANGE, whose time numbers can be used by other time-related calculations (or other numerical functions), then whatever you do, you need to do it in TWO CELLS, so you get the begin-time in one cell and the...

Excel: Formula/Function, skirts dresses, sum formula

skirts dresses, sum formula, quarterly sales: Your email didn t make it through to me, but I HOPE mine did - if you can mail me a sample file I can put some formulas in place for you- I m fairly sure I know what you need, but seeing examples would make it easier to be certain I m giving you what you need...

Excel: Formula Question, text phrase, latoya

text phrase, latoya, question question: Latoya, Sorry I wasn t clear. here s what you need: =IF(ABS(B1-C1) =101,10,IF(ABS(B1-C1)) =51,5,IF(ABS(B1-C1) =0,0, Diff is negative ) With this formula, diff is negative is impossible to get, so you could put in a instead. ...

Excel: Formula required, a60, ashish

a60, ashish, a1: Ashish: Here is a formula you can use: =IF(COUNTIF(A1:A60,FALSE)=0,TRUE,FALSE) The above formula assumes the values in cells A1:A60 are actually Boolean and not merely text. If the values are text then you would have to use this formula: =IF(COUNTIF(A1:A60,...

Excel: Formula to get text, cell b1, cell location

cell b1, cell location, a47: Irfan: You didn t specify if the project number/name is located in the same cell as the text PROJECT NUMBER . Thus, I will assume cell A9 contains the following: PROJECT NUMBER : 12345678 - NORTHERN ALTA INSTITUTE Cell A47 contains the following:...

Excel: Fraction of Time Range, positive reply, mm ss

positive reply, mm ss, format hh: I m not sure what exactly you are looking for, but I have uploaded a new spreadsheet here based on what I think you are looking for. If this isn t what you need then I suggest providing me with a specific example of what you want the output to look like (an...

Excel: FUNCTION, excel, function

excel, function: You could use a function like: = L=Z 5, M=5 Z20, H=20 Z 60 The important part is to format the cell to wrap text (menu Format- Cells- Alignment tab- wrap text checkbox). This way it will show the different lines on the cell. If this is not what you...

Excel: IF Functions and Drop Downs, multiplcation, vlookup formula

multiplcation, vlookup formula, data validation: Laura, I think safe would be a hidden sheet that had a table like this LDF1 0.0xx LDF2 0.0xx LDF3 0.0xx LDF4 0.033 LDF5 0.0xx LDF6 0.0xx Say this is in a sheet named DATA with LDF1 in cell A1. (I am just using these as placeholders -...

Excel: Further clarification on an answer you provided previously., google search, entry cells

google search, entry cells, input cells: Kamal, The term shadow form is of my own making, so it s not surprising you can t find out anything about it! I made it up myself. The answer that you referred to described the shadow form like this: In between these two sheets is the shadow...

Excel: 2003 excel macro is not working in 2007 excel, myfolder, quotes

Excel: 2003 excel macro is not working in 2007 excel, myfolder, quotes

Excel: Calculating daily and weekly OT, overtime hours, thinking the same thing

Excel: Calculating daily and weekly OT, overtime hours, thinking the same thing, monday through saturday

Excel: Column number, column number

Excel: Column number, column number

Excel: code for copy & paste multiple Tables from diff word files to excel at different sheets

Excel: code for copy & paste multiple Tables from diff word files to excel at different sheets

Excel: control tick box from VBA, checkboxes

Excel: control tick box from VBA, checkboxes

Excel: default value in a listbox created via Cell data validation, thanks for your help

Excel: default value in a listbox created via Cell data validation, thanks for your help

Excel: Excel 2007 macros, activex controls

Excel: Excel 2007 macros, activex controls

Excel: Extracting Acronyms, special software, stanley

Excel: Extracting Acronyms, special software, stanley

Excel: excel formula find file name

Excel: excel formula find file name

Excel: FHA 203k Max Mortgage Work Sheet

Excel: FHA 203k Max Mortgage Work Sheet

Excel: Find Macro Function, target

Excel: Find Macro Function, target

Excel: Hyperlinks in Pivot Table, dozen forums, target value

Excel: Hyperlinks in Pivot Table, dozen forums, target value, web adresses

Excel: index/match circular reference problem, h4, cells

Excel: index/match circular reference problem, h4, cells, array

Excel: Macro To Enable Macros, mvps, free software

Excel: Macro To Enable Macros, mvps, free software, macros

Excel: Macro - hide rows, formula

Excel: Macro - hide rows, formula

Excel: Print to specified paper bin, printer driver, api

Excel: Print to specified paper bin, printer driver, api

Excel: Reminder in excel, passport visa, visa renewal

Excel: Reminder in excel, passport visa, visa renewal

Excel: Save with cell contents as file name

Excel: Save with cell contents as file name

Excel: How to find all occurances of a string within a range & list adjacent cell values in another cell, array formula, requirement id

array formula, requirement id, cell values: You need a function macro to do it. Press Alt/F11 to get to the VBE, Use Insert/Module, paste this in: Function Tester(rg As Range, rg2 As Range) As String If rg.Columns.Count 2 Then Tester = CVErr(xlErrNA) Exit Function End...

Excel: IF/THEN formula, cell a1, countif

cell a1, countif, ogilvy: assume the word to look up is in cell A1 and the list of individual words (one per cell) is in column F in B1 as an example =if(iserror(match(A1,F:F,0)), No , Yes ) This is a case insensitive comparison. another way =if(countif(F:F,A1)...

Excel: If(and) formula, control bit, different numbers

control bit, different numbers, f2: Rogers, You may send your file to me at ExpertAtExcel@AOL.COM. However, I won t write your equations for you, unless you hire my professional services to do that. My business is Excel spreadsheets, and I don t work for free. I can look at...

Excel: If formula ??, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information on how to use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: is there a formula to automatically sort data, asking the right question, top menu bar

asking the right question, top menu bar, work trust: David, There IS a formula-based method to do this, but the equations are REALLY complex and horrible! You probably don t want to spend the HOURS it will take to make this work. Trust me, its REALLY bad! Far better would be to write a MACRO...

Excel: formula to calculate pay off DATE, financial functions, loan payoff

financial functions, loan payoff, exponents: Anita, I m sorry... but I am not a financial analyst! I looked through the entire list of Excel 2007 s financial functions and didn t see what I was looking for. But, the functions may have used some terminology that I am not familiar with. I suggest...

Excel: formula to calculate pay off date, principle balance, amortization schedule

principle balance, amortization schedule, interest column: Anita I usually create a table to do this that calculates each payment and the amount of interest and principle made with each payment. The formula to calculate the interest with each payment looks like this. =E18*(0.08/365)*(G19-G18) Where E18...

Excel: IF THEN formula conundrum, excel, average

excel, average, sumif: I am not sure if you have the times just as times or also with the date. That will affect the sorting greatly, if you don t have dates all the times will be clustered together, is that you what you want? In any case, I would recommend to use an additional...

Excel: IF formula - returning False when True, salvage value, true hope

salvage value, true hope, h14: I think the clue is in your statement taht the formatting is the same - changing a format doesn t change the underlying cell value - so a cell formatted to zero decimal places could show 2 but actually contains 1.9 - which isn t the same - I would imagine...

Excel: Which formula should I use?, countif, consecutive rows

countif, consecutive rows, two different ways: Vicky, An UP counter or a DOWN counter is simply a formula with adds 1(UP) or subtracts 1 (DOWN)from the value it had in the previous row , until some logical condition tells it to restart counting from zero (UP) or from a some maximum(DOWN). ...

Excel: formulas, cell reference, waterbottle

cell reference, waterbottle, dates of birth: Connie, You can use ages or date-of-birth, either way. The IF statement is the correct one to use. If you use DOB, you need to compute the age by subtracting the DOB from today s date. Since date calculations such as this result in an answer...

Excel: IF function using MONTH and YEAR, conditional test, true bill

conditional test, true bill, column c: Diana, All you need is a compound conditional test based on the month and the year. Such a compound test can be made with the AND statement, like this: AND(MONTH($A5)=MONTH(AH$1),YEAR($A5)=YEAR(AH$1)) Both phrases must be TRUE. ...

Excel: Gold Purity Calculation Formula, gold purity, format cells

gold purity, format cells, measuring volume: I m not sure what the underlying calculation is supposed to be - the formula you were given goes (PS-PH*Factor1/PS-Factor2)/100 and multiplies that by PS to return the result POSSIBLY this should equate to (PS-PH*(Factor1/PS-Factor2))/100 but as...

Excel: Graphing, xy graph, size shape

xy graph, size shape, zero values: Ben, I m not sure I understand your problem. Values in a cell, and formulas in a cell, are the same thing. A formula equates to a value, and the graph will display all the values in the ranges you specify as the data series , whether they are typed...