You are here:
| Subject | Date Asked | Expert |
| Excel Formula/Macro | 11/25/2009 | Tom Ogilvy |
| Q: Tom: I have a table of information, In sheet 1, column A has codes and column B(text) contains the ... A: Julie Burlison, You really don't need a macro for this. A simple formula like (entered in L2) ... | ||
| Error Macro Assistance | 11/25/2009 | Bill |
| Q: I have a Submit macro (code listed below) that I inherited in which I would like to add Error ... A: Well, EXACTLY what is the problem? What is it NOT doing that it should be doing or what is doing ... | ||
| Another one! | 11/25/2009 | Tom Ogilvy |
| Q: Simply great! You did it!. May I have another one? SABALO, GODOFREDO JR. GORME, wherein ... A: Assume a 3 character suffix with a period as the 3rd character and the string in A1 then: ="Mr. " ... | ||
| Macro | 11/25/2009 | Aidan Heritage |
| Q: I have a feeling that this might be a 'question too far' but let's see! I wonder if it is possible ... A: Sub sorter() Set currentcell = Range("A1") Do While Not IsEmpty(currentcell) Set nextCell = ... | ||
| Automatic File numbering | 11/25/2009 | Miguel Zapico |
| Q: I badly needed an help on automatic case numbering interconnecting with File no. Please open the ... A: In this case, my suggestion would be to use a helper column (let's say L), with a formula like: ... | ||
| SUMIF(s) under inconsistent conditions | 11/25/2009 | Aidan Heritage |
| Q: How can I work with SUMIF(s) under complicated, inconsistent conditions? Or should I use a ... A: Not clear if the Not OK is actually an entered value – if so, then I would use that with a match ... | ||
| Excel formula problem | 11/25/2009 | Tom Ogilvy |
| Q: I'm not sure if this is possible but I thought you might be the man to ask if it was :o) I have ... A: Lee, I am sure this can be done with a sumproduct formula or an array formula. If you want to send ... | ||
| How to identify three consecutive occurences of a string | 11/25/2009 | Tom Ogilvy |
| Q: I have one column of data with 13 different numbers that re-occur in a pre-determined order. E.g. A ... A: Stephanie, assume your first 0 is in cell A1. then in B2 put in the formula =IF(A2<>2,B1+A2,0) ... | ||
| excel hours worked in a day | 11/25/2009 | Aidan Heritage |
| Q: I want to create the following: column 1 hour began, column 2 hour left, column 3 diff between 1 and ... A: Allexperts is a volunteer service, so it’s free – ideally you will enter the times AS times (eg 9:00 ... | ||
| combobox and text box redundant reoutines | 11/24/2009 | Tom Ogilvy |
| Q: I have two questions which are similar but unique. I have a form with 5 comboboxes and 5 textboxes ... A: Scott, First, by form I assume a Userform, but regardless, the below works with a userform or a ... | ||
| IFERROR formula | 11/24/2009 | Tom Ogilvy |
| Q: I have created an spread sheet to be used by many different people with many different versions of ... A: Danielle, say you have =ifError(exp,"") where exp is your expression/formula that could produce ... | ||
| finding duplicates in Excel | 11/24/2009 | Aidan Heritage |
| Q: I am usually great at Excel but this one has me stuck. I have names listed in column A. In column B, ... A: To highlight the cells, use conditional formatting (you didn't say which version of excel so I'll be ... | ||
| Automatically have a check mark entered in Excel? | 11/24/2009 | Richard Stephens |
| Q: I have a worksheet that I am trying to automatically enter a check mark when the previous column ... A: June This can be done if you change the font in the cells you want the tickmark go to in to ... | ||
| Copying columns from another workbook | 11/24/2009 | Bob Umlas |
| Q: My requirement is a little larger version of following problem, and I will follow your guidance for ... A: Adjust the workbooks names accordingly: Const WBA as string = "BookA.xls" Const WBB as string = ... | ||
| Excel 2007 macro for hiding specific columns | 11/24/2009 | Tom Ogilvy |
| Q: Hope you do well I am currently struggling on the below...(excel 2007) I have a range of columns ... A: Dominique Sub HideColumns() Dim r As Range, cell As Range Set r = ... | ||
| Excel Macros | 11/24/2009 | Bill |
| Q: I'm trying to create a form for my staff to complete in Excel which then feeds into a 2nd sheet ... A: Dim FirstBlankCell as Range Set FirstBlankCell = ... | ||
| Excel 2003, complex formulas | 11/24/2009 | Aidan Heritage |
| Q: I need to creat a complex formula, with conditions....I have a total of 12 columns (possible values ... A: Sounds fairly straightforward ... | ||
| Conditional formatting | 11/24/2009 | Craig |
| Q: With regard to the question on this following link ... A: It sounds like you are on the right track. You need to go in to the conditional formatting in that ... | ||
| enter formula and autofill to end of data in the column of selected cell macro | 11/24/2009 | Jan Karel Pieterse |
| Q: I'm trying to extend a formula that creates a column in the middle of a sheet depending on what ... A: Not sure I understand the difficulty here? You formula already sums every other column and if you ... | ||
| FOLLOW-UP | 11/24/2009 | Tom Ogilvy |
| Q: I'm using the IF function for my project, but I encountered a problem when it reaches up to 7 ... A: Victorino, ="Mr. " & MID(A1,FIND(", ",A1)+2,FIND("#",SUBSTITUTE(A1," ... | ||
| Automatic File numbering | 11/24/2009 | Miguel Zapico |
| Q: I badly needed an help on automatic case numbering interconnecting with File no. Please open the ... A: Sorry but I don't accept files any more. Regarding the automation, can you describe what you need ... | ||
| followup question...the e-mail said you couldn't answer and didn't allow mee to rate or nominate you.. | 11/24/2009 | Tom Ogilvy |
| Q: Your answer was spot on and your speed in coming up with it was incredible - if I wanted to select ... A: Marcia, this worked for me. Sub AddColumns() Dim lastcol As Long, i As Long lastcol = Cells(1, ... | ||
| Lookup a value from muliple lines | 11/24/2009 | Tom Ogilvy |
| Q: I have an Excel 2007 Spreadsheet, which has one sheet which contains a long list of names, and ... A: Andy, Say the names in Sheet1 start in row 2. so in B2 (or the next available column) put in a ... | ||
| Copying columns from another workbook | 11/24/2009 | Bob Umlas |
| Q: My requirement is a little larger version of following problem, and I will follow your guidance for ... A: Select wb B's Sheet 1's range B4:B1000 (as far down as the last used cell is), copy, activate wb A, ... | ||
| IF statement | 11/24/2009 | Jan Karel Pieterse |
| Q: i got problem using IF statement which is cannot use in more than 7 input. in my problem also got ... A: You can achieve this with the IF function without a problem. THis formula does what you describe ... | ||
| Help required with Excel Macro | 11/24/2009 | Aidan Heritage |
| Q: I require assistance from you in a college project where I need an excel macro which would save an ... A: easiest way would be with the Filecopy command Dim SourceFile, DestinationFile SourceFile = ... | ||
| Date sensitive password protection | 11/24/2009 | Bill |
| Q: Is there a way to have an Excel password work up to a certain date and then no longer work beyond ... A: One way would be to use something like the code below, however you will need to do something to hide ... | ||
| Looping with VLOOKUP | 11/23/2009 | Richard Stephens |
| Q: I have two columns C and D. I would like to extract ALL the rows from Column D which match a ... A: VRJ The simplest solution would be using the Data Filter option, you can do this on the existing ... | ||
| Looking up information | 11/23/2009 | Tom Ogilvy |
| Q: I have a grid type spreadsheet that I use as a wire-lenght matrix chart. You can imagine that there ... A: Jim, Assume you header data is in B1:J1 (9 racks), and row labels in A2:A10 (Same 9 racks) then ... | ||
| How to Concatenate a Text String from multiple cells containing a search value | 11/23/2009 | Aidan Heritage |
| Q: On 11/20/09 you answered a question that partially helps solve what I'm attempting - I am aware of ... A: I would do this using a User Defined Function in VBA -=- ... | ||
| Multiple Count Formulas (Excel 2003) | 11/23/2009 | Aidan Heritage |
| Q: I am having problems trying to count multiple variables using Excel 2003. I have attached a picture ... A: You can do multiple condition countif or sumif calculations by a clever use of the sumproduct ... | ||
| Format Automation | 11/23/2009 | Tom Ogilvy |
| Q: Hey Tom, My question is regarding excel 2003. I have recorded 2 individual macros(Landscape and ... A: Vish, You say it doesn't process the files, so I assume if you run it, then nothing happens. Is ... | ||
| how to make one cell follow other in specific rule | 11/23/2009 | Miguel Zapico |
| Q: could you please tell me how to do the following: I have to column, one of them for Item code and ... A: One way of doing this is to use VLOOKUP. For that, you will need to create a table with all the ... | ||
| Macro to enter and fix date | 11/23/2009 | Tom Ogilvy |
| Q: I have a spreadsheet with 2 columns. In column A I enter the date and against it in column B I enter ... A: Tony, You would need a macro to do this Right click on the sheet tab where you want this behavior. ... | ||
| Macro to move rows between worksheets | 11/23/2009 | Tom Ogilvy |
| Q: I am trying to create a macro that will cut (remove) all rows that contain “Resolved” in column M, ... A: Claudia, If Resolved is found in column M as your code seems to indicate, then this worked for me: ... | ||
| Looping in Macro | 11/23/2009 | Richard Roberts |
| Q: can the macro look in a set of folders for a specific name based on a specific cell value and copy ... A: Khaled I am definatly not a expert in macros--but I do believe you can do what you want--I suggest ... | ||
| Excel formula question. | 11/22/2009 | Bob Umlas |
| Q: I am a real estate agent who created a spread sheet for our deals. I have a close date on the ... A: First issue is getting the series you want. In cell Q1 enter 12/1/2009. Select Q1:Q2 (Q2 is empty), ... | ||
| Excel Spreadsheet | 11/22/2009 | Aidan Heritage |
| Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ... A: Hopefully that's a different question - the original question seemed to relate to how to put the ... | ||
| Excel Spreadsheet | 11/22/2009 | Aidan Heritage |
| Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ... A: if you are doing this as a one off, then you can copy the results and use edit, paste special to ... | ||
| Check boxes in forms | 11/22/2009 | Aidan Heritage |
| Q: I created a form in EXCEL 2003 on an XP machine using check boxes which, when checked will do ... A: Not sure what method you have used to create checkboxes as they exist in the control and forms ... | ||
Answers by Expert:
Top Expert on this page
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999. I don't answer questions on using Excel in a browser Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis

©2009 About.com, a part of The New York Times Company. All rights reserved.