sum formula, sum function, grand total: Jon: I presume you are using auto-filter. When you add your total line, be sure to leave a blank between the last row of data in your report and the total line itself. That way, when performing an auto-filter, the total line is ignored. If you are...

Excel: Filtering 2 kind format data with changing, hadia, number values

hadia, number values, column type: Hi Goka, To do this follow these steps: 1 - Select the column that contains your data 2 - On the top menu, click on Data and then select Text to Columns 3 - Make sure Delimited is selected and click Next 4 - Click on the Other box and type a forward...

Excel: Filtering Birthdays, custom filter, data columns

custom filter, data columns, month today: Hi Cliff, The formatting in excel would be =Month(Today()) You can use Excel menu Data Filter Advanced Filter to accomplish this. Not sure how that works with Sharepoint? If you want you may send an example to cloverken@yahoo.com and I will place...

Excel: Filtering of cells and Protection of sheet (XLS97) Part II, autofilter, last question

autofilter, last question, ogilvy: Add the line worksheets( Sheet1 ).enableAutoFilter You also need the worksheets( Sheet1 ).Protect UserInterfaceOnly:=true The autofilter must already be inplace (the dropdown arrows must be visible). This will allow you to change criteria or do...

Excel: Filtering Data, odd phenomenon, spread sheet

odd phenomenon, spread sheet, many thanks: Pete, the only reason I can come up with is that there is an extra space in the data. Check the data, and make sure that it is exactly what you think it is. If you still have trouble, send it to me at kawai54@excite.com. I ll take a look. Regards, Jan...

Excel: Filtering of Data Validation Drop Down List, data validation, sql function

data validation, sql function, jan mar: I don t quite understand. Suppose A1 has the data validation for Jan, Feb, ..., Dec. And then you choose Feb. Now you want that list to contain Jan, Mar, Apr...Dec? And then if you select Jan it now contains Mar, Apr,...Dec? How would it ever get restored...

Excel: Filtering Data, conditional formatting, uk shares

conditional formatting, uk shares, detailed description: Hi, Stephen, I m not sure exactly what your question is. Is it that you don t want to have to do the process two times, or that you want to do it a different way, or something else? In this case, it s a little hard to picture what you re doing with seeing...

Excel: Filtering Dates in Excel, microsoft excel 2002, birthdates

microsoft excel 2002, birthdates, perfect world: In a perfect world, Excel would let you filter by formula (maybe we can keep our fingers crossed and hope they implement that in the next version). You won t be able to use a custom filter to single out just the month, but you can do something that will...

Excel: Filtering a Drop down menu, bob bob bob, drop down boxes

bob bob bob, drop down boxes, drop down menus: Data/Filter/Autofilter and use the dropdowns will show only one item of each occurrence -- if you have Name Bob Bob Bob Fred Fred then the dropdown will only show Name Bob Fred As to the code number, the resulting formula would have to already...

Excel: Filtering some data, protein entries, protein names

protein entries, protein names, row entries: I somewhat understand. For best data management, especially if you will be filtering and/or sorting your data, all related information for a particular record should be maintained on a single row. To do what you are asking, filter one row based on the...

Excel: Filtering data matching 2 criteria, array formula, array formulas

array formula, array formulas, data validation: Erwin: I have uploaded a solution here: http://home.swbell.net/nate-sus/excel/specialsort.xls Notes: 1) You did a great job of explaining your question. 2) The solution was quite challenging - unfortunately, I don t believe I could explain how the...

Excel: Filtering data matching 2 criteria, array formula, array formulas

array formula, array formulas, data validation: Erwin: I have uploaded a solution here: http://home.swbell.net/nate-sus/excel/specialsort.xls Notes: 1) You did a great job of explaining your question. 2) The solution was quite challenging - unfortunately, I don t believe I could explain how the...

Excel: Filtering data using Excel 2000, letter category, column headings

letter category, column headings, blank rows: Adrien Sometimes the filter process can be a bit cumbersome to work with. I would suggest you consider a different approach. First copy all of the data to another sheet. Then sort that new sheet based on the letter category. Then subtotal at each change...

Excel: Filtering data, ymba, indirect address

ymba, indirect address, access query: Hi Eric How are you? Please try the following 1. Type in A1:A5 YSYY YPAD YPAD YMML YMBA 2. View - toolbars - forms 3. Draw a combobox 4. Right click on this combobox 5. Type A1:A5 in inputbox and E1 in cell link Then you can associate each...

Excel: Filtering and dropping duplicate records, youth sports organization, national governing body

youth sports organization, national governing body, answer mark: It actually changes quite a bit...SUMIF can only handle 1 criteria...and now there are 2. No biggie...SUMPRODUCT is another option and it can handle multiple criteria. Give this formula a shot and let me know: =SUMPRODUCT(($C$1:$C$1000=C1)*($B$1:$B$1000=B1))...

Excel: Filtering email, e mail addresses, e mail address

e mail addresses, e mail address, mail name: Shah You can filter the list with a simple sort, by e-mail address, then pick out only one e-mail name where there are duplicates, with a formula that transfers the data if the e-mail address is not duplicated. A formula such as this will work. Where...

Excel: Filtering I think, cell values, excel 2000

cell values, excel 2000, e6: Hi Cheryl, I hope I understand your question...you want to search for a word and display the corresponding cell values for column A and column B. Is this correct? If so, I think this solution may help you. I =IF(ISNUMBER(SEARCH($E$6,B1)),MONTH(A1)&...

Excel: Filtering in VBA, associated weavers, false selection

associated weavers, false selection, blank cells: I never mind people asking questions! This job is one that I would tackle in a different way, as the filter is the human way of doing it, but the computer can do it diffently - for the first part Set currentCell = Range( A1 ) Do While Not IsEmpty(currentCell)...

Excel: Filtering in VBA, false selection, blank cells

false selection, blank cells, david answer: Could I change that to say Rows( 2:600 ).Select to ensure I get them wherever they be? YES Also, you don t need to select anything - the recorder does that for you and always records more than what is really needed. First macro Range( A1 ).AutoFilter...

Excel: Filtering in VBA, associated weavers, false selection

associated weavers, false selection, blank cells: Hello David, Thanks for including your recorded code - it was helpful in establishing what you wanted. These require that your database starts in A1 (you can adjust that in the code) and that the sheet with the database is active when you run the...

Excel: Filtering, sam sam, digits

sam sam, digits, region 1: Sam, I can t understand exactly what you want to do. You listed some records in your database as it is now. Could you list some records as you d like it to look after filtering? That way, I ll have a better idea what you d like to do. Thanks, Jan ...

Excel: Filters bug me, pivot tables, autofilter

pivot tables, autofilter, alternate solution: Hi Brian, Unfortunately the AutoFilter has a limit of 1000 unique selections, so this is why you are not getting the rest of them picked up. Depending on what you are trying to do, it may work for you to cut your list down by filtering on one of your other...

Excel: Filters in Excel, douglas m smith, florida customers

douglas m smith, florida customers, counta: Hi Crystal, You can count and total (and nine other things) a filtered list by using the SUBTOTAL function. It ignores hidden row values. I ve pasted in the Excel Help text below. Options 2 and 9 will take care of your specific problems. Douglas M....

Excel: Filters in Excel, florida customers, countif

florida customers, countif, sumif: Crystal, Filtering is good for displaying information, but poor at summarizing it as you have found. To answer your question, no there is not a standard way to display totals or subtotals with filters. You can use the CountIf and SumIf functions to display...

Excel: Financial Spreadsheet, financial spreadsheet, paste values

financial spreadsheet, paste values, absolute references: If you no longer need the formulas, then edit paste special, paste values will resolve the problem. If however you DO need the formulas then you are going to run into problems - the #REF error indicates you are trying to reference an impossible cell - for...

Excel: Financial Spreadsheet, douglas m smith, financial spreadsheet

douglas m smith, financial spreadsheet, dollar signs: Hi, When you copy / paste, the references might end up pointing to different cells or even off the spreadsheet, especially if pasting up or to the left. You can freeze the references using dollar signs. Eaxmples: $A9 - Will always point to column...

Excel: Financial Spreadsheet, financial spreadsheet, absolute and relative references

financial spreadsheet, absolute and relative references, absolute references: You may take a look at the Excel help for absolute and relative references (press F1 and type absolute references, for example) In a nutshell, when you want a column you are referencing not to change when moving the formula, use a dollar sign in front of...

Excel: Financial Spreadsheet, financial spreadsheet, cell references

financial spreadsheet, cell references, dollar signs: When cutting, copying, & pasting columns with formulas involved you need to be careful with the cell references in the formulas. This is important if the column you are cutting and pasting contains a formula. It also matters if the column you are moving...

Excel: Financial Spreadsheet, financial spreadsheet, absolute references

financial spreadsheet, absolute references, relative references: Hi there, If you insert columns and paste other columns with formulas into it, it will mess it up. The reason is that Excel is trying to be smart and it maintains the relative references of the formula. You have three options: 1. Use absolute references...

Excel: Financial Spreadsheet, financial spreadsheet, btinternet

financial spreadsheet, btinternet, c6: send a sample workbook to me at bobumlas@yahoo.com & tell me what you re trying to do. If you have a formula like =C1/C2 in cell C20, then copying C20 into cell C6, for example, will create a #REF error because cell C20 really contains a formula referencing...

Excel: Financial Spreadsheet, e mail address, financial spreadsheet

e mail address, financial spreadsheet, somthing: Roomi Ok send me the file-- I can help you with some suggestions, but as you may note I limit my allexperts questions to 1 a day, and that doesn t mean multiple questions included in one request. Thats because I could spend all of my time answering excel...

Excel: Financial spreadsheet, financial spreadsheet, dataset

financial spreadsheet, dataset, data input: Diane Without knowing specifically wht your data looks like, I obviously can t write a formula or macro for you. However it appears to me that you can do a create a new column and then do a copy paste special with your data. Just think thru the process...

Excel: Financing Formulas, google, loan balance

google, loan balance, small car: Honestly, I had never used the ISPMT formula before, preferring to calculate the interests by multiplying the interest rate for the period by the loan balance at that period. From the testing that I have been doing, I see a similar behavior as you state,...

Excel: Perfect Solution - Thanks, followup question, perfect solution

followup question, perfect solution, tenacity: You re welcome - glad I could help. It s always more enjoyable to be able to help someone who is as understanding as you were of the fact that I do this in my own free time and who is so appreciative and polite. I KNOW first hand how frustrating and...

Excel: Find and Adding..., blank cells, two different ways

blank cells, two different ways, b2: You have explained the problem in two different ways, so I ll assume the first one is the one you want (summing blank cells) - your second method can be done as well but is more complicated (summing on two conditions) Assume that Employee Status is in Column...

Excel: Find Last Cell in Excel, object variables, time david

object variables, time david, messagebox: Hello again Your code seems fine, but I would define some object variables to make it more readable E.g. Dim Act as range Set Act=Sheets(1).range( a1 ) Act.End(xlDown).Select ActiveCell.Offset(1, 0).Select .... To skip the lines that throw errors...

Excel: Find Common Range from 2 Ranges, result output, exit function

result output, exit function, rg: You need a user-defined function. It looks like this one works, so press alt/F11 (takes you to the VBE), use insert/Module, then paste this in: Function MinRange(rg As Range) As Variant If rg.Columns.Count 2 Then MinRange = CVErr(xlErrNA)...

Excel: Find if a cell lands within certain criteria, cell a2, gamet

cell a2, gamet, d3: If the formulas you have shown me are correct, you are not checking anything against A2 or A3 - the formula just shows A1 each time Not sure what you are actually trying to achieve, as the answer related to checking three values, but you are now trying...

Excel: Find cell location, cell location, b3

cell location, b3, cells: Sorry, I don t understand your question (though there really is no question, just needs expressed). If you need to get the address of the cell where 9 was found then use Activecell.Address. I don t know how you have defined Found and you did not say...

Excel: Find combination of cells to total certain value, set target, target cell

set target, target cell, solver menu: Excel have a tool that may fit in your case, called Solver. If it is not present in the Tools menu, you can add it on the menu Tools- Add-ins menu. The way to use solver in your case can be the following. Next to the column where you have the items with...

Excel: Find combinations in Excel, invoices, combinations

invoices, combinations, possibilities: MICHAEL You are right, the number of possibilities is so great that writing the formula to do the calculation would be very difficult. It can be done, but-----. I have attempted this a couple of times trying to find a shortcut, but I haven t been able...

Excel: Find command in a macro, cell reference, variable name

cell reference, variable name, a1: Yes to both. For a prompt use the InputBox function and assign a variable to what was typed in the InputBox function. Use the variable name in place of what you have typed manually in your Find statement. See VBA help for that function. If you reference...

Excel: Find command, group mode, two choices

group mode, two choices, options button: Sanjay, thank you for your kind words. I have two things to tell you. First of all, your question has a very easy answer: 1) Start out by doing a Find in the normal way. The *next* occurrence of the string will be found. (You need to do this to establish...

Excel: Find and concatenate, rw 1, ogilvy

rw 1, ogilvy, ricky: Hello Ricky, I assume you are asking for a macro to do this. Try this on a copy of your data. Sub ABC() rw = 1 Do While Cells(rw, 1) If Left(Cells(rw, 1), 1) = 1 Then rw1 = rw col = 2 Else Cells(rw1, col) = Cells(rw, 1) Cells(rw,...

Excel: IF Find then copy and Paste, c cells, source sheet

c cells, source sheet, srow: Hi Richard, If I understand your question, you want the destination sheet to be the active worksheet, and the source sheet to be a different sheet. In this case I assumed the different sheet is named Go Ahead , but I m sure you can see where to change...

Excel: Find, copy row, leap years, department of public safety

leap years, department of public safety, accident statistics: ...Is there some way to have Excel look for a date in an entry cell ... then find the corresponding date on any page in the worksheet - then paste certain cells on the report page?... Yes but that will require a macro and sorry, as politely as possible,...

Excel: Find Last Date Occurrence, vlookup, last date

vlookup, last date, first instance: Yep. I don t know how to do it without VBA, Thomas. And I don t really do VBA, but it has to do with xlup (you go to the last cell in a column, which would be A65536 if you re using 2003 or below, and then you use the xlUp to find the first USED cell. That...

Excel: Find Duplicate Information, ogilvy, occurance

ogilvy, occurance, b1: In B1 put in the formula =if(countif(A:A,A1) 1, Duplicates , ) then drag fill this down the column (column B). This will mark all cells that are not unique in the list. If you want no marke the first occurance you can use =if(countif($A$1,A1,A1)...

Excel: Find feature in excel 2003, greg grimes, f11 key

greg grimes, f11 key, sheet1: Try this procedure: or say macro Sub finddia() Application.Dialogs.Item(xlDialogFormulaFind).Show www.pexcel.com , 2, , , , True above value 2 returns.. values to be look in value. End Sub GO to VBE: 1.By pressing ALT + F11 key or you can go...

Excel: Find last filled cell in a column, douglas m smith, array formula

douglas m smith, array formula, array formulas: Hi Rich, This will find the row of the last filled cell in column B: =MAX(IF(LEN(B1:B65000) 0,ROW(B1:B65000),0)) However, you will need to hit Control-Shift-Enter instead of Enter to make it an array formula. (If you do this correctly, {} brackets...

Excel: Find a match and insert corresponding data, vlookup, number thanks

vlookup, number thanks, duplicate records: This is perhaps the single most useful function in Excel - at least, it seems to be the one that is used to answer the most questions - so much so that I have a web page to explain it which is www.aidanheritage.co.uk/Excel - but the formula you need is VLOOKUP...

Excel: Find matching record, nav data, hi bob

nav data, hi bob, column c: In your original question, you mentioned WorksheetA and WorksheetB. So now if everything is in the same worksheet, please tell me where the data and master data are located. I didn t save your original question. It s be much easier if you either repeat the...

Excel: Find last occurence in an array', array formula, array formulas

array formula, array formulas, edward jan: For this you ll need to use what s called an array formula. Array formulas can be used over ranges of cells where simple formulas cannot. Let s say that you want to search H1:H100 for the number 54. You want to find the bottom-most row in which it occurs....

Excel: Find in Range, array formula, array formulas

array formula, array formulas, incorrect result: The best way to do this is with an array formula: =SUMPRODUCT((A1:A100= Created )*(C1:C100= Converted )) NOTE: If you are unfamiliar with array formulas, they are entered by pressing CONTROL-SHIFT-ENTER at the same time instead of just ENTER. A correctly...

Excel: Find and Replace, cell borders, half an answer

cell borders, half an answer, format button: Hi Jim, This is sort of half an answer. You can change the border color when you replace a value, by clicking the format button next to the Replace with and changing your format options. When you are Finding though, changing the format of the Find...

Excel: Find and Replace, cells, code work

cells, code work: Hi jim Did my code work for you ? Best De...

Excel: Find & Replace, j1, thanks john

j1, thanks john, ogilvy: sub ReplaceData() Dim r as Range, cell as range set r = Range(Range( A1 ),Cells(rows.count,1).End(xlup)) for each cell in r if len(trim(cell)) 0 then Columns( J:J ).Replace _ What:=Cell, _ Replacement:= , _ LookAt:=xlPart,...

Excel: Find/Replace Macro help, spreadsheets, spreadsheet

spreadsheets, spreadsheet, a3: The way to skip past the error is to insert an ON ERROR RESUME NEXT statement IF you want to do something with the error, then something like If IsError(ActiveCell) Then ActiveCell.Value = test would do for you - obviously replace...

Excel: Find & Replace macro, abc def, case 2

abc def, case 2, ogilvy: Public Sub Priority() Dim s as String Dim cell as Range For Each cell In Selection s = cell.Text select case s case 1 Cell.Value = ABC case 2 Cell.Value = DEF case 3 ...

Excel: Find and Replace - Options, shortcut key, options button

shortcut key, options button, spreadsheets: Once you change it to Columns and Formulas, it ll stay that way until you quit excel or change it manually. If that s really a big deal, you can run this macro and assign it to Ctrl/shift/f: Sub Findit() Application.SendKeys ^f%S{down}%L{up}{up}{down}%n{esc}...

Excel: Find and Replace question, douglas m smith, match case

douglas m smith, match case, case entry: Hi Chris, You are on the right track. In the Find/Replace dialog box, click the Options button (Excel 2002, should be the same or similar on 2003) and then check the Match Case box. Put Po in the Find box and PO in the Replace box. (Notes: Put...

Excel: Find/Replace in VBA, macro search, best guess

macro search, best guess, vba code: Hello Danny. You only put a code snippet up, so I don t know why you perform a second search when the first search is successful or What your WITH qualifier is, so I don t know if you are searching on two different sheets or one. Here is my best guess....

Excel: Find and Replace, paste values, a2

paste values, a2, texts: You can use an additional column and the concatenation formula. For example, if the data is on column A, a formula like this on column B: =A2 & AC will transform the data as you want. After that, you can copy the B column and paste values (menu edit-...

Excel: Find the right most number in a row, days of the month, alternate address

days of the month, alternate address, dfas: Hi Phil, How are you? Please try the following: Sub righmost() Dim sourc, dest Set sourc = Range( e1:ai1 ) Set dest = Range( bb1 ) J = sourc.Columns.Count Do While J = 1 And Application.IsNumber(sourc.Cells(1, J)) = False J = J - 1 Loop If...

Excel: Find a row with Macros, excel spreadsheet, iitbombay

excel spreadsheet, iitbombay, macro recorder: Hi Gustavo, Not everytning can be done my RECORDING of a macro. You may need to modify the recorded macro sometimes. Here are some hints. If you find them insufficient to work it out yourself, please send sample sheets with exact ststement of requirement...

Excel: Find a row, selection range, range selection

selection range, range selection, excel spreadsheet: Gustavo The macro recorder will only take you so far and it does not facillitate all the functions available in VBA. Here is a simple demo...it represents one way to find a value in a column of data or as you say go to a certain row. On a new spread...

Excel: Find/Search, newfx, eastman kodak

newfx, eastman kodak, ssgrx: In an unused column of Worksheet B, enter this formula in row 1 by holding ctrl + shift then pressing enter: =INDEX(WorksheetA!C:C,MATCH(A1&LEFT(B1,FIND( ,B1)-1),WorksheetA!A$1:A$100&Sheet2!B$1:B$100,0)) this will match up the new values. All you need...

Excel: Find text function, human resource department, a3000

human resource department, a3000, c1: If the names are on Sheet1!A1:A3000 and Sheet2!A1:A3000, then this formula in Sheet3!a1 will find the name entered in cell Sheet3!C1 from Sheet1: =IF(ISNA(MATCH(C1,Sheet1!A:A,0)), , See 110 list ) and this formula in Sheet3!b1 will find the name entered...

Excel: Find todays date in a range, d8, d7

d8, d7, ogilvy: Why not just change your formula in D8 to be =if(B8 today(), ,D7) then fill that all the way down the column. nonetheless, here is the code that worked for me: Private Sub Worksheet_Activate() Dim res As Variant Dim s As String, r As Range...

Excel: Find ,Update & Conditional Formating, conditional formatting, match case

conditional formatting, match case, vlookup function: The specification isn t clear enough to write a macro I m afraid - I m not sure if you need to enter details yourself in the C Log workbook - I m THINKING that the VLOOKUP function will probably be a good starting point, perhaps with the IS ERROR function...

Excel: Find & Update, cell b1, fimilar

cell b1, fimilar, sheet1: in EMP log, in an available column, say E, enter this in cell E1: =INDEX( [updated emp log.xls]Sheet1 !$B:$B,MATCH(A1, [updated emp log.xls]Sheet1 !$A$1:$A$10000,0)) This may produce #NA errors, but it s ok for now. Fill this down as far as you need to...

Excel: Find unique records, conditional formatting, unique records

conditional formatting, unique records, tt1: I would use the COUNTIF function in a helper column (i.e. a blank column) to count the records if they match the cell (=countif(a:a,a1 for example) Any records with a one here are unique - you could also use this in conditional formatting to shade those...

Excel: Find unique records, brute force method, t3 t4

brute force method, t3 t4, column output: Hi Tomas, Its close to the weekend - so I ll give you a brute-force-method to solve the issue. You can change the input and output columns and row start as needed in the proc. Private Sub Worksheet_Change(ByVal Target As Range) Dim input_start_row...

Excel: Find unmatched data for 2 different Excel spreadsheets, robert fredrick, excel spreadsheets

robert fredrick, excel spreadsheets, mary alice: Lori The headers shouldn t matter at all. The data, in theory, should all be from one workbook when you are finished, unless there are single entries in both workbooks. The format of the data means nothing to this code, as long as the ID is in column A....

Excel: Find Last used row in a worksheet, last question, exact location

last question, exact location, addr: Not sure what you mean by to the get the address of the last row then Dim Last_Row_Addr as String Last_Row_Addr = Range( A65535 ).End(xlup).Address or Dim Last_Row_Addr as Range Set Last_Row_Addr = Range( A65535 ).End(xlup) Is this what you...

Excel: Find a value and replace with corresponding title, c2 c, abe

c2 c, abe, a2: Edward, Assuming that the column headers are in A1, B1, C1 and D1, the formula in D2 (Longest Path) should be: =LEFT(IF(MAX(A2:C2)=A2,$A$1& , , )&IF(MAX(A2:C2)=B2,$B$1& , , )&IF(MAX(A2:C2)=C2,$C$1& , , ),LEN(IF(MAX(A2:C2)=A2,$A$1& , , )&IF(MAX(A2:C2)=B2,$B$1&...

Excel: Find the left value of a sign, cell b3, e mail

cell b3, e mail, efg: Assume you have ABCD[125$]EFG in Cell B3 =MID(B3,SEARCH( [ ,B3)+1,SEARCH( $ ,B3)-SEARCH( [ ,B3)-1) will give you the 125 as a text string if you want it as a number that you can do math with =MID(B3,SEARCH( [ ,B3)+1,SEARCH( $ ,B3)-SEARCH(...

Excel: Find the left value of a sign, square brackets, digit integer

square brackets, digit integer, e mail: Your examples show a value BOTH before and also after the $ sign. Will this be the case or as the text of your post says ...from the left of the $ sign... will it ALWAYS be from the left only? And, is there other text in the cell? Is the value ALWAYS...

Excel: Find the left value of a sign, lucian, e mail

lucian, e mail, cells: Lucian Sorry, I don t know of another way other than the text to columns. There may well be another way, so I suggest you reask the question of another allexperts person and you might find a better answer. Sorry I couldn t be of more help. Richard...

Excel: Find worksheet in Excel, sheet names, cursor

sheet names, cursor, arrows: Peggy I m not sure what you know, so if I tell you something you already have knowledge of then I apologize. I ll mention two things-- The first is that you can name each worksheet, they don t have to be named sheet 1, sheet 2, etc. To do this place...

Excel: "Find", detail records, word car

detail records, word car, car range: First off, if you have a defined workbook name Truck or Cars that refers to a range, the way you d reference them in code is... Instead of this: Set MyRange = Cars It should look like this: Set MyRange = Range( Cars ) With that nugget, see...

Excel: Finding the 2nd occurrence in a string, occurance, ogilvy

occurance, ogilvy, occurrence: Bill, This finds the second occurance of p in cell A2: =FIND( p ,A2,FIND( p ,A2)+1) the Find on the right tells the find on the left to start looking after the first occurance of p. Replace each FIND with SEARCH if you want it to be case insensitive....

Excel: Finding Automatic Links, cell entries, change source

cell entries, change source, there some sort: It looks like you have Excel 2000, in my Excel 2003 the break link option is present. If that is the case, here is an URL that may help you breaking the links in Excel 2000, check at the end of the page: http://www.microsoft.com/technet/prodtechnol/office/office2000/tips/exlink2.mspx...

Excel: Finding beginning and end, array formula, mouse down

array formula, mouse down, ogilvy: Hello Angela, Assume the unique list starts in cell G2 in H2 put in =Min(if(D2:D200=G2,E2:E200)) in I2 put in =Max(if(D2:D200=G2,E2:E200)) In each case, enter the formula with Ctrl+shift+enter rather than just enter since this is an array formula....

Excel: Finding & Clearing Links in a workbook, address request, paticular

address request, paticular, xla: Links can exist in many places including names (visible and not), objects, charts, etc. It d be easier for me to send you a utility written to do exactly that. So email me your email address & request the findlink.xla and I ll send it to you. I m at bobumlas@yahoo.co...

Excel: Finding a cell associated to a selection from a drop-down, mouse pointer, cell a1

mouse pointer, cell a1, word size: Hi Marshall, Yes, Naming a cell or a range of cells is a very powerful tool used by Excel. It is really simple as well. Just select the cell such as D8 then insert your mouse pointer in the Name Box . The Name Box is located directly above the letter...

Excel: Finding the last cell, cell addresses, expert listings

cell addresses, expert listings, excel expert: Yes, there definitely is a way to do this. When macros are recorded, they record specific cell addresses. You need to write the code yourself to tell them to go to the last cell, first cell, etc. The only thing is, I m not the person to tell you how to...

Excel: Finding A Date within a range of specified Dates, letter column, finding a date

letter column, finding a date, ogilvy: Graham, twogilvy@msn.com You can send me a sample workbook with data and a complete explanation of what you want. Don t make up a simplified example using A B and C if that isn t what you really need - because I will make a formula that is dependent on...

Excel: Finding and Deleting a character, column c, letter c

column c, letter c, commas: Hi Gabe, Click on the Column that has the commas. To select an entire column you would click on the letter of the column. Such as column C you would click on the letter C of column C to select the whole column. Then go to the Excel menu and select Edit...

Excel: Finding data and going to that cell, cell c2, cell b2

cell c2, cell b2, vlookup formula: Steve I may be looking at it wrong, but can you use the find feature in the excel toolbar?? toolbar/edit/find This will allow you to find the text and go to it. As a alternative you could use the vlookup to find the text and also bring up all of...

Excel: Finding data within a range, database type program, cell c1

database type program, cell c1, cell c2: Steve, Saw your question in the rating. The answer is maybe. It depends on the integrity of the numbers in the rate column. i.e. let s say the in column B (30 yr rates) the rates are listed to 4 decimal places. The lowest rate in the range might be...

Excel: Finding data using vlookup, cell b2, bbc model

cell b2, bbc model, excel function: Hi Alex I cut my teeth of COmmodore pets, progamming in basic with a little machine code ( which Ive now forgotten lol),Im actually 500 years lol,well feels like it some times. I never personally worked on the BBC ( a great machine) but a coleague did...

Excel: Finding exceptions in a range of numbers, exceptions, a1

exceptions, a1, match: Assuming your list with missing #s is from 1-100 (change formula accordingly), enter this formula with ctrl+shift+enter: =SMALL(IF(ISNA(MATCH(ROW($1:$100),$A$1:$A$18,0))*ROW($1:$100)=0, ,ISNA(MATCH(ROW($1:$100),$A$1:$A$18,0))*ROW($1:$100)),ROW(A1)) and...

Excel: Finding intermediate age, analysis toolpak, numeric equivalents

analysis toolpak, numeric equivalents, negative signs: Use the TOOLS ADDINS menu to ensure the analysis toolpak is installed, and then use this formula =SUMPRODUCT(--(RangeToCheck =edate(today(),-1),--(RangeToCheck =edate(today(),-6)) Replace RangeToCheck with the actual range you want to check - this MUST...

Excel: Finding lowest integer using a set criteria, lowest interest rate, set criteria

lowest interest rate, set criteria, g1: Steve There are several ways that you might find the lowest interest rate. One would be as follows First create a formula that will segregate the interest rates of all those that re within your date range formula as follows +IF(AND(F6 F$1,F6 G$1),+G6,...

Excel: Finding Match, pivottables, header row

pivottables, header row, product numbers: Pat, I m not clear on the problem. If you do not mind sending me the file, I ll be happy to take a look at it in real time instead of trying to guess over this medium. Send file to xeon REMOVE flux@comcast.net. Take out the REMOVE when you paste the...

Excel: Finding a match, macro tool, macro language

macro tool, macro language, google: Jean: First of all allow me to say as kindly as possible that you re a trainer who trains people using EXCEL and you refer to the macro language, its capabilities, etc. as only a thing called a macro ? I would think a trainer would be more knowledgeable...

Excel: Finding missing Chart numbers, inputbox, max range

inputbox, max range, maximum number: something like this Sub Missing() dim CheckRng Dim minvar As Long, maxvar As Long Dim CheckLoop As Long Dim Missing As String CheckRng = A1:A6 could use an inputbox to get this minvar = WorksheetFunction.Min(Range(CheckRng)) maxvar = WorksheetFunction.Max(Range(CheckRng))...

Excel: Finding a place in a list, b54, number 23

b54, number 23, relative position: If you enter =CELL( row ,B4) into cell B4, it returns its row on the sheet, 4 . Change it to =CELL( row ,B4)-3 and it returns 1 ; B4 is the #1 cell in your B4:B54 list. If you then copy this formula down, it will return for each cell its relative...

Excel: Finding a place in a list, excel tips tricks, selection range

excel tips tricks, selection range, cell contents: 1) =cell( Address ), =cell( Row ), =cell( Column ), =cell( contents ), and a few others 2) use this: Sub inNames() For Each nm In ActiveWorkbook.Names If Not Application.Intersect(Selection, Range(nm)) Is Nothing Then MsgBox...

Excel: Finding a place in a list, array formula, b54

array formula, b54, cell reference: Hello Stu, You can do this with an array formula =SMALL(IF(B4:B54= AAA ,ROW(B4:B54)-3),1) entered with Ctrl+Shift+Enter rather than just enter. If you do that, then reselect the cell and look in the formula bar, the formula will look like this:...

Excel: Finding a range of cells based on sex, column headings, cell d1

column headings, cell d1, sue johnson: In your example, you show no way to distinguish the men from the women. So you could, for instance, add column D to your original data, and have this column identify each person with an M or an F. You should also put column headings at the top of your list...

Excel: Finding a repeating character in a string, occurrance, vba

occurrance, vba, occurrence: Here s one way: =FIND(CHAR(171),SUBSTITUTE(A1, a ,CHAR(171),2)) the char 171 is actually a « character. the a in quotes is the character you re looking for; the 2 at the end is the SECOND occurrance -- if it s a 3 it s the third, etc. A1 is the cell you...

Excel: Finding rows that add up to zero, spreadsheet, vlookups

spreadsheet, vlookups, b800: You need to use Solver to do this, and it will only find 1 solution, even of there are several. If all your #s are in A, leave B blank and in C1 enter =A1*B1 and fill down. these will all be zero. Put the sum of C in D1. Say you go to A800, for example. Using...

Excel: Finding the sum based on adjacent cell, negative numbers, excel 2002

negative numbers, excel 2002, b7: Hey Chris, You can use the SUMIF function to solve your problem. You will just need to change the ranges to suit your worksheet, but here is the formulas you would use: For summing the values next to negative numbers: =SUMIF(A2:A7, 0 ,B2:B7) For...

Excel: Finding text and inserting data in new row., dlh, deana

dlh, deana, cornflakes: Deana, You do not need VBA to accomplish this. Simply combine an IF and LEFT function into one formula to get the desired result. Like this: =IF(LEFT(J1,1)= v , v , i ) This formula, entered in D1, says that IF the first character from the LEFT...

Excel: Finding text and keeping track, array formula, cell b2

array formula, cell b2, cell b1: The following would do the formula for you =NOT(ISERROR(SEARCH( earnings warning ,A1))) which will return true or false Incidentally, an array formula could do it all in one =SUM(IF(NOT(ISERROR(SEARCH( earnings warning ,A1:A700))),1,0)) entered...

Excel: Finding width of DropDown item, len function, shape properties

len function, shape properties, size tab: You can use the shape properties of the combo box to resize the width of it, something like: Sub ResizeShape() ActiveSheet.Shapes( ComboBox1 ).Select Selection.ShapeRange.Width = 200# End Sub You need to provide it with the name of the combo box,...

Excel: Finishing time, format cells, cell b1

format cells, cell b1, cell a1: Greg I am not sure what else it could be. I ve duplicated what you want to do (using the now formula) and it is working fine as long as the formatting is ok. You can send me the formulas you are using and maybe I can spot something in them. It is best...

Excel: Fitting to a page horizontally, page tab, page thanks

page tab, page thanks, spreadsheet: You probably set the page setup to be a certain percentage. When you hide some columns it doesn t change anything. Instead of using the % in the Adjust to setting of the Page tab in page setup, use the Fit to setting. Make it 1 page wide by (leave blank)...

Excel: Fixed column widths, column widths, column width

column widths, column width, excel 97: Romana, I m not sure I m understanding your question. When I want to adjust the width of a column based on the longest character set in the column, I usually use the .AutoFit command like you see below. Or, I set the width by a fixed number like with...

Excel: Fixing in-cell dropdown for Excel Validation, data validation, suitable answer

data validation, suitable answer, menu choice: I don t remember the question (I answer rather a lot!), but I would first check to make sure that the incell drop down box is actually checked in the data validation menu choice for the cell? If this isn t the case, email me a sample file and I can check...

Excel: Fixing in-cell dropdown for Excel Validation, data validation, pivot tables

data validation, pivot tables, wits end: Seems odd that it s happened after the update, BUT it appears to be a lack of in cell drop down - go to the data validation menu and ensure that the in cell drop down option is checked - this should resolve it. My own preference for such worksheets would...

Excel: Fixing copied formulas, speadsheet, editor tools

speadsheet, editor tools, vba code: I need to see the VBA code behind the reset button to be able to help. Can you post the code here, or at least the part behind the button? In order to access the code, one way is to show the Control Toolbox, activate the design mode and right click on the...

Excel: Flag will signal indicating overdue date, conditional formatting, dalgleish

conditional formatting, dalgleish, ogilvy: Blinking is not supported, but you could color the cell Red with conditional formatting. in the menu do Format= conditional formatting. You can get more information at: http://www.contextures.com/xlCondFormat01.html Debra Dalgleish s page --...

Excel: Flag will signal indicating overdue date, due date, aidan

due date, aidan, spreadsheet: If you look at the formatting menu, you will see an option called conditional format - this is what you need to use Use the FORMULA option and put =L20 =NOW() as the formula. Set the pattern to whatever you want - sorry, blinking isn t an option!...

Excel: Flagging Data, top pub, vlookup

top pub, vlookup, colum: Yes it is possible but a lookup will not change the font color or size or appearance. You will need a macro to do what you want done. I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who...

Excel: Flagging Data, b cells, macro security

b cells, macro security, vlookup: Hi Tom How are you? What about the following? 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers- tick the two boxes at left-lower corner 2. Tools - macro - Visual Basic Editor 3. Insert module 4....

Excel: Flagging A Used Quiz Question In Excel, quiz program, paste values

quiz program, paste values, basic logic: You don t say HOW you are looking up what you want to look up. Without knowing that then you make it harder for me and remember I can not see what you see. However, you can use an IF and the ISERROR function in front of the lookup you are using. The basic...

Excel: Flagging A Used Quiz Question In Excel, quiz program, bold italics

quiz program, bold italics, paste values: I am unable to focus on exactly which page you are trying to write to, and you say you have no trouble with the lookup forumlas , so here is some code that might help you when working with multiple sheets. I hope this helps ... fill in your own names where...

Excel: "Floating" calculations, a20, spreadsheet

a20, spreadsheet, sums: They won t float , but you can always insert a line or lines above the line with the calculations. If you change the formula to this then you won y have to adjust it when you insert. Instead of =SUM(A1:A20), for example, use =SUM(A1:INDEX(A:A,ROW()-1))...

Excel: FORECASTING, info charts, excel charts

info charts, excel charts, forecast sales: Excel will happily graph any data you give it, so it sounds as though you will need to enter the data and then simply graph the parts you want to graph- having multiple graphs is probably the easiest way to do it. The sites set out below have some useful...

Excel: FORMULA, vlookup, b3

vlookup, b3, hol: various ways- the EASIEST solution I can think of is to have an extra column in the data, which is set for each column and uses OR to evaluate if HOL exists in any of the columns Failing that, use OR in the vlookup to get round nested if statements =IF(Or($B3=VLOOKUP(D$2,ALLYEAR!$A$2:$N$366,$J3+6),...

Excel: FORMULA, vlookup, b3

vlookup, b3, hol: Philip I think the formula could look like this and still function properly: =IF($B3=VLOOKUP(D$2,AllYear!$A$2:$N$366,OR($J3+6,$J3+7,$J3+8,$J3+9,$J3+10)), HOL , FALSE ) I added the FALSE part not knowing what you really want there. Hope that works...

Excel: FORMULA, array formula, blank cells

array formula, blank cells, decimal place: The only way to fix this formula is to fix the other problem formulas in the spreadsheet. Having any existing NA is going to cause all subsequent formulas to also display NA. You could use something like this on your other formulas: =IF(ISNA(existing...

Excel: FORMULA, douglas m smith, array formula

douglas m smith, array formula, array formulas: Hi John, An array formula will be needed to retrieve this information. The basic formula would be: =SUM(IF(Supplier range = Supplier name,IF(Order number range = Order number,1,0),0)) However, you would need to use Control-Shift-Enter instead of...

Excel: FORMULA, excel formula, b2

excel formula, b2, a2: I m not sure what you re asking, Mario. Could you tell me what is in a cell (and the cell it s in) and what you want your formula to calculate, and then exactly what it would say? For instance, you say how many months between two dates , but you re only...

Excel: FORMULA, excel formula, alternate solution

excel formula, alternate solution, clarification: Mario: Quick question for clarification... Try this formula for me: =DATEDIF(A1,B1, m ) The older date is in A1 The newer date is in B1 This formula essentially calculates the number of comparable months between 2 dates. For example, June 11th...

Excel: FORMULA FOR BUDGET, dear friend bob, middle numbers

dear friend bob, middle numbers, language issue: Maybe it s a language issue, but if the smaller # gets 14% and the larger gets 30% and you want an average % of 20, then the others must all get 18.6666% increase, since the average of 14,30,18.6666,18.6666,18.6666 is 20. I still don t understand.....

Excel: FORMULA in conditional formatting..., cell d2, conditional formatting

cell d2, conditional formatting, sequence 6: Hi Chris, This is a very interesting problem. I ve put more time into it than I usually do on a question. I m close but there still a problem. For data starting in cell C4, here s the conditioning format formula I ve come up with: =OR(AND(OR(ABS(C4-D4)=0.25,ABS(C4-E4)=0.25)),AND(OR(ABS(C4-B4)=0.25,ABS(C4-D4)=0.25)),AND(OR(ABS(C4-B4)=0.25,ABS(C4-A4)=0.25)))...

Excel: FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: Charles - If I ve read this right, what need to do is generate an alternate answer ( N/A ) if the result of your calculation is less than 1. You have two calculations to apply this to, your DIV/0 calculation and your Month To Date calculation. The answer...

Excel: FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: You have totally lost me. I have no idea whay you want N/A in the formulae, thst is what that will deliver rather than #DIV/0. I have no idea why you use the formula = MONTH TO DATE !$AH$4-30, what does that do. I have no idea what you do with the...

Excel: FORMULA, vlookup, hol

vlookup, hol, b3: Philip, It s hard to say. I m not sure what your overall objective is, nor what the contents are of the cells you referenced. In general, I do not see the need to use a nested if statement of you are using a vlookup. I m not sure if you ran into this...

Excel: Focus, excel 2002, tab key

excel 2002, tab key, vba: Would it be tedious to create a custom user form (which will easily be invoked on the OPEN event of the workbook) - if you could do this, the tabbing is automatic, and the textboxes can still be linked to the worksheet. It is going to be tricky (not necessarily...

Excel: Follow-Up, pwd, spreadsheet

pwd, spreadsheet, scenarios: This works, but only with Groups that you have set up prior to Protection Option Explicit Sub ProtectAll() Dim wSheet As Worksheet Dim Pwd As String Application.ScreenUpdating = False Pwd = InputBox( Enter your password to protect...

Excel: Follow up: compare column in excel, array formula, example purposes

array formula, example purposes, column c: Greetings! You are essentially wanting to do a lookup of A&B to see if it exists in C&D. The formula to do this would be as follows. Please note, this is an array formula and must be entered by pressing CTRL-SHIFT-ENTER at the same time. The completed...

Excel: Follow up to Dates, datevalue, value error

datevalue, value error, ogilvy: I gave you an answer, but I don t see why datevalue wouldn t work with the strings you show. I typed them into cells as strings and they worked fine for me. For example Aug 10, 2004 produced 38209. This is the date serial for that date, and formatting...

Excel: Follow up to Dates, douglas m smith, format dates

douglas m smith, format dates, datevalue: Hi Sara, You can use the MONTH function on a date to return the month number (1-12). Likewise, there is also DAY (returns 1-31) and YEAR which returns the year as a four digit number. However, if you want to quickly convert your text format dates to...

Excel: Follow Up Question to Copy & Pasting Range, lookat, pastes

lookat, pastes, a1: In the existing code, try changing the line that now is: Set rangeToCopy = bottomCell.Offset(-12).Range( a1:fp12 ) so instead it s: Set rangeToCopy = bottomCell.Offset(-12,-1).Range( a1:fo12 ) With this change, the code will find product list...

Excel: Follow-up Question re Excel 2007 colors, xlstart directory, classic colors

xlstart directory, classic colors, allen wyatt: 1: create a workbook in Excel 2003 which has the full color pallette in A1:H5. Open this workbook in XL2007. Colors are the same. You can use the interior property in VBA to copy only the color you want: Thisworkbook.Sheets(1).Range( F12 ).Interior.Color...

Excel: Follow Up, cell b2, cell a1

cell b2, cell a1, b12: Let s try with this one: =IF(B2-18 =0,B2-18, Out of range ) As you see here, one way of achieving that goal is to make the same calculation that you want to check in the condition part of the IF function, and if it fulfills the condition, do the calculation...

Excel: Followup to 255-character limit question, excel tips tricks, yellow orange

excel tips tricks, yellow orange, character limit: Start here: http://www.contextures.com/xlUserForm01.html I m leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Aug 10 and Aug 24. If interested, go to http://www.iil.com, click on the yellow/orange Try a free webinar link on the left...

Excel: Followup to 255-character limit question, dialog boxes, text boxes

dialog boxes, text boxes, character limit: Yes -- get away from the XLM & time to learn/use VBA! The XLM technology is only about 10 years old or so!! You actually can still do it with XLM & dialog boxes (upgrade to User forms!) but you d have to have MANY dialog boxes to hold each chunk of 255...

Excel: Followup To: compare column in excel, column c, vlookup

column c, vlookup, number 1: Use Tools/Macro/Visual Basic Editor. Use Insert/Module Copy this in: Function ABCD(ABRange As Range, CDRange As Range, EF As Integer) Dim ret1(), ret2(), retcount As Integer If ABRange.Columns.Count 2 Or CDRange.Columns.Count 2 Or (EF 1 And...

Excel: Followup on Prior Question, sheet tab, jan feb mar

sheet tab, jan feb mar, case 2: IN my example, the macros should be named RunJan RunFeb etc. not Jan, Feb, etc. But either would work, you just need to be consistent. You could even do: Private Sub Worksheet_Activate() Select case month(date) Case 1: Herman ...

Excel: Followup Question, srng, arng

srng, arng, d40: Consider your lines of code: Range( E20 ).Value = WorksheetFunction.Average(STrng) Range( F40 ).Value = WorksheetFunction.Average(LTrng) This code finds puts VALUES into cells E20 and F40. I don t think that s what you want to do. Don t you want to...

Excel: Fomulas and functions, douglas m smith, cell c1

douglas m smith, cell c1, experience confidence: Hi Steve, A formula would need to reference cells to perform the subtraction. If you had 10 in cell A1 and 6 in cell B1, the formula in cell C1 to find the difference would be: =A1-B1 To format cells and data, select Cells under the Format menu and...

Excel: Footer macro dynamic file path, headers and footers, vba help

headers and footers, vba help, spreadsheets: Adding path & filename to a footer is not something particularly difficult. Have you tried recording a macro of yourself doing it manually? You know, there are buttons in the footer setup specifically for this sort of thing. I m not exactly sure why you...

Excel: Footer page numbering by Cell reference, cell reference, page numbers

cell reference, page numbers, thanks in advance: Here is one way of many Sub UpdateFooter() Dim Sh As Worksheet Dim ShCount As Byte ShCount = ActiveWorkbook.Sheets.Count For X = 1 To ShCount Set Sh = Sheets(X) Sh.PageSetup.CenterFooter = Sh.PageSetup.CenterFooter = Sh.Range( A52...

Excel: Footer space in Excel, gbc canada inc, microsoft office 2000

gbc canada inc, microsoft office 2000, using microsoft office 2000: Hi Barb, It appears that the text of your headers are static except for the customer name. It appears that all the characters (including spaces, dashed, etc.) plus that 26th customer name is past the limit. Have you also tried printing just the 26th...

Excel: Footer, slow reply, folder path

slow reply, folder path, page printing: Gina, Sorry about the slow reply technology isn t it great. I know you could do this with VBA/macros. I tried some coding with in the footer and was unsuccessful. Again sorry about the slow reply I replied once but it didn t go through and I did not...

Excel: Footers, header footer, kim kim

header footer, kim kim, setup dialog: Kim You need to select all the sheets to put the same footer or header on them all. 1. Click the first worksheet tab you want to format, then press the SHIFT Key and move to the last sheet and click on other worksheet tabs to select them 2. On the...

Excel: Footers, header footer, header area

header footer, header area, yyyy: Excel is limited in what can automatically be put into the footer/header area, and yesterday isn t one of the options - but you CAN create a macro that sets the header/footer area - this could be set to run on the open event of the workbook - something like...

Excel: Forcing Excel format - Sorting problem, numeric contents, contextual menu

numeric contents, contextual menu, thanks in advance: Assume you column is on Sheet2, column 3 at the end of your macro (after Pasting) With Worksheets( Sheet2 ).Columns(3).Cells .Numberformat = General .Formula = .Value End With this should cause Excel to reevaluate your values and see...

Excel: Forecasting Headcount, cell b2, cell c3

cell b2, cell c3, input cell: My statistics aren t up to much, but assuming you want a linear progression then C4 should contain =$C$3*(1+$B$2)^((ROW(B1)-12)/12) and this can be auto filled down - the results are 79 in C4 80 in C5 82 in C6 etc to 100 in C15 all numbers are...

Excel: Forgotten Excel password, douglas m smith, password crackers

douglas m smith, password crackers, free ones: Hi Leanne, You are going to need some third party help for this. There are lots of commercial password crackers/remover programs available, but there are some free ones. I ve never used any of them so I can t give you a specific recommendation on which...

Excel: Form Button and Relative Reference, relative reference, rest case

relative reference, rest case, reference question: Very easy to have the macro grow & shrink -- has nothing to do with the macro editing itself. You can use currentregion or range...end(xldown), etc. But it s still not clear to me what you mean by the macro referencing its location... if you want the location...

Excel: Format, format button, color button

format button, color button, paint: Eugenio yes you can use the conditional formatting feature of excel. First paint the cell that you want to apply the format to then go to toolbar/format/conditional formatting. In the conditional formatting window the first cell should read---...

Excel: Format, feasterville trevose pa, millcreek drive

feasterville trevose pa, millcreek drive, demo units: Let s say that the phone number is in cell A4. To extract only the number itself, you can use the following formula: =SUBSTITUTE(A4, Phone: , ) The above formula looks in A4 for the text Phone: and substitutes it for (a blank string). Extracting...

Excel: Format, keyboard shortcut, format menu

keyboard shortcut, format menu, style name: Never actually used styles in Excel, but a quick look in help shows Create or delete a style Do one of the following: Create a new style Select a cell that has the combination of formats you want to include in the new style. (You can specify the...

Excel: Format Button, format button, continuation

format button, continuation, cheers: Hi Omer, How are you? I tried and had no problem. Did you do the following: 1. TOOLS - CUSTOMIZE TAB COMMANDS 2. SELECT FORMAT in Categories 3. Look for the buttons you want on the right-hand side 4. Drag the button to the middle of the toolbar...

Excel: Format for Cell, microsoft excel 2002, currency format

microsoft excel 2002, currency format, mouse pointer: ))(( Hi, Jeff Chances are good that the width that column has is not enough to show the whole number and the $ sign. That s why it is properly shown when you remove the decimal places. If my guess were right, solution will be to expand the column...

Excel: Format Cell on Click, run time error, format cells

run time error, format cells, colorindex: Rose, In later versions of Excel, you can allow certain things to still occur after you protect the sheet. I m using Excel 2003; when I click Tools Protection Protect Sheets, a window comes up that allows me to check other options. I checked Format...

Excel: Format Cell Colour Automatically in Excel, format button, colur

format button, colur, font colour: Hi Michael, You can use Conditional Formatting. Select the first cell for your first example and then go to the Excel menu and select Format then Conditional Formatting... the dialog box should say.... cell value is between the input box is .50...

Excel: How To Format A Cell, maximum number, cursor

maximum number, cursor, alignment: Hi Tom, One thing you may have to manually do is to change the height of the row to display all the data. Somewhere in the versions of Excel, they changed the maximum number of characters in a cell from 255 to 1000. However, when I ve seen cells with...

Excel: Format Cells, douglas m smith, format cells

douglas m smith, format cells, vlookup formula: Hi Michael, If you rework your Saves/Payout list, you will be able to read it with a VLOOKUP formula. 0 0 55 1.25 60 1.50 85 2.00 110 2.25 135 2.50 If the above table is in cells A1:B6 and the value to lookup is in cell...

Excel: Format Cells, format cells, bonus structure

format cells, bonus structure, thanks in advance: Michael, you can use the following equation to get the result you are looking for. =IF(D2 =135,D2*2.5,IF(D2 =110,D2*2.25,IF(D2 =85,D2*2,IF(D2 =60,D2*1.5,IF(D2 =55,D2*1.25, NA ))))) It just drills down through the levels until it finds one that applies,...

Excel: Format Check, msgn, input format

msgn, input format, decimal point: Some. The format of the cell will no impact whatsoever on what the user types. I can could type Rick in the cell and that cell could be formatted as Date, for example. Right? So, using the cell s format as a basis for testing is not a good choice. I...

Excel: Format Column, query in access, math functions

query in access, math functions, column c: Lat = Left(Range( C1 ).Value, 2) + Application.Round(Mid(Range( C1 ).Value, 4, 9) / 60, 5) Lon = Left(Range( D1 ).Value, 2) + Application.Round(Mid(Range( D1 ).Value, 4, 9) / 60, 5) Or: For each thing in Range( C1 ,Range( C65536 ).end(xlup)) Lat =...

Excel: Format cell on Click, conditional formatting, mail group

conditional formatting, mail group, vba code: Hello Rose, If you don t like using VBA (macros) then your only option would be to use Conditional Formatting based on some number value or entry you make in the cell (or a nearby cell). If you don t mind VBA then I d recommend coding the double-click...

Excel: Format of comments, keystroke combination, default format

keystroke combination, default format, input box: I m afraid I m not aware of a way of doing it automatically (although there MAY be) but you COULD have a macro assigned to a keystroke combination which asked for the contents of the comment, then set that comment on the active cell with whatever formatting...

Excel: Format a control, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, control tab: Hi Kyle, How are you? I guess that you are drawing a combo box from the control toolbox and you want to use the combo box from the forms tool bar So, 1. VIEW - TOOLBARS - FORMS 2. Draw the COMBO BOX and you get a fialog box with 5 tabs. The last...

Excel: Format for date, douglas m smith, format button

douglas m smith, format button, dialog boxes: Hi Peter, Yes, this is possible, but not through normal formatting. Select the date cells, and then select Conditional Formatting under the Format menu. In the dropdown box, select Formula Is . Enter the following formula in the edit box: =WEEKDAY(A1)=1...

Excel: Format for hours and minutes, chip pearson, cpearson

chip pearson, cpearson, mvps: Hi Peter, Let s say you want to calculate the difference between 8:00 am and 10:00. This of course comes to 2 hours. If you show this as hh:mm, to Excel that is showing 2:00 am, rather than meaning this 2 hours and no minutes is time elapsed. There...

Excel: Format protection, column widths, excel 2000

column widths, excel 2000, spreadsheet: Hello, Debi, If you ve left a cell unlocked to allow for editing, users will be able to modify the cell formatting however they want. They cannot, however, adjust row heights and column widths when the worksheet is protected, as that is considered a...

Excel: Format/Row/Autofit, autofit, excel 2000

autofit, excel 2000, culprit: If you re using merged cells, that could be the culprit -- there are problems in thie regard with merged cells. Also, a cell s display is limited to approximately 1024 characters -- any longer than this and it just won t adjust row-heights correctly all the...

Excel: Format Varying Length Decimal Places, format cells, format numbers

format cells, format numbers, number thanks: If the value is stored as a number, there is no way for excel to know whether you mean 1000111.1 or 1000111.10 the only solution would be to format the cells as Text, then enter the number (format= cells= Number tab, select Text). an alternative is to...

Excel: Formating background according to value, conditional formatting, value question

conditional formatting, value question, background colors: Sorry, as politely as possible, I don t write macros for people on this site who need one, want 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 site would. 99% of all macros...

Excel: Formating Date, cell b1, cell a1

cell b1, cell a1, first date: Hey Tonya, To get the date to repeat in all cells without typing it in, you can follow these steps. (These steps assume that your first date, Jan 02 is in cell A1. You will need to modify the steps if yours starts in a different cell): 1. Type Jan 02...

Excel: Formating Page, paul henson, excel 2000

paul henson, excel 2000, cells: A column can be one width and one width only, so you can t do it - at least, not as you have specified it - HOWEVER, you COULD merge the cells in rows 16-30 with the next column to allow you to achieve your aim (in these cells, the width of the column will...

Excel: Formating Sorted Columns, drop down list in excel, format cells

drop down list in excel, format cells, data validation: The following assumes that each column has values in it. For instance, you click on the header in C4, and in C4:C35 the cells are filled with values, and C36 is blank. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Cells.Interior.ColorIndex...

Excel: Formating a spreadsheet, page break, common factor

page break, common factor, spreadsheet: I could answer this question with a lot of questions as there are a lot of unknowns here. What I ll try to do is give you pieces of code to use and you might able to gleen what you need to build your code. Sorry about that. I may need to see an example...

Excel: Formatting, note numbers, click options

note numbers, click options, help file: I m afraid you are not going to be able to do it - at least, not easily - percentage formatting multiplies the value of the cell by 100 and adds a % sign on effectively - so to enter .22% you need to do .0022 - HOWEVER, you can stop 22 being interpreted as...

Excel: Formatting, cell d1, cell b1

cell d1, cell b1, decimal point: 1. Why the data in cell B1 to B3 is not with decimal point 1. (B1=3.0 B2=5.0 and B3=0.0) Check the formatting - it could be set to Text 2. In cell B4 the formula is =SUM(B1:B3) the result should be 13.0 but it is 0 instead. Check the formatting - it...

Excel: Formatting, current sheet, page break

current sheet, page break, thanks in advance: Hi, Upload your file using the following link: http://www.savefile.com/index.php?id=upload Once you uploaded it will provide you another link, something like: http://www.savefile.com/files/5550756 Post that link here. Regards, Sriraman...

Excel: Formatting, cell a1, colorindex

cell a1, colorindex, format button: HELLO Brian The easy way is to set up a conditonal formula for the range of cells you wish to change color relative to another value. Lets say they key value is in cell a1 and you want the font of cells a2 TO C2 interior colour ( it could eb font color...

Excel: Formatting Cells Automatically, colorindex, interior color

colorindex, interior color, cl: You d need VBA code to do this. Select all the cells involved and run this procedure: (Alt/F11, Insert Module, paste this code. Return to Excel, make your selection, Alt/F8, select ReverseIt , click Run): Sub ReverseIt() For Each cl In Selection ...

Excel: Formatting Cells, douglas m smith, validation data

douglas m smith, validation data, data validation: Hi Amer, You can limit the number of characters to a cell by using Data Validation (Data menu) but that only works with direct data entry. For this problem, you should use Text to Columns. Paste the entire text string into the cells where you only...

Excel: Formatting a cell for latitude/longitude data, latitude and longitude, chip pearson

latitude and longitude, chip pearson, latitude longitude: Hi Meg, I could not even begin to do better than one of the real excel experts Chip Pearson . Check out his page on Latitude and longitude. I believe he explains everything that is possible and the easiest way to display latitude and longitude. http://www.cpearson.com/excel/latlong.htm...

Excel: Formatting cells in Excel for time, billing program, decimal number

billing program, decimal number, decimals: Barb Time is always tricky to work with becaue of the formatting etc. In your example of 6:41 Am in and 5:04 Pm out if both are formatted the same and you subtract one from the other you will get the answer .4326 (if the answer is formatted as number...

Excel: Formatting cells, cell a2, consecutive number

cell a2, consecutive number, feature type: If you just need to automatically create a consecutive number, and you have the number 1 in cell A1, then you would put a formula in cell A2: =A1+1. You can do this manually using the fill feature. Type the number 1 into a cell. Highlight that cell...

Excel: Formatting customs cells in excel, spread sheets, cells

spread sheets, cells, customs: Formatting can t affect the value of a cell. You could use a custom format so that if the cell value is #N/A, the font would be white, and appear invisible. If you want the value to be zero, you have to do it formulaically. For instance: =IF(ISERROR(A1),0,A1)...

Excel: Formatting Date Cells, using microsoft office, microsoft office 2003

using microsoft office, microsoft office 2003, numeric value: The first thing I think I would do would be to format the column as a NUMBER - this would highlight any cells that are being treated as text as the cells would not change to a numeric value. It is my suspicion that part of the problem is that some cells are...

Excel: Formatting In Excel, format feature, b1

format feature, b1, formatting: Tim If the formatting is different in each of the two cells that you are combining the data from, it won t be possible to have two formats, but if not it can be formatted any way that you want, using the format feature. Remember the formula +A1& &+B1...

Excel: Formatting an entry, range target, format option

range target, format option, input data: Sorry, I rushed the previous reply a bit - this macro is entered pretty much as you said (I use Alt F11 to get into the VBA editor, then choose the worksheet from the menu choice, and set it to change on the right hand side. The macro doesn t show on the...

Excel: Formatting error message, revision 3, formating

revision 3, formating, microsoft excel: The message means what it say - I can only find this documented in Excel for mac, but even if this ISN T your platform I would expect the same solution to work:- Error message: Too many different cell formats in Excel for Mac Article ID : 213904 Last...

Excel: Formatting in excel, conditional formatting, format button

conditional formatting, format button, menu format: Hi Nathan, I apologize for my slowness in responding to your question. I am having a problem with posting answers in that my answers keep failing to get sent. I sent an answer yesterday, but now I see that again it never got posted. The easiest way to...

Excel: Formatting in excel, left hand side, format button

left hand side, format button, fourth column: Hi Nathan, You can do this pretty simply by completing the following steps. 1) Click on the square above the 1 and to the left of the A at the upper left hand side of the spreadsheet. This will select the entire spreadsheet. 2) Select Edit from the...

Excel: Formatting excell cells to avoid emtpy space in cell, left hand corner, paste values

left hand corner, paste values, paste data: I think you have answered your own question right here. Turn off the Wrap Text feature. If you want to do this to the entire worksheet, select all cells by clicking on the little square in the upper left hand corner of the worksheet (to the left of the letter...

Excel: Formatting hours and minutes, box hh, negative time

box hh, negative time, peter answer: Peter If you mean showing it as - 10.00 pm 2/3/2004 then no. Excel can t handle negative dates. If you just want a - in front of the text you will have to modify the format into something like this in the Custom section of the Format cell box - hh:mm...

Excel: Formatting hours and minutes, cell a1, negative time

cell a1, negative time, cell b1: Peter, Unfortunately, Excel (and all Microsoft products) cannot handle negative times. However, I have found a way around it. For example, Assume that you have 08:00 in cell A1 and 20:00 in cell B1. Taking the difference, one would expect -12:00. As...

Excel: Formatting individual items inside a listbox, vba programmer, eval version

vba programmer, eval version, internet explorer: Aubrey, There is no standard way to do this that I am aware. You could make a custom ListBox with a Class Module, but that s not a task for the casual VBA programmer like myself. Betting that someone has already ventured down this path, I put excel listbox...

Excel: Formatting MsgBox, score range, documents and settings

score range, documents and settings, grade worksheets: Hi Vivek, I think you will find this formatting more pleasing, and the code more compact. The addition of the With statement enabled qualifying the worksheet without typing it multiple times. Also, I eliminated the path from the file SaveAs since the ChDir...

Excel: Formatting Multiple Workbooks (Files) all at once, column width, data structure

column width, data structure: There is one method I can think of that fits your situation (separate workbooks; not in the same file). 1) Make all of your formatting changes to just one workbook. 2) Select the entire worksheet (or workbook if there are multiple pages). 3) Copy the...

Excel: Formatting Multiple Workbooks (Files) all at once, folder test, sheet names

folder test, sheet names, sheet1: I will try to go step by step, if there is anything that it is not clear please let me know. First, let s set the infrastructure and define what we will do. In this example we will format a range in the first worksheet in all the Excel files inside a folder...

Excel: Formatting Multiple Workbooks (Files) all at once, test ie, test folder

test ie, test folder, documents folder: The reason may be that the file that have the code is not placed in the My Documents folder (or the folder that contains the test folder). Try to save it there and run the code after you have saved it, if it still doesn t work please send more details about...

Excel: Formatting Multiple Workbooks (Files) all at once, empty template, format painter

empty template, format painter, easy solution: Well, without VBA you will never get the same easy solution as you have with worksheets, for the reason that you cannot extend the selection between workbooks. The solutions that you have can be either the format painter, by selecting all the work done in...

Excel: Formatting numbers for merge document, excel spreadsheet, data source

excel spreadsheet, data source, decimal point: Dolly See if these steps work 1. With the main document open, click the Tools menu 2. Select Options… 3. On the General tab, select Confirm Conversion at Open 4. Click OK When you select an alternate data source such as Excel, it will ask you to...

Excel: Formatting Page, format cells, paul henson

format cells, paul henson, column line: Paul To size the rows, paint the rows that you want to size Clic on toolbar/format/row height Put in the height that you want the rows to be. then clic ok. Or you can drag the row line to the size that you want. To size the columns it is easiest...

Excel: Formatting page, cell b1, cell a2

cell b1, cell a2, format cells: Paul Maybe I didn t make my answer clear. As an exercise let me have you do the following. Open a brand new worksheet Paint cell A1 and B1 While these are still painted Go to toolbar/format/cells/alignment and clic the merge cells box. As you...

Excel: Formatting a spreadsheet, header footer, macro code

header footer, macro code, page break: Hey tcvb, This macro should solve your problem. It searches column A for the word Page and inserts a page break when it is found. You would modify the column to whatever column your Page values are in. Sub Macro3() For Each rCells In Range(...

Excel: Formatting a spreadsheet, page break, free time

page break, free time, spreadsheet: No, sorry. I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to write. ...

Excel: Formatting a spreadsheet, c cells, preak

c cells, preak, page break: You can put this in your personal.xls and assign it to a toolbarbutton: Sub InsertPageBreaks() Dim FirstAddress As String, c As Range Set c = Cells.Find( Page , Lookat:=xlPart) If Not c Is Nothing Then FirstAddress = c.Address ...

Excel: Formatting spreadsheets, unused cells, blank cells

unused cells, blank cells, reply thanks: Sorry for the delay in replying to you - the issue seems to be that the sheet has blank cells to the right of the actual data - two fixes I can think of - firstly, set the sheet to one page wide by as many pages tall as needed - in code With ActiveSheet.PageSetup...

Excel: Formatting spreadsheets, page break, spreadsheets

page break, spreadsheets, free time: No, sorry. I do not write macros/code for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to write....

Excel: Formatting Text to Column for ALL worksheets, array array, autofit

array array, autofit, f8 key: Laraine, the code actually looks good and functions fine when I use it, so I am not sure where the problem is arising on your end. I would suggest compiling the project to see if it can find any problems, and then if not, check it by using the F8 key to walk...

Excel: Formatting Text to Column for ALL worksheets, array array, selection commands

array array, selection commands, autofit: Laraine, I was not able to find anything wrong with your code. I did amend it a bit as you ll see below by taking out some of the unnecessary .selection commands, but both your code and my amended code ran fine in a sample workbook I put together with...

Excel: Formatting telephone numbers in Excel, dashes, parentheses

dashes, parentheses, telephone numbers: Whether or not the form is protected is not an issue. It sounds like you formatted the cell correctly and it should work. What you type also matters - this formatting should work if you type only numbers. If you type any dashes, spaces, or parentheses,...

Excel: Formatting worksheet tab, excel 2002, sure what version

excel 2002, sure what version, angie: Hi Angie, I m not sure what version of Excel you are working with, but in Excel 2002 you can do this by following these steps: -Right click on the tab name that you want to format -Select Tab Color -Choose the color you would like for this tab -Click...

Excel: Formatting, target value, sheet tab

target value, sheet tab, zeros: you would need VBA, not formatting to do that. Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value 1 Then Application.EnableEvents = False Target.Value = Target.Value...

Excel: Forms, cell position, combobox

cell position, combobox, combo box: You aren t clear in your question as to what sort of form you are referring to, but I ll assume we are talking a form in VBA - if so, use the OPEN event of the workbook to fire up the form. Don t worry about the current cell position (although activecell...

Excel: Forms, excel forms, approach web

excel forms, approach web, public folder: Hi Christy, Yes, this is something commonly done with Excel. And I should mention that Microsoft Word also has a nice Forms capability that includes checkboxes, textboxes, etc. Word might be preferable if the Form includes a lot of formatted text and the...

Excel: Forms in Excel, column line, format features

column line, format features, column width: Becky I m not sure what ou need to do in working outside the column limitations so its kinda hard to be specific with an answer. It sounds like maybe you need to check out some of the cell format features go to toolbar/format/alignment, and ck out...

Excel: Forms (option button, chech box), visual basic programming, control toolbox

visual basic programming, control toolbox, option buttons: Hi Martin, For simple checkboxes and Options you would go to the Excel menu and select View then Toolbars then Forms from the Forms toolbar there is a checkbox and an Option buttons that you can place on the Excel worksheet. Checkboxes work independently...

Excel: Forms type ListBox multiple selection, listboxes, ogilvy

listboxes, ogilvy, line 6: Try something like this: Sub ListBox1_Change() Dim Msg As String, i As Integer Dim s as String s = Application.Caller Msg = For i = 1 To 7 If ActiveSheet.Listboxes(s).Selected(i) Then Msg = Msg & Activesheet.Listboxes(s).List(i) &...

Excel: Forms, cell position, type f1

cell position, type f1, combo box: Hi Mohamed! How are you? Please try the following 0th Type the list of optins in F1:F6 1st TOOLS-MACRO-VBE 2nd INSERT-USERFORM One Combo Box and 2 commandbuttons 3rd Select the combobox View- Properties window Type F1:F6 in ROWSOURCE 4th INSERT...

Excel: Formual to track time, format cells, cell a2

format cells, cell a2, menu bar: Let s say that cell A1 reads 8:00 AM and that cell A2 reads 4:00 PM . Follow these steps to find the difference in hours. 1) Enter this formula in another cell: =(A2-A1)*24 2) On the menu bar, go to Format/Cells. 3) Click the Number tab. 4)...

Excel: Formula, csv format, csv file

csv format, csv file, comma separated values: Thanks for the nice feedback, Jeff. I forgot to answer your second question. CSV stands for comma-separated values. Let s say you have some data that looks like this: (A1) Mary (B1) Anderson (C1) Chicago (A2) Joe (B2) Smith (C2) New York If you save...

Excel: Formula, ogilvy, windows xp

ogilvy, windows xp: Teresa Assume A1 holds the date =Date(year(A1)+5,Month(a1),Day(A1)) if you want to subtract 1 day =Date(year(A1)+5,Month(a1),Day(A1))-1 if you want the last day of the previous month =Date(year(A1)+5,Month(a1)+1,0) -- Regards, Tom Ogilvy...

Excel: Formula, decimal portion, input format

decimal portion, input format, time values: Understanding date/time values in Excel can be a bit tricky if you re just starting to explore. Armed with some knowledge of how the values are stored you can come up with logical equations to sum and add the values you are entering. Assuming you re...

Excel: Formula, ofc 2003, b31

ofc 2003, b31, b14: Teresa, The first formula worked fine for me in the month of February. for the second question based on the limited information provided: =DATE(YEAR(B31),MONTH(B31)-ROUND(C31/30,0),DAY(DATE(YEAR(B31),MONTH(B31)-ROUND(C31/30,0)+1,0))) worked for...

Excel: IF THEN Formula, h11, n11

h11, n11, parenthesis: Christine The formula does not look correct. You are limited to 7 IF s in one formula. You only have 4 so that should not be a problem. However the formulation appears to be better suited to the IFAND format. For example isn t the first statement in the...

Excel: Formula, cell a1, sheet tabs

cell a1, sheet tabs, sheet1: Hello Mike, The following formula would add together all the values in cell A1 for each sheet that falls between Sheet1 and Sheet31 inclusively. =SUM(Sheet1:Sheet31!A1) If your Sheet1 is named ProjectA and your Sheet31 is named ProjectZ then it...

Excel: About a Formula, xls files, external reference

xls files, external reference, fundamental reasons: Hello, glad to meet another Jan. :-) Where are you from? If I understand you correctly, you d like to know how to link from one spreadsheet to another. I ll explain linking to you, and if I misunderstood your question, please write back again. Linking...

Excel: About a Formula, horizontal cells, xls files

horizontal cells, xls files, patience: This can t be done with formulas, as it s dynamic and dependent on when you enter the data. You would have to have a macro do it, and it d be better to run the macro from a button on file d to pick up the values from a, b, and c. Much of this can be recorded,...

Excel: ? on Formula, douglas m smith, sumproduct

douglas m smith, sumproduct, hi amy: Hi Amy, This is going to be a little complicated but that s what happens when you mix number formats like this. Assume your times are in cell range B4:B10: =SUMPRODUCT(INT(B4:B10))+INT((SUMPRODUCT((B4:B10)-INT(B4:B10))*100)/60)+MOD((SUMPRODUCT((B4:B10)-INT(B4:B10))*100),60)/100...

Excel: Formula, cell b1, cell a1

cell b1, cell a1: ...Is that difficult to do?... Well, that all depends on how much you know about macros - that s a tough question for me to answer for you. The macro you need CAN be kinda be recorded (it will have to be slightly modified to make it the way you need...

Excel: Formula, douglas m smith, brainbench

douglas m smith, brainbench, yy: Hi Steve, It looks like you have the components configured. If you connect them like this: = LATEST 4 WEEKS ENDING &RIGHT( FDM MENS !I3,8) it should give you what you want. For completeness, if you were reading the date directly from cell as...

Excel: Formula between 2 spreadsheets, cell a1, sheet1

cell a1, sheet1, mabe: That is probably because Sheet1!A1 is formatted as Text. Format it as general and the cell where you enter the formula as general and then re-enter it. If you can figure it out, you can send it to twogilvy@msn.com. Include some explanation, because...

Excel: Formula, microsoft excel 97, array formula

microsoft excel 97, array formula, relative references: Stacy There may be a better way, but I would create a word from the two columns such as digitalincome and then sum based on the new criteria. Example cells A1 thru A4 contain descriptions and cells B1 thu B4 contain descriptions. In cells E1 thru...

Excel: Formula, exact contents, column c

exact contents, column c, b2: You don t tell me what s in column C. I can help if you supply me with EXACT CONTENTS of: B2, C2, D1, B3, C3, D2, the address where you re entering this formula, what you ARE getting as a result, and what you WANT to get as a result. When you say ...in the...

Excel: Formula, accounting

accounting: This confuses me: But only do this calculation ( S18*ACCOUNTING!$AD$10 ) IF(SUM(AT18:AV18) 56,56-SUM(AT18:AV18),0),0) ?? Guessing here, since I don t understand what you want: =IF($D18 =ACCOUNTING!$AB$2,0,IF(SUM(AT18:AV18) 56,56-SUM(AT18:AV18),S18*ACCOUNTING!$AD$10))...

Excel: IF Formula, d36, question survey

d36, question survey, happy new year: I wouldn t even use an IF formula to do that. The simple formula to calculate the bonus based on n surveys would be: =((D36 =12)+(D36 =24))/10 If you wanted to add the bonus amount to the total avearge of surveys it would be: =J36+((D36 =12)+(D36...

Excel: Formula, cell references, d1

cell references, d1, c1: You would use an IF function to evaluate first whether or not the sale price is greater than zero. =IF(A1 0, B1-C1-D1, 0) In this case A1 is the sale price, the other cell references are your sales proceeds, principal and commission. Regards, Aaron...

Excel: Formula, first transaction, last transaction

first transaction, last transaction, f7: No formula can be written to copy itself, formulas can not perform actions - they just return results from calculations. If I understand what you need then you will first need to copy the formula I am suggesting all the way to last row of where you think...

Excel: Formula, uniform naming convention, array formulas

uniform naming convention, array formulas, multiple solutions: Choni: This is one of those situations that has multiple solutions. Some require VBA, some require advanded array formulas; however, there is a simple solution that will work for most people: =SUM(Start:End!C9) or =SUM(Sheet1:Sheet50!C9) Start...

Excel: Formula, apple orange, word apple

apple orange, word apple, d1: I believe CONCATENATE() is going to be the formula of choice for this situation. In our example we will have Orange in A1, Apple in B1, Grape in C1, and Seed in D1. For the remainder of the columns to the right, E through however many you need (which will...

Excel: Formula, bad habit, column c

bad habit, column c, d3: Troy I would use a formula like in E3 =if(and(len(trim(C3))=0,len(Trim(D3))=0), ,E2+C3-D3) then drag fill down. This worked fine for me. Normally I would use a form of the simpler formula you presented, but since you say it isn t working,...

Excel: Formula, spreadsheet example, column c

spreadsheet example, column c, hth: Troy, That is odd. Are you sure that you typed the formula correctly. The IF formula should only return FALSE if the last argument is missing. In this case the last argument is D1-B2+C2. I can t find a way to get the formula to return FALSE for me. If...

Excel: Formula, douglas m smith, trucking company

douglas m smith, trucking company, formula usa: Hi Debra, Sorry about the delay. The RANK function should do what you want. If your miles are in cells B2:B100, the formula would be: =IF(RANK(B2,$B$2:$B$100) 9, ,RANK(B2,$B$2:$B$100)) This will just show the top 9. Douglas M. Smith Brainbench...

Excel: How-to Formula, celebrex 200, pharma company

celebrex 200, pharma company, cell a1: Joe, After you have done the text to columns command, run this macro to remove the other non-numeric characters. Expand / change the range in the Range statement (line 2) to the true size of your range. XF Sub NoLetters() For Each cell In Range(...

Excel: Formula, cell a1, cells

cell a1, cells, variables: Hi Erica, =COUNTIF(I2:I845, 10/15/06 ) =COUNTIF(I2:I846, Live ) Another way to do this is to use a variable . Instead of typing into the formula 10/15/06 or Live you would type in a cell such as A1. Then anything you type into cell A1 would be...

Excel: Formula, cell b1, array formula

cell b1, array formula, cell b2: Hi Dlana, You would type a formula similar to the one below, just adjust for the right range that you need. =SUM((Sheet2!A1:A10= Joe )*(Sheet2!B1:B10=100)) Then what you need to do is once you have entered the formula you need to make it an array...

Excel: Formula, database 2, digit numbers

database 2, digit numbers, minor adjustments: John: Can you send me a copy of the formula you are currently using? I browsed through my previous questions, but you would be surprised how many questions I get from people named John :). I suspect we will be able to use something similar to the...

Excel: Formula, e san juan, cell reference

e san juan, cell reference, column c: The formula you want is VLOOKUP This works as follows =vlookup(WHAT,WHERE,WHICH,LOGICAL) What is the value you want to look up (so 7369/9952 etc, but obviously putting in the cell reference is fine) WHERE is the location of the lookup table - the...

Excel: Formula, array formula, array formulas

array formula, array formulas, mouse pointer: Hi John, Here is a good formula that you can use to count unique entries with. =SUM(IF(FREQUENCY(IF(LEN(Range1) 0,MATCH(Range1,Range1,0), ),IF(LEN(Range1) 0,MATCH(Range1,Range1,0), )) 0,1)) I copied this formula from the following site. ...

Excel: Formula, type xxx, happy new year

type xxx, happy new year, occurances: Hi Selena Happy New Year!! I am sorry but I cannot understand well what you mean. But, please try the function countif e.g 1. Type the data you want to count in A1:A10 2. Type xxx in G1 3. Type =countif(A1:A10, G1) Sorry but I think that you need...

Excel: Formula, array formulas, array formula

array formulas, array formula, double array: Hi Selena, Sorry about the headache. It is probably because it is not intuitive to know how to do double array formulas until someone has shown you. That is how I learned about them. If you want the formula on the same worksheet as the column D &...

Excel: Formula, array formula, sheet1

array formula, sheet1, yyy: Selena, You need to use an array formula. Your example isn t very specific so you will need to adjust it to suit your specific need. The following formula will count each row in Sheet1 (from row 1 to 10) where the value in column D= xxx and the value in...

Excel: Formula, occurances, sheet1

occurances, sheet1, spreadsheet: The simplest way is to add a column that concatinates columns D and E of the first tab. For instance, on that first tab, enter in cell F1 the formula =D1&E1 and copy down the column. Then, on the second tab, you can use the formula: =COUNTIF(Sheet1!F:F,...

Excel: Formula, cell a1, digits

cell a1, digits, len: Manny: Presuming the number you mention is in cell A1, you could use this formula: =RIGHT(A1,5) It takes only the right 5 characters from a string. Just in case you need to remove everything to the left of a dash (ie, the number of digits to the...

Excel: Formula, column c, windows xp home edition

column c, windows xp home edition, windows xp home: Hi Gerry, I m really sorry for the delay, but thought I had answered this question. Here s how: First put your beginning balance in column C - Let s say the checkbook starts at row 2. This formula goes in C3: =c2+b3-a3 Copy this formula down...

Excel: IF Formula, null string, sum total

null string, sum total, a2: Hi Dennis, Yes, this is possible. For example, if the two numbers are in cells A1 and A2, then a formula that would do this would be: =IF(AND(ISNUMBER(A1),ISNUMBER(A2)),A1+A2, ) This simply yields a null string if either number is non-numeric. ...

Excel: Formula, b1, a3

b1, a3, a1: Robert: Using just your data above, this formula provides the expected result: =SUMPRODUCT((A1:A3= A )*(B1:B3= X )) That being said, you will need to modify the formula so that the range matches the number of rows actually used...ie, instead of...

Excel: If Then Formula, digit value, translation table

digit value, translation table, b cell: What you don t tell me is where I get the translation table from, but =mid(a1,9,1) would return the 9th character from A1 - if it s the 7 you want to see, then this formula on it s own is enough, otherwise it would need to be combined with a look up table...

Excel: Formula, sign checks, d9

sign checks, d9, ray ray: Ray - The formula you want is SIGN(). SIGN checks a number, and returns a 1 if it s positive, a 0 if it s 0, and a -1 if it s negative. So for your first equasion, determining if the amount is positive or 0, you just test if the number is negative: =IF(SIGN(D9-D4)=-1,...

Excel: IF Formula, cell a1, logical function

cell a1, logical function, switches: You can nest IF formulas, and use the logical function AND, in a way like this: =IF(A1 =400,A1*1.05,IF(AND(A1 600,A1 =800),A1*1.03,IF(A1 =600,A1*1.04, Out of range ))) Other option, that may be easier to read, is to directly use the logical comparisons in...

Excel: Formula, translation table, translation 2

translation table, translation 2, vlookup: Usually, you can create a translation table, that is basically a range of two columns where you relate the letters in one column and the numbers on the other. Once you have it created, it is usually useful to name it (menu Insert- Name- Define), as it will...

Excel: Formula, format cells, input number

format cells, input number, ogilvy: Robert, Select the cells you want to have that behavior do Format= Cells, then select the number tab and go to Custom. Put in a format like: [Red][ 300]General;General General could be a specific format such as #,##.00 as an example. -- Regards,...

Excel: Formula, conditional formatting, input number

conditional formatting, input number, format option: Robert Select the cells that you want to highlight if they go above 300 then go to Format Conditional Formatting and condition 1 use Cell Value is Less Than 300 and change the format option to cell color red. This will make all cells less than 300 red...

Excel: Formula, a4 a6 a8, indirect address

a4 a6 a8, indirect address, cell b1: Prem: Try placing this formula in cell B1: =INDIRECT(ADDRESS((ROW()-1)*2+1,1)) Place this formula in cell C1: =INDIRECT(ADDRESS((ROW()-1)*2+2,1)) Copy the formulas down as far as necessary. Please let me know if you have any additional questions...

Excel: Formula, data columns, value error

data columns, value error, quickview: =SUMPRODUCT(--( All Actions (2) !P4:Q341 =DATEVALUE( 7/1/2007 )),--( All Actions (2) !P4:Q341 =DATEVALUE( 7/31/2007 ))) +SUMPRODUCT(--( All Actions (2) !T4:U341 =DATEVALUE( 7/1/2007 )),--( All Actions (2) !T4:U341 =DATEVALUE( 7/31/2007 ))) +SUMPRODUCT(--(...

Excel: Formula, cell b1, cell a1

cell b1, cell a1, cell c1: Hi Kim, You can use nested IF functions to evaluate the sales % of a product. You want to evaluate the least percentage first and work your way up to the higher percentages. This is the basic structure of nesting IF functions. In this example I placed the...

Excel: Formula, working solution, f1 key

working solution, f1 key, pivot table: My suggested book would be the F1 key - this gives online help. It s worth searching and learning how to use this facilty. The rest of your question is slightly hazy to me - it s SOUNDING as though a pivot table may be the way to go, but I m probably wrong...

Excel: Formula, 2c1, parenthesis

2c1, parenthesis, b1: The invalid name is likely due to using { and [ in your function...Excel requires all parenthesis. In addition, PI has to be entered as PI()....with parenthesis at the end and nothing in them. I did write this formula, based on the information you provided:...

Excel: Formula, row labels, middle values

row labels, middle values, field settings: Quickest way would be to use a pivot table - use the data menu, then pivot table and chart report - follow the wizard, then drop the field you want onto the left (row labels) and the middle (values - in this instance a count) IF it comes up as SUM instead,...

Excel: Formula, f3, parentheses

f3, parentheses, cel: Hi Graham, This one is going to require a follow up from you. From what I can tell, #1 and #4 can be combined to be F3 D3=YES and #3 and #6 can be combined to be F3 E3=NO. Where I struggle though, is with your inbetween conditions. What if D3 is bigger...

Excel: Formula, straight text, sum function

straight text, sum function, concatenation: I m not sure how the 97 is arrived at, so I ll take it as straight text - you BASICALLY need to use concatenation, which does exist as a formula but is easier to use AND SPELL (!) when done as & = 97%/ & text(sum(a:a), $0.00 ) as an example this...

Excel: Formula, cell a2, input sheet

cell a2, input sheet, column c: Kathy, Yep, pretty confusing! I wouldn t try to do this with a single formula. Even if you could figure it out, it d be so whacked that no one could ever decipher it. Here s how I would do it. Let s say that your dates start in cell A2 and the number...

Excel: Formula, dollar figure, lease term

dollar figure, lease term, expiration dates: Teresa, Assume the date rent starts is in A1 and the monthly amount is in A2 in A3 =((Date(year(A1),month(A1)+1,0)-A1)/Day(Date(year(A1),Month(A1)+1,0))) * A2 This excludes the being date, so to add that in if you want it is =((Date(year(A1),month(A1)+1,0)-A1+1)/Day(Date(year(A1),Month(A1)+1,0)))...

Excel: Formula, help windows, dollar figure

help windows, dollar figure, lease term: Teresa, Assume the date rent starts is in A1 and the monthly amount is in A2 in A3 =((Date(year(A1),month(A1)+1,0)-A1)/Day(Date(year(A1),Month(A1)+1,0))) * A2 This excludes the begining date, so to add that in if you want it is =((Date(year(A1),month(A1)+1,0)-A1+1)/Day(Date(year(A1),Month(A1)+1,0)))...

Excel: Formula for Adding numbers sequence, pic id, adding numbers

pic id, adding numbers, excel formula: Philip, Hope you are doing well. You haven t mentioned how you are using it - via Excel formula or via a macro Either way, there is no direct way. The best way is for you to construct the file_name in a loop or something 1. If you are using it in Excel,...

Excel: Formula Array in VBA, string s1, blank row

string s1, blank row, object error: Hello Tom, That was a typo on my part - didn t completely clean up copying your formula - my fault. Thanks for noting it on my rating. Good luck with other experts on future questions. Dim s as String, s1 as String s = _ =SUM(IF(FREQUENCY(MATCH(R[-4]C[7]:R[-1]C[7],R[-4]C[7]:R[-1]C[7],0),MATCH(R[-4]C[7]:R[-1]C[7],R[-4]C[7]:R[-1]C[7],0))...

Excel: Formula Autofill, f4 key, absolute reference

f4 key, absolute reference, b7: You need an ABSOLUTE reference which is $A$1 - which you can do by pressing the F4 key with the cursor on the A1 in the formula bar (or just type the dollars) For reference $A1 would always refer to column A, but variable rows A$1 would always refer...

Excel: Formula for Averages, array formula, array formulas

array formula, array formulas, mouse pointer: Hi Billy, What you need is an array formula. An Array formula is a formula, but instead of pressing the Enter key you need to have your mouse pointer flashing within the formula up at the top of the excel spreadsheet on the formula bar and then press...

Excel: Formula "auto change" when row added, autosum, auto change

autosum, auto change, cell reference: Thanks for the comments. Note: You should click on any row within the table that is in the formula to insert a row, not necessarily the Total row & the new row should be included. ####################################### When using Autosum, if you...

Excel: Formula based on how another cell is formatted., conditional formatting, employee schedules

conditional formatting, employee schedules, jaret: Jaret, There is no way to make a formula based on color. If you really need to do it, I would suggest writing VB code. However, in this instance, I think you need to see whether you can workaround by using a entering Y/N rather than color coding it directly....

Excel: Formula to calculate vacation - USA, douglas m smith, vacation schedule

douglas m smith, vacation schedule, brainbench: Hi Cory, Sorry about the delay. If you build a table with the above values you can retrieve them with VLOOKUP. The table would look like this: 0 0 1 60 2 112 5 136 10 168 15 224 If this table were in cells A1:B6, then you could...

Excel: Formula for calculating sunshine minutes?, sunrise and sunset times, sunrise time

sunrise and sunset times, sunrise time, sunrise sunset: Depends on the EXACT nature of the way you ve entered the times. If A1 has 6:19 AM and B1 has 8:41 PM, then this formula will give the 862 minutes: =60*24*(B1-A1) However, once you enter the formula you will have to reformat it as General or it ll look...

Excel: Formula for cemetery plot layout, douglas m smith, plot layout

douglas m smith, plot layout, feet and inches: Hi Barbara, Here s the basic formula to display inches as feet and inches. Two separate operations are performed to get the feet and inches, and then they are combined together the feet/inches notation. =INT(A1/12)& &MOD(A1,12)& 38 in cell...

Excel: Formula to change cell value color, conditional formatting, cell reference

conditional formatting, cell reference, numerical value: You use conditional formatting! Under the Format Menu, choose Conditional Formatting. A dialogue box appears. Choose [Cell Value Is ] {greater than} 50. You can type 50 or make it a a cell reference. that way if your conditional format value changes...

Excel: Formula for combining data, city state zip, excel 2000

city state zip, excel 2000, copy paste: Assuming the data is columns A, B, & C, and you want the combined data to be in column D, then =A1& &B1& &C1 Copy and paste as far down as you need. Then you copy and paste all of the formulas on top of themselves using Copy, Paste Special As Values....

Excel: Formula for compounding daily interest, effective interest rate, amortization schedule

effective interest rate, amortization schedule, analysis toolpak: If you ve installed and enabled Excel s Analysis ToolPak, there s a function =EFFECT() that will calculate the compound interest for you. If not, you can do it this way: Say the annual interest rate is 5%, so monthly interest rate is 6%/12 or .5%. The...

Excel: Formula to count cells with certain text & other criteria, task numbers, formulas in excel

task numbers, formulas in excel, personnel training: I would create a new column (hidden if necessary) on the Personnel Training Worksheet that concatinates Task Number and GO/NOGO. So if Col B is the Task Number (e.g. 6) and Col D is GO/NOGO, put in Col E the formula =B1&D1 so it d return for instance 6GO...

Excel: Formula to count cells, a101, b90

a101, b90, a100: This should be a start. You might need to fine tune it to get the exact number you want: =if(row() 101,row()-101,100-row()) If you want it to dynamically find the location of the cell with median in it, that would take more work, but I didn t understand...

Excel: Formula Dilema, douglas m smith, column letter

douglas m smith, column letter, row numbers: Hi John, There is a neat function called INDIRECT that will convert strings into a reference. If your sheet name is in cell A1, then this formula will constuct your reference: =INDIRECT($A$1& !$W$9 ) Using it in your formula: =IF(AND($A19=TRUE,INDIRECT($A$1&...

Excel: Formula in Excel, datevalue, error data

datevalue, error data, ogilvy: Assume Jan-06 mean January 2006 and the values are stored as actual dates. =Sumproduct(Data!$D$1:$D$69,--(Month(Data!$E$1:$E$69)=1),--(Year(Data!$E$1:$E$69)=2006),--(Data!$F$1:$F$69= L )) You must look at the same number of rows in each section. Since...

Excel: Formula Excel 2002 EASY, incorrect entries, excel 2002

incorrect entries, excel 2002, iname: Assuming you are counting the number of correct and incorrect entries, the formula would be: NumberOfCorrectEntries/TotalEntries. However, the actual implementation depends on the layout of your spreadsheet. If you want send it to me at rmadsen@iname.com....

Excel: Formula in Excel, vlookup function in excel, blank rows

vlookup function in excel, blank rows, numbers report: A thorough explanation would take longer than I have right now, but I ll try to get you started. I m assuming you have a table of information that you will be looking up a value in - I think this is your REPORT B. This table needs to be set up with no blank...

Excel: Formula for Excel, format cells, current date

format cells, current date, sp3: From the menu, click Format, Cells. Select the Number tab. Select Date and choose any date format. Then choose Custom and type YYYY as the custom format. So, if you have 3/24/2006 in a cell then it will appear as 2006 after applying the format above....

Excel: Formula error, cell b1, cell a1

cell b1, cell a1, conditional formatting: The answer you got would never work as the piece C1 2/29/4 is comparing c1 against 2 divided by 29 divided by 4, not a date. In any case, to see if a cell is blank by a given date, you would need a macro (VBA). Right-click on the excel LOGO near the file menu...

Excel: Formula for excel (office2000), value entries, text entries

value entries, text entries, office2000: If the goal is to just count text entries in a column, you could just use the COUNTA function. =COUNTA(A1:A100) The above would return a count of entries in the column regardless of text or value. If you have multiple text entries in the column and...

Excel: Formula execution, window options, select tools

window options, select tools, apostrophe: From the main menu, select Tools, Options, View. If the box marked Formulas (under Window Options ) is checked, it will cause your sheet to show formulas rather than results, as you ve described. It s not clear to me what else could cause this problem....

Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, cell c1

cell a1, cell c1, text numbers: This would create a HORRIBLE formula, Rose, if it can be done at all. Two suggestions: 1. Split your data up. Select column A and do Data-- Text to Columns and turn it into 3 columns (or more)...and go from there to get your numbers. You can also use x...

Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, math portion

cell a1, math portion, cell c1: Well, that makes things more difficult. The two formulas you will need to get the data (one for the math portion and the other for the ga number) you need for the final formula you already have will both be about as long as your arm - too long for me to write...

Excel: Formula to find numbers within a cell containing text, numbers and spaces., cell a1, split application

cell a1, split application, cell c1: Change Function Getno(rg As Range, no As Integer) As Integer to Function Getno(rg As Range, no As Integer) As Double You mean specifically change 11 to .012? If so, put this in just before End Function: If Getno=11 then Getno=.012 This will change...

Excel: Formula/format to match certain character, question column, sap r 3

question column, sap r 3, character question: This is one UDF that uses regular expressions. I have tried with the data you have provided, and it returns positive matches for all them when setting the chunk size to 6, failing only in the Rocky one when the chuck size is set to 7. To use the function,...

Excel: Formula Help, b10, arrays

b10, arrays, a5: Can it be done? There are so many ways to do it, I m not even sure where to start... Most experts I suspect would recommend a LOOKUP style function. That s a pretty common way to attack it. Although, they d probably tell you to enter values in cells,...

Excel: Formula help pls, calcs, c450

calcs, c450, chars: There wasn t sample data, but it SOUNDS as though you need SUMPRODUCT =SUMPRODUCT(A1:A450,C1:C450) which would multiply A1 by C1, A2 by C2, A3 by C3 etc and display the total result in the cell. You DO need to specify a given range, it will not work...

Excel: Formula help pls, row header, header row

row header, header row, calcs: Thanks for rating and thanks, I appreciate them. Regarding the issue, the #NAME error usually appears when the formula name is not recognized, what version of Excel are you using? And which language? I can assure that the formula exist on Excel 2003 and...

Excel: Formula help pls, button search, calcs

button search, calcs, chars: Deborah - There is such a formula. It s called SUMPRODUCT(), and it does just what you re looking for. Rather than me try to explain it (it would be a long email!), I will refer you to the Insert Function button (it s the Fx button on the formula...

Excel: Formula help for retrieving and calculating specific information, pivot table, taxable items

pivot table, taxable items, value error: It SOUNDS as though you would be best to use the DATA menu, and then Pivot Table and chart report - this would give you the data summarised as you want it - would this work for you? If not, I can probably come up with formulas, but the flexibility of the...

Excel: Formula help, douglas m smith, column letter

douglas m smith, column letter, cell references: Hi Sarah, The formula structure would be: =(60+11)*1.5*1.06 Substitute the cell references for the values to get the actual formula. Since I don t know the specific layout of where the other numbers are, I ll give you general guidance on how to...

Excel: Formula for Interest, format cells, due interest

format cells, due interest, happy new year: Hi Alicia Happy new year to you too!!! Please try the following 1. Type 54000 in A1 2. Type 10% un B1 3. Type letter s date in C1 4. Type Payment s date in D1 5. Compute the number of days the payment was due, type in E1 =D1-(c1+10) 6. Format E1...

Excel: Formula for incremental projected data., cell a2, kudos

cell a2, kudos, weighted average: Diana: I uploaded an example here... http://home.swbell.net/nate-sus/excel/diana-incremental.xls It is slightly more flexible than an IF then formula. It allows you to weight each month (row 2). The formulas then automatically calculate based on...

Excel: Formula inquiry, excel sheet, reagent

excel sheet, reagent, excel 2002: Barrett I hate to say it but some of the logic of that formula is faulty. I can t check it right this moment, I need to hit the road, but I should be able to take another look this afternoon. The true and false segments are the same, which is obviously...

Excel: Formula to Lock a Row/Column, visual basic programming, format cells

visual basic programming, format cells, table wizard: Good morning Ed! 1) I ll start out by saying you can lock cells, rows, columns, and an entire sheet in Excel with Protection . With a selection, choose format- cells- protection and then ensure there is a checkmark in locked . Now click on tools- protection-...

Excel: Formula/macro, help windows, dollar figure

help windows, dollar figure, lease term: Not sure I understand what you want, but assuming you need to know how many days are in a month, then here is one of many ways: =EOMONTH(A1,1) The formula assumes the date, 9/24/05. in your example, is in A1. Format the cell with the formula in it to...

Excel: Formula for matching numbers, array formula, matching numbers

array formula, matching numbers, absolute reference: Simplest solution is to use conditional formatting (format menu, conditional formatting) to shade the cells IF they match the criteria - use the formula option of the conditional formatting box. A more complicated approach would be something along the lines...

Excel: Formula with multiple criteria, multiple cells, array formula, completion times

array formula, completion times, autosum: Hello Rebecca Tonks =LARGE(IF(($A$2:$A$4293= Job1 )*($G$2:$G$4293= Task A ),$E$2:$E$4293),1) LARGE(IF(($A$2:$A$4293= Job1 )*($G$2:$G$4293= Task B ),$E$2:$E$4293),1) entered with Ctrl+Shift+Enter rather than just enter since this is an array formula worked...

Excel: Formula needed please, array formula, vlookup

array formula, vlookup, certificate numbers: The question doesn t quite make sense to me - certainly you can pull in information based on certain conditions - vlookup would do this, as could an array formula - if you can mail me a sample of what you are trying to do it will be easier to give you a working...

Excel: Formula Percent Increase or Decrease, aidan, year end

aidan, year end, previous year: $L253 should be $L$253 if you need to lock in place both column and row. You don t need the SUM =X-Y will give you a value. so =(L258-$L$253)/$L$253 appears to be what you want my email if needed (eg for attachments in any follow up) is...

Excel: Formula Problema, cell a1, sheet1

cell a1, sheet1, number 3: Hi, John. The experts here aren t linked up, so feel free to ask me when you don t get satisfaction elsewhere. Here s an example. Say you ve got 2 workbooks open, called Book2 and Book3. You re putting formulas in Book2, which you want to get data from...

Excel: Formula on percentage, decimal places, ogilvy

decimal places, ogilvy, a2: Michelle, A2: 23.94 B2: 10 C2: =(A2-B2)/A2 will produce 0.582289055973267 format C2 as Percent with the number of decimal places you desire (or none at all) and will display you 58% you can drag fill this formula down the column. -- Regards,...

Excel: Formula problem, cell a2, cell a1

cell a2, cell a1, whole number: Here s an example. You have a number in cell A1, for example 2.7895. In A2, you want to round it to a whole number followed by the words per hour. In cell A2, you d use the formula: =ROUND(A1,0) & per hour which would return: 3 per hour If...

Excel: Formula problem, b5, lower bound

b5, lower bound, ogilvy: Excel will nest 7 deep in xl2003 and earlier. =IF(B5 0, ,IF(B5 =250,0,IF(B5 =500,B5*0.1,IF(B5 =1000,B5*0.015,IF(B5 =1500,B5*0.025,IF(B5 2000,B5*0.3, 2000 )))))) Note that you don t need to check the lower bound as long as it is a progressive test...

Excel: Formula problem, douglas m smith, b19

douglas m smith, b19, brainbench: Hi Dave, The best way to trap this is with the ISNA function. It is used to evaluate if there is an #N/A error: =IF(ISNA(INDEX(B19:C34,Match(B43,B19:B34,0),2)), ,INDEX(B19:C34,Match(B43,B19:B34,0),2)) This will return a if there is an #N/A error....

Excel: Formula QUestion, douglas m smith, conditional formatting

douglas m smith, conditional formatting, format button: Hi Dan, You can do the with conditional formatting. I m assuming that there will only be one entry per row from column J to column BP. Select the cells in column A and then select Conditional Formatting from the Format menu. Select Formula Is from...

Excel: Formula Question, douglas m smith, cell d2

douglas m smith, cell d2, bonus payout: Hi Jerry, This can be done with a look up table. First construct a table like this, for example, in cell D2:E6: 0% $0.00 25% $48.45 50% $47.97 75% $47.50 100% $46.55 If you put the percentage to lookup in cell A1, the formula to retrieve...

Excel: Formula Question, b5, ogilvy

b5, ogilvy, g5: Hello Michelle, You don t indicate what I am to look for in H5. Assume if H5 is empty, then you consider it not paid =if(H5= ,Today()-B5,0) or assume the amount owed is in G5, so if H5 is =G5, then consider it paid =if(H5 =G5,0,Today()-B5) ...

Excel: Formula Question, column c, warm regards

column c, warm regards, perfect solution: Dear Shawn, To my knowledge, SUBTOTAL function will not support IF as additional argument. One suggestion could be, to add column which is combination of Filtered column and indication whether the number is greater than Zero. For instance imagine, (1)...

Excel: Formula Question of dates, cell reference, difference in time

cell reference, difference in time, public holiday: Sorry for the delay in replying - it s been a public holiday weekend here in the UK, and I forgot to update my profile to record that fact. Not totally sure what you are after here, but something like =text(A1, mmmm-yy ) & - & TEXT(DATEVALUE(TEXT(A1,...

Excel: Formula Question regarding Display of Date Ranges, cell reference, difference in time

cell reference, difference in time, 11 october: Hello Weston, Let us say you have Sep-07 in cell A1. Further what is actually stored in A1 is 9/1/2007 (in US date format), so that A1 has an actual date in it. In A2 you could put =TEXT(DATE(YEAR(A$1),MONTH(A$1)+(ROW()-2)*12,DAY(A$1)), mmmm-yy )&...

Excel: Formula Question w/in SUMPRODUCT, array formula, sumproduct

array formula, sumproduct, zero value: Yes, a + is just for addition. The above formula is taking the positive total number of records in column B that have a year 2003, the positive total number of records in column L that have the month of JUL, and the amount in column I and feeding it...

Excel: Formula Question, ms excel 2000, cell b2

ms excel 2000, cell b2, a1: Jeff, I would say that you have 2 options for this. The first is to just use the formula =A1 in any cell where you want that to happen. The other option would be to write or record a macro that made that happen and place a button on the toolbar to repeat that...

Excel: Formula question, david cowling, cell a1

david cowling, cell a1, cell reference: Hi, Dave, This has to do with the process of combining two elements together. The sign is text, but the cell reference is not. (The cell reference needs to be calculated, or translated it into its value.) Whenever you combine elements, you use the...

Excel: Formula question - partial matching of any cell in a range within a string, source cells, array formula

source cells, array formula, text result: In C1 enter this formula =if(isnumber(match( * &A1 * ,B:B,0)), Found , Not Found ) then drag fill C1 down the column to match the last of the A values. that would check each value of A1 against column B and indicate if it is in at least on instance...

Excel: Formula question with times, format cells, military format

format cells, military format, military time: As long as your times are within the same day, you can just use a simple formula like: =B1-A1 to get rid of error values if either field is null you could use: =IF(OR(ISBLANK(A1),ISBLANK(B1)), ,B1-A1) For this to work, you need to make sure all...

Excel: Formula question, ms excell, ms xp

ms excell, ms xp, column names: Hi John, Unfortunately, when using this service extra spaces, coloring and column names are cut out, so I m just guessing which set of values show up in your column I. In any case, the formula that should be placed in I14 is: =COUNTIF(I1:I13, x )/ROWS(I1:I13)...

Excel: Formula never reaches 0, depreciation table, 0000000000000000000

depreciation table, 0000000000000000000: I am not sure exactly why that happens, but if you want to avoid it you may use rounding formulas where you want to make the comprobation. In the case you have run, you can use: =ROUND(A1-0.2,3) IF this precision is enough for you it will avoid the problem....

Excel: Formula reference with multiple workbooks, statistical data analysis, isna

statistical data analysis, isna, excel 2002: Indirect will work if the other workbook is open (it doesn t work to create references to closed workbooks. A1: Subject.1.xls change . . .IF(ISNA(IF(VLOOKUP($C7,[subject.1.xls]Sheet1!$A$1:$H$771,7,FALSE)=0 . . . to . . .IF(ISNA(IF(VLOOKUP($C7,Indirect(...

Excel: Formula result, ms excel 2000, format validation

ms excel 2000, format validation, result format: format won t work because it changes the FORMAT not the VALUE of the cells (I presume you mean conditional format) VALIDATION WOULD work if you put it on the correct cell - if this is applied to the cell that contains the value the formula is going to use,...

Excel: Formula to Sort a list, comma separated values, coloum

comma separated values, coloum, sort key: The way to do this, Marc, is by creating a second column to help out. Let s say that your data is in A1:A8. Follow these steps: 1) If column B is not empty, create a new, blank column at B. 2) In B1, enter the following formula: =MID(A1,SEARCH(...

Excel: Formula to Sort a list, coloum, sort method

coloum, sort method, aidan: is the @ sign contained in the cell, or is it shorthand for a gap between columns? This would make a difference. Secondly, are the lengths always known? IF @ indicates a column break AND the lengths are known, then the SUMIF function would do what you want....

Excel: Formula to Sort a list, coloum, sumif

coloum, sumif, 4 steps: Hi Marc! How are you? Please think about the following: Assuming your data is in A1:A8 1st. Type in B1 =VALUE(MID(A1,8,6)) 2nd Type in C1 =VALUE(MID(A1,1,5)) 3rd Copy B1:C1 to B2:C8 4th Type in C1 =SUMIF(B1:C8,B1,C1:C8) 5th The data-sort 6th...

Excel: Formula to Sort a list, coloum, sort feature

coloum, sort feature, e numbers: Marc I suggest that you separate the data into two columns (I.E. numbers on the first column and length in the second) (sounds like it may be in this format now) then use the data/sort feature (to sort from top to bottom in length) then use the subtotal...

Excel: Formula to Sort a list, coloum, row header

coloum, row header, field settings: Marc, I hate to say it but, wake up man! Sort of just kidding. Specifically what you are asking can t be done with formulas. However, you can use a pivot table to summarize that data instantly, though it would have to be reversed, in that it would show...

Excel: Formula to Sort a list, data cells, coloum

data cells, coloum, cell a2: Hi Marc, This is going to be a multi-part solution. First, we will need to separate out the quantities and lengths. If your combined numbers (above) start in cell A2, the formulas for cells B2 and C2 are: B2: =VALUE(LEFT(A2,FIND( @ ,A2)-2)) C2:...

Excel: If Then Formula's, logical number, last thoughts

logical number, last thoughts, hyper link: I am not so sure of what you want to achieve, what I use to do when I have to extract data from one sheet to the other is to use the formulas MATCH and INDIRECT, in the following way: First, I use an additional column to track the rows in the original worksheet...

Excel: Formula's, array formula, array function

array formula, array function, lotus 1 2 3: Assuming: your data is in A1:B20, program codes are in A, payment type codes are in B, both program codes and payment types are entered as values, you want to count how many records in that range have a program code of 1 AND ALSO have a...

Excel: Formula's, cell a2, cell a1

cell a2, cell a1, mar c: Regina I can t give you a specific formula because I don t know what you data looks like, but myabe I can give you some ideas. First the running total If you have a total that is calculated form the entries to date, then its just a matter of adding...

Excel: Formula to spread costs based on start date and end date, average salary, cell references

average salary, cell references, spreadsheet: Russ: The simplest way is to divide your annual amount by 52 and apply that amount to each week. I uploaded a sample spreadsheet demonstrating this here: http://home.swbell.net/nate-sus/excel/weekly.xls NOTE - Each cell references the annual amount...

Excel: Formula in Time, finish time, time functions

finish time, time functions, time cell: The formula is missing the last part, that correspond to what happens if the statement is false. You can try with this one: =IF(M9 =TIME(8,0,0),M9+TIME(0,10,0),M9) In this case, if the time is less than 8:00 AM, it will show the time itself (the content...

Excel: Formula for time, minute slots, cell a1

minute slots, cell a1, time slot: crystal If you have 8:00 in cell A1 this formula will add 30 minutes to it, drag the formula cell down (click and hold the mouse button down on the bottom right of the cell with the formula in it, it will show a little black box in that corner) and it will...

Excel: Formula or VBA for this ???, consolidated budget, code framework

consolidated budget, code framework, sorry in advance: I m guessing that all files are in a common area, and only those files are in that area - if so, use this code snippet t fs = Application.FileSearch With fs .LookIn = C:My Documents .FileName = cmd* If .Execute(SortBy:=msoSortbyFileName,...

Excel: Formula for getting values from other sheet, douglas m smith, mumbai india

douglas m smith, mumbai india, brainbench: Hi Sanjay, If the part numbers are unique in the lists, MATCH and INDEX can be used. On sheet one, the following formula would look for a part number match on sheet two and then return the ajacent value: =INDEX(Sheet2!$B$1:$B$500,(MATCH(A1,Sheet2!$A$1:$A$500,0),1)...

Excel: Formula not working, cell b1, cell a1

cell b1, cell a1, conditional formatting: A macro is the only way an alarm or pop up will notifiy you in this case. You could add an additonal condition in the IF statement to check to see if todays date is after Mar 1 AND the cell is empty and if so have the formula return the warning message in...

Excel: Formula works part of the time, analysis toolpak, excel 2000

analysis toolpak, excel 2000, network server: The formula you are using is in the Analysis toolpak, and therefore ONLY works if the analysis toolpak (an add-in) is installed. You can put a macro in place to check for this and load it, but it is a simple fix - it s a one-off on each machine, they just...

Excel: Formula in another worksheet, minus signs, boolean values

minus signs, boolean values, k2: Hello Jane {=SUM(( Sheet 1 !E2:E1505=E3)*( Sheet 1 !K2:K1505=K5))} This assumes the sheet name is actually Sheet 1 with a space before the 1. It if is just Sheet1 with no spaces, you don t need the single quotes. {=SUM((Sheet1!E2:E1505=E3)*(Sheet1!K2:K1505=K5))}...

Excel: Formula, hth, excel 2000

hth, excel 2000, syntax: Hi Dan, You need to use the linking formula syntax. Here s what the syntax looks like if the file with the lookup list is currently open: =VLOOKUP(A26, [HouseLoan.xls]Bank Monthly Transfers !$A$27:$B$36,1,FALSE) Here s what it looks like if the file...

Excel: FormulaArray property, array formula, proper syntax

array formula, proper syntax, compilation error: Yes but you still have syntax problems. Why do you think you need a double quote BEFORE R1C1? Diuble quotes are only needed to add the liereal text of the VBA line to the variable AND then for the proper styntax for the VBA line itself Remember the double...

Excel: Formulae, target cells, random function

target cells, random function, random value: Kay, XeonFlux = ecks-e-on-flucks There are two ways to deal with this that come to mind. 1. After you make the random number in the cell, copy it and paste it as values in the same cell. The downside to this is that you ll have to do it each...

Excel: Formulae, b9, formulae

b9, formulae, c10: Harsh, In sheet 1, lets put the following values in these cells, A2 - 0% B2 - 5% A3 - 25% B3 - 8% A4 - 50% B4 - 10% A5 - 75% B5 - 12% A6 - 100% B6 - 15% B9 - where you enter your weekly target (2100) B10 - Achieved amount (e.g. 3675...

Excel: Formulae Protection, douglas m smith, password passwords

douglas m smith, password passwords, cell a1: Hi Martin, Protection can be set for each cell. It fact, all your cells are probably set to be locked. To check / set this, select Cells under the Format menu and then select the Protection tab. If Locked is checked, then the cells protection has been...

Excel: Formulae, douglas m smith, loop statements

douglas m smith, loop statements, loop statement: Hi Kay, Probably the best way to handle this is with a VBA Do loop. Something like: Do Until Result = False Code that tests your conditions Variable Result stores True or False based on the tests Loop This type of loop runs until the tests...

Excel: Formular to count number of entries in a row, douglas m smith, counta

douglas m smith, counta, brainbench: Hi Jake, To count cells with just numbers, the COUNT function will work. To count cell with both numbers and text, use COUNTA. In your example: =COUNT(A1:E1) would be 3 =COUNTA(A1:E1) would be 5 =COUNT(A2:E2) would be 0 =COUNTA(A2:E2) would be...

Excel: Formulas, dollar values, fortunately

dollar values, fortunately: Hi, Ken, Fortunately, AVERAGE is a function that will indeed operate across worksheets (as will SUM, and some others). Let s say that your worksheets are named Sheet1 through Sheet16. The cell you want to average on each sheet is F14. The formula would...

Excel: Formulas, aidan, many thanks

aidan, many thanks, tick: Not sure I follow the question - if you have put a formula in a cell and pressed Enter, or clicked on the tick in the formula bar, the formula would then be locked in place. Obviously, if values in cells that the formulas reference change, the result in the...

Excel: Formulas, exact details, many thanks

exact details, many thanks, cells: I m not sure I follow what s happening. But try pressing the Esc key when whatever it is that s happening happens. Maybe you could describe exact details, like what cell you enter =E54-E53 in and when this shows up again (in what cell are you typing what?...

Excel: Formulas, pivot tables, pivot table

pivot tables, pivot table, sheet1: There are a few options available for this problem, the best solution would be to use a Pivot table to show a count of what joe has or any other options. If you don t know about pivot tables have a look at this link otherwise get back to me (I will be out...

Excel: Formulas, input formulas, toolbar tools

input formulas, toolbar tools, spreadsheet: Joon When you say the formula doesn t work anymore I m not sure if you mean it isn t calculating at all, or it calculating incorrectly, etc. etc. Try this --copy the spreadsheet to another workbook and see if it will calculate there. Check your options...

Excel: Formulas, variable number, g20

variable number, g20, lpn: Assuming the other cells are A3 & A4: =C1*IF(AND(A3= RN ,A4= Medicaid ),2,IF(AND(A3= lpn ,A4= medicaid ),3,IF(AND(A3= RN ,A4= waiver ),4,IF(AND(A3= XX ,A4=xyz),5))))+D1 etc. but this way you re limited to 7 combinations. For unlimited combinations, it...

Excel: Formulas, many different ways, macro security

many different ways, macro security, c7: Hi Gary, There are many different ways to accomplish this depending on the functionality you want. Here are several choices: 1. Simply eliminate the formulas of the two cells, leaving only the values behind. You can do this manually by copying each cell,...

Excel: Formulas between 2 spreadsheets, target value, target address

target value, target address, cell b1: Hi Dan, It is possible with a formula but gets more complicated than a macro. You could use something like this macro. First create a Blank workbook named Book1.xls and a workbook named Book2.xls keep them both open. Switch to Book1.xls copy the following...

Excel: Formulas, microsoft office xp, home office computer

microsoft office xp, home office computer, target address: There s no such formula. A formula returns a value to the cell containing the formula. However, you can do this in VBA. Press Alt/F11, use Insert Module, enter this: Sub MoveA1ToA2() Range( A2 ).value = range( A1 ).value End Sub But this has nothing...

Excel: Formulas, microsoft office xp, colorindex

microsoft office xp, colorindex, red cells: Hi Alma, I am not sure if you are familiar with user-defined functions, but this is how I will answer this question. You need to follow these steps: 1) In Excel, click on Tools on the top menu, then Macros, then Visual Basic Editor. 2) When Visual...

Excel: Formulas, douglas m smith, t score

douglas m smith, t score, brainbench: Hi Denine, I m not totally clear on your situation but I see basically what you are trying to do. You are on the right track logically. If you use the COUNTIF function, it should solve your problem. For example, the following formula count the numbers...

Excel: Formulas, dollar change, comparison sheet

dollar change, comparison sheet, blank cell: Lenora Ok column D should be % of difference between col B and col C. I assume you want this to be % increase from 04 to 05. By running total I m guessing that you want it to change each time you change some of the sales numbers, but I m not sure what...

Excel: Formulas, vba macros, douglas m smith

vba macros, douglas m smith, john walkenbach: Hi Pimp, I would recommend you focus on functions, formulas, and non-macro features (conditional formating, data validation, text to columns, etc.) of Excel before you get heavy into VBA macros. A great book for tips and techniques: http://www.iil.com/iil_shortdescr.asp?sku=PT248&ld=home1005...

Excel: Formulas, douglas m smith, cell b3

douglas m smith, cell b3, cell b2: Hi Jason, My first guess is that the calculation mode is set to manual. Press F9 to recalculation. Tools / Options / Calculation / Automatic to turn it back on. My second guess is that formula references might be absolute instead of relative. Example:...

Excel: Formulas not calculating, littlewing, gmail

littlewing, gmail, excel spreadsheet: Hi again Go into tools options and calculation and see if the manual calculation is selected, if it is then use automatic. I am sure you have looked at this but you never know. If this doesn t work then can you send the file to richard.littlewing@gmail.com...

Excel: Formulas and Text, microsoft excel 2000, five dollars

microsoft excel 2000, five dollars, watermark: It sounds like you re trying to recreate a watermark in a cell, am I right? It s a great idea, but you can t have two different items in the same cell. You can add a picture or watermark to the background of the entire sheet, but not a single cell. Even...

Excel: Formulas, target cell, zip code area

target cell, zip code area, vlookup: Assuming that by database you mean list of codes and names , then the easiest solution is vlookup - which is a question I ve been asked almost more than any other and have therefore created a webpage explaining this - it s at http://www.aidanheritage.co.uk/excel/...

Excel: Formulating a correct formula, gross pay, belveder

gross pay, belveder, s gross: If the tax info is on Sheet1 and the value looked up is on another sheet, you can use: =(A6-INDEX(Sheet1!3:3,MATCH(A6,Sheet1!A3:D3)))*INDEX(Sheet1!2:2,MATCH(A6,Sheet1!A3:D3))+INDEX(Sheet1!1:1,MATCH(A6,Sheet1!A3:D3)) Your table wasn t set up right for a...

Excel: Formules in VBA, traditional formulations, collom

traditional formulations, collom, misstake: Aad, When you say put the calculations in VBA I m going to assume you want to be able to rewrite your formulas with code so that if someone deletes it you can replace with VBA. This is can be done but you may have to educate yourself with the R1C1 formulation...

Excel: Fraction trouble, douglas m smith, cell a1

douglas m smith, cell a1, cell b1: Hi Barrett, I don t think you can force the fractions from simplifying but there is a workaround. If A1 contains your total tests and B1 contains the positive count, the following formula would do the trick: =B1& / &A1 This simple version will work...

Excel: Free Chart Object from Cell References, dim arr, cell references

dim arr, cell references, macro code: Here s code which will work for one series - you can adjust for the others. Run DoFormula. Sub DoFormula() ActiveSheet.ChartObjects(1).Activate xvals = buildformula(Sheets( Area2 ).Range( B25:U25 )) vals = buildformula(Sheets( Area1 ).Range(...

Excel: Free Chart Objects From Cell References ?, cell references, macro code

cell references, macro code, b85: Hey Ken, Sorry about the delay in answering your question..this was a tough one! I think this will solve your problem: One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the Shift key, and choose Edit,...

Excel: Freeze the user form, gyula

gyula: Try to copy the Layout event into your Userform code: Private Sub UserForm_Layout() Application.ScreenUpdating = False Me.Left = 100 Me.Top = 100 Application.ScreenUpdating = True End Sub Change the left and top values to what you...

Excel: Freezing cells, freezing cells, empty cells

freezing cells, empty cells, data validation: lynne I don t know of a way to lock the individual cells, however two things usually work for me 1--hide the cells by hiding the columns or rows, 2--use data validation- and put a note in that lets the user know that they are not to enter any info...

Excel: Freezing panes across tabs, panes, tabs

panes, tabs, all sorts: Hi Nick, Unfortunately, I think you re going to have to do them individually. You can do all sorts of things by highlighting multiple tabs and making the same change across all of them, but freezing panes doesn t appear to be one of them. Sorry, ...

Excel: Frequency formula HELP!!!!, frequency formula, data names

frequency formula, data names, correct values: Hi Lena, Yeah just send me the file to cloverken@yahoo.com means does not equal means nothing. Therefore means does not equal nothing or blank cells in other words the formula will ignore blank cells. Also the question is not how many...

Excel: Frezzing a page, windows xp home, r50

windows xp home, r50, excel 2000: Hi, Jim, Unless you re willing to write a macro to make this happen, I would suggest simply hiding the unavailable rows and columns. Let s say that you only want users to see K10:R50. You can hide columns A:J and S:IV, and hide rows 1:9 and 51 to the...

Excel: Full Name in AFirst Name..., street suffix, mailing labels

street suffix, mailing labels, secondary address: OK, now you have TWO questions. First one re how to make mailing labels and the 2nd one is a new one I don t understand -- why not just delete the columns you indicated? Send me your email address and I can send a sample of how to make mailing labels from...

Excel: Function, excell spreadsheet, acomplishment

excell spreadsheet, acomplishment, change colors: Hi Riordan How are you? Please do the following 1. Type in A6 =Now() 2. Type in B6 01/31/07 3. Select B6 4. Format - Conditional formatting select from left to right Cell Value is Less than =$A$6 Press Format Select Tab Font Change color...

Excel: Function, array formula, array formulas

array formula, array formulas, horizontal array: I am not good at statistics, so I don t know if the results make sense, but this is how you usually work with array formulas: In this case, first type this figures in some place in the worksheet, one number per cell. For the example, the x values will be...

Excel: IF, Then Function, array formula, array formulas

array formula, array formulas, header rows: Hi Apollo! I think I have a good idea of what you are looking for. Can you review this spreadsheet and see if it meets your expectations? http://home.swbell.net/nate-sus/excel/prov.xls The formulas to accomplish the task you requested are fairly...

Excel: IF Function, decimal fraction, percentage value

decimal fraction, percentage value, d6: Hi Tai, Yes, the formula in cell E6 would be: =IF(D6 300,D6*0.1, ) This displays a null string (which will appear as blank) if D6 is less than or equal to 300. In your problem statement you didn t actually specify what you wanted to happen if D6...

Excel: Function:Address, e24, e15

e24, e15, e50: I had a typo - the funcion s name is INDIRECT, no IDIRECT. =COUNTIF(INDIRECT(A1), =W ) Not sure if this will work but try it anyhow. Create a formula in A1, for example, that builds the address you need, something like: =ADDRESS(I12-9,5,4)& : &ADDRESS(I12,5,4)...

Excel: Function to calculate age, format cells, number 0

format cells, number 0, decimal places: Hi Joe, How are you? If you want to compute the age in years: A5: birth date e.g. 15/03/1970 B5: =today() C5: =year(B5)-year(A5) Select C5 Format- cells- Number 0 decimal places If you want to compute the age in days D5: B5-A5 Select D5 Format-...

Excel: Function call from other modul, xlstart folder, xls workbook

xlstart folder, xls workbook, xls file: Hi Markus, You can copy your custom function to a personal.xls workbook. This workbook is available everytime you open up excel and for any workbook you are working on. If you have not done this before you will need to create a Personal.xls file. To do...

Excel: Function/Formula/Macro (Extract Names and paste to a new sheet), dropdown arrow, copy switch

dropdown arrow, copy switch, namex: You can list the names you want to check, say in M1:M10. Your formula, say in G2 (row 1 being column headers), is =IF(ISNA(MATCH(D2,M$1:M$10,0)),FALSE,TRUE) and fill down. Now filter the list (A1:G100, for example) (data/filter/autofilter) and in the G s...

Excel: Function/Formula/Macro (Extract Names and paste to a new sheet), array formula, blank cells

array formula, blank cells, namex: Okay...based on my interpretation it appears you have two lists. List A contains a bunch of names. List B is shorter and contains another list of names. You want to look at list B and export those names that appear in List A to a new list (List C). Something...

Excel: Function Question, logical test, true value

logical test, true value, andrea: Ben - You can do this. I will give you the function, you will need to change things to customize it to your needs. It is a basic If statement, except you need an And statement to allow you to check the range: =IF(AND(A1 =5,A1 =10), true ,0) Hope...

Excel: Function Question, logical test, excel functions

logical test, excel functions, true value: Hi Ben I dont do Excel functions as my profile says but I could write some code for you which would perform the same task. Let me know if you would like me to do that for you. You may wish to email your spreadsheet ; limbic_lesion@hotmail.com...

Excel: Function Question Again, catalog numbers, 3333333333

catalog numbers, 3333333333, 1111111111: You will need to create a unique listing of the catalog numbers first ONLY IF you don t want each catalog number s total repeated. You can use Data. Filter, Advanced Filter for that. To create a total of each catalog s total then: =SUMIF($A$1:$B$10,C1,$B$1:$B$10)...

Excel: Function Range, logical test, true value

logical test, true value, a2: Ben, I hope my understanding is correct. (1) Let us say A1 has a value between 1 to 40. (2) In A2 you want OK if the value is between 5 & 10 (Both inclusive). Else A2 will be 0 (Zero) In which case formula in A2 will be - =IF(AND(A1 =5,A1 =40), OK...

Excel: Function to Search cells, cell a1, excel sheet

cell a1, excel sheet, parfums: Here s a formula that says: If the string eau is found in A1, display eau-de-parfums , otherwise, if the string lotion is found in A1, display body-lotion , if neither is found, display a blank: =IF(ISERR(SEARCH( eau ,A1)),IF(ISERR(SEARCH( lotion...

Excel: Function that can search text and then average an adjacent column, blank rows, countif

blank rows, countif, adding new records: try this assuming your data is from =SUMIF(A1:A10, Device: name2 ,B1:B10)/COUNTIF(A1:B10, Device: name2 ) to get the average of the devices labeled Device: name2 Expand the range in the formula to include blank rows below the data now where...

Excel: Function using IF and COUNT, h12, g100

h12, g100, c3000: Hi Bob How are you? How is this 1. Write the bin range 59, 100,159,200,259 in G12:G100 in G12:G200 2. Write the numbers you want to count in C1:C3000 4. select H12:H200 3. Type in H12 =FREQUENCY(C1:C18,G12:G21) 4. Press Ctrl Shift Enter. ...

Excel: If Function, column c, several thousand

column c, several thousand, b2: Steve, I don t have the exact fix for you, but it should work OK. The formula would look something like this: =IF(VLOOKUP(A1,C:C,1,FALSE) 0, YES , NO ) and would need to be adjacent to all cells in column A, so just drag it to the bottom of the data. It...

Excel: Functions, square foot basis, estimate form

square foot basis, estimate form, exact price: I would use vlookup to do this, but it requires that the database is properly formatted - see my website at www.aidanheritage.co.uk/Excel for an example. You may wish to send me the file to be able to give you a specific working example - if so, my address...

Excel: Functions, cell a1, double quotations

cell a1, double quotations, cell a2: Hi Mansoor, The IF function evaluates a condition and returns a value you specify. For instance. If you have the value 1 in cell A1 and in cell A2 you type the formula =IF(A1=1, Red , Blue ) The value in cell A2 will return Red if there is a 1...

Excel: IF Functions, spredsheet, representitive

spredsheet, representitive, match: Mike, The problem is with the MATCH function. It returns an error if the lookup value isn t found in the look up array. In other words, if Entry isn t found in B10 an error (#N/A) is returned. That causes the rest of the equation to error out if the...

Excel: If Functions, excel 97, test result

excel 97, test result, many thanks: Yes, you can nest up to 7 levels of bracket - but I m SLIGHTLY puzzled in that a number between 1000 and 500 is already handled in the current test and would evaluate to 6 - it s the IF(A1 500,6 part of the formula the basic pricinciple is =if(test,result...

Excel: Functions don't work, rogue cells, format cells

rogue cells, format cells, excel 2000: if you go to the format cells option, you will find that the rogue cells are set to TEXT format - this means that anything that is typed in them is regarded as text not a formula - change the format to ANYTHING else and it will work (though obviously make...

Excel: Functions/Formulas, format cells, variable column

format cells, variable column, brain cells: Cindy, Here s an algorithm that works as far as the examples you ve given. Where it might break down is under the following circumstances: 1. you have a value that starts with a letter other than V or E 2. You have a number value that starts with 0...

Excel: IF Funtion, column headings, vlookup function

column headings, vlookup function, usre: Use the VLOOKUP function. =VLOOKUP(lookup value, lookup table range,column to return, FALSE) Always use the FALSE at the end! SO to return the program for Acevedo: =VLOOKUP( Acevedo , A1:E5,2,FALSE) assuming your lookup table range is A1:E5. ...

Excel: Further Help - Vba Solution to trimming Date and Time to date, format cells, time values

format cells, time values, time info: It doesn t sound like you need a VBA solution. It sounds like you need to select the column and do format= Cells= select the number Tab, then in the categories List box select the entry for data and then select the appropriate date format you want to...

Excel: Further Questions on Delete Rows on Protected Sheets, vba code, allexperts

vba code, allexperts, many thanks: Hello Neng Yong, If you mean that you want the macro to operate on the sheet that is currently selected you can use this version of the macro: Sub DelSelectedRow() With ActiveSheet check to make sure a row is selected If Selection.Rows.Count...

Excel: Further questions on delete rows on protected sheets, blank row, allexperts

blank row, allexperts, vba code: Hi Kao, Hope you are doing well. Attached is the code that does what you need when you press Shift+DEL. Only thing to remember is that you need to copy this code into the ThisWorkbook module If you have a specific password, you can set that in the module...

Excel: What is the Future of Excel VBA ?, functionality changes, video demo

functionality changes, video demo, change thanks: Hey Ken, From what I gather Excel 12 contains mostly user interface and functionality changes. All previous coding procedures will be supported. There will be no new commands to learn, but shortcuts to certain menu items will probably change. This...

Excel: Future of Excel VBA ?, macro capabilities, productive method

macro capabilities, productive method, code structure: Ken, As I understand it, Office 12 will be based extensively on the XML format. The files you save today with VBA code in them should be fully functional. There will certainly be some new code structure to support the full fidelity XML schemas that Office...

Excel: 2 Problems: Filling blank spaces & a similar problem to vlookup, b2, a1

Excel: 2 Problems: Filling blank spaces & a similar problem to vlookup, b2, a1

Excel: 2 sided Printing for entire workbook, win2pdf, pdf document

Excel: 2 sided Printing for entire workbook, win2pdf, pdf document

Excel: 2007 colors, rgb values, colours

Excel: 2007 colors, rgb values, colours, accent

Excel: 3 axis graphs, graph wizard, axis

Excel: 3 axis graphs, graph wizard, axis, axis values

Excel: Access the Date Picture Taken attribute, excel macro, possible solutions

Excel: Access the Date Picture Taken attribute, excel macro, possible solutions, attribute

Excel: Activate macros, change security settings, design mode

Excel: Activate macros, change security settings, design mode, cells

Excel: Activating a Macro by clicking a cell, macro names

Excel: Activating a Macro by clicking a cell, macro names

Excel: Active X Buttons Resizing, button sizes, font sizes

Excel: Active X Buttons Resizing, button sizes, font sizes

Excel: ADDRESS of a range

Excel: ADDRESS of a range

Excel: Adding Cells in Multiple Worksheets, tardies, shift key

Excel: Adding Cells in Multiple Worksheets, tardies, shift key, assumptions

Excel: Adding cells from different worksheets, dear tom, many thanks

Excel: Adding cells from different worksheets, dear tom, many thanks, money

http://en.allexperts.com/q/Excel-1059/f/Adding-dashes-txt-cell.htm

Excel: Adding hours over 24 in Excel, ss, mm ss

Excel: Adding hours over 24 in Excel, ss, mm ss, precise answer

Excel: Adding Leading zeros to a dataset in order to sort, cell b2, opy

Excel: Adding Leading zeros to a dataset in order to sort, cell b2, opy, aa3

Excel: Adding the numbers within "one" cell?, abc, example company

Excel: Adding the numbers within "one" cell?, abc, example company

Excel: Addition from existing worksheets, sum formula, sum value

Excel: Addition from existing worksheets, sum formula, sum value

Excel: Age Calculation in years, legal adults, current date

Excel: Age Calculation in years, legal adults, current date, birth date

Excel: Alarm, cell a1, validation

Excel: Alarm, cell a1, validation

Excel: Align Checkboxes, page layout, checkboxes

Excel: Align Checkboxes, page layout, checkboxes, alignment

Excel: Apeend data from multiple spreadsheets into one master spreadsheet, master cells, mycell

Excel: Apeend data from multiple spreadsheets into one master spreadsheet, master cells, mycell, number 0

Excel: Append with leading zeros, s gross, c function

Excel: Append with leading zeros, s gross, c function, punctuation

Excel: Application-defined or object-defined error

Excel: Application-defined or object-defined error

Excel: Array formula to sum time, format cells, formulae

Excel: Array formula to sum time, format cells, formulae

Excel: Assign Macro To a Button, open folder, hyperlink

Excel: Assign Macro To a Button, open folder, hyperlink

Excel: Assign macro run keys, onkey

Excel: Assign macro run keys, onkey

Excel: Auto Column Width, cells

Excel: Auto Column Width, cells

Excel: Auto numbers, type mismatch error

Excel: Auto numbers, type mismatch error

Excel: Auto Resize Cells, column headings, home tab

Excel: Auto Resize Cells, column headings, home tab, change column

Excel: Autofill for blanks, cell values, pivot table

Excel: Autofill for blanks, cell values, pivot table, illustration

Excel: Autofill hyperlinks, root directory c, literal references

Excel: Autofill hyperlinks, root directory c, literal references, file suffix

Excel: Autofilter with 2 header rows, jerk, phrase

Excel: Autofilter with 2 header rows, jerk, phrase

Excel: Automatic calculation, vba

Excel: Automatic calculation, vba

Excel: Automatic links?, network software, file recovery

Excel: Automatic links?, network software, file recovery

Excel: Automatic row resize for additional data, line separator, 11 years

Excel: Automatic row resize for additional data, line separator, 11 years, cells

Excel: Automatic time and date stamps, footers, colleen

Excel: Automatic time and date stamps, footers, colleen, date time

Excel: Automatic Value cell change, 6a, 7b

Excel: Automatic Value cell change, 6a, 7b

Excel: Average Formula

Excel: Average Formula

Excel: How to add a button, hyperlink, security issues

Excel: How to add a button, hyperlink, security issues, wb

Excel: adding a new line in excel inside a function, line string, line character

Excel: adding a new line in excel inside a function, line string, line character

Excel: alphabetize, header row

Excel: alphabetize, header row

Excel: Getting around the Max condition in Excel 2003, array

Excel: Getting around the Max condition in Excel 2003, array

Excel: arrange/compare side by side two worksheet, workboot, window menu

Excel: arrange/compare side by side two worksheet, workboot, window menu

Excel: auto move?, newstart, cheers

Excel: auto move?, newstart, cheers

Excel: automate 'fill down' in excel, blank cells, cell a1

Excel: automate 'fill down' in excel, blank cells, cell a1

Excel: automatic progressive invoice numbers, microsoft forums, microsoft site

Excel: automatic progressive invoice numbers, microsoft forums, microsoft site, increment

Excel: average without blanks or zeros, countif, c2

Excel: average without blanks or zeros, countif, c2

Excel: Blank cells still plot as zero in chart after processed through 'IF' formula, blank cells, problem thanks

Excel: Blank cells still plot as zero in chart after processed through 'IF' formula, blank cells, problem thanks, formulae

Excel: Button Color Change after Click, command button, shapes

Excel: Button Color Change after Click, command button, shapes

Excel: blank cells and zero's, reply

Excel: blank cells and zero's, reply

Excel: blink, sid

Excel: blink, sid

Excel: Calculating Dates with Durations, holiday dates, workday

Excel: Calculating Dates with Durations, holiday dates, workday, workdays

Excel: Calculating number of weekdays between two dates, toolpack, click tools

Excel: Calculating number of weekdays between two dates, toolpack, click tools, ins

Excel: Calling DLL from VBA for Excel 2007, cmd

Excel: Calling DLL from VBA for Excel 2007, cmd

Excel: Calling various macros based on cell value, cell a1, example macros

Excel: Calling various macros based on cell value, cell a1, example macros, macro name

Excel: Cascading or Filtered selections?, validation

Excel: Cascading or Filtered selections?, validation

Excel: Cell will not calculate, window options

Excel: Cell will not calculate, window options

Excel: Cell color, conditional formatting, colour codes

Excel: Cell color, conditional formatting, colour codes, column type

Excel: Cell Protection and COlors, text cells, header text

Excel: Cell Protection and COlors, text cells, header text, rst

Excel: Cell validation?

Excel: Cell validation?

Excel: CHaracter Count

Excel: CHaracter Count

Excel: Change cell color based on value, cell references

Excel: Change cell color based on value, cell references

Excel: Change Source Data on Protected Sheet, false application, target

Excel: Change Source Data on Protected Sheet, false application, target

Excel: Changing font to all caps, word switch, change case

Excel: Changing font to all caps, word switch, change case, piece of cake

Excel: Checking columns of data for matching cells, then identifying where unmatched cells are, col1, two rows

Excel: Checking columns of data for matching cells, then identifying where unmatched cells are, col1, two rows, b2

Excel: COMPARING CELLS, len function, c1

Excel: COMPARING CELLS, len function, c1

Excel: CONCATENATE and Italic, question index, address value

Excel: CONCATENATE and Italic, question index, address value, cell address

Excel: CONVERTION OF NUMARIC IN TO WORD, word vba, search number

Excel: CONVERTION OF NUMARIC IN TO WORD, word vba, search number, microsoft solutions

Excel: Color fill in cell, what formula?, cell color, orange color

Excel: Color fill in cell, what formula?, cell color, orange color, arrow

Excel: Column format, six years

Excel: Column format, six years

Excel: Columns - letters instead of #s, column headings

Excel: Columns - letters instead of #s, column headings

Excel: Combination Formula in Excel, d64, different numbers

Excel: Combination Formula in Excel, d64, different numbers, many thanks

Excel: Combining TextToDisplay with Hyperlinks, excel function, cell b1

Excel: Combining TextToDisplay with Hyperlinks, excel function, cell b1, type microsoft

Excel: Combo Box in Excel 2000, validation

Excel: Combo Box in Excel 2000, validation

Excel: Command Button, busy schedule, answering questions

Excel: Command Button, busy schedule, answering questions, time out

Excel: Command Button font, character spacing, button text

Excel: Command Button font, character spacing, button text, command button

Excel: Comment Box???, data validation, message tab

Excel: Comment Box???, data validation, message tab

Excel: Commission spread sheet, vlookup, mistake

Excel: Commission spread sheet, vlookup, mistake