| Subject | Date Asked |
|
| Adding formulas worksheet to worksheet | 11/19/2009 |
Q: I've been able to figure out how to add a cell in one work sheet to a cell in another worksheet. ...
A: This can be because of the use of absolute references. Check that the row number in the reference ...
|
| excel solver | 11/18/2009 |
Q: I need help figuring out how i am going to do this - please help me - not sure what to put in solver ...
A: You can use the column D to record the quantities, and then use a formula like this on E1, for ...
|
| ISTEXT cant return proper true/false results | 11/16/2009 |
Q: I am currently working on a type of score card and I want the the formula to do a simple task. if in ...
A: The problem here is that ISTEXT doesn't work with multiple cells as a parameter, so in this case it ...
|
| logic statement using time | 11/16/2009 |
Q: I'm trying to write a logic statement and I'm having difficulty with time. This seems very simple, ...
A: You can use the TIME function to be sure that the format is comparable. Something like: ...
|
| how to pevent blank space in drop down list in Excell | 11/16/2009 |
Q: Sir, I am using Excel version below 2007. I make a list with 100 raws in which having data in 50 ...
A: You can make a dynamic named range and make it the source of the drop down. For example, if you ...
|
| VLOOKUP | 11/15/2009 |
Q: You answered a Q for me on Vlookup, it looks like it can do the job but I need more help. Question ...
A: You are right, VLOOKUP would do the job. The #N/A result will appear when the formula doesn't find ...
|
| "Average if" function | 11/15/2009 |
Q: How do you replicate the "average if" function in Excel 2003. Listed below is the formula I'm using ...
A: The formulas SUMIF and COUNTIF should ignore the blank cells. If you want to be sure, select the ...
|
| phone book database | 11/14/2009 |
Q: Is it possible to develop a phone book in Excel that functions the same as in Outlook
A: It is possible, you may check in the templates from Microsoft for something to base your application ...
|
| Drop-down menu and checkbox or Tick | 11/12/2009 |
Q: I really hope that you can help me with this. I have 12 sheets in one book. and another book to ...
A: Depending on the type of tick that you want, you don't need to use VBA. My recommendation is to use ...
|
| MAX(IF Fucntion | 11/11/2009 |
Q: I have two sheets: sheet1 has a list of my data and sheet2 summarizes the information. Sheet1 Column ...
A: You have the right idea, that formula will work if you limit the array size. For example: ...
|
| Vlookup for Multiple Columns | 11/10/2009 |
Q: Would you kindly assist me with formula that will look at the date in column 12, if there is no ...
A: The formula will have to be a bit more complicated, with nested ifs; something like: ...
|
| TIME STAMP COPY | 11/10/2009 |
Q: I am using the following time stamp as you recommended in one of your answers ...
A: To copy 3 cells, change the resize part of the formula, something like: Target.resize(,3).copy ...
|
| Excel question | 11/9/2009 |
Q: I am using excel to calculate a tax return for capital gains on stock investments. The transactions ...
A: You can use VLOOKUP for this. I am supposing that you have a table on Sheet2, with each day on ...
|
| Excel Formula | 11/9/2009 |
Q: I have two columns of information, one column is a number relating to an address in the second ...
A: I am not sure if I have understood correctly, but it looks like VLOOKUP can help you in this case. ...
|
| Formatting Dates in a Formula | 11/7/2009 |
Q: =if(today()+45>A1,"Warning: Payment is coming due","") In your example above, how should A1, which ...
A: In order for the formula to work, it has to be in a Date format. You can check if it is a date ...
|
| Excel | 11/6/2009 |
Q: I want to link the totals of certain columns of several worksheets to a summary worksheet in the ...
A: What do you need to do exactly? Link one cell at a time, or sum all of them? The procedure you ...
|
| Cell color changes automatically | 11/6/2009 |
Q: I have a problem with excel. Whenever I fill a cell with a color it changes automatically. For ...
A: The sheet may have conditional formatting on that cell. Select the cell and check it on the menu ...
|
| Sorting an Excel Address List | 11/4/2009 |
Q: I need to sort an Excel Address database of 2500 addresses according to zip code for mail sorting ...
A: You are right, that shouldn't happen. The only explanation that I can think on is that some of the ...
|
| Excel | 11/3/2009 |
Q: How do you move address that are in one column to multiple columns
A: You can use the menu Data->Text to columns. This will start a wizard, where you will be able to ...
|
| Count IF (i think) | 10/29/2009 |
Q: I am an Excel Beginner and stuck on a simple formula. I am trying to to get a cell to count(+1) if, ...
A: In Excel 2003, you can use the SUMPRODUCT formula for this, something like: ...
|
| Can #VALUE be used in a logical test? | 10/21/2009 |
Q: I have a set of 10 strings in 10 different cells. I want to test to see if a certain text string is ...
A: You can use the ISERROR formula for this. For example: ...
|
| Dropdown Choices | 10/20/2009 |
Q: How do I have a drop down list display ranges of data from other worksheets. I want to have a master ...
A: You can use the data validation techniques from the following pages: ...
|
| Combing Excel Files | 10/19/2009 |
Q: Hey Miguel, I just wanted to ask a following question on a solution that you helped me with before. ...
A: It looks like you are really close. Try with this range definition instead of the one in the code: ...
|
| Combing Excel Files | 10/16/2009 |
Q: Hey Miguel, I just wanted to ask a following question on a solution that you helped me with before. ...
A: That is a different macro. On the original page with the code, you have code for that at: ...
|
| Combing Excel Files | 10/16/2009 |
Q: Hey Miguel, I just wanted to ask a following question on a solution that you helped me with before. ...
A: Which part is not working? Does it not get all the data? Change the line starting with "Set ...
|
| Excel 2003 Cell Formatting | 10/15/2009 |
Q: I have a question please. I have a workbook that I am creating that is basically listing; Alabama ...
A: You can insert cells individually from the contextual menu. Select the cell with the data, right ...
|
| Value for false or true | 10/15/2009 |
Q: I have aprobelm ,in if condition <0 its appearing 0, IF VALUE IS >0 its appearing TRUE, BUT I WANT ...
A: I am not sure of the details of the formula you are using, but the IF can handle both the TRUE and ...
|
| Conversion of Excel column to a single cell | 10/13/2009 |
Q: How do i convert multiple columns into one single cell, for example. a a a a a a a a want to ...
A: The way that I normally do this is not elegant, but it works. I start with a formula like this on ...
|
| Excel loop and copy | 10/13/2009 |
Q: I need help. I have created a spreadsheet called "Total" and under A6 cells has the heading total ...
A: You may use one of the solutions on this page: http://www.rondebruin.nl/tips.htm Specially the two ...
|
| Excel: IF/Then | 10/9/2009 |
Q: I have a series of If then statements that I need to perform in Excel. For example: If Cell H54 ...
A: You can type the following on cell E57: =IF(H54="N",C70,"") This will return the value on C70 if the ...
|
| Combining Worksheets | 10/8/2009 |
Q: Hey Miguel, the macro worked perfectly but only one more question it is arraging the data column by ...
A: This one is a bit trickier, as the code was aimed for columns, but this should work. I had to hard ...
|
| Combining Worksheets | 10/8/2009 |
Q: Hey Miguel I just have a quick question i was wondering can you help me create a macro that combines ...
A: The issue here is that the variable Last is not modified, so the values are overwritten. Try with ...
|
| Increment the cell number | 10/7/2009 |
Q: I need to copy a formula from one cell to 12 cells , when i copy the formula in each cell the column ...
A: There is a problem with the first cell, as that range is only 10 cells while the others are 11 ...
|
| Combining Worksheets | 10/7/2009 |
Q: Hey Miguel I just have a quick question i was wondering can you help me create a macro that combines ...
A: Here is another article from the same author, that has a single sub so it shouldn't give you the ...
|
| clear contents of unprotected cells | 10/7/2009 |
Q: I'm using Excel 2003. I have a set up a spreadsheet which contains both unprotected %26 protected ...
A: You can do that using the menu Edit->Find. Follow these steps: 1. Open the menu Edit->Find 2. Click ...
|
| Combining Worksheets | 10/6/2009 |
Q: Hey Miguel I just have a quick question i was wondering can you help me create a macro that combines ...
A: You can check this article on the MSDN network, that has code samples in a detailed level that I ...
|
| Fill in blanks without overwriting | 10/5/2009 |
Q: I need a macro that will fill in blank cells in a row without over writing what already There. ...
A: No problem, if you need a macro you may try with something like: Sub FillBlanks() For i = 2 To ...
|
| Fill in blanks without overwriting | 10/5/2009 |
Q: I need a macro that will fill in blank cells in a row without over writing what already There. ...
A: Usually on this situation I don't use a macro, but a helper column with a formula like this: ...
|
| Excel addition corresponds to date | 9/24/2009 |
Q: I'd some values in excel sheet corresponds to dates. And I've added a date range. Now I need to know ...
A: You could use the SUMIF formula for this, something like: ...
|
| Macros and pivot tables | 9/24/2009 |
Q: grateful if you could help. I have created a pivot table and wish to select from the drop-down ...
A: You can use the worksheet events for this. This code can give you some ideas: Private Sub ...
|
| excel formula | 9/23/2009 |
Q: Is there a formula for excel that can find duplicate social security numbers on a worksheet and then ...
A: You could use a helper column with a formula like this: =COUNTIF(A:A,A2) This could be on B2, and ...
|
| Countif Formula - I think | 9/23/2009 |
Q: I am trying to write a very basic formula but cannot get it to work. Basically I have a list of ...
A: You are right, you can use COUNTIF. For example: Greater than 0 but less than 5: ...
|
| excel fomulae | 9/23/2009 |
Q: I am a bit of a newbie at excel and in the process of compiling a spreadsheet to calculate the ...
A: It looks like an scenario for VLOOKUP. In this case, I will assume that you have a table on a ...
|
| Duplicate | 9/22/2009 |
Q: If i have same data , how can i find duplicate and can they be coloured using formula eg a b a c d ...
A: You can use the menu Format->Conditional Formatting for this. Select your range of data, and enter ...
|
| Excel - vlookups | 9/11/2009 |
Q: I need to lookup values in column A and column B from one set of data, find those two identical ...
A: In this case, as the data that you want to bring is numeric, we could use the SUMPRODUCT formula ...
|
| Excel Question on (show only) | 9/10/2009 |
Q: Can Excel Show only cells that contain more than 30 characters in a cell? I have this huge spread ...
A: Depending on what you need to do, you may use different approaches. For example, if highlighting ...
|
| excel 3 columns | 9/9/2009 |
Q: I have 3 columns with contract name, employee title i.e. (skilled or semi/skilled) and hours. I am ...
A: You can use the SUMPRODUCT formula, something like: =SUMPRODUCT(--(A1:A100="Contact ...
|
| Excel - Formula for Looking up max and min values from specific groupings | 9/9/2009 |
Q: I am looking to create a formula that will return the earliest start time of all the products on a ...
A: If the line is on column B and the data in columns C and D, you could use some array formulas like: ...
|
| Rounding | 9/8/2009 |
Q: I am trying to find multiple percentages of a number. in cell B2 I have the number 500 In cell A2 ...
A: Sorry, I am unable to replicate your problem. If B2 contains the number 500 and A2 contains the ...
|
| Excel formula | 9/4/2009 |
Q: I have a workbook made up of several tabs and I would like to include a summary tab that will sum up ...
A: To consolidate information, I have found that the articles on this page explain it better that I can ...
|
| Excel Links | 9/3/2009 |
Q: I recently linked two separate excel spreadsheets and added a bit of VB code to automatically open ...
A: I am not sure on what the error is, sorry. In the menu Edit->Links, you should be able to modify ...
|
| excel formula | 9/2/2009 |
Q: i have same type of data[text]in sheet1 ,sheet2,sheet3,sheet4,sheet5.Sheet1 is my main sheet.i need ...
A: In this case, you can use a formula on the separate sheets that you want to have linked, something ...
|
| Opening Files via macro's | 9/1/2009 |
Q: Hey, If i have a directory containing files in the format DP##### - ####.tif and, within a ...
A: Yes, it is possible. As this is not a workbook, but a .tif file, my recommendation would be to ...
|
| Excel Extra Rows and Columns | 8/31/2009 |
Q: This has happened to me many times in the past, and I cannot figure out how to do it. I have a ...
A: The way that I usually remove the extra spaces is selecting them, and then using "delete row" or ...
|
| Excel formula problem | 8/6/2009 |
Q: Using Excel 2003 SP2 Hi there, I wonder if you can help me ? I have three columns A1, B1 and C1 ...
A: This formula is a bit long, but I chose it because it is easier to read and understand what it does: ...
|
| Column headings | 8/5/2009 |
Q: 1) How do I set it so that my column headings are visible no matter what part of the sheet I am ...
A: 1. You can use the menu Window->Freeze panels for that. Select a cell, go to that menu option, and ...
|
| Arithmetic Operations In A String | 8/5/2009 |
Q: How Can I Do Arithmetic Operations In A String formatted cell in excel workbook I want to do the ...
A: You can use an old Excel 4.0 macro function for this. The reference is here, on the Excel tips and ...
|
| AVGIF | 8/4/2009 |
Q: I'm trying to use the average function with the IF function together. Here is an example: A1 -3 A2 ...
A: The formula that you have is right, you just need to enter it as an array formula. So type the same ...
|
| excel shading | 8/4/2009 |
Q: Can you help me? What will I do to shade the CELLS with the same value in one column? e.g. <COLUMN ...
A: You can use conditional formatting for this. Select all the cells that you want to format in the ...
|
| Converting my formula to calculate time (Overtime spreadsheet) | 8/3/2009 |
Q: I am working in 2003 excel. I have created a Time sheet to calculate overtime. However,I am trying ...
A: If the number is in time format, you may need to convert it to hours first. You can try with ...
|
| Linking Text, different Columns | 7/31/2009 |
Q: I wish to link 2 columns. 1st column is Gender (options male and female). Next Column is Injury ...
A: You can do that with the autofilter (menu Data->Filter->Autofilter) It is not a formula, but it ...
|
| xl format a date | 7/30/2009 |
Q: I want a date format in cells, that I only need to enter 2 digits, eg 30, and the cell displays ...
A: If you only want to type 2 digits, it will not be possible to alter the contents of the cell to make ...
|
| Excel function | 7/30/2009 |
Q: i want to compare entries in a column to find sequential repetition. for example if in a column ...
A: In this case you may use the text functions like SEARCH or FIND, to identify a suitable match inside ...
|
| Excel | 7/30/2009 |
Q: I am working with Excel and want to do this summation: A B C D E G 0 2 3 ...
A: To modify the expression in terms of ranges you could do something like this: ...
|
| Reference cells | 7/29/2009 |
Q: I am trying to reference a cell from another sheet in a workbook. What is happening is I am trying ...
A: Check the format of the destination cell (menu Format->Cell) and be sure that it is set to General. ...
|
| Excel | 7/29/2009 |
Q: I am working with Excel and want to do this summation: A B C D E G 0 2 3 ...
A: You can try with array formulas. For example, the formula that you have written there could be ...
|
| Excel function | 7/29/2009 |
Q: i want to compare entries in a column to find sequential repetition. for example if in a column ...
A: You can use the OFFSET and MATCH formulas, something like: ...
|
| Excel Calculator | 7/28/2009 |
Q: .:-) I got with me a price list with the variables like; [1] Size of Book [2] Binding Type ...
A: If I understood correctly, you may try something like this with the first five variables: ...
|
| data set | 7/28/2009 |
Q: I have a column of data, how can I pull off all the scores above a certain level for example 400? I ...
A: That is a tricky one, and doesn't have an easy solution. The way that I usually solve that is by ...
|
| data set | 7/27/2009 |
Q: I have a column of data, how can I pull off all the scores above a certain level for example 400? I ...
A: The hidden column can work. What I usually do in these cases it to use a helper column with a ...
|
| Excel 2000 functions & formula | 7/26/2009 |
Q: I'm new here and don't know anything about creating functions in excel and would like to hire ...
A: This may be a bit advance, but I think it is a solid and scalable solution, that is also easy to ...
|
| concatenate | 7/24/2009 |
Q: I want to concatonate the highest number in a row with the cell next to it, how do I do that? I have ...
A: In this case my suggestion would be to use a helper column, with a formula like: =A1+(B1="*")*0.01 ...
|
| lookups! | 7/24/2009 |
Q: I have to colums of data that relate to each other. for example: col A Col B 45 4 37 3 ...
A: The Ctrl+Shift+Enter signals Excel to evaluate the formula as an array formula. You can read a good ...
|
| Flag Cells | 7/24/2009 |
Q: Hey Miguel, I needed assistance in developing a formula which flags or deletes cells based on 2 to 3 ...
A: That is strange, as it worked well for me. Could you evaluate the formula (menu Tools->Formula ...
|
| Lookup ref, max and min | 7/23/2009 |
Q: I have a problem that I think requires a formula that goes beyond my excel knowledge. In column b I ...
A: For this formula I will assume that the months in column B are sequential, as I will use them to ...
|
| Protection the Excel cells | 7/23/2009 |
Q: In a shared workbook I want to Protect only particular column say column "D" and rest should be ...
A: When you protect the sheet, there is one check box for enabling the autofilter on the section "Allow ...
|
| Flag Cells | 7/22/2009 |
Q: Hey Miguel, I needed assistance in developing a formula which flags or deletes cells based on 2 to 3 ...
A: In that case, with the same formulas on columns I and J as before, you could use something like this ...
|
| concatenate | 7/22/2009 |
Q: I want to concatonate the highest number in a row with the cell next to it, how do I do that? I have ...
A: You could use the MATCH and OFFSET formulas for the second part, with something like: =MAX(A1:A100) ...
|
| hit a wall with excel :( | 7/21/2009 |
Q: I am working on an Excel spreadsheet and am running into some roadblocks with some formulas to ...
A: In this case you can use the same VLOOKUP formula that you used for the formula, and check for error ...
|
| Protection the Excel cells | 7/21/2009 |
Q: In a shared workbook I want to Protect only particular column say column "D" and rest should be ...
A: To protect only a column, select the full range of data except the column D, go to menu ...
|
| Excel formula | 7/18/2009 |
Q: I have a lookup cell that looks at a spacific cell =LOOKUP(V101,AL$2:AM$42,AM$2:AM$42) What I need ...
A: The reason is that the condition on the IF formula checks if the formula is empty to choose between ...
|
| what if Analysis | 7/17/2009 |
Q: Situation: i have created a drop box that includes 4 different options. I want to add a value to ...
A: In this case, with 4 different options, you can use nested IF formulas, something like: ...
|
| Need a Formula to Search Upwards | 7/16/2009 |
Q: This question pertains Excel 2003. I have been able to create some decent formulas combining Match, ...
A: You are right, search up is not easy, as the formulas tend to give the results that they find first ...
|
| excel problem | 7/16/2009 |
Q: I want to multiply a value of size which is less than 1000,2000,etc to the respective quantities ...
A: There are not enough details to craft a formula. From the question, something like: =A1*Sheet2!A2 ...
|
| Microsoft Excel Question | 7/15/2009 |
Q: You have a list of contracts and due dates for annual maintenance fees. You wish to determine when ...
A: One way of providing a note could be with a formula like this on the place where you want the note ...
|
| Excel drop down | 7/14/2009 |
Q: Is it possible to create drop-down menu for navigating from sheet to sheet in excel?
A: I answered a similar question some time ago: ...
|
| EXCEL 2003: Assign numeric value to letter so it can be filtered and sorted | 7/14/2009 |
Q: I've been asked by a client to find a way to take a column of numbers with letters, and find a ...
A: My suggestion here would be to use a separate column with a formula like this: ...
|
| countif statement | 6/24/2009 |
Q: col.A has company A,B and C; col.D has amounts for the various transactions for each company, some ...
A: You can use the SUMPRODUCT formula for that, something like: ...
|
| Countif with dates | 6/24/2009 |
Q: I want to count the amount of time the Month of June 2005 appears in a column with dates entered. A ...
A: It looks like the format of the cells have changed from text to date. You can either change the ...
|
| Find Button | 6/23/2009 |
Q: How can I insert a find button option on spreadsheet that's protected. ANSWER: When you protect ...
A: If you are using the built-in find function, you don't need to insert any type of object. You can ...
|
| Repetitive entries | 6/22/2009 |
Q: Miguel, How to count repetitive names/entries as one? I have entries like given below col A (Title) ...
A: In this case I would suggest to use the Advanced Filter (menu Data->Filter->Advanced Filter). You ...
|
| select large nonadjacent ranges of cells | 6/20/2009 |
Q: How do I select all of the following cells: H10-H1000 and L10-L1000 AND P10-P1000 AND T10-T1000 i ...
A: Sorry for the late, I was out for the weekend. Regarding your question, you could use the menu ...
|
| Excel Formula Error #Value | 6/18/2009 |
Q: I have been struggling with the formula below, I can't figure out what is wrong with it. ...
A: The problem with the formula is that, due to the location of the parenthesis, they are several ...
|
| Listbox | 6/18/2009 |
Q: I have a quite simple question. Well lets say, several, but all in all pretty simple I guess. I need ...
A: The easiest option to create and maintain is to use the menu Data->Validation on the cell. If you ...
|
| Excel | 6/17/2009 |
Q: I am trying to create a lookup formula that uses data some of which matches cell references and it ...
A: Can you post the lookup formula that you are using? It will help me determine how Excel considers ...
|
| Excel Question | 6/17/2009 |
Q: . this is the second time i m need of ur help .. u have answered my querry before and has worked ...
A: You could use a helper column with a formula like: =IF(A1<2000,A1&"/K",A1&"/S") This will add either ...
|
| writing to txt file using vba at specific location | 6/16/2009 |
Q: i need to write some data in an excel file to a text file. I've a macro for this. But the ...
A: There is an Excel formula that can help here, that is REPT. For example, with the data that you ...
|
| Excel Worksheet Question | 6/16/2009 |
Q: Is there a way to link worksheets within the same workbook Such that the following is accomplished? ...
A: It can be done, but you will have to group the sheets manually. To do that, select one worksheet, ...
|
| adding times | 5/21/2009 |
Q: I would like to add three different cells with time displayed as mm:ss (even if > 60 minutes)in each ...
A: You can use the standard sum formula for this, something like: =A1+B1+C1 or =A1+B1*2+C1*3 And then ...
|
| IF THEN formula conundrum | 5/18/2009 |
Q: I have to sort yeras of temperature readings taken hourly into Day and Night averages. I'm trying ...
A: I am not sure if you have the times just as times or also with the date. That will affect the ...
|
| Formula | 5/18/2009 |
Q: I have very little experience with excel and i would appreciate any help. I basically need help ...
A: Without more information on the layout, I will assume that the values out of 55 are in range A2:A7, ...
|
| replacing data with updated worksheet | 5/17/2009 |
Q: Miguel: I am creating financial spreadsheets for a non-profit that will have many people working on ...
A: The question is too broad, but you may find useful information in this page: ...
|
| conditional formatting | 5/15/2009 |
Q: First I want to thank you for your help in this matter. I have an Excel (2007) sheet in which I ...
A: You may use the VLOOKUP formula here. Check the help for the details, but the basic usage is that ...
|
| removing at from a date and formatting date... | 5/15/2009 |
Q: I have dates as follows; ----------------------------------- Monday 01 June 2009 at 19:30 Tuesday ...
A: I would use an indirect method here. First I would use the menu Data->Text to columns to split the ...
|
| possible to query value when determing which row to paste large coniguous section? | 5/14/2009 |
Q: I have 60 abstracts and each abstract has between 1 and 10 authors. The first column contains the ...
A: My take on this task would be the following: 1. Backup your workbook before starting 2. On a ...
|
| FUNCTION | 5/14/2009 |
Q: I want function to print in a cell L=Z<5, M=5<Z20, H=20<Z<60
A: You could use a function like: ="L=Z<5, M=5<Z20, H=20<Z<60" The important part is to format the cell ...
|
| Vlook ups | 5/13/2009 |
Q: Hey Miguel, I have hit a wall and hope you can help! I have two worksheets in excel (2003), in the ...
A: You could use the INDEX/MATCH combination in place of the VLOOKUP, something like: ...
|
| column formatting into one page | 5/12/2009 |
Q: i was wondering if you know how to format one column into a full page? right now i have only column ...
A: You could use a formula like this on B2: =OFFSET($A$1,47*(COLUMN()-1)+ROW()-1,0) And copy it to the ...
|
| choosing specific cells | 5/12/2009 |
Q: The cells A1 to A1000 contain numbers (that is the first column). I want to create a column (let's ...
A: You can use the following formula on C2: =ISEVEN(ROW()) Copy down the formula all the way to C1000, ...
|
| Copy and paste | 5/11/2009 |
Q: I am trying to paste a list from another worksheet but need to leave spaces (empty rows). Is there ...
A: I think I understand. You may try using absolute references in the formulas that you want to move. ...
|
| Trouble with my Max VBA code | 5/11/2009 |
Q: I have a table with several columns the first one being names of stocks (Stock 1, stock 2, etc) and ...
A: The error is on the definition of MaxCloseResult. It should be a string, not a range. Also, the ...
|
| Adding Values For Same Code | 5/11/2009 |
Q: I was trying to use a function to look down the values in COL1 and where the values are the same ...
A: SUMIF can be a good solution for this. I suppose that you are using a separate column for the ...
|
| how to use excel formula | 5/11/2009 |
Q: Sir, Firstly, I thank you for your prompt response to my query on excel formulas, secondly the ...
A: I am not aware of having answered this question before. Having said that, from your description you ...
|
| sort gender | 5/10/2009 |
Q: i want to sort information from a list and the results to appear on another sheet. i have a list of ...
A: My recommendation for this is to use the autofilter (menu Data->Filter->Autofilter). Filter the ...
|
| Calculation of Days, Time difference | 5/6/2009 |
Q: i have to calculate the days, time difference of two vlaues, and i am having problem when first date ...
A: The problem that you have there is with the date format. That spreadsheet is probably using the ...
|
| Multiple Conditional Formulas | 5/5/2009 |
Q: I want to create a formula that looks at a specific cell and if that cell falls in a range then ...
A: You can use the VLOOKUP function for this. It has a bit of pre work, like setting a table with the ...
|
| Tab delimited text file | 5/4/2009 |
Q: I am writing code that will parse data from a tab delimited text file into an exisiting excel sheet. ...
A: In this case I would recommend to run the text to columns wizard after the code. You can record a ...
|
| Allexpertsq | 5/3/2009 |
Q: Sir, In aa excel file i have got 2 sheets. Sheet no. 2 contains the backup of attendance of every ...
A: The exact formula will depend on the layout and the complexity of the data. It can range from a ...
|
| Changing Week Ranges to Months | 5/1/2009 |
Q: I have a spreadsheet with data reported by week. I'd like to be able to format it by month instead; ...
A: My suggestion for this one is the following: use an additional column and write the corresponding ...
|
| Count cells in column A whose corresponding cell in column B contains criteria | 4/30/2009 |
Q: I'm trying to write a formula that will count the number of nonblank cells in a column (A1:A50) ...
A: You could use the SUMPRODUCT formula for this, something like: ...
|
| Filter on excel | 4/30/2009 |
Q: I want to end up with only tree line i.e Plant Name 1 Sammie 10 Sammy 1001 Sally Present spread ...
A: You can use advanced filter for this. Format the headers as bold, so Excel recognize them as ...
|
| VLOOKUP formula combinations | 4/29/2009 |
Q: I have 2 spreadsheets I am working from saved separately. The first is run from an internet ...
A: I am assuming in my answer that you have created the structure on your destination sheet in a way ...
|
| Excel formula for Expiration Dates, etc. | 4/28/2009 |
Q: I have an elaborate spreadsheet to calculate the due date of a 90-day review, taking into ...
A: Regarding the formula, you could do something like: =IF(ISBLANK(B6),"",IF(ISBLANK(S6),"N/A",B6+90)) ...
|
| Coping formulas | 4/28/2009 |
Q: Here's what I have going on. I'm using a main spreadsheet which contains quarterly information. On a ...
A: The way that I usually do this is with the menu Edit->Replace. You can make the copy of the sheet, ...
|
| Conditional Formating | 4/28/2009 |
Q: I want to color One cell while considering two values. I have Dates (Monday, Tuesday, Wednesday, ...
A: I suppose that the headers would change over time, as if not you could directly color the cells that ...
|
| differentiate two columns in excel | 4/28/2009 |
Q: i want to differentiate two columns in excel...i have two columns i have to compare/ differntiate ...
A: Can you send more details on the layout, and what you are trying to achieve? The very basic ...
|
| Data reduction in excel | 4/27/2009 |
Q: I have time series data collected every second for 1 day. I need 10 second averages. The time ...
A: Usually the pivot tables work wonders when making groups, but there are other alternatives. In this ...
|
| Excel help | 4/27/2009 |
Q: I am currently setting up a spreadsheet in which I am trying to compare multiple dates. In column ...
A: You may try with a formula like: =IF(OR(AND(ISBLANK(O3),G3>F3),O3>F3),"PAST DUE","OK") You show a ...
|
| excel percentage | 4/26/2009 |
Q: miguel, i dont know if i'm confusing myself with this percentage stuff but here is what i have. i'm ...
A: The formula that I normally use it the first one, =((B61-C61)/C61)*100. This is the difference ...
|
| sumif and index at the same time | 4/23/2009 |
Q: Miguel, I'm using the index formula to find where the column and row match from multiple sources ...
A: Sorry for the late, I didn't connect during the weekend. In this case, I have assumed that the ...
|
| Maxa | 4/22/2009 |
Q: I want to obtain the highest value, in this case a date, if the criteria in the sheet matches ...
A: A few pointers: - Be sure to enter it with Crtl+Shift+Enter. Excel will put curly brackets around ...
|
| Excel formula error = #value! | 4/21/2009 |
Q: What mistake have I made in the following formula. ...
A: Sure, but I don't understand the difference that the code makes on the formula. For example, to ...
|
| Golf outing Spreadsheet. | 4/21/2009 |
Q: Miguel,I hope that you have some knowledge of the game of golf. I have been working on a spreadsheet ...
A: My suggestion for this would be to include an extra row, probably better at the header, and fill it ...
|
| Macro Code for Empty cell recognition | 4/21/2009 |
Q: Is there any way to use VBA code to automatically to find and go to first empty row in another file? ...
A: You can use End and Offset for that. For example, this statement will move the selection to the ...
|
| Column header reference on Cell | 4/21/2009 |
Q: I need to have Column header reference on cell
A: You could use a formula like this: =OFFSET($B$4,0,MATCH(1,C10:H10,-1)) You can insert columns, and ...
|
| activities completion dates follow up | 4/21/2009 |
Q: I have a list which includes activities and activites dead lines dates, I want to give a formula ...
A: I suppose that the completion status is on a separate column, and it is entered manually. If it has ...
|
| Excel formula error = #value! | 4/20/2009 |
Q: What mistake have I made in the following formula. ...
A: You had a few parenthesis out of place, and also the way to check several conditions is with the AND ...
|
| regression macro to work on multiple sheets | 4/20/2009 |
Q: Miguel thanks in advance for your help. If you paste the below macro into excel and run it you will ...
A: You can change the instances where the "Sheet1" is hard coded to the sheet where you want to run the ...
|
| excel cells | 4/20/2009 |
Q: I would like to get a total when entering multiple numbers into the same cell, as in counting the ...
A: You will certainly need to use another cell as B1 for that. Cells in Excel can contain either ...
|
| Conditional rollup of data | 4/17/2009 |
Q: Ok here is my problem, I am trying to sum a rolling 3 months into a "Total 3 months cell". The ...
A: The exact format would depend on the layout, but this can give you some ideas. For example, if the ...
|
| Accumulating numbers | 4/15/2009 |
Q: I need to have a number accumulate every month in excel, do you know how to do that? Thank you in ...
A: The exact formula will depend on your layout, but this technique may be useful for you. If you want ...
|
| Find and Report Data | 4/15/2009 |
Q: ok, Hopefully I can get this out right. I have a row. In that row there are cells that have a text ...
A: You can use the INDEX/MATCH combination, for example: =INDEX(A1:H1,1,MATCH("Up",A1:H1,0)+1) This ...
|
| Excel Formula | 4/15/2009 |
Q: I have a problem in excel regarding formula. Date Value 1 Apr 2 Apr 3 Apr 4 Apr 80 5 ...
A: I have assumed that there is always a gap of 7 spaces between values. With that, you could do a ...
|
| Use of Excel 'Text to Columns' wizard | 4/14/2009 |
Q: I want to use 'Text to Columns' to format data that I copy/paste into Excel from an outside source. ...
A: As far as I know, there is no way to change the default separator of the text to columns. You may ...
|
| Macro Confusion | 4/14/2009 |
Q: Hey Miguel, im currently using a macro where it drag downs cell properties to x number of rows, but ...
A: You can, just add variables for those values and populate them using an input box. For example, ...
|
| multiple linked lists | 4/14/2009 |
Q: I know that I can link 2 lists eg 1 list fruit/veg and by selecting one the appropriate fruit or veg ...
A: I use to recommend the tutorial on this page for dependent lists: ...
|
| Finding out addresses of cells based on index based string search in a sheet | 4/13/2009 |
Q: I am stuck with a problem wherein I am trying to find the cell address of a value inside a ...
A: My recommendation on this case would be to include the Market Area number on a separate column, and ...
|
| FORMULA FOR TIMESHEETS | 4/10/2009 |
Q: I HAVE THE FORMULA FOR SUBTRACTING TIMES LIKE START 1:00 END 4:00 BUT WHAT ABOUT START 10AM END 5PM ...
A: I am not sure what formula you are using, but you can try with a simple subtraction: =B1-A1 The # ...
|
| Sorting in excel office 2007 | 4/10/2009 |
Q: When I sort (1 2 3 2a 2b 2c) I get just that. I want the sort to look like (1 1a 2 2a 2b 2c 3) ...
A: The problem is probably on the format of the cells, it is considering the numbers as number and the ...
|
| Excel text to number | 4/9/2009 |
Q: I have certain criteria on the excel sheet. So if someone types in any text in a cell and press ...
A: You can do that with a custom format (menu Format->Cells->Number tab). This one, for example, will ...
|
| Drop Down Lists within Excel | 4/9/2009 |
Q: How do I add additional items to a drop down list on a file created by another user?
A: Depends on the type of drop down list. If it is based on data validation, select the cell, go to ...
|
| Hyperlinking | 4/9/2009 |
Q: I have been having some difficulties with variable hyperlinking in excel. I am trying to use ...
A: The hyperlink formula should work the same if you have a text string or a cell reference with that ...
|
| VBA excel beep | 4/8/2009 |
Q: I simply want excel to emit an audible "beep" when the value entered into any cell B12- Z20 is not ...
A: In order to emit only a beep you would need VBA. If you don't mind that it shows also a message you ...
|
| Hyperlinking | 4/8/2009 |
Q: I have been having some difficulties with variable hyperlinking in excel. I am trying to use ...
A: I would need more information on how you are creating the links and what error messages you receive, ...
|
| count date | 4/8/2009 |
Q: I have a spreadsheet that has dated entries. I need a formula that will count the last date ranges ...
A: You could use the COUNTIF formula, for example: =COUNTIF(A1:A50,MAX(A1:A50)) I have used the MAX ...
|
| Writing a formula that occurence of finacial values between two dates. | 4/8/2009 |
Q: Miguel, I am trying to write a formula to return the occurence of two values between set weekly ...
A: You can try with a formula like: ...
|
| Question about Random Numbers | 4/8/2009 |
Q: cell A1:A50 have random numbers generated by formula ( 0 to 20 ), now what ever we do these numbers ...
A: If they are random numbers there is no formula to force them into that series without losing the ...
|
| excel formula | 4/8/2009 |
Q: i have a range of values in column A starting from cell 1 as shown below.i want to count the number ...
A: Sure. It is still an array formula, entered on cell A1 of Sheet2: =SUM(--(ISNUMBER(SEARCH("FLOREAL ...
|
| Removing middle initial - with a twist... | 4/7/2009 |
Q: Greetings: I have about 16,000 names that are in first name and last name columns. The first names ...
A: Here goes one, with a few nested ifs, that should cover the three conditions: =IF(ISERROR(SEARCH(" ...
|
| Macro Confusion | 4/7/2009 |
Q: Hey Miguel, im currently using a macro where it drag downs cell properties to x number of rows, but ...
A: Sure, the sentences to change would be: Dim orig As Range, addre As String, dest As String to Dim ...
|
| excel - how to stack multiple sheets | 4/7/2009 |
Q: I have a workbook with 40 sheets (each sheet contains a budget for a cost centre and each of the ...
A: You can use VBA for that. Here is one page with various tips that can be useful, with code samples: ...
|
| Macro Confusion | 4/7/2009 |
Q: Hey Miguel, im currently using a macro where it drag downs cell properties to x number of rows, but ...
A: You may have more control if you define dest as Range, and use something like this to populate it: ...
|
| two wosksheets with one common column--need to merge them | 4/6/2009 |
Q: I am working with a large data base of 6800 rows which has zip codes for the different subjects ...
A: You can use the VLOOKUP function for this, something like this on cell B1: ...
|
| SUM Function | 4/6/2009 |
Q: I have a workbook with individual sheets for each month. Within each sheet I capture day by day data ...
A: I am not sure on what can be causing your issue, as your method should work. One suggestion that ...
|
| excel formula problem | 4/6/2009 |
Q: Iam having a problem with my formula working properly. I'm trying to add minutes from a dynamic ...
A: I am not sure if the SUMIF formula is the right one here, you may try with a simpler approach like: ...
|
| Cell Value Comparison | 4/6/2009 |
Q: I need some help from you. I really wish and hope you can help me on this. This is solve a very big ...
A: Due to time constrains I am not doing custom code on this site any more, so I cannot help you with a ...
|
| Question about Random Numbers | 4/5/2009 |
Q: i have a set of question No:1 How to generate a series of random numbers ( between 17 to 37 ) i need ...
A: For the first part of the question you can use the RANDBETWEEN function, something like: ...
|
| Excel, nested if structure | 4/4/2009 |
Q: I have this structure,what I want to happen is if the years service if < 1 show 'NE' as the error ...
A: I am not sure what you want to show with NE, if it is a text or the actual error code. If it is the ...
|
| offset | 4/3/2009 |
Q: i have a list of comodities in column B and bushels in column C. what i want exel to do is find all ...
A: You could use the SUMIF formula for that. For example: =SUMIF(B1:B100,"Corn",C1:C100) This will add ...
|
| exel | 4/2/2009 |
Q: i have two pages of names in excel i need to make it so if i add or remove a name from one sheet it ...
A: You can use one of them as the master, where you do all the changes, and use formulas like this on ...
|
| adding start and stop times together | 4/2/2009 |
Q: I am trying to add start and stop times together for hours worked during one day, and I keep getting ...
A: I am not sure on what formulas you are using, but with time values you can use a direct subtraction. ...
|
| EXCEL COPY ERROR | 4/2/2009 |
Q: I have a date in a spreadsheet and when I copy the cell to a cell in another spreadsheet the date is ...
A: That may happen if the option for 1904 date system is active on one workbook and not in the other. ...
|
| excel formula to convert min:sec to seconds | 4/1/2009 |
Q: I am trying to do two things on excel: 1. Format a cell to display "min:secs" ie. 7:30 (seven mins ...
A: You are doing right with the format, but the data entry may be the problem. If you type 7:30 in a ...
|
| Excel | 3/31/2009 |
Q: I have 2 worksheets sheet 1 has a column of 800 serial numbers (alpha numeric) sheet 2 has a column ...
A: Use a formula like this on cell B2 of sheet1: =NOT(ISNA(MATCH(A2,Sheet2!$A$1:$A$10879,0))) I am ...
|
| reference cell from other xls file | 3/30/2009 |
Q: I have two excel file under different folder but the same parent folder. ...
A: In this case you may use the function INDIRECT.EXT, that can be found here: ...
|
| Excel Formula - IF and/or OFFSET?? | 3/30/2009 |
Q: I'm trying to do something that seems very simple. There will be a cell reference typed into a cell ...
A: You can use the INDIRECT formula for that. For example: =OFFSET(INDIRECT(C10),8,0) This will return ...
|
| Formula Help! | 3/30/2009 |
Q: I am attempting to make a formula calculating the below: I am trying to caluclate the comparison ...
A: I am not sure on what is the layout of the formulas, but probably you could use the IF function: ...
|
| Difference between Average and Sum/number in Excel | 3/30/2009 |
Q: What would be the difference between the following formulas? I want to find an average. All cells ...
A: You answered my next question, because the most common reason of difference between those formulas ...
|
| Maxa | 3/30/2009 |
Q: I want to obtain the highest value, in this case a date, if the criteria in the sheet matches ...
A: The first argument on the IF function is probably causing you problems. I am supposing that you ...
|
| Excel Formula | 3/27/2009 |
Q: I've got a list of 400+ client's and I'm using the =DATEDIF($E$2, TODAY(), "Y")formula to calculate ...
A: You may remove the $ sign from the 2 on the $E$2 reference, to make the row relative. This should ...
|
| Conditional formatting | 3/27/2009 |
Q: I am trying to conditional format a cell that contains a date, I want to compare it to a another ...
A: Sorry for the late, I didn't connect during the weekend. Regarding your question, you can use a ...
|
| excel copy and paste | 3/27/2009 |
Q: I wanted to know how do I copy and paste special only the excel values that are greater than 0 ...
A: Even with VBA the PasteSpecial feature doesn't work in the way that you need. You could try with ...
|
| excel copy and paste | 3/27/2009 |
Q: I wanted to know how do I copy and paste special only the excel values that are greater than 0
A: As far as I know there is no built in mechanism that allows you to do that. The workaround can be ...
|
| if statement | 3/26/2009 |
Q: i am sorry i could not do more follow up on this link ...
A: By design, that formula will give that error if the two numbers on the range C4:C6 are the same. If ...
|
| if statement | 3/26/2009 |
Q: i am sorry i could not do more follow up on this link ...
A: My fault, with the copy/paste I repeated the MAX formula on the second operator. Try with something ...
|
| If statement | 3/26/2009 |
Q: let's see if i can even explain what i am looking for I will have six coulumns but there will be ...
A: Sure. The first part 0.5*(MAX($C$4:$C$6)+MIN($C$4:$C$6)) will take the biggest and the smallest ...
|
| Merging files | 3/26/2009 |
Q: Argh! Please help! I have several worksheets that I need to merge and keep getting an error msg ...
A: The merge option from the menu is part of a full process of sharing the workbook, distributing it to ...
|
| Help I'm frustrated!!! | 3/25/2009 |
Q: The data on 1 worksheet is organized in the this way: 2008 2008 2008 2008 2008 ...
A: These are examples using two different formulas: Books read on a month: =SUMIF(A2:H2,"Month1",A4:H4) ...
|
| Xcell | 3/25/2009 |
Q: How do lock an cell that has a format
A: By default cells are locked, but you can be sure of that on the menu Format->Cell->Protection tab. ...
|
| Excel Charts and formulas | 3/25/2009 |
Q: I have a chart with month over month data for the year, with formulas in the cells. Since its only ...
A: If you have formulas, you may tweak them to show the #N/A error instead of a blank, as that will ...
|
| if statement | 3/25/2009 |
Q: what i want to do is to check if there is numeric value in the cell. i have 6 cells but only 3 ...
A: Then we can do something like: =IF(COUNT(A1:F1)>3,"Enter only 3 ...
|
| If statement | 3/25/2009 |
Q: let's see if i can even explain what i am looking for I will have six coulumns but there will be ...
A: You could use the same idea of MAX/MIN, something like: M1: ...
|
| If statement | 3/24/2009 |
Q: let's see if i can even explain what i am looking for I will have six coulumns but there will be ...
A: If you are sure that only two values will be entered on the A section and one on the B section, you ...
|
| Macro/formula help | 3/24/2009 |
Q: OK i feel kinda silly asking this but i need some help. i have a report that i need to run i macro ...
A: I don't know how you have structured your macro, but if you are using any range object to populate ...
|
| if statement | 3/23/2009 |
Q: what i want to do is to check if there is numeric value in the cell. i have 6 cells but only 3 ...
A: It is possible, it will depend on what you want to show. For example, if you want to show the three ...
|
| Excel 2003 data entry | 3/21/2009 |
Q: I am doing some data entry manually. let's say I have 3 columns of data and 1000 rows for data ...
A: I would keep the ENTER key in the default option, and change the routine a bit to finalize the first ...
|
| Excel - WORKDAY function to add partial days | 3/20/2009 |
Q: My question is quite simple and I assume it is a common scenario, but I couldn't find a relative ...
A: You could combine the SUM and the WORKDAY, using the former for the decimals and later for the days. ...
|
| One text/ number string | 3/20/2009 |
Q: I would like to join large number of text strings into one text string with comma in between each ...
A: One potential workaround that I normally use for this is the following: 1. Enter the numbers one by ...
|
| changing columns and sorting | 3/19/2009 |
Q: How do I change column Q to the front of the spreadsheet (A). And if I sort the front column by ...
A: Sorry for the late, I have been busy on the weekend. To change the column Q to the front of the ...
|
| Excel Question | 3/19/2009 |
Q: Using a VLOOKUP funtion on one tab and want to reference the average of numbers on the other tab. ...
A: Does it really have to be a VLOOKUP? You can achieve the same result on a simpler way with a ...
|
| Vlookup and Cell Reference | 3/19/2009 |
Q: 'ActiveCell.Formula = _ "=VLOOKUP(a2,'[book10]check'!B$2:I$229,8,0)" Check is the sheet name ...
A: I am not sure on what you want to achieve here, but my suggestion would be to make a loop on the ...
|
| if statement | 3/18/2009 |
Q: what i want to do is to check if there is numeric value in the cell. i have 6 cells but only 3 ...
A: You could use some formulas like this: M1: =IF(COUNT(A1:F1)>3,"Enter only 3 ...
|
| lookup function | 3/18/2009 |
Q: Need help on what should be a simple formula. I have a column of cities that i need to search. ...
A: Actually, it is not a simple formula. In those cases I usually use an extra column to store the ...
|
| Formula for Time keeping | 3/18/2009 |
Q: I did the formula that was given to get the total number of hours work per day and the time is ...
A: When that appears is probably a matter of cell format. Try first to increase the column width, so ...
|
| excel formula | 3/17/2009 |
Q: I have a date received and a date completed. Like this: 23 days late because I received - 01/25/09, ...
A: You can treat dates as numbers and do the same operations that you would do between them. For ...
|
| copying hyperlinks | 3/16/2009 |
Q: I have built a long worksheet with a frozen section at the top which contains hyperlinks to related ...
A: You are right, as I wrote the formula it repeats the name of the last sheet that was evaluated. Try ...
|
| Dependent Dropdown | 3/16/2009 |
Q: I have an excel file called BOQ.xls where i have two sheets Costing & Data in Sheet "Data" 5 Colums ...
A: For the data validation, here is a link that explains a step by step process: ...
|
| array formula problem | 3/16/2009 |
Q: I have tried a lot of versions of this formula and can not get it to work. ...
A: It looks like you are trying to add the numbers on column N for the values that satisfy one of the ...
|
| copying hyperlinks | 3/13/2009 |
Q: I have built a long worksheet with a frozen section at the top which contains hyperlinks to related ...
A: Yes to both. In fact, you can simplify the formula by using one cell with the CELL formula, like: ...
|
| copying hyperlinks | 3/12/2009 |
Q: I have built a long worksheet with a frozen section at the top which contains hyperlinks to related ...
A: You may use the CELL formula for this. For example, you can put a formula like this on A1: ...
|
| excel column header pointer | 3/11/2009 |
Q: I have columns with a date as the title for each column. I have used networkingdays to show if the ...
A: In order to move cells, you can either copy/paste/delete original or cut/paste. Both have different ...
|
| Possible Combinations Help | 3/11/2009 |
Q: http://en.allexperts.com/q/Excel-1059/2008/4/Possible-Combinations-3.htm On the possible ...
A: You are right, I cannot think on an easy way to tweak the formulas to allow exceptions. If ...
|
| excel find in array | 3/11/2009 |
Q: I tried this for a while but couldnt come up with solution. I have a an array A1 to M50(could ...
A: You can use an array formula similar to this: ...
|
| Excel | 3/10/2009 |
Q: I am issuing payroll and I'm looking for the formula to convert minutes into a dollar amount. ie, ...
A: It depends on how you are storing the hours/minutes worked. If they are stored as time, a formula ...
|
| Excel 2003 | 3/10/2009 |
Q: Can you put a frame around a excel header
A: As far as I know, you cannot frame it within the header/footer section on the Page Setup. You ...
|
| copy first character in a cell | 3/6/2009 |
Q: I originally had to copy a series of data in the format 7110-01-04 and excel recognized that as a ...
A: Probably Excel is converting the values into dates, so you may use YEAR instead of LEFT to get the ...
|
| Goal Seek or Solver Help (Solving multiple cells) | 3/6/2009 |
Q: I'm try to run some analysis on a database of products. The columns I'm working with are Price, ...
A: In this case the most effective solution would be with custom code. You can record the process ...
|
| automating sum(X,Y) | 3/5/2009 |
Q: being very new to excel i am looking to see if a couple of things can be achieved. Using the ...
A: With your first question, if you select the cell C2, go to menu Edit->Copy, select the cell C3 and ...
|
| Extracting Rows | 3/5/2009 |
Q: I have two files. File A has 100 entries. File B has 1000 entries. There are 100 entries in file B ...
A: You could the MATCH formula and the autofilter to create the list, and then copy/paste the results. ...
|
| Calling a Named Range in a formula | 3/5/2009 |
Q: If I have several named ranges in a spreadsheet and want I want to write a formula which calls one ...
A: You can use the INDIRECT for that, something like: =COUNTIF(INDIRECT("'"&B1&"'!E:E"),F3) I am ...
|
| how to cross duplicate? cross compare | 3/4/2009 |
Q: this is hard to explain in words. i have this item no.1 no.2 ==================== A 2 ...
A: This is a solution with formulas, and two extra columns. In this case I have entered the original ...
|
| Attainment Percentages | 3/4/2009 |
Q: My boss and I are having a bit of a debate about attainment percentages. We start out with on ...
A: I cannot tell you if your boss is correct or not, as it depends on how the metric is defined and ...
|
| Combined Formula | 3/4/2009 |
Q: I'm going crazy with if's and's and or's.. ugh. Please help. I have two cells.. say A and B. If ...
A: You could include the OR function to make a formula like: =IF(OR(A1=0,B1=0),"Insufficient ...
|
| Rounding | 3/3/2009 |
Q: I need a formula that will round a number up to the nearest £0.05 or £0.09. The number must always ...
A: I am assuming that you want to round up, as 1.768 is closer to 1.75 but you expect it to be 1.79. ...
|
| missing dates in excel | 3/3/2009 |
Q: "I will get data from a machine at every one hour. I have some historical data @ hourly basis i.e., ...
A: If the hours are in column B, you could use a formula like this to detect where the gaps are and how ...
|
| Counting number occurences of the data seen after a filter has been applied. | 3/2/2009 |
Q: I am trying to only count data that is seen after a filter has been applied. I am applying the ...
A: There is no single formula that can do what you want. For example, you can use SUBTOTAL to ...
|
| Vlookup table arrays | 3/2/2009 |
Q: I use Vlookup's to generate data from various excel 2002 spreadsheets. The data array in the ...
A: You can use the INDIRECT function for that. For example: =VLOOKUP(A2,INDIRECT(A1 & ...
|
| View Locked Cells on a spreadsheet | 2/27/2009 |
Q: Is there a way to view all of the locked cells on a spreadsheet?
A: You can use the Find for that. Go to menu Edit->Find, open the Options, open the Format selection ...
|
| Possible Combinations Help | 2/27/2009 |
Q: http://en.allexperts.com/q/Excel-1059/2008/4/Possible-Combinations-3.htm On the possible ...
A: These are the formulas for four columns, and also taking headers into account. I have limited the ...
|
| Linking to another cell in same workbook, different worksheet | 2/27/2009 |
Q: I'm having difficulties with the link feature. When I enter = in the function cell and then go to ...
A: The format of the cell should do it. Try in the following order, first ensure that the format of ...
|
| COUNTA Used with Autofilter | 2/25/2009 |
Q: I use Autofilter often and normally Excel 2003 will show at the bottom of the spreadsheet "X of XX ...
A: You can use the SUBTOTAL formula on top of your filtered data, with something like: ...
|
| Excel Graph | 2/25/2009 |
Q: I made a line graph in excel showing information for 3 shifts for the work week. When I look at the ...
A: It will depend on the range of data that the chart is using. Right click on the chart, select ...
|
| Formula that I can't work out.... | 2/24/2009 |
Q: I need help with a function in excel. Basically what I want to do is have an amount of money that is ...
A: I am not sure if I have understood what happens on year2 and after. I will assume that you want to ...
|
| ASCII text files | 2/24/2009 |
Q: I need to create an ASCII text file and I am not sure where to begin. I have the information to ...
A: There is no immediate way of doing it. In this page there are some solutions, using VBA code and ...
|
| separate text in cell | 2/24/2009 |
Q: I hope you can help me with my problem. I've data (text) in excel that i'm trying to clean. However ...
A: In this case you can probably use the menu Data->Text to columns. Select the range of data with the ...
|
| price decrease | 2/23/2009 |
Q: I am trying to decrease a list of prices by 1.8%...what formula would I enter? Is there an easy way ...
A: You could use a formula like: =A1*0.982 If you want to change the original values, you could type ...
|
| Formula to summarize number of Calls | 2/23/2009 |
Q: I have a call log as raw data and I want to summarise it in a way so that I can get a count of calls ...
A: One way is to use the COUNTIF formula, for example: H6: =COUNTIF($B$5:$B$9,">=" & ...
|
| Expiration dates | 2/21/2009 |
Q: I am creating a spreadsheet that lists expiration dates for individuals with specific appointments. ...
A: You can use conditional formatting for this. Go to menu Format->Conditional Formatting, and then ...
|
| ASCII text files | 2/20/2009 |
Q: I need to create an ASCII text file and I am not sure where to begin. I have the information to ...
A: I am not sure on what you need here. If you need to create an ASCII text file, with no format at ...
|
| Pull data from antoher spreadsheet | 2/20/2009 |
Q: I am keeping a running track of inventory accuracy daily. Every day a new file is created through ...
A: If you are using INDIRECT, you may use the non-Microsoft INDIRECT.EXT function, that will allow it ...
|
| Excel Field Validation with fixed length | 2/19/2009 |
Q: I am putting field validation with 35 characters in one column in excel 2003. when I insert less ...
A: The important part about this formula is that it doesn't overwrite the existing values, so you will ...
|
| Formula | 2/19/2009 |
Q: I'm quite new to excel and can't seem to find a way with any formula to do what I want. I want to ...
A: The formulas that may help here are MATCH and ISNA. The first one searches a range for a given ...
|
| Transfer formula from another sheet | 2/18/2009 |
Q: I am trying to transfer a formula from one sheet to another. On my "Master" sheet, I have an IF ...
A: This works on Excel 2003, so it will probably work on 2007. Select all the worksheets where you ...
|
| Counting Numbers | 2/18/2009 |
Q: Expert I have data in Two Column Like This: Zone No. East 4 East 8 East 6 East 31 East 5 East 9 ...
A: I have set your original data on range A1:B33, and the table starting on D1. The headers I have ...
|
| Validation for duplicate records | 2/18/2009 |
Q: I have a spread sheet where users will copy data from another excel sheet. i have provided ...
A: In this case my recommendation would be to use conditional formatting for the task column, so it ...
|
| If then...string | 2/17/2009 |
Q: I am trying to create an IF THEN formula with a pull down box included. Where an item can be select ...
A: Yes, it is possible, but in this case my recommendation would be to use VLOOKUP instead of IF. To ...
|
| EXCEL | 2/17/2009 |
Q: HOW DO I COUNT ONLY TEXT IN AN ARRAY CONTAINING NUMBERS AND TEXT?
A: You can use the COUNT and COUNTA formulas, something like: =COUNTA(A1:A100)-COUNT(A1:A100) The first ...
|
| Validation for duplicate records | 2/17/2009 |
Q: I have a spread sheet where users will copy data from another excel sheet. i have provided ...
A: That formula will check for duplicates on the current sheet, if you want to check for duplicates on ...
|
| Referencing External Sheet Based On Text | 2/17/2009 |
Q: For each of those addresses there is an excel file. For example '1234 New Street.xls' and so on. ...
A: This is possible with an Excel extension, that includes the INDIRECT.EXT formula. Check the add on ...
|
| sending a macro to someone else | 2/16/2009 |
Q: I am trying to send a macro to a co-worker how can I send it without attaching it to a workbook
A: You can save the macro as a file with extension .bas and send that file to him. To do so, open the ...
|
| Excel - Counting time between 18:00 - 6:00 | 2/16/2009 |
Q: How would I reverse the formula discussed under the "Counting time between 18:00 - 6:00" question. I ...
A: Sorry for the late. I assume that you are referring to my answer to that question, specifically ...
|
| MS Excel Comparing two lists | 2/15/2009 |
Q: Comparing two lists I hope you can help with this issue I am having, I have some experience in ...
A: This formula may be too complex, but it doesn't require macros. Select the range C1:C12 and enter ...
|
| Formula trouble result not what I expect | 2/14/2009 |
Q: I have this formula that does an IF or and looks to an INDEX and I want it to return a value but its ...
A: In that case, my suggestion would be to make a separate formula for each case. It is not pretty and ...
|
| Compare cell range content for duplicates | 2/13/2009 |
Q: I need to compare a range of cell content in order to identify dupes without removing them. Any ...
A: You can use the COUNTIF formula on a separate column, and then filter by numbers greater than one. ...
|
| partial values in a field | 2/12/2009 |
Q: I'm trying to come up with a formula that will look at only the 4th through 7th values in a field so ...
A: You can use the MID formula for that, something like: =MID(A1,4,4) In this case, it will take the ...
|
| Excel Combination of numbers formula | 2/12/2009 |
Q: I would like to develop a formula in Excel that will display all combinations of numbers for a ...
A: I had a reader comment on one of my answers that can give you some ideas on how to proceed: ...
|
| Need an Excel Formula | 2/11/2009 |
Q: I have two different columns which includes some same figures. I need to match those items. If I try ...
A: You can use the MATCH function, comparing each element on one column against the other column. For ...
|
| excel spreadsheet issue | 2/11/2009 |
Q: what is the green symbol in a cell on the upper left side? I am using excel speadsheet. it only in ...
A: That is usually a sign that the cell has some type of error. You can control on which cells it ...
|
| importing time as text from excel to access | 2/11/2009 |
Q: I need to import and excel sheet into Access. The columns that are time - as in time on and off - ...
A: I am not sure on how to import to Access, but to make them text in Excel you can use the TEXT ...
|
| In VB, selecting the first blank cell in a column | 2/11/2009 |
Q: I'm trying to write an excel macro that will copy the contents of a worksheet and paste them into a ...
A: You can combine the COUNT function from Excel with the Offset from VBA, with something like: ...
|
| Cascaded validation | 2/11/2009 |
Q: I am trying to find a way to validate a cell based on the value chosen from a list in another cell. ...
A: It looks like you are in the right direction. I am not sure if you know this tutorial for this type ...
|
| Need help with IF statement(s) | 2/10/2009 |
Q: My question is: I have a list, and there are many names on it, a lot is double or more. I want to ...
A: It is an array formula, and the way to tell Excel that it is an array formula is to press down Ctrl ...
|
| Table to Text to Time | 2/10/2009 |
Q: I have a very basic html based table that displays From To Call Date/Time ...
A: You can use paste special again to solve it. In this case, type the number 60 on a cell, copy it, ...
|
| Table to Text to Time | 2/10/2009 |
Q: I have a very basic html based table that displays From To Call Date/Time ...
A: I am not sure on what the specific steps that are failing, so I will give you some general advice ...
|
| Excel Function IF and Quartile | 2/10/2009 |
Q: I have two columns of data: column A shows the category (which is a colour)and column B shows the ...
A: You can use the QUARTILE formula for that. If you can sort the data, the formula can be used ...
|
| excel formula | 2/10/2009 |
Q: I need a formula that will work out prices for me. eg. if i buy between one and ten products then ...
A: You can use the VLOOKUP function for this. First craft a table with the ranges, for example on ...
|
| excel formula | 2/10/2009 |
Q: In column A i have to input the whole numbers, now for every given numbers i need to check that ...
A: You can try with the following formula on C2: =IF(ISNA(MATCH(A2,$B$2:$B$11,0)),"","X") And copy it ...
|
| Recursive formula checking values | 2/9/2009 |
Q: problem. I have a list of workstations ID to which I deployed software on several days (also ...
A: With that layout, you could use the following array formula on cell D2: ...
|
| Conditional Formatting in Excel 2003 | 2/9/2009 |
Q: If the value in one cell is less than the specified cell in the conditional format, I wish the text ...
A: In order to determine why it is behaving that way, I would need to see the formula that you used on ...
|
| Excel MATCH function and linking to a closed file | 1/27/2009 |
Q: I am using Excel 2003 with Windows XP. I have written a formula using the INDEX and MATCH functions ...
A: As far as I know, that is the way that the MATCH formula works. I am not aware of custom made ...
|
| counting consecutive blank cell | 1/25/2009 |
Q: E FIRST TRY TO UNDERSTAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need to ...
A: You are almost there with that array formula. Change the MAX to the LARGE function, and you will ...
|
| excel 2003 conditional formatting | 1/24/2009 |
Q: I have start date on column d and end date on column e (dd-mmm-yy)-- i would like to highlight those ...
A: It looks like you are doing the reverse of the conditional formatting, changing the values of a cell ...
|
| How can I get texts toghether? | 1/23/2009 |
Q: Good Morning Mr. Zapico, how are you? I'm a brazilian 23 years old guy, that works a lot with Excel ...
A: You may use the concatenation function "&" to get the text together on a cell, for example: ...
|
| days, weeks, and months | 1/22/2009 |
Q: Miguel, You've been able to answer my other complex time formula questions, so I figured you'd be ...
A: Using today's date you could do something like this: week1: ...
|
| VB formula generates value error unless one does a manual enter on formula bar | 1/22/2009 |
Q: This problem has taken me by surprise. I use this formula ...
A: The error may be in the international set. Try changing the SI to IF in the VBA code, and check if ...
|
| Survey | 1/22/2009 |
Q: Hey, I am doing this type of inspection sheet where I have some questions which its answers ...
A: You can use VLOOKUP for this. On a separate sheet create a table with a format like: Response ...
|
| Formal | 1/22/2009 |
Q: I have an excel workbook I'm building with over 25 taps. What I want to do is enter the date into ...
A: If the cell is the same on all tabs, you can select all of them (right click on the tab name, then ...
|
| How to write degrees | 1/22/2009 |
Q: i have a doubt how to write degress eg: ( 10 -3)this -3 i have to write as a 10the power of -3. i ...
A: It depends if you want to calculate or show. If you want to calculate, you can use the ^, something ...
|
| excel | 1/21/2009 |
Q: to create a master template which can be updated easily I want any information including logos to ...
A: There are solutions for the two issues that you have, but they are not exactly simple. For the zeros ...
|
| excel formula | 1/20/2009 |
Q: Here's another one I'm wrestling with. I need the spreadsheet to display a discount on a price when ...
A: Sure, and sorry for the late response. You can use two IF formulas here, something like: ...
|
| excel formula | 1/19/2009 |
Q: I have created a spreadsheet in which one cell (A24)- when clicked - presents a drop down menu with ...
A: You could use the OR formula for this, something like: ...
|
| Create Ledger Automatically | 1/19/2009 |
Q: i have table in excel like this Date Particulars Debit Credit 1/1/09 Cash 2000 ...
A: The simplest way of achieving this is by using the autofilter (menu Data->Filter). It will create ...
|
| formula conversion | 1/19/2009 |
Q: an excel file in my office has a cell with a formula of =+IF(A1>0,(A1*37.5)/A1+12.5*A1,0). i can ...
A: If you want to reverse the formula, you could do with something like: =(A2-37.5)/12.5 In this case, ...
|
| Excel Queary | 1/17/2009 |
Q: If i key Mr J D Woods or Mr D Woods into A1, then in B1 i should get the result as Wood J D or ...
A: Sorry for the late response, it has been a long weekend. Regarding your question, the trick is on ...
|
| Lottery | 1/16/2009 |
Q: I used the combin formulae to find out the amount of time i could get 6 sets of numbers from 37 ...
A: Sorry for the late response, this has been a long weekend. Regarding your question, usually the ...
|
| radio buttons in excel | 1/14/2009 |
Q: I am working on a survey which has 4 categories - basic, advanced etc. Each category is assigned a ...
A: Forms should work fine. In fact, if you group them together, you only need one linked cell for the ...
|
| Roundup formulas in Excel 2003 | 1/14/2009 |
Q: I have a formula =SUM(G2*$J$23-E2) G2 is 208, j$23$ is .12, and e2 is 44. The answer is -19.04. ...
A: You can use the ABS formula on top of this one, to make it return always the positive value, ...
|
| Roundup formulas in Excel 2003 | 1/14/2009 |
Q: I have a formula =SUM(G2*$J$23-E2) G2 is 208, j$23$ is .12, and e2 is 44. The answer is -19.04. ...
A: I am assuming that you want it to be -20. It makes a difference, as the formula that is presented ...
|
| Excel Formula | 1/13/2009 |
Q: Miguel, I am using an Excel 2003 Workbook to calculate 12 month rolling totals for my production ...
A: There are a few things that you can do, with different degrees of inconvenience. For example, you ...
|
| An Excel Question | 1/11/2009 |
Q: i have the folowing table in excel sheet: Bus_ID Location Company Fuel 1 ...
A: In this case I would use the autofilter (menu Data->Filter). You can filter by the Location column ...
|
| adding multiple cells | 1/9/2009 |
Q: I am trying to add 7 cells. The problem is if 4 of the 7 cells have numbers and the other 3 do not I ...
A: Sorry for the late response, I was out for the weekend. In this case, you could use the N function, ...
|
| excel - function | 1/9/2009 |
Q: A1 = 2m B1 = 2m If so, and we multiply the cell A1 & B1, is it possible to get an answer ...
A: You could use custom format to do that, but not the superscript. Select the cells A1 and B1, go to ...
|
| turn the color of the letter | 1/8/2009 |
Q: i want to know how to get the letter into red if i hit that particular letter in excel sheet.like ...
A: You can use the menu Format->Conditional formatting for this. Select the cell that you want to ...
|
| confussed!!! | 1/8/2009 |
Q: So.. I'm trying to enter data into excel and I want the cells to be numbered but I was wondering if ...
A: If you use a cell to store the number, you can input the number 1 on the first cell (for example ...
|
| Insert Formula & Autofill Range | 1/7/2009 |
Q: Sorry I am not an expert, but I have a reset button that fires a macro. I want to clear cells insert ...
A: I am unable to replicate your error, the code works as expected in my computer (autofilling the ...
|
| countif | 1/7/2009 |
Q: I have a countif formula that counts a range, outputting how many 'a', 's' or 'c' etc there are. (a, ...
A: COUNTIF will only work with one condition, so you can write three formulas and add them together, ...
|
| Spreadsheet | 1/6/2009 |
Q: can you please answer this: I have a spreadsheet where I have to maintain certain figures on a ...
A: I will start with the graphs, and that may also solve the hiding issue. The Excel charts will not ...
|
| Unsure of formula for comparing averages | 1/6/2009 |
Q: I may be overthinking this but I'm comparing total average for two data groups and I'm not sure of ...
A: I am not sure on what you want to compare, but probably you can do it with an IF formula. For ...
|
| Excel File | 1/6/2009 |
Q: I have an excel file with times not formatted in the right format. example 5;00 or 13;00, the times ...
A: One potential solution can be to use the menu Edit->Replace, and substitute all the appearances of ...
|
| Filters | 1/5/2009 |
Q: I am working on a problem of 21> age <25. If I use custom I can get it to work. If I set it up in ...
A: The way the criteria field works on the Advanced Filter can be a bit counter intuitive. The filter ...
|
| Averaging and returning values in Excel | 12/18/2008 |
Q: I have 3 large columns containing numbers of data (negative and positive numbers all below 200 in ...
A: If your ranges start at the cells 10, 20, etc, then you can change the formula to: ...
|
| formula change | 12/18/2008 |
Q: In one column I have a function to calculate the average of a set of data points, each row will have ...
A: One way of doing this can be the following: - Select the column with the formulas that you want to ...
|
| check for data in 2 cells and make calculation bases on the cell with data | 12/18/2008 |
Q: I am trying to do the following: Input data in either cell A1 or B1 then have cell F1 run 1 of two ...
A: You could use an IF function to decide which formula to run, something like: ...
|
| filter | 12/18/2008 |
Q: I have a spreadsheet, want to sort it, and am using "auto filter" which works great. My problem is ...
A: I am not clear on the exact layout, do you have separate rows for each animal, or they are in the ...
|
| Excel Summary Sheet | 12/17/2008 |
Q: I have a worksheet where each column contains a company and the three rows below show a specific ...
A: In this case, I would suggest to bring all the data to the summary sheet, with all the accounts on a ...
|
| excel calculation | 12/16/2008 |
Q: I need to find out how to calculate a total from the best 4 of 6 figures. I currently use a formula ...
A: You can use the SMALL or LARGE formulas. The accept two parameters, the first one being the range ...
|
| Adding 12, 24, 18, 36 months to existing date based on a table | 12/16/2008 |
Q: I am trying to add months to an existing date based on a cell that contains a score, if that score ...
A: You are very close. The only part that is missing is make the +12 dynamic, to do so we can use the ...
|
| Alternate shading only when data changes? | 12/16/2008 |
Q: Can shading alternate based on a change in data? Ex. Col A lists "ADT" in rows 4-11; changes to ...
A: Good question. For this I would use an additional column, that can later be hidden, with a formula ...
|
| Excel Vlookup | 12/16/2008 |
Q: I have an excel sheet which has columns- Item name, cost price, selling price, profit, buying price, ...
A: Sorry, but I don't write custom code any more on this site, it became too involving. My suggestion ...
|
| large, min for text | 12/15/2008 |
Q: Miguel, I came across your answer to a previous question regarding mode for text. ...
A: In this case, the LARGE formula needs some tweaking to do the trick. The reason that it is ...
|
| Excel - shading empty rows | 12/15/2008 |
Q: Is there a way to automatically shade empty rows? I am good with formulas and conditional ...
A: You could use conditional formatting for that. Select the range that you want to be shaded, and ...
|
| excel or access solution urgent | 12/14/2008 |
Q: i have two columns of data every one of this data is consisting from 14 digits i want to link this ...
A: In this case, you may have uncovered some records on column A that are not in column B. My proposal ...
|
| excel or access solution urgent | 12/13/2008 |
Q: i have two columns of data every one of this data is consisting from 14 digits i want to link this ...
A: You could use a third column beside the one with 2179 elements (I will suppose column B), with a ...
|
| user form | 12/13/2008 |
Q: I have this problem with a userform I created. I'm a beginner at VBA tho. It has 2 option buttons ...
A: With the code you posted, I would add the statements as below: If Opt1A Then Application.Run ...
|
| Excel Monthly Production Tracking | 12/13/2008 |
Q: I have a real challenge in front of me. I own a small manufacturing company, and we produce several ...
A: In this case, you could craft a table like: Product Metric Time Value XYZ Machine ...
|
| Auto list in numeric order | 12/12/2008 |
Q: I'm pretty green in Excel. I have a list of numbers that I add to periodically. I would like the ...
A: You would need macros to do that. You can go to menu Tools->Macro->Record new macro, and record a ...
|
| Append cell value to a different spreadsheet | 12/12/2008 |
Q: I am also using Excel 2003 and Windows XP. I have many spreadsheets with one worksheet each ...
A: My suggestion would be to open both workbooks, type the equal sign on the cell A1, and navigate to ...
|
| user form | 12/12/2008 |
Q: I have this problem with a userform I created. I'm a beginner at VBA tho. It has 2 option buttons ...
A: For the first issue, I would include an Exit Sub statement, something like: MsgBox "Veuillez saisir ...
|
| HELP with IF / VLOOKUP function - please! | 12/12/2008 |
Q: I have one large table of internal codes associated with to cities & states. There are three ...
A: You could try with something like: =IF(ISNA(VLOOKUP(A2,'codes to ...
|
| If Statement Macro | 12/12/2008 |
Q: Please help me, i'm trying to use If Statement in VB Script Macro for an example I want my ...
A: What I meant by creating the table on the range was to enter these values on these cells: A1: 0.3 ...
|
| If Statement Macro | 12/12/2008 |
Q: Please help me, i'm trying to use If Statement in VB Script Macro for an example I want my ...
A: You could do it with VLOOKUP. Create a table with the following data, for example on the range ...
|
| Compareing two data fields | 12/11/2008 |
Q: I am working on something for my manager. Basically he has a spreadsheet that has employees first ...
A: In this case, you could use SUMPRODUCT, for example: ...
|
| Sum row on column criteria | 12/11/2008 |
Q: I want to sum one individual row with one or more criteria are where the values are spread through ...
A: I could suggest checking the Excel help for the OFFSET formula, as it can create ranges based on ...
|
| Adding a number each week | 12/10/2008 |
Q: I'd like to learn a formula that would automatically add my paycheck to a running total. For ...
A: One option could be to prepopulate the spaces where you want the new figures with formulas like: ...
|
| Multiple filters in one Excel spreadsheet | 12/10/2008 |
Q: Hope you can help me. I am an avid user of Excel but by no means a power user. I have a sales ...
A: As far as I know, Excel doesn't support two automatic filters on the same worksheet. If you want to ...
|
| If, then formula? | 12/9/2008 |
Q: I am wondering if there is a formula that would allow me to input different words in the same cell ...
A: Sorry for the late response, life is busy near the holidays :) Regarding your question, you could ...
|
| I need to delete rows with a zero or negative number | 12/9/2008 |
Q: I have created a macro to manipulate an excel file using an inventory stock status report exported ...
A: I would suggest the use of filters after the macro (Data->Filter->Autofilter, and custom filter to ...
|
| pin number in excel | 11/25/2008 |
Q: I have created a form in excel for a sales person to input discounts to a customer, this form then ...
A: One way would be to use a custom format for the cell (menu Format->Cells->Number tab->Custom) as: ...
|
| Microsoft Excel Help | 11/24/2008 |
Q: I have the following table of information Ref Name Dat1 Freq Value 12345 Steve 3473 1 4000 ...
A: I am assuming that the structure will not change, in the sense that the references are grouped ...
|
| assigning number values to words | 11/23/2008 |
Q: I have a spread sheet of data that has all word values like 'agree' 'disagree' 'neutral'...etc. how ...
A: If it is a limited set of values, you could use an IF formula for this, something like: ...
|
| MS Excel help | 11/23/2008 |
Q: I have a problem in MS Excel spreadsheet, I have a weekly data of NASDAQ like Oct 17, 2008, Oct 10, ...
A: You could use the VLOOKUP function for this. It search on the first column of a range of data, and ...
|
| Difficult problem | 11/21/2008 |
Q: I have the following problem: hours | startdate | enddate| week1 | week2| week3 | week4 10 ? ...
A: Are you sure that you are entering it as an array formula? The #VALUE! error will appear if the ...
|
| Difficult problem | 11/20/2008 |
Q: I have the following problem: hours | startdate | enddate| week1 | week2| week3 | week4 10 ? ...
A: With your current layout, you could use some array formulas like these: First value: ...
|
| Text to Equal Number | 11/19/2008 |
Q: I am creating a spreadsheet and i have apx 10 colums with the word YES in them, i want this to equal ...
A: I am not sure on what you are trying to do here. If you want to count the number of YES in each ...
|
| Excel Formula | 11/18/2008 |
Q: So I come back again with another excel formula question. This one should be really simple. Am not ...
A: In this case I would recommend a pivot table. Select the full range of data, go to menu Data->Pivot ...
|
| Transfer data in text file into a spreadsheet | 11/18/2008 |
Q: Could you please tell me how can I change a data in text file into a separate column in a regular ...
A: One way is to use Text to columns for this. The first step would be to copy the full text range to ...
|
| drop down list... | 11/18/2008 |
Q: Basically, I have created a list on sheet 2 of a spreadsheet and I want to pick from this list on ...
A: Yes, it is possible. You can name the range that you want to use as the origin for the list (select ...
|
| Excel | 11/6/2008 |
Q: sir, i have some entries to do in excel. like a daily attendance. now i have students that enter and ...
A: You can use the VLOOKUP formula for that. For example, if you create a list of numbers and names on ...
|
| Formula Calculating Correctly | 11/6/2008 |
Q: I have a formula: =IF(AND(ISBLANK(K4))*(K4<H4)*K4<RPTDate,--(NOT(L4="ACTL"))*1). The problem is the ...
A: I am not sure of what you want, probably the error is on the way that the comparisons are grouped ...
|
| Conditional Formatting Question Again | 11/3/2008 |
Q: Miguel, Welcome back! I need your help, again. Recently, you provided me a formula that would ...
A: The formula you have posted is missing the parenthesis for the NOT function, maybe that is the ...
|
| Calculate Date and Time Difference | 10/31/2008 |
Q: I have entry(d column) ,exit (e column) times on my excel. What I need to do is take a look at day ...
A: Sorry for the late, I was out for the weekend. Regarding the question, I am not sure on how you are ...
|
| Formatting | 10/30/2008 |
Q: I have a formula, that is an if then statement. The formula works fine, except for when I set a ...
A: The formula can only carry values, for the format you need to set up conditional formatting. You ...
|
| Log | 10/29/2008 |
Q: Honor Gain = (Log[1.1](Absolute Value(Your Honor - Defenders Honor) + 1) + 15) * ((Defenders ...
A: I am not sure if I got this right, specially on the second parameter. The cell references are made ...
|
| Date Formula | 10/23/2008 |
Q: I am trying to create a formula that will calculate the 4th Thursday of each month, starting with ...
A: You could use a formula like: ...
|
| Excel Formula | 10/23/2008 |
Q: Zapico, Perhaps you might help with the following problem, Mr. Umas was unable to understand: ...
A: For the sake of simplicity, I would assume that each group contains only one person, and that you ...
|
| excel formulas | 10/23/2008 |
Q: I am trying to create an excel spreadsheet that i can enter three separate amounts into that will ...
A: It looks like the question is addressed to a different expert, I would try to make a simple answer, ...
|
| Excel Help-Combinations | 10/23/2008 |
Q: I have a homework question that I need help with.I'm not sure if I can do it on excel.There are ...
A: I am bit rusty at math, so the numbers may be wrong, but I think that number of combinations here ...
|
| [Update] Improvement to Macro - Delete multiple cells at 1 time? | 10/22/2008 |
Q: I asked you a question previously and you referred me to this website: ...
A: In this case, I would recommend to use the Cells collection, something like: For Each myCell In ...
|
| Excel Formula | 10/22/2008 |
Q: I’m trying to create a table, using a formula and not a pivot table, in where repeated false data is ...
A: I misunderstood the question. You may try with a formula like this: ...
|
| Improvement to Macro - Delete multiple cells at 1 time? | 10/22/2008 |
Q: I asked you a question previously and you referred me to this website: ...
A: To avoid the type mismatch error, you can check that the parameters that you are passing to the ...
|
| MULTIPLE V-LOOKUP | 10/21/2008 |
Q: Scenario 1 A B 1 Name Marks 2 Amir 25 3 Bobby 63 Sheet 1 ...
A: I have answered this question before, you can find it at: ...
|
| Bill Reminder | 10/20/2008 |
Q: Miguel, I got a challenge for you. I am working with a budget forecast I'm trying to create in ...
A: :) The main reason that a SUM formula won't work is that there are some text values on the range, ...
|
| Excel Formula | 10/20/2008 |
Q: I’m trying to create a table, using a formula and not a pivot table, in where repeated false data is ...
A: I am not sure if this what you are looking for, this formula will return the group on column A if ...
|
| counting cells | 10/20/2008 |
Q: I have a spreadsheet in Excel, with actual / forecasted spends for every month in columns (from ...
A: You can use TODAY in your SUMIF, but only if your dates are entered as dates, not as text. If so, ...
|
| Excel Vlookup | 10/20/2008 |
Q: I have 2 worksheets in a workbook. The first worksheet has a list of employees who are absent and ...
A: If you are using VLOOKUP by now, you just need to use a broader range of data, and change the third ...
|
| Excel Database Search | 10/18/2008 |
Q: I have an Excel database where every row is a new data entry. (I'll simplify my database by using an ...
A: Sorry for the late response, these have been really busy days. Regarding your question, my ...
|
| time card worksheet | 10/17/2008 |
Q: I have a time card worksheet, how can I make one of my columns to always be PM and maybe just be ...
A: I know that this is not the same, but I cannot think on any way, without the use of worksheet macro ...
|
| Excel Merge | 10/17/2008 |
Q: I need to merge a worksheet full of data into 30 separate worksheet. Help
A: Sorry for the late response, it has been a busy end of the week for me. The examples on the ...
|
| Rearrange Randoms | 10/16/2008 |
Q: Can you help? A1:A10 = 1 to 10 B1:B10 = Random Numbers C1:C10 = Ascending Sorted of B1:B10 (Using ...
A: Sorry for the late response, it has been a busy end of the week for me. Regarding your question, I ...
|
| Combining 2 Pivot Tables | 10/15/2008 |
Q: First time I have ever tried such a forum.... Accountant who has self taught Excel... The Q: I have ...
A: The times that I have needed to combine pivot tables, I have chosen to combine both data origins in ...
|
| Time Average with Autofilter | 10/15/2008 |
Q: What formula to use to have an average adjust to data that is being filtered? For Example: If I ...
A: You can use the SUBTOTAL formula, for example: =SUBTOTAL(101,A4:A100) This will recalculate the ...
|
| Microsoft Excel Formulas | 10/15/2008 |
Q: I have to keep scores in a fantasy football league, I am fairly new with excel. I need to know how ...
A: I am not sure if this is what you are looking for, but I hope that at least it can give you some ...
|
| days into year,month and day | 10/15/2008 |
Q: Please advise on how to convert the 10258 days into 28 years 1 month and 1 day in excel
A: It depends on how you want to make the calculations. For example, you may use directly the date ...
|
| vlookup/for each? | 10/15/2008 |
Q: "Hi, I have coloumn containing data. Say I'm looking for the word "excel" in any combination in ...
A: You could try with an array formula like this: ...
|
| Vlookup | 10/14/2008 |
Q: In my cell A3 is a number 001700. In my "rates" spreadsheet the number reads 1700. Excel doesn't ...
A: If you want to ensure that the formula looks for a number, you could do something like this: ...
|
| excel comparision | 10/14/2008 |
Q: I am having two excel files.One of them has the values which are allowed and the other one is the ...
A: I think that you addressed the question to other expert, but I would try to answer with an ...
|
| advanced goal seek | 10/14/2008 |
Q: respected sir 1.we have any advanced options like getting goal by changing n multiple cells by equal ...
A: There is an add in called solver, that can fit your needs. You may need to install it from the menu ...
|
| If the encapsulated function's result is >=1, return 1, otherwise return the function's result. | 10/13/2008 |
Q: I hope you can help and thank you so much if you can :-). I have the following main function ...
A: You could try with the MIN function instead of another IF function, something like: ...
|
| dependent lists | 10/11/2008 |
Q: I want to creat dropdown list for three titles( VALIDATION OPTION). A , B , C. In one cell(A1) A ...
A: You can repeat the same procedure that you did for the first dependent list. You will create names ...
|
| How can I put in continuous numbers in Excel? | 10/11/2008 |
Q: Sir, I'd like to thank you for your prompt response to my previous query. Sir, how can I get ...
A: In order to make Excel understand that it is a formula, you must start by typing the equal sign. ...
|
| Moving Rows | 10/11/2008 |
Q: In a previous post you supplied this solution (which works great) to move rows of data based on the ...
A: You can use the Or statement to link various conditions together. In this case, change the line: ...
|
| Excel | 10/10/2008 |
Q: I have many columns will diferent values in all of my columns. Is there a way I can identify if ...
A: I suppose that you want to check for consecutive values on the same column, one after the other. If ...
|
| How can I put in continuous numbers in Excel? | 10/10/2008 |
Q: Sir, I'd like to thank you for your prompt response to my previous query. Sir, how can I get ...
A: You could do that by typing the first number, and then use a formula like this on the second row: ...
|
| Combining Data | 10/9/2008 |
Q: Hey I am back with another question. By the way, the indirect.ext function has done wonders for me ...
A: You could combine the OFFSET function with the INDEX and MATCH. For this, you need an extra column, ...
|
| Survey Mail Lists | 10/8/2008 |
Q: I am trying to help out someone in our office streamline a VERY time consuming process. I'm hoping ...
A: You could use the MATCH function. For example, if your export is on the range A1:A1000 on sheet2, ...
|
| Conditional formatting for error values | 10/8/2008 |
Q: I have been following your advice on how to prevent blank values from being plotted on line charts ...
A: In this case, I would check first the formula on the worksheet before implementing it on the ...
|
| Getting data from multiple workbooks | 10/8/2008 |
Q: I have multiple workbooks i.e(09082008.xls) representing each day's totals and i want to sum the ...
A: You cannot use the SUM function here (or at least it is not going to give you any advantage), but ...
|
| Schedule for processing 15 units a day | 10/7/2008 |
Q: I am creating a schedule for processing 15 units a day. The user will enter the date the first unit ...
A: One way of doing this, that can simplify the formulas, is to craft first a list of all the weekdays ...
|
| Excel 2003 - Copy rows from a total list of a workbook to be filtered on 5 people? | 10/7/2008 |
Q: I have a requirement. I have a Issues list in one spreadsheet and I need to copy only those rows ...
A: In this case I would use the Advanced Filter instead of the Autofilter, as you can apply the five ...
|
| Excel Macro | 10/7/2008 |
Q: I am just starting out at this. I am having a problem generalising a working macro I wrote. The ...
A: You may try using the ActiveSheet object, for example when setting the Source range: ...
|
| Lookup where result factor have multiple cells | 10/6/2008 |
Q: I have the following template (see attachment) where I have the following formula in cell C17: ...
A: In this case, I would switch to an array formula, the previous one worked well with three levels, ...
|
| Microsoft Excel 2000 Data analysis | 10/6/2008 |
Q: Sir, I am an undergraduate medical student doing a descriptive clinical study. I have entered ...
A: In this case, because of the tabular format that you have given to your data, the tool that can help ...
|
| excel links | 10/3/2008 |
Q: I want to link a cell from one of several worksheets to a cell on sheet 1. That I can do.. but I ...
A: One function that can help you in this INDIRECT. It takes a string, and if it can be interpreted as ...
|
| Multiple count/sum | 10/3/2008 |
Q: Refering to an ealier question can you help me Based on this formula I have two Sheets - Sheet1 ...
A: You could use a formula like this: ...
|
| Excel Date | 10/3/2008 |
Q: I would like to have a fx in excel that will create a column of consecutive dates (in the format ...
A: Excel needs to recognize the first cell as a date. You may check this article about the Fill ...
|
| Lookup where result factor have multiple cells | 10/3/2008 |
Q: I have the following template (see attachment) where I have the following formula in cell C17: ...
A: This formula could do the trick: ...
|
| excel formula | 10/2/2008 |
Q: Greetings, I've been given a work assignment and I am not sure how to accomplish it. I work for an ...
A: You cannot have both things at the same time on a cell, formulas and values, but you can use the ...
|
| EXCEL MACRO RUN TIME ERROR PROBLEM | 10/2/2008 |
Q: From the sheet 1, I need to copy the 2nd observation from column B (Nasdaq Ticker for Mutual funds) ...
A: There are some things that probably are not working as expected: - The offsets are usually after ...
|
| Excel Date | 10/1/2008 |
Q: I would like to have a fx in excel that will create a column of consecutive dates (in the format ...
A: To create consecutive dates on a column, you just need to type the first day that you want, and the ...
|
| Multiple tests within one cell | 10/1/2008 |
Q: I want to test multiple sets of data to come up with a result. To give you a little background, we ...
A: I am not sure if I have understood the formulas, but it looks like you could benefit from the use of ...
|
| Excel macro | 10/1/2008 |
Q: I have compiled the following code on sheet 2 of my workbook and it works. However when i go to run ...
A: If you want to do that by code, you could load an array with the values, create a loop, and use the ...
|
| Logical tests | 10/1/2008 |
Q: I am having problems doing a red, amber, green scenario using nested 'If' tests. I need to show if ...
A: There are two things here, what are the contents of the cell and what are the formats. The contents ...
|
| Excel macro | 9/30/2008 |
Q: I have compiled the following code on sheet 2 of my workbook and it works. However when i go to run ...
A: You can use the statement: On Error Resume Next If you are going to do that, I would recommend ...
|
| Conditional Formatting Question | 9/30/2008 |
Q: I have to count the # of conditional formatting errors returned. Is it possible to create a formula ...
A: Actually a pivot table is usually a good idea to consolidate data, but you know better than me if it ...
|
| Conditional Formatting Question | 9/30/2008 |
Q: I have to count the # of conditional formatting errors returned. Is it possible to create a formula ...
A: As you are using a formula for conditional format, you can use the same logic inside a SUMPRODUCT ...
|
| query regarding excel macro | 9/30/2008 |
Q: is there any possibility to compare entire row with another row.(i.e to check for the duplicate rows ...
A: If you want to remove duplicates, I would suggest the advanced filter (menu Data->Filter->Advanced ...
|
| Pivot Table | 9/29/2008 |
Q: when i make pivot table on data, after made pivot table, when i add more data and click referesh ...
A: The manual way is by using the wizard again, going back to the step 2 and selecting the new range of ...
|
| How to use Workday formula properly. | 9/29/2008 |
Q: I have to create a spreadsheet that automatically tracks deadlines. I've figured out how to ...
A: One way of doing this is by using Goal seek. For example, with you date on A1, enter a formula ...
|
| Returning multiple rows of data doing lookup | 9/29/2008 |
Q: Although I have 2 to 3 major problems I'll start with the biggest. I have a Text file that I import ...
A: I had the same problem recently with the import data feature, it worked out when I changed it to a ...
|
| Excel VBA - Intersection | 9/29/2008 |
Q: I am trying to write a bit of code which looks at a column and a row, finds where they intersect ...
A: As you are defining ranges, you could use the Entire Column and Row directly on the definition, ...
|
| dependent lists | 9/29/2008 |
Q: I want to creat dropdown list for three titles( VALIDATION OPTION). A , B , C. In one cell(A1) A ...
A: This is a tricky one, as you probably cannot use validation directly on the cell, if you want to ...
|
| macro to insert a 'group name' in a colum | 9/27/2008 |
Q: I don't have any experience with Excel macros. In one column I have STREETS names (could be more ...
A: I still think that VLOOKUP can solve your issue. I will suppose the following ranges: Customers: ...
|
| R1C1 formula without dollar sign | 9/27/2008 |
Q: I am trying to write a code for dividing 2 cells, which I will subsequently drag to other rows, but ...
A: If you want to have relative references, use square brackets on the numbers on the RC references, ...
|
| Excel Question | 9/27/2008 |
Q: My question is a follow up to my previous question : ...
A: In this case you can use a formula similar to the one in B10, but instead of concatenating text it ...
|
| macro to insert a 'group name' in a colum | 9/26/2008 |
Q: I don't have any experience with Excel macros. In one column I have STREETS names (could be more ...
A: Sorry, but not enough. What are the rules to assign a group name? Do they exist anywhere, and if ...
|
| Automatically Insert Thick Border Line | 9/26/2008 |
Q: I have a 2000 item database. For public presentation I will make three sorts for easy assessment. Is ...
A: Actually, it is not simple. Conditional formatting allows you to change the borders, but you cannot ...
|
| Excel Question | 9/26/2008 |
Q: My question is a follow up to my previous question : ...
A: You can write the following formula in B10: =COUNTIF(A1:A10,"C") & "/" & =COUNTA(A1:A10) And thanks ...
|
| SETTING FIXED SORTING CRITERIA | 9/25/2008 |
Q: AFTER COMPLIMENTS COULD YOU PLEASE HELP ME SETTING A FIXED SORTING CRITERIA IN A PROTECTED ...
A: If you are doing the Top 10, you could get the values using the LARGE function, and the INDEX/MATCH ...
|
| time and date macro in excel | 9/25/2008 |
Q: I want to create a macro for the time and date in excel, but I need the time to be the current time ...
A: You can modify your macro, substituting the hard coded values that the macro is using for the VBA ...
|
| formula to count date range | 9/25/2008 |
Q: Miguel. I have a spreadsheet that has dated entries. I need a formula that will count the date ...
A: You could use two COUNTIF formulas, something like: =COUNTIF(A1:A100,"<=" & ...
|
| Finding the duplicates | 9/25/2008 |
Q: I have a problem in actually find the duplicates in the below example. Is there any formula that I ...
A: You could use SUMPRODUCT to find duplicates, with a format similar to: ...
|
| Excel Updating Dates | 9/25/2008 |
Q: I have a workbook with many sheets. Once a quarter, the data is updated. I have in the footer of ...
A: If you record a macro, you could take a look at the generated code to be sure it will fit your ...
|
| Formula for Excell | 9/24/2008 |
Q: Good afternoon, I have a training log spreadsheet of individuals. I need to have a formula that ...
A: Even if it doesn't look the right formula, you can use SUMPRODUCT for this. For example, if the ...
|
| Excel help on calculating closing stock and credit sale notices | 9/24/2008 |
Q: Sir, My fried having a computer sales and services shop. At present I am maintaining the opening ...
A: In this case I would use the Autofilter (menu Data->Filter->Autofilter), and use a custom filter on ...
|
| excel subtractions | 9/24/2008 |
Q: hey can you help me with how to subtract 1101-111 with each digit per cell. also showing the carries ...
A: I am not sure if this is what you want, this will split the result of the subtraction on its digits: ...
|
| Excel Updating Dates | 9/24/2008 |
Q: I have a workbook with many sheets. Once a quarter, the data is updated. I have in the footer of ...
A: If the date is always on the same cell, you can select all the sheets that you want to update at ...
|
| two criteria sumif | 9/23/2008 |
Q: column A contains dates in the following format month/day/year. column B contains numbers. I want ...
A: There is a SUMIFS function on Excel 2007, that allows multiple condition for SUMIF, but that is not ...
|
| Creating a Password for a Custom View | 9/23/2008 |
Q: I'm working with a worksheet, that has several columns that contain information that we don't want ...
A: Here is some code that can give you ideas: Dim strPass As String Sub ShowColumns() strPass = ...
|
| Excel help on calculating closing stock and credit sale notices | 9/23/2008 |
Q: Sir, My fried having a computer sales and services shop. At present I am maintaining the opening ...
A: In order to replace the contents of the cells you will need macros, so if that is a must you can ...
|
| random assignment | 9/22/2008 |
Q: i have a list of about 200 names and i want to randomly distribute them between two people. do you ...
A: You could use the RAND() formula, and then decide if you want to freeze the values or keep them ...
|
| Excel lookup & query generation | 9/22/2008 |
Q: I have a matrix of table which to define compatible accessories for my company products. (Cell from ...
A: You may create a matrix in a separate sheet, with formulas like this on B2: =IF(Sheet1!B2=1,"INSERT ...
|
| Creating a Password for a Custom View | 9/22/2008 |
Q: I'm working with a worksheet, that has several columns that contain information that we don't want ...
A: My recommendation here would be to use the menu Tools->Protection->Protect Sheets with the standard ...
|
| Excel help on calculating closing stock and credit sale notices | 9/22/2008 |
Q: Sir, My fried having a computer sales and services shop. At present I am maintaining the opening ...
A: The exact formula will depend a lot of your layout. For example, if your sheet2 has the full name ...
|
| Help with IF(AND formula | 9/22/2008 |
Q: I need help writing a formula. I think it will be an IF(AND formula. The user of the worksheet ...
A: You are right, the IF and the AND formula can fit here. For example: ...
|
| Excel Formula | 9/22/2008 |
Q: I am pretty good on Excel, but am completely out of ideas as to how to write a formula to meet this ...
A: In this case I would use Solver instead of Goal seek. If you don't have it installed, you can do it ...
|
| VLOOKUP | 9/22/2008 |
Q: Scenario 1 A B 1 Name Marks 2 Amir 25 3 Bobby 63 Sheet 1 ...
A: For the first question, if you can make some type of rule based on the name, it is feasible. For ...
|
| #Vaule | 9/20/2008 |
Q: I am trying to make a database for quotes I have figured out the Vlookup feature with a dropdown box ...
A: The #VALUE! error with the VLOOKUP formula usually happens because the lookup value on not found on ...
|
| A macro for e-mailing from an Excel Workbook | 9/20/2008 |
Q: I wonder if you could possibly help me. I am trying to install the following macro in my Excel ...
A: This macro uses Microsoft Outlook, and from the error that it gives, it looks like the machine ...
|
| Enter data in a protected Excel sheet | 9/20/2008 |
Q: I have created a protected Excel sheet. Now, how can the user insert or enter data in chosen cells ...
A: You can format the cells where you want to enter the data to accept it even on protected worksheets. ...
|
| Excel formula question | 9/19/2008 |
Q: I am having trouble converting the format of the way our AS400 is spitting out dates. For instance ...
A: If the format is stable (months and days with two digits always, years over 2000), you could use the ...
|
| Date stamp macro code edit. | 9/19/2008 |
Q: I have asked previously, but the person is not accepting a follow up right now, so i'm reposting the ...
A: Your question didn't came out formatted, so I am having a hard time determining your layout and ...
|
| Lookup a value when a date falls between to dates | 9/18/2008 |
Q: Please see attached picture . I have two tables in excel : Table-1 : contains these columns ...
A: I see that you are using Excel 2007, the SUMIFS function is not available on 2003. The solution I ...
|
| Sorting out the Top 10 | 9/18/2008 |
Q: I am collating stats for my ice hockey team and would like to set up a tab with the top 10 scorers ...
A: One of doing this without macros is the following. It is based on having unique values for each ...
|
| Button Configuration and Text Display | 9/17/2008 |
Q: Just a quick question. I've been having trouble with a macro i'm working with. In a worksheet I ...
A: I am assuming that you have your month and year captured in numeric format on the variables usrMonth ...
|
| Excel Help | 9/17/2008 |
Q: I need a formual to figure out the 'month' an employee would be eligible for benefits - 90 day from ...
A: I suppose that you want to return the next month to that hire date + 90 days. To do so, you could ...
|
| Time sheet value conversion | 9/17/2008 |
Q: I've made a time sheet to help my co-workers save time by calculating the time they worked for them. ...
A: To get the complete decimal numbers, you could use a formula like: =A1*24 If you want to round up on ...
|
| Want to read the result of a formula | 9/16/2008 |
Q: Good evening! I need to count the result of a formula. On cell A2 I have a date. On cell G2 I have ...
A: Yes, I would need more information. From what I have read, it looks like you want to count the ...
|
| Excel graphs | 9/16/2008 |
Q: I need to create a graphs (ex, y=ax+b) and on the same chart, I need to show some points (ex, A (x1, ...
A: You can try with the following steps: - In a blank sheet, prepare a table with the points that you ...
|
| Advanced Filtering | 9/16/2008 |
Q: I have a random list of numbers and prices that I need to trim down to about 200 and on top of this, ...
A: The sort answer is yes, probably it is possible. I would need more information on how you want to ...
|
| Interest/principle calcuation on mortgage | 9/15/2008 |
Q: Im am trying to figuare out the interest and principle portion of my monthly payment. There are lots ...
A: Usually the templates and programs that deal with mortgages use the financial functions available. ...
|
| excel checkbook | 9/15/2008 |
Q: the running balance column just quit running. the free program I downloaded works for so many ...
A: I am not sure on the details, but you may want to review the Excel help for the absolute and ...
|
| Lookup columns | 9/15/2008 |
Q: I have 2 continuosly updated columns of Dates and Occupancies like below: Date Occupancy ...
A: Supposing that the source data is on sheet1, both sets of data have headers on row 1, and you are ...
|
| urgent | 9/14/2008 |
Q: actually I have a project plan and time schedual for this project and I want to creat a pop-up ...
A: Pop ups would require VBA, so my proposal would be to use conditional formatting. Select the ...
|
| formulas in cells | 9/12/2008 |
Q: I tried typing a formula in a cell, preceded as usual with an equals sign. But it keeps showing the ...
A: Try to change the format to general, and then edit the formula (either on the formula bar or by ...
|
| Working with dates in Excel | 9/12/2008 |
Q: I need help developing a formula that will allow me to count cells based on whether or not it meets ...
A: If I have understood correctly, you want to check the number of dates that are older than 365 days ...
|
| Excel Merge and Delete | 9/12/2008 |
Q: Miquel,system wouldn't let me ask another question of you so I am starting over. Here is my bogus ...
A: I have used it on an Excel workbook, applied the formulas outlined in the previous answer: ...
|
| Excel Merge | 9/12/2008 |
Q: I need to concatenate data in column P where data in column E matches. Example: the data in P ...
A: In this case, I need to understand the layout better to provide a formula that fits. Please post ...
|
| Excel Merge | 9/12/2008 |
Q: I need to concatenate data in column P where data in column E matches. Example: the data in P ...
A: Sure. The first thing I would do is to change the formulas on columns Q and R to values, so they ...
|
| Pasting to a growing column | 9/12/2008 |
Q: In Cell A1, I have values that are periodically updated. I would like to grab this value, whenever ...
A: Yes, the code should be on the Report worksheet. The modified code would be something like: ...
|
| Excel Merge | 9/11/2008 |
Q: I need to concatenate data in column P where data in column E matches. Example: the data in P ...
A: In order to concatenate the values on column P where the values on column E are the same, you could ...
|
| Limiting display of middle name | 9/11/2008 |
Q: I would like to limit the display of a column of middle names to just the first letter of that name. ...
A: Good question, there are some mechanism to add extra things to the text but, as far as I know, none ...
|
| Excel default | 9/11/2008 |
Q: If I type numbers separated by dots, for ex. 4.5 or 3.9, excel automatically changes them to date, ...
A: This is probably due to the regional settings of the computer. Go to the computer Control Panel, ...
|
| Pasting to a growing column | 9/10/2008 |
Q: In Cell A1, I have values that are periodically updated. I would like to grab this value, whenever ...
A: In this case you will need VBA to make the cell expand. Right click on the tab name, select View ...
|
| VBA EXCEL Selection.copy | 9/10/2008 |
Q: Zapico, next I have the macro I´ve created to ease an extremely boring copy and paste task. I have ...
A: I am not sure on what you need to do, I can see that you used the Ctrl+Arrow to move around the ...
|
| Countif | 9/10/2008 |
Q: I have an excel sheet with about 40,000 rows and with many duplicates. I can extract the unique ...
A: It will depend if the duplicates are in a single column, or you are trying to check them over ...
|
| Excel Question | 9/10/2008 |
Q: My question is a follow up to my previous question : ...
A: I will suppose that 6 questions attempted means that all the other cells are blank. In that case, ...
|
| Populating data between 2 sheets in Excel | 9/9/2008 |
Q: I have question on data manipulation. In one workbook I have 4 sheets. Two of them named ‘Vehicle ...
A: This solution will use one row for each model, and you will need to play a little with the format of ...
|
| Excel Stacked Bar Chart | 9/9/2008 |
Q: I'm trying to build a stacked bar chart in excel that shows "Family Households" as the total with ...
A: You could use a layout with the information that you have on column A transposed on the first row, ...
|
| Excel | 8/29/2008 |
Q: I was wondering if there is a way to sort and copy what is in column a in worksheet a to column a in ...
A: You can record a macro that can run the process for you. To do so, go to menu Tools->Macro->Record ...
|
| Split column by count of characters Excel 2003 | 8/29/2008 |
Q: how can I split column by count of characters, I have a big file with addresses and I need to take ...
A: You can use the RIGHT formula for this, for example: =RIGHT(A1,5) This will return the last 5 ...
|
| RTD tranfer between two excel workbook/sheet | 8/29/2008 |
Q: I'm trying to transfer data from excel that is stored on one computer to an excel that is on another ...
A: Excel supports links between workbooks, and it can link between network resources. You will ...
|
| Insert cell value (date) into a link | 8/28/2008 |
Q: I have been struggling with this for some time. I am building an annual summary sheet that is ...
A: No problem, you can make the part of the row dynamic using the ROW formula, for example: ...
|
| Formula | 8/28/2008 |
Q: I am looking for a formula which can identify and return the top 20 units sold (in descending order) ...
A: Sorry, I think that I misunderstood the layout. You can try with the following array formula: ...
|
| Running a macro for multiple workbooks | 8/28/2008 |
Q: I have 3 workbooks and a master workbook, all are in a same folder. The master file is 'FC Backlogs' ...
A: My suggestion here is that you modify the macro to work with a workbook object, either the active ...
|
| macro to save and close multiple workbooks | 8/27/2008 |
Q: I have a little over a 100 workbooks that I need to apply the same macro to, this macro works just ...
A: The code looks good, my only suggestions would be to use a variable that is not a reserved word, and ...
|
| Insert cell value (date) into a link | 8/27/2008 |
Q: I have been struggling with this for some time. I am building an annual summary sheet that is ...
A: There is a formula called INDIRECT that can fit here, the problem is that it doesn't work with ...
|
| VBA macro | 8/27/2008 |
Q: I have a VBA macro from work which transfers our reports from one excel sheet to another and then ...
A: The code works with two workbook names, cwb for the current workbook, and nwb for the opened ...
|
| Formula | 8/27/2008 |
Q: I am looking for a formula which can identify and return the top 20 units sold (in descending order) ...
A: I am not sure on how you can identify the on-hand quantity, probably with a COUNT function over a ...
|
| Before print event | 8/27/2008 |
Q: I have tried to write the code so that the document has to be saved before it can be printed. I know ...
A: You could use the workbook event BeforePrint for that, for example: Private Sub ...
|
| Excel spreadsheet | 8/27/2008 |
Q: I have a large spreadsheet with a number of calculations built such as sums, percentages etc. I ...
A: You can use the menu Edit->Go To->Special, and select Constants there. This will create a multiple ...
|
| concatenate two address functions | 8/26/2008 |
Q: how do i concatenate two address functions? eg: rng1.address = C2:T16 and rng1.address = C30:T33, i ...
A: You could use a code similar to: Set c = Cells.Find(What:="Valid", After:=ActiveCell, ...
|
| Ranking | 8/26/2008 |
Q: Rank Player Red Blue Green Points 1 AO 6 10 8 24 4 CKK 4 4 8 16 6 DKYH 3 2 0 5 5 LKPD 2 10 2 14 ...
A: You can use an additional column, where you consolidate those four columns with different weights. ...
|
| concatenate two address functions | 8/26/2008 |
Q: how do i concatenate two address functions? eg: rng1.address = C2:T16 and rng1.address = C30:T33, i ...
A: that makes more sense. My suggestion in this case would be to craft the chart using the series ...
|
| spellcheck | 8/26/2008 |
Q: Can I write a command that prompts the user to do a spell check every time he/she attempts to save a ...
A: You can use the Workbook_BeforeSave event for that. To do that, right click on the small Excel icon ...
|
| automatically insert row w consecutive date | 8/25/2008 |
Q: I am a neophyte with Excel having almost no experience with VBA and macros. I am importing a list ...
A: You don't need to write the full range on the definition, unless you have gaps on it. Thy changing ...
|
| automatically insert row w consecutive date | 8/25/2008 |
Q: I am a neophyte with Excel having almost no experience with VBA and macros. I am importing a list ...
A: You will need VBA for that. Go to menu Tools->Macro->Visual Basic Editor, in the new window go to ...
|
| concatenate two address functions | 8/25/2008 |
Q: how do i concatenate two address functions? eg: rng1.address = C2:T16 and rng1.address = C30:T33, i ...
A: If you are using a workbook function, the concatenation operator is &. So, for example, if you have ...
|
| Modifying file name using a macro | 8/25/2008 |
Q: I have a formula -> COUNTIF('[FC1 Backlog 081808.xls]Query1'!$A$2:$H$65536,L5) in a file named ...
A: the need to fully automate the process was the part that I missed. For that, you could use a code ...
|
| find duplicates in excel. | 8/25/2008 |
Q: I have a very long list of numbers in column A, it can be as long as 65,000 numbers and in column B ...
A: In this case I would recommend the use of a good text editor, like textpad or notepad2. You can ...
|
| average the 1st n non-zero numbers in a row | 8/24/2008 |
Q: I want to find the average of the 1st 6 non-zero numbers in a row. Sometimes I will have a row that ...
A: You could try with a formula like this: ...
|
| Sum function and sort | 8/24/2008 |
Q: The values I have in cells that I am adding up with the sum function change from day to day based on ...
A: You could use the SUMIF formula for this, for example if your companies are in A1:A50 and the values ...
|
| Check for existing data | 8/23/2008 |
Q: I have a userform that takes the user's input and enter it into a the excel database. I would like ...
A: don't worry about the definition of variant versus integer, that shouldn't matter. Regarding your ...
|
| find duplicates in excel. | 8/23/2008 |
Q: I have a very long list of numbers in column A, it can be as long as 65,000 numbers and in column B ...
A: I am not sure on how you can have 90,000 rows in Excel 2003, as the limit is lower, are you working ...
|
| Excel Help | 8/22/2008 |
Q: I am trying to write a formula that will look for a match between words of worksheet 1 and a column ...
A: The word that can make this task really difficult to do with formulas is replace: do you mean that ...
|
| Daily Interest Rate over a period of time. | 8/22/2008 |
Q: I have a spreadsheet I created for work which list attorney fees paid over a period of time but the ...
A: It looks correct for 1997, if you use it for 1996 it will give you $2,917.80. Sorry for the formula ...
|
| Modifying file name using a macro | 8/22/2008 |
Q: I have a formula -> COUNTIF('[FC1 Backlog 081808.xls]Query1'!$A$2:$H$65536,L5) in a file named ...
A: You can do that by using the menu Edit->Replace. Type on the Find what field something like ...
|
| Formula to get cell reference of a lookup | 8/22/2008 |
Q: I am seeking to get a cell reference (row and column) for the cell in which particular text is ...
A: You can use the MATCH formula for that. It returns the position of the matched result on the ...
|
| cross referencing question | 8/21/2008 |
Q: I have a quick questions. I have 2 lists of names, each list is in a separate sheet in one workbook. ...
A: You could use a formula like this on cell B2 of the first sheet: =NOT(ISNA(MATCH(A2,Sheet2!A:A,0))) ...
|
| Transpose multiple records from rows into columns. | 8/21/2008 |
Q: Miguel, I think my question/problem is same as the post 8/4/2008. However, I have no knowledge ...
A: The formula that I provided doesn't need the use of macros, so you don't need to worry about that. ...
|
| Writing a Macro For Excel Workbook to e-mail a page using attachment function | 8/21/2008 |
Q: I was interested in being able to write a Macro for an Excel workbook that e-mailed each sheet of my ...
A: Regarding the email, you may check this page for samples and code: http://www.rondebruin.nl/tips.htm ...
|
| Daily Interest Rate over a period of time. | 8/21/2008 |
Q: I have a spreadsheet I created for work which list attorney fees paid over a period of time but the ...
A: I have assumed the following layout for this solution: - Start date on cell A3 - Start amount on ...
|
| Excel left lookup | 8/21/2008 |
Q: "Hi Having trouble doing a left lookup. I have the following Vlookup in cell (E5) ...
A: You can use the INDEX/MATCH combination for this. Following your formula, it could be something ...
|
| Excel Sum Array Within a Index Function? | 8/20/2008 |
Q: I am trying to do a couple of things here. There is a report that is formatted monthly such that ...
A: Unfortunately, FORMULATEXT is not a standard Excel formula, so it doesn't work well in array ...
|
| labels | 8/20/2008 |
Q: I have MS Excel 2002. How do I print labels without one line (the phone number)?/ If you could ...
A: One way of doing it is by making a copy of the worksheet, and removing all the phone numbers from ...
|
| Date Alerts | 8/20/2008 |
Q: Situation: We’ve constructed a ‘Hold’ list for job applicants. Jobs are not always available in the ...
A: You could use an additional column with a formula like: ...
|
| Help compairing multiple colum in Excel | 8/20/2008 |
Q: i have a total off 10 colums( from A to J) A=Name, B=LOB, C=Manager, D=Supervisor, E=Location,F= ...
A: You could use a formula like: =AND(B2=F2,C2=G2,D2=H2,E2=I2) This will return TRUE if all the values ...
|
| offset formulas | 8/20/2008 |
Q: You will have to excuse my ignorance but i really know nothing about offset formulas and how they ...
A: You are right, the OFFSET formula may work for you in this case. The formula accepts 5 parameters, ...
|
| Coloring cells of different formula | 8/20/2008 |
Q: Miguel Zapico: For example, a worksheet contains some cells which has formulas linked to other ...
A: As far as I know, that is not possible with workbook formulas, as they work with the result of the ...
|
| A question on ranking. | 8/19/2008 |
Q: I'm using excel to formulate a ranking system based on points and ranks. In this case "fantasy ...
A: I don't know the details of your implementation, but it looks like you need to keep the historical ...
|
| Comparing 1 excel file at a time with a master excel file | 8/19/2008 |
Q: I am dealing with the inventory project at work and dealing with excel files that have computer's ...
A: You could try with the MATCH formula, while having the master workbook open. It could look ...
|
| VLOOKUP, Names in 3rd Argument | 8/19/2008 |
Q: I am currently developing a spreadsheet that calls data from a table on Sheet 1, and puts it into a ...
A: You can add another name to the table, call it Data_headers (range A1:G1?), and change the third ...
|
| NEXT results | 8/18/2008 |
Q: I'm trying to aggregate totals for all funds in the following: Fund A, x amount Fund A, y amount ...
A: In this case, maybe an advanced filter can work. Select the range, go to menu ...
|
| Move row from one sheet to another based on cell value | 8/18/2008 |
Q: I currently have a worksheet called "CountryWide" that contains closed items that are marked by a ...
A: You will need macros for that, as there are no formulas to cut and paste. This code may give you ...
|
| NEXT results | 8/18/2008 |
Q: I'm trying to aggregate totals for all funds in the following: Fund A, x amount Fund A, y amount ...
A: I am not sure what you want, but here are a few things that may help: - The SUMIF formula. You can ...
|
| Excel hyperlink direction | 8/18/2008 |
Q: I have been asked to set up an e-mail list in Excel, easy enough. I am using MS outlook for my ...
A: As far as I know, that cannot be done directly. One thing that works, and it is only one manual ...
|
| return blank cells as 0's | 8/18/2008 |
Q: I'de like to get zeroes instead of blank spaces in a table of roughly 6,000 codes. The issue is that ...
A: You could do this manually in a couple of steps, by selecting the whole area, going to menu ...
|
| Clearing contents of excel via function command | 8/17/2008 |
Q: I am currently developing a financial model in excel which will generate a lot of data on the cells. ...
A: I am not sure about 2007, but one way of doing that is by recording a macro while performing the ...
|
| Excell Formula | 8/16/2008 |
Q: In collum 1 i have a list of start dates, in collum 2 i have a list of end dates. Is there any way i ...
A: I am still confused by the date format, so I am not sure if this will be the answer that you expect. ...
|
| Pivot Table, Refresh Problem | 8/15/2008 |
Q: When I refresh my Pivot Table, it inserts a blank before 1/2/2009. I have no idea why it is doing ...
A: The blank records usually appear when there are blank rows in the data origin. There are some ways ...
|
| excel countif formula | 8/15/2008 |
Q: Is there a formula for counting ranges in a column? What I am trying to do is ask excel to count ...
A: You can use the COUNTIF formula twice to achieve this, for example: ...
|
| Excel 2000 | 8/15/2008 |
Q: I have a List on sheet one of lets say apples and oranges. What I want to do is when I type in the ...
A: You can use conditional format for this. Select the range A11:F17, go to menu Format->Conditional ...
|
| excel formula for multiple worksheets | 8/15/2008 |
Q: i have typed a date in E1 on worksheet 1 I know want a date plus 7days on in E1 on the other 51 ...
A: The contents of cell A1 are not used, it is just a reference to the current worksheet. What is ...
|
| Manager and Employ | 8/14/2008 |
Q: I have Excel 2003 The next scenario: I have 2 sheets: “Employ Profile” and “Management Table” In ...
A: If you don't mind gaps on the employees names, you could use a formula like this on A2: =IF('Employ ...
|
| Alphabetizing Worksheets | 8/14/2008 |
Q: I wanted to know if there was an easy way to alphabetize (around 150) worksheets in a workbook. You ...
A: You could use a code like: Sub CopyTemplate() With Sheets("Subcontractors").Range("B1") ...
|
| Have the reference to tabs be in a cell | 8/14/2008 |
Q: Miguel, I have a formula to go and find values on a specific tab: ...
A: This can be done with the INDIRECT formula, something like: =INDEX(INDIRECT("'" & A1 & ...
|
| excel formula for multiple worksheets | 8/14/2008 |
Q: i have typed a date in E1 on worksheet 1 I know want a date plus 7days on in E1 on the other 51 ...
A: If you can follow a naming schema for the worksheets, you may use a solution like: ...
|
| Alphabetizing Worksheets | 8/14/2008 |
Q: I wanted to know if there was an easy way to alphabetize (around 150) worksheets in a workbook. You ...
A: I have modified the code for the previous answer: Sub SortSheets() Const iStart As Integer = 30 ...
|
| Formulas | 8/13/2008 |
Q: why we should use " $ " In formulas such as : = B3*$C$1 and is there any different forms for using ...
A: These symbols will make a reference to a cell absolute. This means that if you copy the formula to ...
|
| PIE BY REGION and Country &Stacked Chart | 8/13/2008 |
Q: I was wondering if you could help create a pie chart by region and within that pie chart by country ...
A: My recommendation here will be to make the separate charts for the regions and countries as simple ...
|
| Assign Macro to Each Option in a Combo Box | 8/13/2008 |
Q: i have created a spreadsheet which uses 2 combo boxes. The first is linked to cell C9, and the ...
A: You are right, as far as I know the only option to change the currency format of a cell ...
|
| Convert Amount to Words | 8/13/2008 |
Q: My country's currency is Ringgit Malaysia and Sen. How shall I amend the VBA codes in spellnumber if ...
A: I suppose that you are using the spellnumber from this site: ...
|
| Excel 'Import' Function | 8/13/2008 |
Q: I am trying to import selected text (say column B) from a numbered (say column A) list that i have ...
A: You can use VLOOKUP for this, for example: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,FALSE) This will return ...
|
| Creating a list | 8/12/2008 |
Q: Hey Miguel I am trying to create a list in excel except the values I am trying to pull are not all ...
A: As far as I know, there is no way of making a list for data validation with a range that is not ...
|
| excel : sum of values | 8/8/2008 |
Q: Could you please help me with the following ? Is there any way to make in excel the sum of values ...
A: You may use the following array formula: =SUM(--(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))) Enter it ...
|
| Cinditional Format cell | 8/8/2008 |
Q: I want to hightlight the row A:L columns, if cell D4:K4 has value entered (it will be numbers from 1 ...
A: You can do that selecting the full range that you want to format, go to menu Format->Conditional ...
|
| Excel nested if | 8/8/2008 |
Q: I need to refer to a specific date of a share price reference. I need to check whether it is a ...
A: Yes, you can present the data by quarters. Right click on any of the elements of the date column, ...
|
| Excel nested if | 8/6/2008 |
Q: I need to refer to a specific date of a share price reference. I need to check whether it is a ...
A: I am not sure on where you want to put the values, if directly on the table or when you are making ...
|
| Formatting multiple worksheet in on workbook | 8/5/2008 |
Q: Please advise how I may give identical formatting to multiple worksheet in a single workbook ...
A: You can do that by selecting multiple sheets (with either the shift or control keys while clicking ...
|
| special paste | 8/5/2008 |
Q: I HAVE A QUESTTION ABOUT SPECIAL PAST IN EXCEL. MY TABLE HAVE MANY FORMULA AND A SPECIAL FORMAT. ...
A: You don't need to have both at the same time, as the "Values and number formats" include the ...
|
| transpose using vba in excel | 8/4/2008 |
Q: i have a list of restaurants with their addresses and phone numbers. sample: AL NAFADI ...
A: This is not an easy task, mainly because it looks like the layout of the information doesn't keep ...
|
| macro | 8/4/2008 |
Q: I enter a value(5) in A2. And i should give comments in H2 if i enter a value in the cell ranging A2 ...
A: This is one way of doing it. The macro uses the worksheet_change event, checks if the value has ...
|
| Excel 2003 Formula Problem | 8/2/2008 |
Q: Help! There is an error in this formula and I need help in fixing it. The formatting is ‘number ...
A: The formula looks right, the error that you get is due to have the formula in an unlocked cell. You ...
|
| Excel 2003 Formula Problem | 8/2/2008 |
Q: Help! There is an error in this formula and I need help in fixing it. The formatting is ‘number ...
A: There may be other formulas, but I would need to know more about the type of information that is on ...
|
| Convert Columns to Text | 8/2/2008 |
Q: i am receiving customer BOM's which consists location details in rows, i want to convert this one to ...
A: You can use the concatenation formula for that, something like: =A1 & "," & A2 & "," & A3 And so on. ...
|
| VBA Help | 8/1/2008 |
Q: I created a macro to format a spreadsheet after it is exported from Access and I need help with one ...
A: The range C7 is hard coded, you can avoid it with something like: ...
|
| conditional formatting - drop down menu | 8/1/2008 |
Q: I am looking how to solve the following situation : (I will indicate what I know to solve and where ...
A: With the first question, you will need VBA to control that behavior, as you will need to add or ...
|
| formula to count a value with more than 1 criteria | 8/1/2008 |
Q: i have a sheet on which there are three columns. column A is a date, B and C are text. e.g. date ...
A: You could use the SUMPRODUCT formula for that, something like: ...
|
| Excel Macro out of synch | 7/31/2008 |
Q: Would you know how to create a macro in Excel for the following situation? If not, do you know who I ...
A: Sorry, probably I haven't understood what you want. The solution I proposed was a formula, but if ...
|
| Excel Formatting Question | 7/31/2008 |
Q: Could you please help me with this problem? I have a spreadsheet with sample values like this…. Any ...
A: The conditional formatting applies the first format that fulfills the condition. If you want a ...
|
| month percentage | 7/31/2008 |
Q: What is the formula I can use in excel to display the percentage of the month that =today() is? ...
A: You are right, because the formula is taking full days, and today is 31, any addition to that will ...
|
| Followup | 7/31/2008 |
Q: Well - I think part of nmy prob can be replicated without citrix..I run the macro to add the buttons ...
A: You could keep using the object myControl. I have tried with: myControl.OnAction = "Macro1" And ...
|
| Excel Formula | 7/30/2008 |
Q: I am having trouble coming up with a formula that works. Here is my problem: Column A ...
A: You can use array formulas for this, for example, to get the sum of a single model, you may use ...
|
| Excel Pivot Table/Chart | 7/30/2008 |
Q: I have an Excel 2003 graph chart on WIN XP that imports data from an external database application ...
A: As far as I know, the extra format things that you require are not available within a pivot chart. ...
|
| Excel - Saving Invoice data to database | 7/30/2008 |
Q: I am using Excel 2003. I have a worksheet setup has an invoice, and the customer's details & ...
A: Following your previous code, this might do the trick: Sub CopyCells() Dim rngData As Range, ...
|
| Excel - Saving Invoice data to database | 7/30/2008 |
Q: I am using Excel 2003. I have a worksheet setup has an invoice, and the customer's details & ...
A: Your idea is not bad, the part that you can reuse from my code is the For loop. It has two things ...
|
| Excel - Format Cell, Font Color | 7/29/2008 |
Q: Hey, Here is my question. I have a cell "W12" that at random time might have a blue font color. I ...
A: This is probably achieved with Conditional Formatting. Select the cell B12, go to menu ...
|
| Converitng Numbers to Text | 7/29/2008 |
Q: I would like to create a macro where I could insert a nr in a cell, but this nr is equivalent to one ...
A: In this case, I would suggest using the VLOOKUP formula. You can create a table with the ...
|
| Excel Macro out of synch | 7/29/2008 |
Q: Would you know how to create a macro in Excel for the following situation? If not, do you know who I ...
A: If the values to line up have to be exactly the same, she could use the following IF formula: ...
|
| function not working | 7/29/2008 |
Q: i Have used the below function to find the rate applicable if a particular service is done on my ...
A: You could use the OR formula for this, something like: ...
|
| excel | 7/29/2008 |
Q: How can we link two excel files with macro, or more specifically i want to use the data from one ...
A: Yes, you can link that, but usually a macro is the more complex of the solutions, and it usually ...
|
| blank rows in pivot tables | 7/28/2008 |
Q: How do I get rid of rows in pivot tables that are summarizing zero data? I am referring to rows ...
A: As far as I know, there is no direct method to remove all the zero data fields, in fact the ...
|
| Followup | 7/28/2008 |
Q: Well - I think part of nmy prob can be replicated without citrix..I run the macro to add the buttons ...
A: I have also recorded a macro, and modified it to fit your needs. It looks like: Sub AddButton() ...
|
| Doing a search for cells to use as variables in ranges | 7/28/2008 |
Q: Miguel, Here I am again in the production of another macro. This time I am trying to use the Find ...
A: For the column part, you could modify your code a bit, something like ... Set Newcol = ...
|
| Excel - Saving Invoice data to database | 7/28/2008 |
Q: I am using Excel 2003. I have a worksheet setup has an invoice, and the customer's details & ...
A: Sorry for the late response. Regarding your question, the full solution will depend on how the ...
|
| Input box queries | 7/26/2008 |
Q: I have some problem in the below coding which is in Excel VBA using for to get the inputs. The ...
A: Sorry for the late response, here are my code suggestions for your questions: 1. If you want the ...
|
| Doing a search for cells to use as variables in ranges | 7/25/2008 |
Q: Miguel, Here I am again in the production of another macro. This time I am trying to use the Find ...
A: As you are using the Find method, it will return a range object. You can use the Column property ...
|
| I forgot to tell you something about chart questions I just asked... | 7/25/2008 |
Q: I forgot to mention something very important! The work sheet data is pulled from another worksheet, ...
A: I should read all the questions before answering, sorry I didn't see this explanation, as you are ...
|
| Consolidating data | 7/25/2008 |
Q: If I have one cell that contains a list with multiples of the same word, is there a way to show them ...
A: This is not a simple task, and I cannot think on a simple formula that can do the trick. Recently I ...
|
| Hyperlinking Books | 7/25/2008 |
Q: Suppose I have two separate books, Book 1 and Book 2. Book 1 contains worksheet A, B, and C. Book ...
A: If you want to create hyperlinks, you may try with the HYPERLINK formula, for example. ...
|
| Excel Macro Help | 7/25/2008 |
Q: I found your answer to the question at this link very helpful. ...
A: You may use the workbook object that you have created instead of the Windows, that should avoid the ...
|
| Excel VBA Formula | 7/24/2008 |
Q: I have a multiple sumif formula. This one works for 2 securities ActiveCell(1, 3).FormulaArray = _ ...
A: There was an error with the construction of one of the IF formulas, the new part should replace the ...
|
| Conditional Checkbox Use and Chart Labels | 7/24/2008 |
Q: I have two questions: Question 1: Say I have 3 checkboxes. 1 and 2 plot data sets and 3 makes both ...
A: For the first question, it will depend on the type of check boxes that you are using. If they are ...
|
| Amalgamation of data | 7/24/2008 |
Q: Good afternoon; I'm trying to combine data from two worksheets into a third that will include all ...
A: The VLOOKUP formula can be used to bring the data, but in order to combine both sheets in a third ...
|
| Counting time between 18:00 - 6:00 | 7/24/2008 |
Q: Is there a way to count how many hours are between certain time ranges? For example workers get paid ...
A: This is a good challenge! I hope this time the formula will cover all the cases: ...
|
| Counting time between 18:00 - 6:00 | 7/23/2008 |
Q: Is there a way to count how many hours are between certain time ranges? For example workers get paid ...
A: Here is a formula that may work: ...
|
| Transposing | 7/23/2008 |
Q: I have a large amount of data in 2 columns, one being the yearly population and the other being the ...
A: I am assuming that the original data is on sheet1, it is ordered by country and year, it has just ...
|
| Excel Macro Help | 7/23/2008 |
Q: I found your answer to the question at this link very helpful. ...
A: Sure. There is an object there that you may not be familiar with, and it is the ...
|
| Purchase Order | 7/23/2008 |
Q: If you dont mind I have one forther question. Using the save macro is there a way the form will save ...
A: You can use the following code, with the information hard coded: Sub SaveOrder() Dim strName As ...
|
| excel dates formatting | 7/22/2008 |
Q: I have a list of date in a text format like "080808", which is Sept. 8, 2008. I need excel to change ...
A: I am assuming that the format is ddmmyy, if that is not the case you may need to swap the factors ...
|
| COUNTIF time format | 7/22/2008 |
Q: What's the best formula to use if I wanted to count each cell containing time for cases that went on ...
A: The formula looks right, maybe you are not entering it as array formula (Ctrl+Shift+Enter)? One way ...
|
| Auto Sum | 7/22/2008 |
Q: I have a spreadsheet for sales which keeps changing as more invoices are entered in it. I want to ...
A: The 0,0 is the offset itself, but we don't want the formula to move, just to resize. The 1 at the ...
|
| Amalgamation of data | 7/22/2008 |
Q: Good afternoon; I'm trying to combine data from two worksheets into a third that will include all ...
A: One Excel function that can help here is VLOOKUP. In this case the link looks the corporation ID, ...
|
| Creating filename based on tab names | 7/22/2008 |
Q: Miguel, I work for the Navy and have been creating some useful generalized macros for spreadsheets. ...
A: I have modified the code to include a loop, and also a line to close the generated workbook, to ...
|
| Rolling Calendar Calculations | 7/22/2008 |
Q: I'm trying to create a rolling calendar where the user will select the first month from the drop ...
A: If you want to do this with text, you may do the following. In a different worksheet, type the list ...
|
| month percentage | 7/21/2008 |
Q: What is the formula I can use in excel to display the percentage of the month that =today() is? ...
A: You can try with a formula like this: ...
|
| Creating filename based on tab names | 7/21/2008 |
Q: Miguel, I work for the Navy and have been creating some useful generalized macros for spreadsheets. ...
A: You could try with something like: Sub SaveSheet() Dim strName As String strName = ...
|
| Calculation of Overtime Hours greater than 8 and less than 12 | 7/20/2008 |
Q: Column D: Time In [8:00am] Column E: Time Out [12:00pm] Column F: Time In [1:00pm] Column G: ...
A: In this case, I would use the results from previous calculations to get the overtimes. For example: ...
|
| adding ticks | 7/19/2008 |
Q: I am trying to add ticks in a column. I have heard on the internet that you can do this by using a ...
A: Probably they are referring to the COUNTA function, that returns the number of cells in a range that ...
|
| Purchase Order | 7/19/2008 |
Q: If you dont mind I have one forther question. Using the save macro is there a way the form will save ...
A: Yes, it is possible. If you use the previous code, the variation would be something like: Sub ...
|
| Excel function for Covertion of numbers to words text in Indian system | 7/19/2008 |
Q: I wish to know Excel function or command that converts a number to text format
A: The formula is called TEXT, and you can use all the available formats for the cell with it, ...
|
| excel & comumn #1 | 7/18/2008 |
Q: Probably an easy question 4 u. It can be done, or it can't. If it can, I looked everywhere on how ...
A: Sorry for the late, and yes, Excel has a way of doing that. Instead of using the numbers, you can ...
|
| Excel Row combined and unique | 7/18/2008 |
Q: I am having a problem with Excel concerning the appearance of multiple values. I have on sheet ...
A: Yes, it is. Use absolute reference for the range with the information, and relative reference for ...
|
| Excel Row combined and unique | 7/18/2008 |
Q: I am having a problem with Excel concerning the appearance of multiple values. I have on sheet ...
A: In fact, the formula does that as it is now. If you have other names in the range, no matter the ...
|
| Excel Row combined and unique | 7/17/2008 |
Q: I am having a problem with Excel concerning the appearance of multiple values. I have on sheet ...
A: This solution includes a VBA function, that is the following: Function ConsolidateStrings(strName ...
|
| COUNTIF time format | 7/17/2008 |
Q: What's the best formula to use if I wanted to count each cell containing time for cases that went on ...
A: You could use the following array formula: =SUM(--(MOD(A1:A25,1)>0.77)) Enter it with ...
|
| Excel database | 7/17/2008 |
Q: I have set up a template for purchase orders is there a way that I can save it with a " save|" macro ...
A: You shouldn't have issues with the path, as the macro takes it from the current workbook using ...
|
| Excel database | 7/16/2008 |
Q: I have set up a template for purchase orders is there a way that I can save it with a " save|" macro ...
A: Sorry for the late, I was complicating it more that it should. You can try with a code like this: ...
|
| Index, match vs, sumif formulas | 7/16/2008 |
Q: I collect data, with several (ie. 7 to 10) criteria that must be met to return and sum. Question is ...
A: In this case, if you can create a pivot table with the data that can be the quickest and less memory ...
|
| Excel database | 7/16/2008 |
Q: I have set up a template for purchase orders is there a way that I can save it with a " save|" macro ...
A: The short response is yes, you can, but I would need more information to give you detailed code. ...
|
| Join columns (2nd part) | 7/16/2008 |
Q: for your advise, but I can not make this because maybe the blank cells will be reduced in the ...
A: In this case, we can try with a VBA solution. Here is the code: Sub JoinColumns() Dim i, j, k ...
|
| Auto populating a calendar | 7/15/2008 |
Q: Hey Miguel I have no idea if this is possible, but I have included a picture to help me explain ...
A: In this case you may try with a formula that behaves the same on each cell, so you can copy and ...
|
| join columns | 7/15/2008 |
Q: Zapico, I have been reading the answer that you gave to a excel user about how to join several ...
A: I see where the problem is. The column J has more information below the data that you want to ...
|
| join columns | 7/15/2008 |
Q: Zapico, I have been reading the answer that you gave to a excel user about how to join several ...
A: Ok, I have changed the approach a little bit, using logical evaluation instead of IF functions, and ...
|
| sorting and countif for duplicates | 7/15/2008 |
Q: Hope you're doing well.I have these questions regarding countif formula. I have a data in one column ...
A: I think that the formula itself is not meant to be used cell by cell, but as a general check if ...
|
| join columns | 7/15/2008 |
Q: Zapico, I have been reading the answer that you gave to a excel user about how to join several ...
A: that made a lot sense. The problem there might be the COUNTA formula using the A and B column ...
|
| Auto populating a calendar | 7/14/2008 |
Q: Hey Miguel I have no idea if this is possible, but I have included a picture to help me explain ...
A: One way of doing this is by using VBA. This code will populate the selected month tab with the ...
|
| Drop Down Checklist | 7/14/2008 |
Q: I want to create a drop down list within a cell with the ability of housing a checklist. ...
A: To visualize the code, open the Visual Basic Editor on the menu Tools->Macro, and there navigate on ...
|
| Multiple data retrieval into multiple rows | 7/14/2008 |
Q: I am trying retrieve multiple row data into multiple cells. For example, In student details sheet, ...
A: This is one technique that may be helpful in this case. For it, you need an extra column on your ...
|
| Calculations between workbooks | 7/14/2008 |
Q: I am looking to take numbers from multiple workbooks and or sheets within the workbook and inserting ...
A: There are some ways to hide a cell, you could do any of the following: - Hide the column or row ...
|
| join columns | 7/14/2008 |
Q: Zapico, I have been reading the answer that you gave to a excel user about how to join several ...
A: I don't know exactly what you are trying to do, but there is one thing that may help. The second ...
|
| excel 2003 | 7/12/2008 |
Q: on excel 2003, could you tell me how do you create a formula for names example name Reilly Joseph. ...
A: I am not sure on what you mean in these questions, sorry. For the first one, there are some ...
|
| Excel | 7/11/2008 |
Q: I have several worksheets with contact information that I want to keep separate (different ...
A: This can be a huge task, depending on the layout of the information and the way that you would like ...
|
| Input box queries | 7/11/2008 |
Q: I have some problem in the below coding which is in Excel VBA using for to get the inputs. The ...
A: I have changed the code for the questions 1 and 2, here it is: Public Sub Supplier_Creation() ...
|
| Combobox data | 7/11/2008 |
Q: You are my favourite exper to me. 1. I am trying to list out the names of the student from Sheet1 ...
A: The concrete answer will depend on which type of combo box you are using. I will assume that you ...
|
| Drop Down Checklist | 7/10/2008 |
Q: I want to create a drop down list within a cell with the ability of housing a checklist. ...
A: Sorry for the late, I was searching from one of my previous answers for a worksheet that did ...
|
| =SUM(IF... | 7/10/2008 |
Q: This is driving me crazy! I don't have much Excel experience but I've jumped into the deep end by ...
A: In this case, I would recommend the SUMPRODUCT formula. It is not an array formula, but it uses ...
|
| Excel Count | 7/10/2008 |
Q: I have an excel spreadsheet, in which i have conditional formatting to it, meaning that if a cell ...
A: If you are looking for the cells with conditional formatting, and all the cells have the same ...
|
| excel lookup | 7/10/2008 |
Q: i have many worksheets and i want to summarize these worksheets in a summary worksheet. For example, ...
A: In this case, I would recommend a pivot table with multiple consolidations ranges, as it will make ...
|
| changing the background color of a cell based on a drop down menu selection | 7/9/2008 |
Q: I have this spreadsheet that I created with choices such as In-prog, Fail, Finished etc... How do I ...
A: You can do this by adding a line that sets the value to the right of the cell, something like: ...
|
| converting numbers to h:mm | 7/9/2008 |
Q: I need to key in time in h:mm format. Then average across months. However, this format will not ...
A: I haven't been able to replicate the issue; the cell I enter the data in has the format as general ...
|
| changing the background color of a cell based on a drop down menu selection | 7/9/2008 |
Q: I have this spreadsheet that I created with choices such as In-prog, Fail, Finished etc... How do I ...
A: You will need VBA to handle more than 3 conditions on a cell. This is one way of doing it, this ...
|
| excel lookup | 7/9/2008 |
Q: i have many worksheets and i want to summarize these worksheets in a summary worksheet. For example, ...
A: It will depend on how you plan to layout your summary worksheet, and to which point you want to have ...
|
| Every second Thursday | 7/9/2008 |
Q: I am trying to create a formula that will enter the date of my next expected payday (ie. every ...
A: The other answer was for a different case, I understand that it would be hard to adapt it to this ...
|
| Populating cells in multiple sheets based on text in a different tab. | 7/8/2008 |
Q: I know there is a lot to read, but I want to ensure that you understand my situation. Below is how I ...
A: I think that the method proposed is good, but it needs to change the starting point of the OFFSET ...
|
| excel formula | 6/23/2008 |
Q: How do I formulate the following in Excel 2003? If A1 is an odd number then B1 If A1 is an even ...
A: You can use the ISODD formula from the Analysis toolpak for this, something like: ...
|
| MACRO | 6/19/2008 |
Q: Hey Miguel, I am trying to create (which i think should be a macro) to delete an entire row if ...
A: This code may give you some ideas: Sub RemoveRows() Dim i, rngData As Range ' Change ...
|
| Alphabetizing Worksheets | 6/18/2008 |
Q: I wanted to know if there was an easy way to alphabetize (around 150) worksheets in a workbook.
A: I hope that I have understood correctly what you mean by alphabetize, this macro will sort all the ...
|
| simplify code | 6/18/2008 |
Q: I have here my original code and the next set of codes is the one that I simplified. Is there ...
A: You did a good job, the only other thing that I would simplify is to remove the Select lines, and ...
|
| Using the If function with two conditions set to a variable | 6/17/2008 |
Q: I'm working on a tool that will check a worksheet for cells that have text or a number say, less ...
A: In this case, concatenating the value to compare and the comparison won't work, as the line is ...
|
| Excel & comparing two columns of text | 6/17/2008 |
Q: I have text in Column 1 and text in column 2, in column 3 I need a formula that says, IF there is ...
A: The formula will only work if the range where you are looking contains the longer words, in this ...
|
| Excel & comparing two columns of text | 6/17/2008 |
Q: I have text in Column 1 and text in column 2, in column 3 I need a formula that says, IF there is ...
A: You could use the MATCH formula for this, something like: =IF(ISNA(MATCH("*" & B1 & ...
|
| sumproduct | 6/17/2008 |
Q: Miguel, I have a been trying to tweek this formula without success. What do I do wrong???? My need: ...
A: The problem here is the OR function, SUMPRODUCT expects an array and that function returns a single ...
|
| Formulas from one sheet to another | 6/16/2008 |
Q: I have a Microsoft spreadsheet: I have different sheets for different months. I need A balance ...
A: You can use direct references for the cells, for example, if the worksheet is named March and the ...
|
| code | 6/16/2008 |
Q: I have a table with 18 columns.In the last column, some values are in letter C which means ...
A: I hope this code can give you some ideas: Sub RemoveRows() Dim rngData As Range Set ...
|
| User Defined Function | 6/16/2008 |
Q: I have used the following UDF and it worked well.(see below) But now I want to do the opposite and ...
A: You can reverse the function with something like: Function NumGrades(Grade As Integer) As String ...
|
| FORMULA | 6/15/2008 |
Q: CELL L14 IS 55 WRITE A FORMULA TO BE ENTERED IN P14 CONSIDERING THESE THREE CONDITIONS AS FOLLOWS: ...
A: Sorry for the late response, I was out for the weekend. You could use a formula like: ...
|
| How to return DATE+1, excluding weekends and holidays | 6/13/2008 |
Q: I wonder if you could help. I have a column of data (in dd/mm/yyyy format) and I would like to know ...
A: You are right, the formula doesn't contemplate the case where the holiday is a Friday. In this ...
|
| Code error | 6/13/2008 |
Q: I am making a simple VBA command but I received errors. The lines enclosed with >>>>>>> are the ...
A: 1. Yes, the Range("A1") is used twice, but the first time it is used to represent the initial cell ...
|
| How to return DATE+1, excluding weekends and holidays | 6/13/2008 |
Q: I wonder if you could help. I have a column of data (in dd/mm/yyyy format) and I would like to know ...
A: Good question, excluding the weekends can be done with the WEEKDAY formula, something like: ...
|
| Excel VLookup | 6/12/2008 |
Q: Please help me out on this If have the data sheet that has the following information BAND May 1, ...
A: VLOOKUP may be the right choice here, but the required information must exist and it should be ...
|
| Code error | 6/12/2008 |
Q: I am making a simple VBA command but I received errors. The lines enclosed with >>>>>>> are the ...
A: I have modified the code a bit, to format only the values with data on the row 1, and assuming that ...
|
| Auto Update Time | 6/11/2008 |
Q: I am currently trying to make a macro in Excel 2003 using the VBA application. I want the macro to ...
A: There is an easier alternative; I am not sure why the error happens, but you could trap it adding a ...
|
| Consolidating | 6/11/2008 |
Q: I have a large amount of data that I am trying to consolidate but no in the typlical way...I have a ...
A: I understand, with too many variations a pivot table will not be useful. I would suggest two ...
|
| Excel manage data | 6/11/2008 |
Q: I have a huge data with thousands of entries, for example 3000 rows. I would like to only select 1 ...
A: You could do this by using an additional column, with a formula like: =IF(MOD(ROW(),10)=0,1,0) This ...
|
| Consolidating | 6/10/2008 |
Q: I have a large amount of data that I am trying to consolidate but no in the typlical way...I have a ...
A: In this case my recommendation would be a pivot table. First make sure that you have titles on the ...
|
| Auto Update Time | 6/10/2008 |
Q: I am currently trying to make a macro in Excel 2003 using the VBA application. I want the macro to ...
A: There is no worksheet event associated to the type of changes that you want to track, so there is no ...
|
| CSV file and XLS file | 6/10/2008 |
Q: I want to know why a csv file always have a symbol of that of excel with a letter A as subscript. Is ...
A: If the formula is returning an error, it is probably because either the column D or E are returning ...
|
| Spreadsheet security | 6/9/2008 |
Q: I design spreadsheets at work for the use of everyone else in my company - so we can't really ...
A: My suggestion in this case would be to put that information in a VBA module and protect the macros ...
|
| CSV file and XLS file | 6/9/2008 |
Q: I want to know why a csv file always have a symbol of that of excel with a letter A as subscript. Is ...
A: Usually the csv files are associated with Excel, so the default option when you double click one of ...
|
| Excel - Creating Columns | 6/7/2008 |
Q: I was google'n ways to create multiple column excel sheets. Your example of populating the new ...
A: Sorry for the late, I was out for the weekend. I am not sure where I used that formula, but what is ...
|
| VBA - Excel Outlook Appointment | 6/6/2008 |
Q: I would generate a macro that allows to generate an outlook appointment getting all information ...
A: Sorry for the late response, I was out for the weekend. Regarding the code, this may give you some ...
|
| code to clear cell contents based on condition | 6/6/2008 |
Q: I am in need of a method to clear contents group of cells based on a condition. To explain more, I ...
A: I have recorded a macro using the menu Edit->Goto->Special Cells->Visible cells only, and modified ...
|
| formula | 6/5/2008 |
Q: I am doing cricket stats on microsoft excel 2003 and i need to work out peoples batting average. ...
A: I am sure that there is a way to define which values are to be excluded, but in your question there ...
|
| code to clear cell contents based on condition | 6/5/2008 |
Q: I am in need of a method to clear contents group of cells based on a condition. To explain more, I ...
A: My suggestion here would be to use the autofilter (menu Data->Filter->Autofilter), filter by blanks ...
|
| Setting alerts on Excel | 6/5/2008 |
Q: I am setting up an HR related spreadsheet detailing dates of supervision and when ...
A: You could use a formula like this for the conditional formatting: =AND(A1-TODAY()<=7,A1>=TODAY()) I ...
|
| hyperlink to open a word doc | 6/4/2008 |
Q: I am trying to create a macro that will open a blank word document via a hyperlink in any given ...
A: You could use a macro like: Sub OpenWord() Dim appWord, docNew Set appWord = ...
|
| Excel User defined function conflicting with Solver | 6/4/2008 |
Q: In reference to Miguel's response to a user-defined function to perform a summation..... I have ...
A: Sorry, I would need to know more details about the UDF that you are referring to, and how you are ...
|
| Exported Excel Worksheet | 6/3/2008 |
Q: I provide a worksheet to my boss in another state monthly. It is an export from an Access Database ...
A: You may use the menu Edit->Replace, expand the options, and select Workbook in the "Within:" drop ...
|
| Microsoft Excel Formula help | 6/3/2008 |
Q: I'm trying to create an equation that will pull data from another worksheet. Let me start from the ...
A: In this case I would suggest a pivot table instead of formulas. It looks like you have the right ...
|
| Advanced refreance and defaul value for a drop down list | 6/2/2008 |
Q: I hope you can help me out. I have multiple questions in one problem. See attached image for ...
A: You are right, I should have noted that the formula works if you enter it before enabling the data ...
|
| cell block | 5/30/2008 |
Q: You solved a problem a few weeks ago for me which was brill. I have another question and I'm not ...
A: Sorry for the late response, I was out this weekend and forgot to set the vacation mode. Regarding ...
|
| hyperlinks | 5/30/2008 |
Q: This is a MS Excel (Office Professional 2003) question. I'm very proficient with formatting and ...
A: You may not need VBA for this, the HYPERLINK formula accepts a cell reference as the first ...
|
| OFFSET | 5/30/2008 |
Q: I have two worksheets: 1 and 2. In worksheet 1, I have all my raw data. In worksheet 2, I want to ...
A: You could use a code like this: Sub ProtectSheets() Dim sht As Worksheet, strPass As String ...
|
| Advanced refreance and defaul value for a drop down list | 5/29/2008 |
Q: I hope you can help me out. I have multiple questions in one problem. See attached image for ...
A: I would use IF formulas in this case, in a way that you can fill up the worksheet2 with many rows in ...
|
| deletion of worksheet that may be a reference | 5/29/2008 |
Q: I have several worksheets in one file. I want to delete one of the worksheets. Is there a quick way ...
A: Sorry for the late, I was thinking on complicated macros, when the easiest can be just to use one of ...
|
| OFFSET | 5/29/2008 |
Q: I have two worksheets: 1 and 2. In worksheet 1, I have all my raw data. In worksheet 2, I want to ...
A: With that formula, you are doing an offset of 40 columns (column J is the 10th column), I am not ...
|
| OFFSET | 5/28/2008 |
Q: I have two worksheets: 1 and 2. In worksheet 1, I have all my raw data. In worksheet 2, I want to ...
A: You can use the OFFSET formula for this, for example: =SUM(OFFSET(Sheet1!$A$1:$D$1,0,4)) This will ...
|
| Page Field filters another Page Field | 5/28/2008 |
Q: Example I have Division, Vendor, Customer, and Sales. I want to use Division and Vendor as a Page ...
A: As far as I know, there is no simple way to make Excel link the page fields on the pivot tables. My ...
|
| combination of items from 2 sets of text | 5/27/2008 |
Q: 2 sets, or 2 columns, one column contains first names x, y, z (1st, 2nd, 3rd rows); the 2nd column ...
A: You could use the OFFSET formula combined with the ROW, starting with something like: ...
|
| Sum multiple cells into one value on another worksheet | 5/23/2008 |
Q: I have a worksheet that has multiple values for one item. I need to sum that and then bring that sum ...
A: You can use SUMIF in this scenario, it would be something like: ...
|
| Number Combinations | 5/23/2008 |
Q: I want to find out all of the different 6 number combinations from 1-49, without duplications, can ...
A: Another expert from the site, Tom Ogilvy, answered a similar question with an elegant code, here is ...
|
| Excel 2003 | 5/22/2008 |
Q: I have 2 worksheets in one book. I want to sum in groups of 5 cells from sheet 1 into a sinle cell ...
A: The idea of the formula is to construct a range using OFFSET, and then apply the SUM over it. The ...
|
| Excel 2003 | 5/22/2008 |
Q: I have 2 worksheets in one book. I want to sum in groups of 5 cells from sheet 1 into a sinle cell ...
A: If you want a formula that can be dragged across the column, you may need to take a different ...
|
| updating all workbooks in a folder using 1 macro | 5/22/2008 |
Q: I am in no way shape or form an expert with using VBA in Excel 2003, and I need a macro that can ...
A: This macro assumes that the files will be in a folder called Files, located in the same directory as ...
|
| Formula for changing font color when within 30 days of a date | 5/21/2008 |
Q: Using excel 03, how can I change the font color of a date in one column based on a date in an ...
A: You can use conditional formatting for this. For example, you can select cell A1, go to menu ...
|
| Indexing a list in excel | 5/21/2008 |
Q: If I have a long list that was alpha sorted. How can I set up an index such that one could click on ...
A: If your data is column A on sheet1, you could use formulas like: ...
|
| NetworkDays Formula | 5/21/2008 |
Q: In Excel, the Analysis ToolPak option is already selected in the AdIns option. However, when I am ...
A: If the analysis ToolPak is installed, maybe the issue is on the parameters passed to the function. ...
|
| Different Rounding Results | 5/20/2008 |
Q: In my #1 spread sheet I am keeping track of material heavy weight minus light weight = net weight. ...
A: I would need to see the spreadsheet in this case, as I don't know what may be the reason without ...
|
| NETWORKDAYS Function | 5/20/2008 |
Q: When using the NETWORKDAYS function in Excel 2003 how can you add in the company's holiday so when ...
A: What I would suggest here is to use a named range. Write the holidays on a separate sheet, select ...
|
| Text Validation | 5/19/2008 |
Q: Excel 2003 Scenario: Column H may contain data or blanks, Column N would contain free text and if ...
A: You may do this with conditional formatting. For example, select the cell N2, go to menu ...
|
| NETWORKDAYS Function | 5/19/2008 |
Q: When using the NETWORKDAYS function in Excel 2003 how can you add in the company's holiday so when ...
A: You enter them as individual dates on a given range, and then enter that range reference as the ...
|
| create formula for percentage | 5/19/2008 |
Q: I wish to know the percent increase or decrease of two numbers. ie: if I bought something for $2495 ...
A: If you have the original value on cell A1, and the increased value on cell A2, you could use a ...
|
| I need help with funtion | 5/18/2008 |
Q: I would like to sum numbers in a column, but using 2 criteria. I only founf a function with one ...
A: You can use the SUMPRODUCT formula for that, something like: ...
|
| Copying data from sheet to another | 5/16/2008 |
Q: I run a report twice a week with over 1000 entries. One of the many columns of the report is a part ...
A: You may try the VLOOKUP formula, it will search column A for the part number and return the ...
|
| Pivot Tables and Calculated Formulas | 5/15/2008 |
Q: I am using Excel 2003, have created a pivot table and manual calculations outside of the pivot ...
A: Can you be a little more specific? Are you trying to make a calculated field or a calculated item? ...
|
| Hiding rows with | 5/15/2008 |
Q: I would like to hide rows that have a "0" value in them. Example: Column A rows 5-30 have machine ...
A: If you just want one run, you could use a button from the forms toolbar and assign the macro to it. ...
|
| Hiding rows with "0" value | 5/15/2008 |
Q: I would like to hide rows that have a "0" value in them. Example: Column A rows 5-30 have machine ...
A: If you don't want to use macros, you can use autofilter (menu Data->Filter->Autofilter) for that. ...
|
| Excel Reconciliation | 5/15/2008 |
Q: I have a follow up question to the one below. The only problem that I'm having with this formula ...
A: If you need the extra ones, this formula will give you the rows where the value can be found, or an ...
|
| problem | 5/14/2008 |
Q: here is my problem. on sheet 1 i have some calculations (cells a10 - a30 - just for an example). on ...
A: The solution may depend on your desired layout. If you don't care about gaps in the layout on ...
|
| Min "If" Function | 5/14/2008 |
Q: I would like to show the minimum number that appears in column b if a certain criteria is met in ...
A: The MIN formula is more tricky, as usually zero will be considered the minimum value. You may use ...
|
| RE: code for closing an excel file without saving it (go through the code) | 5/14/2008 |
Q: I have developed some code for Excel application through which i am going to create an excel ...
A: You can use the first parameter of the Close method to avoid the prompt. Set it to True of False ...
|
| changing font colour in diffrent cell | 5/13/2008 |
Q: i know how to do conditional format on a cell but what I'm trying to do is change the color of cell ...
A: You can still do that with conditional formatting on cell A1, select "formula is" in the type of ...
|
| Excel formula question | 5/12/2008 |
Q: Can you please help me create a formula, I'm inexperienced with formulas... I have two columns of a ...
A: The AVERAGE formula can do the work over the column C, something like: =AVERAGE(C1:C10) You can also ...
|
| coloured cells | 5/12/2008 |
Q: Miguel, I have few questions. 1)Is it possible to count colour cells?(Any formula?) 2)Any formula ...
A: 1. Not possible with the standard functions, you may use custom VBA for this. Check this site, for ...
|
| Excel Forumula | 5/12/2008 |
Q: I need a series of Formulas ( for 4 years ). I have 2 numbers to compare and about 4000 lines. Qty ...
A: It helps a little more on the mathematical side of the situation, I will try to produce some ...
|
| Excel Macro's | 5/8/2008 |
Q: I read the following post and found it very helpful... ...
A: In this case, you may choose to hard code the columns, something like: Sub CopySelectedItems() Dim ...
|
| Excel Macro's | 5/8/2008 |
Q: I read the following post and found it very helpful... ...
A: You could add an IF statement before the copy, something like: Sub CopyOpenItems() Dim ...
|
| Excel Forumula | 5/7/2008 |
Q: I need a series of Formulas ( for 4 years ). I have 2 numbers to compare and about 4000 lines. Qty ...
A: Sorry, I am not sure of the pattern. From examples 1 and 3, it looks like the comparison of 4 years ...
|
| Excel buttons | 5/7/2008 |
Q: I'm pretty good these days with macro writing and using excel to access databases etc so can do some ...
A: You may try with the menu Windows->Freeze panels. This will freeze a part of the screen, so you can ...
|
| EXCEL - DATE | 5/6/2008 |
Q: I have a pie chart i'd like to update each day with the data in the row for that date. See sample ...
A: You are close, the idea of name is good, they just need a different definition. Try defining the ...
|
| Excel MODE text | 5/6/2008 |
Q: I am trying to find the Mode of a column containing ~2000 Text codes (they are mutual fund symbols). ...
A: You are right, the MODE formula only works with numbers. You could use these array formula instead: ...
|
| Excel formula | 5/6/2008 |
Q: Can you tell me how/if it is possible to do the following? I've created a table which shows an ...
A: You can try with a modified version of the formulas given on the previous answer. The point is that ...
|
| Excel formula | 5/6/2008 |
Q: Can you tell me how/if it is possible to do the following? I've created a table which shows an ...
A: If you have multiple columns, you may have multiple formulas, each one with an IF statement showing ...
|
| cell splitting | 5/5/2008 |
Q: Miguel 1)How can we split the cell diagonally and enter data in each (two) sections? Is it possible? ...
A: The cell cannot be split diagonally, you can enter a diagonal line in the border tab of the Format ...
|
| autofilter option | 5/5/2008 |
Q: Miguel, Autofilter option works vertically downwards,is there any option for horizontal direction? ...
A: That is a good question, it doesn't come by default, but it could be "faked" using VBA. This page ...
|
| Dynamic Chart | 5/5/2008 |
Q: Hey Miguel, I need some more help... I understand the dynamic chart for having one column being ...
A: If you are having 1684 rows to be charted, the approach of dynamic names may not be most efficient ...
|
| data validation | 5/5/2008 |
Q: Miguel, 1)How can I auto update the list created in data validation.I keep some blank cell for ...
A: Unfortunately, there is no easy way to do number 1. As far as I know, data validations don't accept ...
|
| Excel | 5/5/2008 |
Q: I found you answer below very helpful. I actually used it with "right" rather than "left". But why ...
A: You could so something like: =IF(COUNTIF(A1:A24,A25)>0,D7,A25) You may need to change the ranges, as ...
|
| Excel | 5/5/2008 |
Q: It's me again. What I actually am trying to do is to get only what is between "the last forward ...
A: In the case that the extension is always going to be jpg, you can use a formula like: ...
|
| Row,Column intersection | 5/4/2008 |
Q: I have made a table as below colA colB colC colD colE ITEM 1MAY 2MAY 3MAY 4MAY A ...
A: It is possible, but it may be a little hard to explain, as it uses an additional column with array ...
|
| Row,Column intersection | 5/4/2008 |
Q: I have made a table as below colA colB colC colD colE ITEM 1MAY 2MAY 3MAY 4MAY A ...
A: In this case, I would recommend a data entry on a tabular format, with the date side by side with ...
|
| Row,Column intersection | 5/3/2008 |
Q: I have made a table as below colA colB colC colD colE ITEM 1MAY 2MAY 3MAY 4MAY A ...
A: The part that is difficult here is to filter the items with formulas, in order to show only the ones ...
|
| Dynamically Building a Chart | 5/2/2008 |
Q: I am needing to create a line graph that automatically updates with information from a spreadsheet ...
A: The technique that they present here is adequate, but you need to modify the formula to fit your ...
|
| Clearing check boxes using macro | 5/1/2008 |
Q: I tried your solution to this, but either I'm missing something or it's broken. Likely to be the ...
A: Yes, it does, thanks for telling. Try with this code instead: Sub ClearBoxes() Dim myShape As ...
|
| Clearing check boxes using macro | 4/30/2008 |
Q: I tried your solution to this, but either I'm missing something or it's broken. Likely to be the ...
A: Well, I have tried the code that I published before and it works as expected: Sub ClearBoxes() ...
|
| RETURNING PARTIAL DATA RETURNS FROM WITHIN A LIST | 4/29/2008 |
Q: I have put together a crude spreadsheet for predicting UK football scores over the last year, with ...
A: In this case I would use an additional column with a formula like: =(G2="Chelsea")*ROW() In fact, I ...
|
| creation of duplicate file | 4/28/2008 |
Q: I work in an environment with multiple users on workbooks. This morning I opened a file and two ...
A: You can make that thing happen by going to the menu Window->New Window. Basically Excel will have ...
|
| questions about excel | 4/27/2008 |
Q: i actually have 3 questions..... 1. what are chart handles? 2. what is a chart legend? 3. how do you ...
A: 1. I think that the chart handles are the same as the windows handles, points in the frame that will ...
|
| Excell work | 4/26/2008 |
Q: I am going to send one excel sheet to x, y and z by mail. X has to fill only x column, y has to fill ...
A: One way of doing this may be to hide the columns that you don't want your users to fill, save three ...
|
| Creating a Leaderboard | 4/26/2008 |
Q: I'm doing a hockey pool and would like to create a "Leaderboard" sheet for the entrant's scores. ...
A: This probably could be achieved with some combination of INDIRECT and RANK formulas, but I am not ...
|
| Hidden tabs | 4/25/2008 |
Q: There is a formula in one cell that has a certain tab as reference that is no longer there. yet ...
A: If the spreadsheet no longer exists, the formula would reflect this by showing the #REF!, so if the ...
|
| COUNTIF Questions | 4/24/2008 |
Q: I assume that a countif statement is my criteria for this questions, but please change if necessary ...
A: I would suggest the SUMPRODUCT formula instead of COUNTIF or SUMIF, I find that it may give you more ...
|
| Locking external references | 4/23/2008 |
Q: I work for a manufacturing company that makes machine parts and I am working on their database in ...
A: As far as I know, this is the usual behavior of Excel. What you can do is to close the file with ...
|
| Engaging an Indirect Reference | 4/23/2008 |
Q: I am referencing cells in closed workbooks. As we know the Indirect function does not work if the ...
A: This could be achieved with a macro, but my recommendation is to switch to INDIRECT.EXT, a version ...
|
| Invoice number problem | 4/23/2008 |
Q: I have some problems in Excel between Text & Numbers. I Exported invoice data from Oracle P11i ...
A: Regarding the questions on date and number format, you can change the formula to something like: ...
|
| Combinations from values in a column | 4/22/2008 |
Q: I'm trying to figure out how to generate all combinations from a list of N values in a column, let's ...
A: Sorry for the late response, I have crafted some code to make the combination of three elements, you ...
|
| automatic date and time entry | 4/22/2008 |
Q: Can there be an automatic date and time entry in the neigboring column on the same row when a value ...
A: I suppose that you are trying the iteration solution, so I will suppose that you have the iteration ...
|
| Invoice number problem | 4/22/2008 |
Q: I have some problems in Excel between Text & Numbers. I Exported invoice data from Oracle P11i ...
A: You may use the TEXT formula, and some IF conditionals, to force the text format that you want. For ...
|
| Excel 07 Time Stamp | 4/22/2008 |
Q: Is it possible to add a cell at the beginning of a row & subsqnt rows that will insert the time the ...
A: There are some ways of using timestamps in Excel, they are well explained here: ...
|
| minimum of the first 3 digits in a range and count | 4/21/2008 |
Q: Could you please tell me how to select only the first 3 digits in a range and find the minimum of it ...
A: You could use an array formula similar to: =MIN(--LEFT(D7:J7,3)) & " X " & ...
|
| Excel Find | 4/21/2008 |
Q: I want to find negative values (but not specific) by using the find & replace features. I don't want ...
A: One simple way of achieving this, but that may lead to false positives, is to look for the minus ...
|
| Excel project | 4/19/2008 |
Q: 1. We are working on a new Hewlett Packard xw4400 Workstation with 4 Gigs of RAM, 300 Gigs of hard ...
A: Sorry for the late response, I was away for the weekend. My usual approach to this type of issues ...
|
| excel | 4/18/2008 |
Q: CORES 1 2 3 4 5 6 weight 101.0 101.0 102.0 103.0 104.0 105.0 Height 1 ...
A: You can use the INDEX/MATCH combination for this. For the sample, I have supposed that the table ...
|
| debugging error unknown | 4/18/2008 |
Q: this is the program that i am trying to create, there is a debugging error there, is there anyway to ...
A: Sorry, my fault, I didn't understood correctly. Forget about the code on the last follow up, and ...
|
| excel function for multiple lookup values | 4/17/2008 |
Q: I am looking for an Excel function(s) that can essentially do what VLOOKUP does but with a range as ...
A: I may have understood incorrectly, but it looks like you want to do a lookup to the column of the ...
|
| Dependent cells | 4/17/2008 |
Q: Quick question, I have a drop down list that needs to have certain data put in the cell next to it ...
A: This can be achieved by creating a lookup table with the products that can be chosen and the prices, ...
|
| find names in excel and pull info from 3 columns | 4/17/2008 |
Q: I get a spreadsheet daily that has first, middle and last name fields. I need to find those names on ...
A: For this scenario, my recommendation would be to create an extra column on each sheet with the ...
|
| debugging error unknown | 4/17/2008 |
Q: this is the program that i am trying to create, there is a debugging error there, is there anyway to ...
A: If you want to do it with code, you can try something like: Dim mySheet As Worksheet Set ...
|
| Excel Formula | 4/17/2008 |
Q: I hope you can help, and understand what it is I am attempting! My husband has a pig sale coming up ...
A: I am not sure if I have understood correctly, do you want to enter the buyer number or the pig tab ...
|
| Excel Formula | 4/16/2008 |
Q: I hope you can help, and understand what it is I am attempting! My husband has a pig sale coming up ...
A: That formula would probably be VLOOKUP, as this can fit well in your scenario. For example, to ...
|
| Excel convert nth cell in a column to row. | 4/16/2008 |
Q: Could you please tell me how to convet every nth cell in a column to a single row using excel ...
A: Sorry for the late, I tried to come up with a single formula that you could use on the other sheet ...
|
| Excel convert nth cell in a column to row. | 4/15/2008 |
Q: Could you please tell me how to convet every nth cell in a column to a single row using excel ...
A: You can use the OFFSET formula for that, for example: =OFFSET($A$1,(COLUMN()-1)*3,0) Entering this ...
|
| my program | 4/15/2008 |
Q: this is my program Sub Msg() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Dim ...
A: Sorry for the late, you may try setting the series instead of the data source, something like: ...
|
| numbering of pages | 4/15/2008 |
Q: Miguel I have an Excel 2003 house keeping problem. I have one large file that contains many sub ...
A: Sorry, I misunderstood your question. The Auto should be the value for what you want, and you can ...
|
| Using formula answer in anouther formula | 4/14/2008 |
Q: Miguel, I want to sum a column in a cell (H14)[=SUM(H8:H13)] then in anouther cell (H15) I want to ...
A: The VLOOKUP formula should work with a formula, value or cell reference in the first parameters with ...
|
| Populate fields in Word document from Excel | 4/14/2008 |
Q: How can I export data into word document fields from an excel spreadsheet I have contracts that ...
A: I am not completely sure on this one, but I think that the mail merge option in Word (menu ...
|
| Drop-down unfluence another drop-down | 4/14/2008 |
Q: I have created a drop down list with 3 values "A", "B", "C" if i choose "A" it goes to Sheet A and ...
A: Yes, it is doable, but as the solution uses names, you many not be able to use A, B and C in the ...
|
| my program | 4/14/2008 |
Q: this is my program Sub Msg() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Dim ...
A: What I would do is to use one single iRow variable, to ensure that both ranges have the same number ...
|
| formula explanation | 4/12/2008 |
Q: miguel hope you remember me. the formula you sent me is working perfectly. but i was not able to ...
A: The INDIRECT function takes a text argument, and returns the range corresponding to that string, so ...
|
| Compare data in same column and give unique ref | 4/11/2008 |
Q: I have something on similar lines which i am not able to figure out. Hope u can help me out... I ...
A: In this case, you may use an additional column (let's say E), with a formula like: =RIGHT(B1,6) And ...
|
| Autopopulate cell format (font color, fill color) | 4/11/2008 |
Q: I've created a master sheet based on slave sheets using this formula. I plugged into a master cell: ...
A: Unfortunately, that cannot be achieved by formulas, and probably not even by conditional formatting, ...
|
| Possible Combinations | 4/11/2008 |
Q: I have 3 columns of data, not all the same amount of data. I would like excel to create three more ...
A: If you have 10,000 cells in one column, I hope that you have Excel 2007, as the previous versions ...
|
| Possible Combinations | 4/11/2008 |
Q: I have 3 columns of data, not all the same amount of data. I would like excel to create three more ...
A: I mean that, if you are expecting around 1000 combinations, select the range D1:F1, go to menu ...
|
| using vlookup on two columns | 4/11/2008 |
Q: I trying to use vlookup to look up values in two columns and return the value in the third colums. ...
A: VLOOKUP can only look values on one column, you may use the INDEX/MATCH combination instead. Here ...
|
| Possible Combinations | 4/11/2008 |
Q: I have 3 columns of data, not all the same amount of data. I would like excel to create three more ...
A: I am supposing that the data is on columns A, B and C, and that you have no headers. With that, you ...
|
| Display count per column | 4/10/2008 |
Q: I have a category column with many different entries. Can I have a cell that counts how many time ...
A: You can use the COUNTIF formula for that, something like: =COUNTIF(A:A,"entry") You can use cell ...
|
| look up values problem | 4/10/2008 |
Q: is there any lookup formula which can return a value not in the same row but from given column and ...
A: Sorry for the late response, this was not an easy one. This array formula should work, I am not ...
|
| reg compare rows | 4/9/2008 |
Q: I really aprreciate your help on this, the last one you posted more useful, have one last followup ...
A: The latest formula that I wrote, the one starting with =IF(MOD, is suitable to be copied and pasted ...
|
| compare two cells and count | 4/9/2008 |
Q: I have a worksheet in that I need to compare two rows and count how many times row1>row2 and display ...
A: you may try with this modified array formula: ...
|
| look up values problem | 4/9/2008 |
Q: is there any lookup formula which can return a value not in the same row but from given column and ...
A: If you are looking for values on column A, and want to return values two rows below the found one, ...
|
| look up values problem | 4/9/2008 |
Q: is there any lookup formula which can return a value not in the same row but from given column and ...
A: For that, you can try the INDEX/MATCH combination, and in any case you should be able to code the ...
|
| Forumla for checking cell a = x and cell b =z | 4/8/2008 |
Q: hopefully you can help me I have to create a spreadsheet that reports performance in responding to ...
A: In this case my recommendation would be to create a pivot table with the data, as that will give you ...
|
| Time to decimal | 4/8/2008 |
Q: I have a problem with excel 2003, in a time sheet I have a time total that looks like this ...
A: The number behind the time is a decimal number, expressed as fractions of a day. So, if you want to ...
|
| formatting cells based on result of formula | 4/8/2008 |
Q: Is there a method to format cells simply by using colours. For example if i calculate many cells and ...
A: It makes sense, and you can find some good examples in this page: http://www.rondebruin.nl/tips.htm ...
|
| Excel 2003 | 4/8/2008 |
Q: I have many sheets in a work book how do I get the printer to print the page number on each page as ...
A: I suppose that you are grouping your sheets before printing, as that will make the numbers ...
|
| formatting cells based on result of formula | 4/7/2008 |
Q: Is there a method to format cells simply by using colours. For example if i calculate many cells and ...
A: You can do that by selecting the cells to format, and using the menu Format->Conditional formatting. ...
|
| Array formula | 4/6/2008 |
Q: Miguel In your last reply to my query (sorting of month), you had recommended to use array formula. ...
A: This is a good question, and in my experience the answer is yes, an extensive usage of array ...
|
| microsoftexel | 4/5/2008 |
Q: I AM WORKING ON MS EXCEL 2003,I WANT TO FILL THE TEXT IN THE ALL SHEETS WITH SAME COLUMN NAME IN ...
A: You can update the same cells in many worksheet in a single stroke by selecting multiple tabs. To ...
|
| compare two cells and count | 4/4/2008 |
Q: I have a worksheet in that I need to compare two rows and count how many times row1>row2 and display ...
A: I am not sure on the layout that you have posted, so the formula below is more generic. My ...
|
| Sort with date | 4/4/2008 |
Q: Miguel I have table with list of dates in it (a single column with, dd/mm/yy-format).I want to count ...
A: In this case, you may use an array formula like: =SUM(--(MONTH(A1:A100)=5)) Enter it with ...
|
| Autofilling | 4/4/2008 |
Q: I am using Excel 2003. There I am trying to do the following steps to Autofill. 1. Selecting A1 ...
A: For this one I am not sure if there is a way to change the behavior, sorry. One workaround may be ...
|
| Replace with blank | 4/3/2008 |
Q: In an excel workbook with many sheets, I've set up a formula in column I of each workbook to run an ...
A: In this case I would recommend using the autofilter (menu Data->Filter->Autofilter), filter by ...
|
| comparing data | 4/3/2008 |
Q: Expert, Thank You for you time. I have 2 separate spread sheets. Each sheet has a "item number" in ...
A: You can use the INDEX/MATCH combination here, something like this on cell B2: ...
|
| Expiry Date Alert | 4/3/2008 |
Q: I have a list of trainees,their certificate dates, and expiry dates, how do I flag a warning up 30 ...
A: One way of doing it is by using conditional formatting. Select the cell(s) that you want to format, ...
|
| Autofilling | 4/3/2008 |
Q: I am using Excel 2003. There I am trying to do the following steps to Autofill. 1. Selecting A1 ...
A: Nice question, I hadn't noticed this behavior before. It is controlled by an option in the menu ...
|
| lreynolds@scottcompanies.com | 4/2/2008 |
Q: My tab key goes form Row A to Row K. it used to let me enter row a,b,c,d and hit enter and go bleow ...
A: It may be that the worksheet has been protected, and the columns B to J have been locked, so the tab ...
|
| Calculating business hours between two dates | 4/2/2008 |
Q: I have date-times of when an order was received and when it was completed. I subtract the received ...
A: You are right, I didn't addressed any special cases. This formula, for example, uses an IF ...
|
| If Statemnt in excel | 4/2/2008 |
Q: How would I write the if statement for the following: I have 10 cells, C3-L3. If all cells have ...
A: It will depend a lot of the actual layout of the information, if the student is always in the same ...
|
| Colouring each second row of big format excel table | 4/2/2008 |
Q: The situation is: I have a very big format table in MS Excel sheet (about 10 000 rows). I need to ...
A: One way of achieving this, that will stay even if you remove rows, is to use conditional formatting. ...
|
| my program | 4/2/2008 |
Q: this is my program Sub Msg() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Dim ...
A: The ranges that you are defining for the chart are the source of the error, you need to explicit the ...
|
| Calculating business hours between two dates | 4/2/2008 |
Q: I have date-times of when an order was received and when it was completed. I subtract the received ...
A: You can use the NETWORKDAYS function from the Analysis ToolPak Add-in, to get this result. For ...
|
| follow up on ranging | 4/1/2008 |
Q: this is a follow up question {is there like an easier way to do it rather than recording it one by ...
A: You can change the Range part of the formula to the name of the column, for example to check column ...
|
| Determining length of trend before significant retrace | 4/1/2008 |
Q: In range of data I need to find the largest numerical movement in one direction without (x) ...
A: I am not sure about how to create User Defined Functions in Excel 2007, but this UDF should do the ...
|
| Vlookup Function | 3/31/2008 |
Q: i am using the following formula ...
A: One way of doing this is by opening the two books, editing the formula, select the Materials!A:B ...
|
| go to reference with double click | 3/31/2008 |
Q: I have a summary sheet which refers to a great amount of other worksheets on tebs within the same ...
A: You can do this with the BeforeDoubleClick event in the summary worksheet, with a code like this: ...
|
| ENTER SHADING | 3/30/2008 |
Q: USING EXCEL 2003,WRITE A FORMULA FOR CELL G5 CONSIDERING ALL THREE CONDITIONS AS FOLLOWS: If the ...
A: You can do this by using the menu Format->Conditional Formatting. Select the cell to shade, open ...
|
| Transfer Conditional Formatting between Worksheets | 3/29/2008 |
Q: Is it possible to Transfer Conditional Formatting between worksheets. i.e. Have a conditional format ...
A: The short answer to the question is no, there are no formulas that bring directly formats as they ...
|
| Excel OR statement | 3/29/2008 |
Q: I'm in need of a little help with my basics of my formula. Basically this is what i'm wanting the ...
A: You can do it with a formula, something like: ...
|
| Removing middle initial | 3/28/2008 |
Q: I have a spreadsheet with around 40,000 names. I need to remove the middle initial if their is one. ...
A: Knowing that, this formula will search for names with a unique letter between two spaces, and if ...
|
| Excel Formatting/Sorting | 3/28/2008 |
Q: I have an excel file containing several thousand entries that I need sorted by year, they are in the ...
A: One possible cause is that some of the dates are formatted as text, you can check that easily by ...
|
| Removing middle initial | 3/28/2008 |
Q: I have a spreadsheet with around 40,000 names. I need to remove the middle initial if their is one. ...
A: Assuming that the middle initial will always have a dot, and no other names will have dots on it, ...
|
| Excel Problem | 3/28/2008 |
Q: A B C D E F G H I 1 E/W 1 2 3 6 36 2 2 4 6 96 ...
A: If there is going to be always 5 rows of different between the found value and the values that you ...
|
| ranging | 3/28/2008 |
Q: is there like an easier way to do it rather than recording it one by one, say if i have hundreds of ...
A: The iRow part is a variable that should contain the number of rows. I proposed initially the ...
|
| Iteration/circular formula/goal seek | 3/27/2008 |
Q: I have a slightly complicated sheet. The formulas are correct, but I am at a point where I need to ...
A: Goal seek is good for simple iterations, but if you have a complex model, you may want to use ...
|
| Links to seperate sheets | 3/27/2008 |
Q: Currently I am working on making a spread sheet to be used some some executives to track the ...
A: You can create links with the HYPERLINK formula, something like: =HYPERLINK("[C:\My ...
|
| Formula | 3/27/2008 |
Q: I have a database that records goods ordered by our company but found to be damaged on delivery.Each ...
A: In this case, the INDEX/MATCH combination may be more handy. For example, to return the first value ...
|
| Formula | 3/27/2008 |
Q: I have a database that records goods ordered by our company but found to be damaged on delivery.Each ...
A: I suppose that you have the data related to the identity numbers all in the same row, but in ...
|
| ranging | 3/27/2008 |
Q: is there like an easier way to do it rather than recording it one by one, say if i have hundreds of ...
A: You say that you have 100+ buttons to program, what do they have to do exactly? From the code, you ...
|
| Excel Question | 3/27/2008 |
Q: how do I get the data I enter on the first sheet to automatically enter itself on the appropriate ...
A: It will depend on how you define "appropriate". The simplest scenario would be to have fixed ...
|
| how to autofilter merged cells | 3/26/2008 |
Q: I have a spreadsheet set up so it looks something like this: Thing Color ...
A: My first advice would be to get rid of the merged cells, they are really painful to work with. In ...
|
| excel | 3/26/2008 |
Q: if some cell in colA and colB are blank and . I want to copy some cells in col c and d to colA & b. ...
A: You can do this by using two additional columns, with formulas like: Cell E1: =IF(A1<>"",A1,C1) Cell ...
|
| ranging | 3/26/2008 |
Q: is there like an easier way to do it rather than recording it one by one, say if i have hundreds of ...
A: Sorry, I don't have the full picture of what you are trying to achieve. Can you please provide some ...
|
| setting end of cells as limit | 3/26/2008 |
Q: here is my question, i have a project that requires me to create charts with the amount of numbers ...
A: In this case, you may want to be more precise on your series. My recommendation would be to record ...
|
| Excel Question | 3/25/2008 |
Q: I have column with duplicate values. I want to be able to extract values that are duplicated more ...
A: You can use conditional formatting for this. To do it, select the cell A1, extend the selection to ...
|
| Unique overlaping times | 3/25/2008 |
Q: I like to evaluate how many unique overlapping times i have in my table any second or minute. Here ...
A: I am assuming that the data will be ordered by Start Time, so we can use formulas for the solution. ...
|
| setting end of cells as limit | 3/25/2008 |
Q: here is my question, i have a project that requires me to create charts with the amount of numbers ...
A: In this case, I would use a different approach, based on code. Include this line on the variable ...
|
| If statements | 3/25/2008 |
Q: I am making a spreadsheet for work where each piece of equipment needs to be ordered so many weeks ...
A: You can do this using conditional formatting. Select the cell F20, go to menu Format->Conditional ...
|
| Array Formula | 3/24/2008 |
Q: I have been working on a spread sheet/database for a charity that I work for. I have a sheet with ...
A: You have the right idea, but it looks like there are some errors on formatting. Try this array ...
|
| setting end of cells as limit | 3/24/2008 |
Q: here is my question, i have a project that requires me to create charts with the amount of numbers ...
A: One way of achieving this is by using dynamic names. You can set up names (menu ...
|
| If Then Macro Code | 3/23/2008 |
Q: I have a table with columns A through N rows 1 through 10. I will have number values in each cell ...
A: This can be achieved without the use of macros, by selecting the cell B1, expanding the selection to ...
|
| Excel 'auto fill' for users | 3/23/2008 |
Q: I have a workbook that has two main data sheets and two other sheets to ease data entry. On one of ...
A: As far as I know, there is no easy way to achieve this, so probably you are doing it in the most ...
|
| If formula | 3/22/2008 |
Q: I want to create a formula to populate cell P37 when the total in cell P36 is greater than 40. My ...
A: Sorry, I am unable to replicate your issue. The formula is correctly written, you don't need to use ...
|
| Next Autofilter Result | 3/20/2008 |
Q: I have an excel question for you. I am trying to develope a piece of code that will interact with an ...
A: Sure, you can add an If construction to check if the value is going to be empty, and show a message ...
|
| Next Autofilter Result | 3/20/2008 |
Q: I have an excel question for you. I am trying to develope a piece of code that will interact with an ...
A: Where do you get the out of range error? I have hardcoded the name of the support worksheet, if you ...
|
| If Formula | 3/19/2008 |
Q: Each Cell between A2 and Z2 is going to contain an A, B, or C within the cell. Let's assume A2:D2 ...
A: It will depend a lot on how you plan to extract the information, but one thing that can help you are ...
|
| Calculating Number of Weekends in a date range | 3/19/2008 |
Q: How to find total number of weekends in a given date range. The start/ end dates are given in two ...
A: One way of achieving this is by using the NETWORKDAYS function from the Analysis ToolPak add-in, ...
|
| Next Autofilter Result | 3/19/2008 |
Q: I have an excel question for you. I am trying to develope a piece of code that will interact with an ...
A: One way of doing this is by writing down the list of unique values on a separate worksheet, use ...
|
| Excel Calculation for group Golf Games | 3/19/2008 |
Q: I am trying to get a spreadsheet to calculate the amount of subtraction 1 or 2 with respect to ...
A: After seeing the file, I have tried this formula in F9 on the Tidewater sheet, copied it to each Net ...
|
| Inserting a name from a database | 3/19/2008 |
Q: I have a list of names and of codes of those names,which kind of formula can I create to match the ...
A: Sorry, I need more details on what you are trying to achieve. What is the layout of your data? You ...
|
| Inserting a name from a database | 3/19/2008 |
Q: I have a list of names and of codes of those names,which kind of formula can I create to match the ...
A: I am supposing that you want to match the name to the code in a separate place than the database, so ...
|
| need help for wildcard | 3/19/2008 |
Q: I want to display today's date in a cell, when certain other cell contains any character.
A: You can use the SEARCH formula for this, for example this formula will show today's date if the cell ...
|
| Excel function | 3/18/2008 |
Q: This is the current function =IF(AC9=0,(IF('PA FTE'!I7>0,('PA FTE'!I7-'PA ...
A: In this case, I think that the most convenient solution is a user defined formula, something like: ...
|
| Referring to the cell containing the vba function | 3/18/2008 |
Q: (The question looks long, but I think you can read it in a couple of minutes and it will save you ...
A: In this case, the object that you are looking for is Application.Caller. When used in a single cell ...
|
| Inserting min and sec | 3/13/2008 |
Q: How do you type length of time of an activity in excel. For example if ran for 2min:30secs, how can ...
A: You can type with a 0: in front of it, something like: 0:2:30 This will recognize it as ...
|
| Compare 3 columns | 3/12/2008 |
Q: I have 3 columns (A,B and C) of names and am trying to find names that appear in all 3 columns and ...
A: In this case I would recommend the use of an additional column (let's say E) with a formula like ...
|
| List | 3/12/2008 |
Q: Miguel, Pl refer following example. Col-A Col-B Col-C Col-D A 5 A C B 7 ...
A: In this case, I would use some additional columns, one per element that you want to bring in. They ...
|
| Date Formula & weekends, holidays | 3/11/2008 |
Q: Hope you can assist with either of this this 2 part question. I'm using excel 2003 in windows xp. I ...
A: You can use the NETWORKDAYS formula from the Analysis ToolPak Add-in, something like: ...
|
| If Statemnt in excel | 3/11/2008 |
Q: How would I write the if statement for the following: I have 10 cells, C3-L3. If all cells have ...
A: Now I am the one who doesn't have it clear, sorry. My main confusion point is, do you consider the ...
|
| Instering the value of a cell which is text into formula | 3/11/2008 |
Q: I saw that in the past you answered nearly the same question (shown below), but I'm having problems. ...
A: The INDIRECT formula doesn't work on closed folders, but there is a similar formula in an add-in ...
|
| If Statemnt in excel | 3/11/2008 |
Q: How would I write the if statement for the following: I have 10 cells, C3-L3. If all cells have ...
A: You could use SMALL twice, but not MIN twice. MIN will only return the minimum value, while SMALL ...
|
| Conditional counting | 3/11/2008 |
Q: I have a table , containing order numbers in one column, and in other column their status is ...
A: In this case, you can use the SUMPRODUCT formula, something like: ...
|
| excel 97 function | 3/10/2008 |
Q: "I need to create an excel spreadsheet that will keep track of 5 years of data recorded monthly. ...
A: In case that the data is added row by row, you could still use this formula. For example: ...
|
| Replicate Conditional Formating | 3/10/2008 |
Q: I have selected a row from which I have applied some conditional formatting. The formatting relies ...
A: You can set up the conditional formatting using Formulas instead of values, selecting "Formula Is" ...
|
| Excel Formula | 3/10/2008 |
Q: I am trying to find a fomula for a report that I am working on for my boss. The report populates a ...
A: My recommendation here would be to use the VLOOKUP formula, so you can keep both the codes and the ...
|
| excel 97 function | 3/10/2008 |
Q: "I need to create an excel spreadsheet that will keep track of 5 years of data recorded monthly. ...
A: If there are no special complications, a direct SUM formula on the range with the latest 12 months ...
|
| If Statemnt in excel | 3/10/2008 |
Q: How would I write the if statement for the following: I have 10 cells, C3-L3. If all cells have ...
A: If all the numbers are either positive or zero, you may not need and IF function, but something ...
|
| Question with filling formulas in excel | 3/9/2008 |
Q: I've been trying to figure this out for some time, but I've been unsuccessful. Let's say I have a ...
A: The notation for absolute references in Excel is the use of the dollar sign. If you want to fix the ...
|
| Replicate Conditional Formating | 3/9/2008 |
Q: I have selected a row from which I have applied some conditional formatting. The formatting relies ...
A: If you have managed to it for one row, you can extend the conditional formatting by selecting the ...
|
| ranking data | 3/9/2008 |
Q: I have this formula: ...
A: This is an interesting challenge, if you don't mind adding two additional columns the solution below ...
|
| "Anchoring" the first few rows of excel | 3/8/2008 |
Q: I use the first few rows for the statistics of the columns (average, min, max...). But then, when I ...
A: The option to anchor rows and columns in on the menu Window->Freeze panels. It works on the top ...
|
| excel help | 3/7/2008 |
Q: =IF(ISBLANK(D21),"",SUMIF(Sheet2!A:A,Invoice!D21,Sheet2!B:B)) above is the formula that i'm using ...
A: I have tried the formula, and for 49.99 the formula gives 3.299, so we may need to adjust the ...
|
| Cross Reference Fields | 3/7/2008 |
Q: Miguel, I have tried different functions but with no success. From our ledger system only one ...
A: In this case I would recommend a pivot table. Select the range with your data, go to menu ...
|
| excel help | 3/7/2008 |
Q: =IF(ISBLANK(D21),"",SUMIF(Sheet2!A:A,Invoice!D21,Sheet2!B:B)) above is the formula that i'm using ...
A: Here is a different approach for this one, I think it works better when adding more segments: ...
|
| Excel | 3/7/2008 |
Q: I have an Excel spreadsheet that has several lists. For example, I have a category that lists nodes ...
A: in this case I think the Pivot table is the more fitting option. To do it, select the full range ...
|
| a lookup that displays all the information in the form | 3/7/2008 |
Q: have created a form in vba that writes data to a spreadsheet called inventory. Currently we have ...
A: The code is almost there, you just need to change the MsgBox to whatever fields you want to fill. ...
|
| How to Match Values Between 2 Sheets & Copy Corresponding Data ? | 3/7/2008 |
Q: Suppose we have 2 sheets Sheet1 and Sheet2. Sheet 1 In Sheet 1 Column A we have some names like ...
A: You can use the VLOOKUP function for that, something like: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,FALSE) ...
|
| Excel | 3/6/2008 |
Q: I have an Excel spreadsheet that has several lists. For example, I have a category that lists nodes ...
A: There are several options, but the more appropriate one will depend on the layout of the data. If ...
|
| Command button | 3/6/2008 |
Q: I am using Excel 2003. I made a spreadsheet for expense reports.When I travel to different places, ...
A: In that case, every cell that you want to have the zero should have the third parameter of the IF ...
|
| copying and printing reciept | 3/6/2008 |
Q: I have a spreadsheet with data in for a company where the buyer enters the number of beans it needs ...
A: I don't know the exact details, but my suggestions would be the following: - Format the entry page ...
|
| Command button | 3/6/2008 |
Q: I am using Excel 2003. I made a spreadsheet for expense reports.When I travel to different places, ...
A: My suggestion here would be to use a check box from the Forms tool bar, link it to a cell, and then ...
|
| Macro Code | 3/6/2008 |
Q: It is me again. Sorry to keep bothering you. I did what you recommended by using the absolute ...
A: The first error is because you can only have one Worksheet_Change macro per page, the solution that ...
|
| two dimensional lookup | 3/5/2008 |
Q: I am trying to create a formula to look up a table based on two reference values. Basically, I need ...
A: You may want to replace the HLOOKUP function with the MATCH one, as the later will return the ...
|
| VBA Last numeric entry | 3/5/2008 |
Q: I would like to insert some VBA programming into a macro to find the last numeric entry that is not ...
A: but there are still some details that I would need to craft the VBA code. The first one is about ...
|
| a lookup function | 3/4/2008 |
Q: I have a spreadsheet that is populated by a VBA form. The user enters the data into the form that in ...
A: If you are using Option Explicit the answer is yes, on other cases is it not necessary but ...
|
| a lookup function | 3/4/2008 |
Q: I have a spreadsheet that is populated by a VBA form. The user enters the data into the form that in ...
A: You are right, that formula will work on the cell, not from the code. For the code, you could use ...
|
| Conditional Formatting w/ Dates | 3/4/2008 |
Q: Miguel, I am working on a preventative maintenance worksheet which tracks our work scheduled for the ...
A: You can use conditional formatting for this, both for the cell with the date and the others in the ...
|
| Auto filter option | 3/4/2008 |
Q: I am herewith giving a small example for my query. COL A COL B A RON TOM ...
A: Unfortunately, I think that both filling the ranges or using formulas are the easiest ways to do ...
|
| VBA Last numeric entry | 3/4/2008 |
Q: I would like to insert some VBA programming into a macro to find the last numeric entry that is not ...
A: I hope this code can give you some ideas: For Each myCell In ActiveSheet.Range("A:A") ...
|
| a lookup function | 3/4/2008 |
Q: I have a spreadsheet that is populated by a VBA form. The user enters the data into the form that in ...
A: One way of achieving unique entries is through data validation. You can check this page for a ...
|
| Calculate Age Question | 3/4/2008 |
Q: I've tried: =TODAY()-"CELL WITH DATE OF BIRTH" and hit enter. When I do this, I get another date. ...
A: You are on the right track, one way of doing it is to format the cell with a custom format like: yy ...
|
| Tab Color Missing | 3/3/2008 |
Q: How can I change Tab colors on my Worksheets when the tab color on drop down list is missing when I ...
A: Sorry, I misunderstood your question. One potential case where the Tab color is not available is ...
|
| column chart in excell | 3/3/2008 |
Q: Good Morning, I want to put a threshold line across a column chart (3D prefered). I have 10 ...
A: I don't have any experience with 3D graphs, this works on 2D graphs, but I am not sure about the 3D ...
|
| Tab Color Missing | 3/3/2008 |
Q: How can I change Tab colors on my Worksheets when the tab color on drop down list is missing when I ...
A: You can modify the color palette on the menu Tools->Options->Color tab. The Modify button lets ...
|
| comparative excel sheet | 3/2/2008 |
Q: Miguel. I work at a public library and have been asked for help by the Library Director. The ...
A: Merged cells are usually a pain to work with, I would use them only in presentation worksheets, ...
|
| LOOKUP function | 3/2/2008 |
Q: I have a some "Order numbers" in a single column.They begin with CO and PR. CO is ranging from 900 ...
A: The VLOOKUP function doesn't work on columns to the left, you may use the INDEX/MATCH combination ...
|
| LOOKUP function | 3/1/2008 |
Q: I have a some "Order numbers" in a single column.They begin with CO and PR. CO is ranging from 900 ...
A: In this case, I would use the VLOOKUP function instead, it has a parameter that can force an exact ...
|
| Logical Formula | 3/1/2008 |
Q: Column E is the “Reported date” & Column I is the “completed date” and I have a Period of time ...
A: I suppose that you want to use TODAY() just in the case that I8 is empty. If that is the case, you ...
|
| comparative excel sheet | 2/29/2008 |
Q: Miguel. I work at a public library and have been asked for help by the Library Director. The ...
A: This is one of the main features of spreadsheets, you can use the cell reference inside the ...
|
| If then Macro | 2/29/2008 |
Q: I am beginning macro user (mostly recording macros) and I am finding it difficult to do the ...
A: If you want it to work automatically, you can put the code in the Worksheet_Change event of the ...
|
| Display number of formulas used | 2/29/2008 |
Q: Miguel Is there any way (any command,formula)to find out or display the number of links , formulas ...
A: One way is by using macros, this macro for example will create a new sheet with all the formulas ...
|
| Imported data | 2/28/2008 |
Q: I have downloaded data from another program into excel. I want to put the name and total hours ...
A: One way of doing it is with an additional column, where you enter a formula like: ...
|
| If then Macro | 2/28/2008 |
Q: I am beginning macro user (mostly recording macros) and I am finding it difficult to do the ...
A: You can try with a code like: Sub HideColumns() If Sheets("Inputs").Range("D16").Value = "Y" ...
|
| Help with formula | 2/28/2008 |
Q: I have 2 columns of information. Column C is a list of states, sometimes the states appear more ...
A: In this case you can use the SUMPRODUCT formula, something like: ...
|
| checkboxes | 2/28/2008 |
Q: I currently have over 1000 different checkboxes within my spreadsheet and each one is in a different ...
A: You are welcome, I am happy to hear that it worked. Regarding the size of the file, I am not the ...
|
| Thank you and one other question | 2/28/2008 |
Q: I recently asked a question here and both yourself and one of the other folks here were both so ...
A: I am still not sure on how you create checkboxes from the Drawing toolbar, I can only find basic ...
|
| checkboxes | 2/28/2008 |
Q: I currently have over 1000 different checkboxes within my spreadsheet and each one is in a different ...
A: Sorry, my fault, I should have included a check for the type of shape first. Try with this code: ...
|
| Thank you and one other question | 2/28/2008 |
Q: I recently asked a question here and both yourself and one of the other folks here were both so ...
A: I am not sure on what you mean by drawing checkboxes, but my recommendation would be to record a ...
|
| Excel reference to worksheet | 2/27/2008 |
Q: Excel XP. Is there a way to reference a second sheet in a workbook using the VARIABLE TEXT in a ...
A: You can use the INDIRECT formula for this, something like: =INDIRECT(A1 & "!A1") Where in the cell ...
|
| checkboxes | 2/27/2008 |
Q: I currently have over 1000 different checkboxes within my spreadsheet and each one is in a different ...
A: You can use a code like this to make the link, and then remove it from the book: Sub LinkCells() ...
|
| Multi column list (Ref) | 2/27/2008 |
Q: I am referring to the question asked to you (Not by me) on 7/30/2007 for "Multi column list".You ...
A: The combo box appearance is done by using the menu Data->Validation. When you select to allow a ...
|
| excel function | 2/27/2008 |
Q: i have a problem in excel. i have a table with money currency. i update the table everyday. i have a ...
A: The more common scenario here is when a column may have blanks, but there is still another column ...
|
| VBA Cell Value change help | 2/26/2008 |
Q: My moto is to whenever i change value for column O From 4 to 191, it should display the msg box ...
A: You can try with a code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim ...
|
| accessing multiple worksheets | 2/26/2008 |
Q: I am using the following formulas to calculate the qty of claims and the total of claims for each ...
A: There are some formulas that can accept groups of worksheets, based on order, for example this ...
|
| excel function | 2/26/2008 |
Q: i have a problem in excel. i have a table with money currency. i update the table everyday. i have a ...
A: If you have the currency in column B, you could refer to the latest entry with a formula like: ...
|
| Page number location in Excel footer | 2/25/2008 |
Q: Setting up the page numbers in my document is easy, but the page numbers want to go to the second ...
A: If you have two lines on the other sections, one thing that you can do to relocate the page number ...
|
| COMBIN | 2/25/2008 |
Q: I work at a Bank and am trying to figure out the number of account possibilities we can have if each ...
A: In this case you don't need the built-in statistical functions, the number of combinations will be ...
|
| Excel: Vlookup/Hlookup? | 2/24/2008 |
Q: I have a table with 43 rows and 78 columns. The rows indicates years and the columns headers are ...
A: You can use the INDEX and MATCH formulas for this case. Supposing that the range to lookup (row and ...
|
| List of Dates - Less Weekends and Holidays | 2/23/2008 |
Q: In Column A, I would like to list the dates, (i.e, 1/1/08, 1/2/08, 1/3/08...) beginning with the ...
A: In this case I would use an additional column, with a formula like: ...
|
| I have a blank cell where there should be a value | 2/22/2008 |
Q: I have ...
A: You can change the formula in O8 to something like: ...
|
| Copy columns from one sheet into different columns | 2/22/2008 |
Q: have 2 sheets. Sheet1 and sheet2.I have 4 columns in sheet1 and 3 columns in sheet2. All columns ...
A: The macro will depend on the set of rules that the data needs to follow, if there is any consistency ...
|
| Excel | 2/21/2008 |
Q: Sir: How do you move data from 1 worksheet to another within a work book? I input numbers in ...
A: You can use the cell reference with the sheet name in the formula, something like: =SheetA!D6 You ...
|
| Collapsing multiple lines of data for a single student with a formula or macro? | 2/21/2008 |
Q: I am trying to automate a simple, but repetitive function, but having no success. I have a huge ...
A: in this case you may want to use the VLOOKUP formula. It may take some time to set up the first row ...
|
| footer only on last page | 2/21/2008 |
Q: You have helped me in the past so, I'm hoping you can help me one more time! I need to only have my ...
A: There is a potential issue because the macro is calling itself every time it prints out, and also, ...
|
| excel change by val not working | 2/21/2008 |
Q: one of the worksheets is a summary that I have setup links from eahc of the 4 remaing sheets to ...
A: The code should not be used on the Brantford sheet, as it will trigger the event and enter an ...
|
| Collapsing multiple lines of data for a single student with a formula or macro? | 2/21/2008 |
Q: I am trying to automate a simple, but repetitive function, but having no success. I have a huge ...
A: I am not exactly sure on the final layout that you are trying to achieve, but you may try any of ...
|
| countif | 2/21/2008 |
Q: how can i use count if with this range ...
A: Because the range is not continuous, I would use the SUM formula instead. For example: ...
|
| excel change by val not working | 2/20/2008 |
Q: one of the worksheets is a summary that I have setup links from eahc of the 4 remaing sheets to ...
A: You can approach this with at least two options, using the Calculate event on the summary sheet, or ...
|
| error if value not filled in | 2/20/2008 |
Q: Hope you had a nice vacation. I have a vba form that writes information to a spreadsheet that is ...
A: In this case, you can add individual If checks for the fields that are not mandatory, to avoid using ...
|
| error if value not filled in | 2/20/2008 |
Q: Hope you had a nice vacation. I have a vba form that writes information to a spreadsheet that is ...
A: You can code IF constructions to check the conditions. For example, if you only want to execute the ...
|
| Search the specific word/number and return with rows | 2/20/2008 |
Q: I have stock register for my Spare Part business in EXCEL 2003, I want one cell where I can put part ...
A: In this case my recommendation would be to use autofilter, as it will enable you to select the part ...
|
| recording macro to uncheck checkboxes | 2/19/2008 |
Q: I have several columns of checkboxes (from forms menu)in a particular worksheet and I want to be ...
A: This code will search for check boxes in the same column as the selected cell, and clear the ...
|
| confusion with date | 2/19/2008 |
Q: Miguel, I have entered a date "5/02/2008" in a cell, and given a reference to another cell. But ...
A: To ensure that the date is shown properly, on the same menu where you checked the cell format, ...
|
| list display | 2/19/2008 |
Q: Miguel I have a sheet contains Name, Addresses,Tel no (of our customers)in three respective columns ...
A: In this case, I would recommend OFFSET and MATCH. If the name to look up is in cell E2, the ...
|
| need vba code to monitor a process | 2/19/2008 |
Q: ok i wrote this as a test and it freezes at the second line every time. doesn't matter what line i ...
A: The error is because the objItem doesn't have the property PercentProcessorTime. I haven't worked ...
|
| More macro help please | 2/19/2008 |
Q: This is the version that you supplied me with. Dim cell As Range, rw As Long For Each cell In ...
A: Taking advantage of the row by row way that the For Each statement cycles on the range, the code ...
|
| Excel Autofilter Problem | 2/19/2008 |
Q: I am having risk assessment data for several projects in Excel sheet. The simplified table structure ...
A: You can do some formatting work with the group feature, and then the project names will be one click ...
|
| excel formulas | 2/15/2008 |
Q: I am an ebay seller and keep track of fees on excel to help me know how much I will get for item ...
A: This formula should do the trick: ...
|
| Excel formulas | 2/15/2008 |
Q: I looking to add a group of cells that contain hours and minutes. ie 1 h and 15 min would be 1.15 in ...
A: One way of doing it is with an array formula similar to this: ...
|
| Automove rows to another sheet | 2/15/2008 |
Q: i have a couple of sheets on the same workbook. on the first sheet. i have lots of data about 200 ...
A: this code should do the trick: Private Sub Worksheet_Change(ByVal Target As Range) If ...
|
| Macro help | 2/15/2008 |
Q: I have a macro that copies only a certain colour of text from a range of other coloured text. the ...
A: I have simplified the code, and it is working fine for me, copying the values and the format: Sub ...
|
| Automove rows to another sheet | 2/15/2008 |
Q: i have a couple of sheets on the same workbook. on the first sheet. i have lots of data about 200 ...
A: In order to help you better, I would need to know more details on what you are trying to achieve. ...
|
| pivot counting individuals | 2/15/2008 |
Q: I am having trouble trying to make a pivot table only count a ref number once even if it appears ...
A: This technique will work only for the common properties, but it looks like they are the ones that ...
|
| % in Excel | 2/14/2008 |
Q: I have a question about finding a formula for a %. Say I have a gift store that uses a credit card ...
A: The % that they took can be found dividing the fee by the total earned, in your case the formula ...
|
| Cell to show zero | 2/14/2008 |
Q: Miguel, Main summary sheet is having the reference of my 40 sheets(models).If the cell is blank in ...
A: You can usually solve this in two ways, one is the conditional formatting, setting the ink of the ...
|
| shortages display | 2/14/2008 |
Q: I have 42 models and its spares. I have created separate sheet for each model and listed spares in ...
A: In this case, the formula will be more complicated, as you need to determine the worksheet from the ...
|
| 3-D Drawing | 2/13/2008 |
Q: Miguel, In Excel, I want to creat a 3-D drawing, consists of a block and cylinder. I could creat ...
A: To interact with the forms you have two useful tools, the draw menu on the left of the drawing ...
|
| Border the cell | 2/13/2008 |
Q: A small query...The cell border option (the last one in the chart, which creats thick border around ...
A: You can select all the cells that you want to format, go to menu Format->Cells, and go to the Border ...
|
| PM task list | 2/13/2008 |
Q: I am doing up a list of PM's (preventative maintenance) for our operators that is to be tracked ...
A: I am not sure on how you have formatted the data, but my recommendation here would be to do it in a ...
|
| shortages display | 2/13/2008 |
Q: I have 42 models and its spares. I have created separate sheet for each model and listed spares in ...
A: I think it can be done with a formula, but I would need to know the criteria for selecting the ...
|
| Calculating time when it straddles midnight | 2/13/2008 |
Q: How do you subtact the time difference when the start time is before midnight and extends past ...
A: You can check if the start time is greater than the end time, and adjust by one day. Something ...
|
| create macro to clear checkboxes and lists | 2/12/2008 |
Q: I have been using Excel 2003 to automate a production schedule. On a particular worksheet, I have a ...
A: This code will clear all the check boxes, and put the first value on all the drop down lists in the ...
|
| compilation on single sheet | 2/12/2008 |
Q: Miguel, I have to keep records of 40 models & its spares. I have created 40 worksheets for them( in ...
A: What I would do in this case is to create a table in a separate sheet relating the model names with ...
|
| CurrencytoWord Conversion | 2/12/2008 |
Q: I put on VB Script to convert figures into words when i put =currencytoword(A1) and in Cell A1 i put ...
A: I suppose that you have imported the VBA code for a function called CurrencyToWord, and probably ...
|
| compilation on single sheet | 2/12/2008 |
Q: Miguel, I have to keep records of 40 models & its spares. I have created 40 worksheets for them( in ...
A: I was going to suggest exactly those two formulas, so I think I would understand better the ...
|
| code that freezes excel | 2/12/2008 |
Q: I tried the code to monitor changes to a worksheet and find that when i use it it freezes my ...
A: Probably you are using this code also on the Summary sheet, so Excel in entering an endless loop, as ...
|
| Sort & Copy the list | 2/12/2008 |
Q: Miguel A small query again. I am having different models(sepatare sheet foe each model) & each ...
A: In this case, I would take a manual approach, with the option of recording it to a macro, with the ...
|
| date change in excel as vales are updated | 2/11/2008 |
Q: I have a workbook that is used to show the availability of product. The values are updated on an ...
A: Yes, you can change the code to something like: Private Sub Worksheet_Change(ByVal Target As Range) ...
|
| date change in excel as vales are updated | 2/11/2008 |
Q: I have a workbook that is used to show the availability of product. The values are updated on an ...
A: You can use the Worksheet_Change even on each of the sheets that you want to monitor, with a code ...
|
| VBA Form writing to multipe spreadsheets in the same workbook | 2/11/2008 |
Q: It is Frank again with a new question. I have created a VBA Form that writes data to a spreadsheet ...
A: My suggestion would be to link the drop down list with the names of the worksheets, and then use the ...
|
| daily working hrs. | 2/11/2008 |
Q: Miguel Zapico I just want to know, how to calculate daily working hours in excel. Eg. In time ...
A: You can either enter each value on an individual cell with time format, and then make the sum of ...
|
| Text at Background | 2/10/2008 |
Q: Its a simple query but unable to solve.How to creat a text at the background of the page. If I am ...
A: I suppose that you are looking for some kind of watermark on the print. If so, that is usually ...
|
| Display cell name | 2/10/2008 |
Q: Your guidence will be helpful to me in this small problem. I have entered a value in a cell and ...
A: One way of doing it is using a custom formula, something like: Function CellName(rngData As Range) ...
|
| unrestricted If function | 2/10/2008 |
Q: I want to know if there is a way to give a condition and an arbitrary state to a cell. For example, ...
A: That is not possible as you are describing it, because a cell can contain a value or a formula, but ...
|
| SUMIF with month and date | 2/8/2008 |
Q: I require output for a particular month of a year. I have entered dates in one column (i.e ...
A: You can use the SUMPRODUCT formula for this, something like: ...
|
| removing specific text boxes | 2/8/2008 |
Q: We have an older spreadsheet that has thousands of un-used text boxes. Is there a way to delete ...
A: Ok, we may try to simplify one of the IF statements with a different comparison, using the ShapeType ...
|
| Difficulty shading between lines in a combo graph | 2/8/2008 |
Q: I am having a very difficult time trying to shade in between two lines on an XY scatter graph. I ...
A: In this case, you may need to craft additional series to draw the areas that you you want to ...
|
| Difficulty shading between lines in a combo graph | 2/8/2008 |
Q: I am having a very difficult time trying to shade in between two lines on an XY scatter graph. I ...
A: This is probably due to the chart type used for the area. If you use stacked area instead of area, ...
|
| removing specific text boxes | 2/7/2008 |
Q: We have an older spreadsheet that has thousands of un-used text boxes. Is there a way to delete ...
A: You use a macro for this. For example, this one will look on the active sheet for all the shapes ...
|
| Auto Weekly Report | 2/7/2008 |
Q: I would like to make my daily entry data (report) to be appear on weekly basis automatically on the ...
A: I suppose that you want one consolidated figure for the week, so the data will be numeric. In this ...
|
| excel based macro question. | 2/7/2008 |
Q: This is murthy. I have a problem in excel 2003. That is a string separated by special character Like ...
A: You can avoid that using the text to columns wizard; in the final step there is a box to enter the ...
|
| Extract formula from a cell | 2/6/2008 |
Q: my ultimate goal is to compare whether two cells have the same formula entered in them. The do NOT ...
A: Ok, in this case, you can use a UDF similar to this: Function ShowFormula(rngCell As Range) ...
|
| Date reminder in Excel | 2/6/2008 |
Q: I wish I could know how to make a date pop up to remind me of a maturity date in Excel please? I am ...
A: In this case, I would use conditional formatting instead of a pop up, as it will require the use of ...
|
| Cancel and X buttons going wrong on my userform | 2/5/2008 |
Q: Miguel I have a module that calls on a userform that I've designed. This then uses these inputs to ...
A: You are right, it looks like the public variable is not taken into account. As an alternative, you ...
|
| vlookup | 2/5/2008 |
Q: I am using a vlookup to return the values of a complete row based on a product code. I am finding ...
A: The formula written as itself is usually due to the format of the cell. If it is formatted as text, ...
|
| vlookup | 2/5/2008 |
Q: I am using a vlookup to return the values of a complete row based on a product code. I am finding ...
A: this helps, I took the wrong assumption. In this case you can continue using the VLOOKUP formula, ...
|
| How to intersect values in two columns | 2/5/2008 |
Q: I am working with Excel. In my sheet i have two columns with some data. Some columns in both fields ...
A: One way of doing this can be with a third column, for example C, using a formula like this on C1: ...
|
| vlookup | 2/4/2008 |
Q: I am using a vlookup to return the values of a complete row based on a product code. I am finding ...
A: The solution will depend on what you want to do with the results and how you want to perform the ...
|
| combo-box | 2/4/2008 |
Q: I am stuck in this problem in Excel. Here's the situation. I have two worksheets... WS#1 and WS#2. ...
A: You can use an array formula to get the information. Provided that your table is on range A1:D10 on ...
|
| excel based macro question. | 2/4/2008 |
Q: This is murthy. I have a problem in excel 2003. That is a string separated by special character Like ...
A: If you can elaborate more on how you get the data to the sheet I may be able to give a concrete ...
|
| Divide Sheet. Macro | 2/4/2008 |
Q: ! I have a question about dividing a sheet onto 2 well thats not the exact... well let me try to ...
A: You could do this with formulas, in the example I have used range B7:F8 for the normal hours and ...
|
| excel based macro question. | 2/3/2008 |
Q: This is murthy. I have a problem in excel 2003. That is a string separated by special character Like ...
A: In this case my suggestion will be to record a macro (menu Tools->Macro->Record new macro) while ...
|
| Transpose Macro | 2/2/2008 |
Q: I'd like to write a macro that can transpose a table of data. I'd like to macro to be able to ...
A: That is more clear, the scenario is good for the first example, and we can get rid of the Selection ...
|
| Transpose Macro | 2/1/2008 |
Q: I'd like to write a macro that can transpose a table of data. I'd like to macro to be able to ...
A: I would need to know what are the criteria for creating the combination of rows/columns, is it based ...
|
| Ranges in IF Statment | 2/1/2008 |
Q: I am trying to place a range in an IF statement. I named my range "first". "first" is located on ...
A: This should normally work, you can check what happens step by step by selecting the cell with the IF ...
|
| combining macros | 1/31/2008 |
Q: I have a list that will pull data from the source and populate to the master. When the source ...
A: You can see what is happening step by step using F8 on the Combinemacro code. This will highlight ...
|
| Formula question | 1/31/2008 |
Q: I need to search a column for a specific value, search another column for the same value, and input ...
A: If the third value is numeric, and there are no row duplications, you can use the SUMPRODUCT ...
|
| Ascending List | 1/31/2008 |
Q: I'm trying to create a giant list in ascending numerical order (1,2,3,4,etc.) Is there a way I can ...
A: One quick way of generating a list like that on a whole column is the following: - Select the whole ...
|
| matching | 1/31/2008 |
Q: Good day. I hope you can help me. I wanted to cut and paste the data from one column to another. ...
A: This code will check for consecutive rows with the same values on columns B and C and move the two ...
|
| CSV file with excel | 1/31/2008 |
Q: im filling the webform by picking data from csv file,capturing error messages from web form and ...
A: It is possible to capture data from the web to an Excel file, but it depends heavily on the web ...
|
| Formula Help | 1/30/2008 |
Q: I’m trying to glean from a row of data the lowest value and am having difficulty with ignoring blank ...
A: In this case you could use the INDEX and MATCH formulas, something like: ...
|
| hyperlink and IF function or Vlookup? | 1/30/2008 |
Q: I tried using =Vlookup(A2,Sheet2!A:F,3,False)to find information on the Sheet2 base on Sheet1, but ...
A: In this case, where you need more than one result, one quick but manual way to get the data is to ...
|
| excel formula for multiple worksheets | 1/29/2008 |
Q: In column $A I have the current date input on each call followed by call information in other ...
A: In this case I would recommend the use of a pivot table. With a pivot table you can have the dollar ...
|
| Filtering Multiple Dated Columns | 1/29/2008 |
Q: Still a bit of a newb to excel. I did read Jan's answer on using the autofilter to find specific ...
A: Sorry, I don't know exactly what answer you are referring to, so my response may not be accurate. ...
|
| Silly question | 1/29/2008 |
Q: I know this is probably really easy, but I am getting complete brain freeze today. I am having a ...
A: I suppose that you are using a formula like: =Sheet1!A1 To bring the name. You can wrap it on an IF ...
|
| Linking to images | 1/29/2008 |
Q: I have a spreadsheet which contains lots of links to various images. The image links which are in ...
A: I couldn't replicate your issue, I tried inserting an hiperlink to a .jpg file, and it opened fine ...
|
| nested functions | 1/29/2008 |
Q: What can be done if more than 7 nested functions is needed? ANSWER: It will depend on what needs to ...
A: They may not be best choice in that case, but maybe they can help to simplify the formula enough to ...
|
| open a file whit vba | 1/29/2008 |
Q: i have to file, file1 and file2, the file1 have a Sub Workbook_Open, and i need in a macro in the ...
A: You can disable the event before you open the file, and enable then after, something like: ...
|
| hyperlink and IF function or Vlookup? | 1/28/2008 |
Q: I tried using =Vlookup(A2,Sheet2!A:F,3,False)to find information on the Sheet2 base on Sheet1, but ...
A: It will depend on the type of information that you want to bring back; from your formula it looks ...
|
| Excel | 1/28/2008 |
Q: How do write mathematical signs on excel? Example square root or sigma sign etc.
A: You can use the Microsoft Equation object to draw those symbols. To do so, go to menu ...
|
| more sheet navigation | 1/28/2008 |
Q: greetings, i saw your responses on this ...
A: In the example, the cell A1 contains the data validation drop down list, the place where the user ...
|
| Recurring date of the 18th | 1/28/2008 |
Q: I need to have a recurring expense in my expense report that is the 18th of every month. Can you ...
A: The exact parameters will depend on your layout, but one formula that can help you in this case is ...
|
| How to hide #value without changing the formula | 1/27/2008 |
Q: I'm using =IF(ISERROR(formula),"",formula) and it's giving what I want: the cell(let's say B1) is ...
A: You could wrap your current IF formula on another IF, checking if A1 is blank. Something like: ...
|
| Excel Formula | 1/25/2008 |
Q: I am working on a formula to create an automated email in Excel with multiple data from a ...
A: You can add the CHAR(10) formula where you want to have the line breaks, see this page for an ...
|
| Excel Formula | 1/25/2008 |
Q: I am trying to display work shifts in Excel. Example if the time is between 7:30 AM to 3:30 PM then ...
A: You can use the VLOOKUP function for this. To do so, create a table similar to this in a separate ...
|
| Formula Help | 1/25/2008 |
Q: I’m trying to glean from a row of data the lowest value and am having difficulty with ignoring blank ...
A: To get the smallest value on a range, you can use the MIN function, something like: =MIN(A1:A5) For ...
|
| Linking numeric values with text in the same cell | 1/25/2008 |
Q: I want to know if there is a way to mix text and numbers in the same cell, where the text is static ...
A: You can use the TEXT to format the numbers, and the concatenation function to glue them with ...
|
| excel question follow-up | 1/24/2008 |
Q: I discovered an error with this calculation. I was looking at future publication dates, when I ...
A: The attachment didn't got published, but I have tried the following variation of the formula, and it ...
|
| Getting a Date returned and not its number, as a result of using an IF statement | 1/24/2008 |
Q: I have this formula which returns a date number =IF(A1>DATEVALUE("12/31/07"),A1,"Attention Needed") ...
A: If you are not planning on using the number for any further calculation, you can use the TEXT ...
|
| Excel Function | 1/24/2008 |
Q: i.e. =(SUM(12 months)/SUM(12 months last year) - 1. I wanted to know why that -1 is included at ...
A: The -1 is because growth is usually presented as a percentage over last year, and it is important if ...
|
| formula to enter the correct date (this past Sunday) | 1/24/2008 |
Q: I really liked your answer to "formula for recurring Thursdays in Excel." How do you create a ...
A: This situation can be achieved with a simpler formula, taking advantage of the Sunday giving a ...
|
| Search a range of cells | 1/23/2008 |
Q: I hope this is an easy question. I have the following formula. ...
A: The problem is that the SEARCH function normally only works with one cell at a time, so in this case ...
|
| Excel formula | 1/23/2008 |
Q: I would like a formula to remove all alpha characters (Upper and lower case) from a alpha-numeric ...
A: This function will work the other way around, checking if the character is a number and keeping it ...
|
| Conditional Cell Formatting | 1/22/2008 |
Q: i am creating a job costing spreadsheet and want to make column B display an employees wage when ...
A: You can use the VLOOKUP function for this. Supposing that the list with employees/wages is on range ...
|
| dynamic named ranges--expand across columns | 1/22/2008 |
Q: Miguel, How can I create a dynamic named range that expands across columns? I found some ...
A: In my experience, the dynamic ranges only work well in graphs when used on each Series definition, ...
|
| dynamic named ranges--expand across columns | 1/22/2008 |
Q: Miguel, How can I create a dynamic named range that expands across columns? I found some ...
A: As far as I can tell, the dynamic named range is correctly defined. To check that, you can use the ...
|
| Excel remove surname from full name | 1/22/2008 |
Q: I have the following fields in excel. -Full name {Name(s) and Surname} -Surname I would like to ...
A: If the full name is on cell A1, and the surname on cell B1, you could use a formula like this ...
|
| daily working hrs. | 1/22/2008 |
Q: Miguel Zapico I just want to know, how to calculate daily working hours in excel. Eg. In time ...
A: You can add directly the daily hours, with the SUM formula, something like: =SUM(C1:C6) This won't ...
|
| Compare 2 Columns for Differences in 2 Different Workbook | 1/21/2008 |
Q: I have 2 column of data(as below) in 2 different workbooks (ex. december list and january list). I ...
A: In this case, you may need an additional column on workbook 2. On that column, you can use a ...
|
| Y axis Autoscale not working - VBA workaround required | 1/21/2008 |
Q: For my y-axis I have my autoscale enabled: With ActiveChart.Axes(xlValue) ' .MinimumScale = ...
A: You can use the MIN and MAX worksheet functions on the code to obtain those values, something like: ...
|
| Date scaling using VBA on an excel chart | 1/20/2008 |
Q: Miguel I've got the following scale coding for my dates on my x axis: With ...
A: In this case, I would experiment first with the different options on the menus, and after finding ...
|
| Dynamic Name for a Chart Series | 1/20/2008 |
Q: Miguel I'm trying to make a series name dynamic. I'm plotting FTSE returns versus date and I'd like ...
A: You are right, you can use the & to concatenate the values. The other thing that you need to change ...
|
| Text Search | 1/18/2008 |
Q: I have two lists of text one bigger than the other and I need to find out how many items in the ...
A: You can use the MATCH formula, something like: =IF(ISNA(MATCH(K33,$A$1:$A$251,0)),"n","y") It also ...
|
| Compare 2 Columns in Excel for Differences | 1/18/2008 |
Q: I have 2 column of data(as below). I want to highlight both cells if the value is difference Sample ...
A: You can do this by using conditional formatting. To do select the full range of data, starting with ...
|
| to run a macro from command line in excel | 1/18/2008 |
Q: how to run a macro from command line in excel without opening excel?
A: You need at least to create an Excel object, I don't know if that will qualify as open it in your ...
|
| Formula for Recurring thursdays in Excel | 1/17/2008 |
Q: I have a newsletter template in Excel which is updated monthly. I have the date automatically ...
A: All the hard coded values are in these formulas: ...
|
| Formula for Recurring thursdays in Excel | 1/17/2008 |
Q: I have a newsletter template in Excel which is updated monthly. I have the date automatically ...
A: You are right, I was so focused on the day part that I forgot the month. The formula is getting ...
|
| Formula for Recurring thursdays in Excel | 1/17/2008 |
Q: I have a newsletter template in Excel which is updated monthly. I have the date automatically ...
A: This is a long formula, but it should do the trick: ...
|
| Simple Stores Inventory in Excel | 1/16/2008 |
Q: How r u doing? I am looking for a stores inventory created using MS - Excel, if u have any such file ...
A: I have not created a file for that purpose, but you can check on the Microsoft Office templates ...
|
| MATCH INDEX Formula | 1/16/2008 |
Q: Its pretty simple but its begining to bug me. What it is, is that i have in A1:A10 Dates which i ...
A: This formula should work even with the gaps: =IF(ISNA(MATCH(C1,Date1st,0)),"Not ...
|
| Tab Key | 1/16/2008 |
Q: I have a spreadsheet with 6 columns. After I finish entering data for each row, I would like the ...
A: One way of doing it can be the following. Select the full six columns where you enter data, go to ...
|
| Matching exact values of column a, range being col b, and value being col c | 1/15/2008 |
Q: I am trying to pull value C. For example if i have Product group 20, Qty 63 it would return value ...
A: I have place the Product Group to look for in cell E2, and the quantity on cell E3, with that this ...
|
| Excel Compare data and format accordingly | 1/15/2008 |
Q: I would like to be able to automatically highlight/colour cells in two separate columns of data - ...
A: One way of doing it can be conditional formatting. Select the values on column A starting with A1, ...
|
| using data in Excel | 1/15/2008 |
Q: I have a spreadsheet with sets of info fields for 23 separate days on one tab. One set for Jan 2, ...
A: It is possible, but the final formula will depend a lot of the current layout that you have on the ...
|
| Excel Ranking | 1/15/2008 |
Q: I am trying to rank a list of 10 numbers and I know how to rank using the RANK function. I have a ...
A: You could use an additional column, with a formula like: =0.5*(COUNTIF($B$1:$B$12,B1)>2) This will ...
|
| Enable Fill Color in Cells | 1/13/2008 |
Q: I am running Excel 2003 on a new HP Pavilion, Windows XP. I cannot get the cell color to change ...
A: Sorry, I was unable to reproduce the error. My guess is that the workbook may be protected, you can ...
|
| excel macos | 1/13/2008 |
Q: have two matrices of one 30X40 and second 1X40.I want to check the common numbers of second matrix ...
A: I don't know exactly how you wanted the layout, so this solution will write the numbers that fit the ...
|
| excel | 1/11/2008 |
Q: i want to return a date for text or numbers entered into a cell ex: if cell "e3" you enter number ...
A: You can use VBA and worksheet events to achieve this. For example: Private Sub ...
|
| Mathematical summation formulas | 1/11/2008 |
Q: How can I get excel to sum from i=1 to i=s-1 the equation ((s-1)*...*(i+1))/a^(s-i-1). I need to ...
A: You can create a custom function in VBA, something like: Function TheEquation(s As Integer, a) ...
|
| Excel Question about reversing columns | 1/11/2008 |
Q: I'm using the indirect function in excel which someone just told me about. It's great... but I have ...
A: In this case I think the easiest thing would be to make INDIRECT refer to the last column. To do ...
|
| inserting an asterisk as a character in a cell | 1/10/2008 |
Q: I have a formula that calculates something. I'd like to put an asterisk next to that output to ...
A: The main problem that you will have with that is that if you actually enter the asterisk on the ...
|
| Enable Automatic Refresh | 1/10/2008 |
Q: Using Excel 2003, I have many workbooks with embedded queries and pivot tables. I need to automate ...
A: I can think on two potential workarounds. - One is to adjust the Startup Prompt in the menu ...
|
| "Enable Automatic Refresh" | 1/10/2008 |
Q: Using Excel 2003, I have many workbooks with embedded queries and pivot tables. I need to automate ...
A: I would recommend to set all the pivot tables and queries to refresh automatically on each opening ...
|
| macro to hide selected rows | 1/9/2008 |
Q: range is from row 6 column Q to row 137 column Q if row has X in column Q, I want to hide that row I ...
A: You can try a macro like this: Sub HideRows() For Each cell In ActiveSheet.Range("Q6:Q137") ...
|
| time stamp changes in excel | 1/8/2008 |
Q: I was reading an earlier post regarding time stamping in excel. I have a similar problem where I ...
A: In your case, maybe the second option, using worksheet events, could fit better. I have modified ...
|
| Excel | 1/8/2008 |
Q: I am having trouble figuring the formula to show Markup percentage not just percentage of items. We ...
A: Using this article as a reference: http://en.wikipedia.org/wiki/Markup_(business) You can craft the ...
|
| Excel | 1/8/2008 |
Q: my question is like this, i have a list of people that owe me money so in section C their names will ...
A: Probably what you have used is the autofilter (menu Data->Filter->Autofilter), that is useful for ...
|
| macro that produces daily dates between a start and end date | 1/7/2008 |
Q: Miguel Is it possible to write a VBA code that produces daily dates in a column between a start and ...
A: This macro may give you some ideas: Sub GenerateDates() Dim dtStart, dtEnd As Date Dim i As ...
|
| Excel macro regarding decimal addition | 1/7/2008 |
Q: Dim Broker_Tot_Storage, Broker_Usable_Data_Storage, Broker_Date_Rep_Storage, Broker_All_File_Sys, ...
A: If you want to avoid this behavior, you can remove the type definition while you are defining the ...
|
| Help on Macro | 1/4/2008 |
Q: I want to ask you a question, I think it is easy, but I had not found the answer by myself... please ...
A: I am using Excel 2003, but the macro should work anyway: Sub WriteVariables() Dim i, j, k As ...
|
| Conditional Formatting | 1/4/2008 |
Q: I am having trouble populating a formula dealing with dates. I have to keep track of training ...
A: In that case you may substitute the TODAY() formula for a cell reference, or even the DATE formula, ...
|
| Conditional Formatting | 1/4/2008 |
Q: I am having trouble populating a formula dealing with dates. I have to keep track of training ...
A: I suppose that the value on the cell is the expiration date, and you want to compare it with the ...
|
| Excel Question - IF function | 1/4/2008 |
Q: I have run into a major brain fart in Excel. I have created a playoff bracket for fun with some of ...
A: Nice worksheet! You may try a formula like this on C15: =IF(C10="(6) Washington","(1) Dallas","(2) ...
|
| email hyperlink | 1/4/2008 |
Q: I need to add a email hyperlink, but I don't want to use outlook. I want to know if I could use ...
A: This is harder than it looks, as the email hyperlink that you can create in Excel has a ...
|
| Autocomplete...sorta | 1/3/2008 |
Q: The lawfirm I work at enters client info (name, address, etc) into an excel document to merge into a ...
A: That will be more complicated, these are two ways that you can explore to achieve it. 1. Using ...
|
| Excel macros | 1/3/2008 |
Q: I have two matrices one is 30X50 and second 40X35 filled with numbers and texts. I want a macro ...
A: This code takes the matrices from sheet1 (30x50) and sheet2 (40x35), and print the results on the ...
|
| Autocomplete...sorta | 1/2/2008 |
Q: The lawfirm I work at enters client info (name, address, etc) into an excel document to merge into a ...
A: You can use the VLOOKUP formula for this. First, you need to craft a table with the information ...
|
| simple Excel question | 1/2/2008 |
Q: Miguel Zapico, I'm working with Excel long time and know lots of thing, but I don’t know some ...
A: You can use the OFFSET formula, something like =OFFSET(A1,COUNT(A:A)-1,0) It assumes that the first ...
|
| Hide rows based on another cells data validation | 12/21/2007 |
Q: I trying to hide 6 rows (rows 2-7) below a value in cell E1. However i need to replecate this ...
A: I don't know which type of validation you want to do, this example will hide the rows if the value ...
|
| Combining Worksheets into a Master Worksheet | 12/20/2007 |
Q: I have a workbook with 24 worksheets. Sheet1 being the Master, Sheet2 being the visible sheet when ...
A: For this type of transformations, you may check the following web page: ...
|
| Excel non-matching text | 12/20/2007 |
Q: I have an excel worksheet with 4 columns, First Name and Last name of the same students repeated ...
A: If they are ordered, meaning that the names on each row should match, you could two additional ...
|
| Excel macro | 12/19/2007 |
Q: YrPPWk,Sub Unit,Last Name,FI,MI,D/A,Hours,Day,Qty,, 2007262,SU 187,JOHNS,V,R,169,WK,Sat,8,, ...
A: I wouldn't do this with a macro, but use a pivot table instead. You can use a calculated item to ...
|
| Excel Sum If | 12/18/2007 |
Q: I have a spreadsheet that each line item has a value ($ amount) in column L and a status in Column ...
A: There is a function call SUMIF that can do what you require. For example, to get the sum of the ...
|
| Split Excel Colomn | 12/17/2007 |
Q: Could u pls help me how to split a single colomn in to multiple colomn . ANSWER: You can select the ...
A: I suppose that you are asking for the reverse of the split process, for that, you can use the ...
|
| Excel 2003: copy sheet to another workbook, updating links | 12/17/2007 |
Q: I have 2 copies of the same workbook, Apples and Bananas, with tabs of the same titles (Tab1, Tab2 ...
A: One way of doing it is to use the menu Edit->Replace, and replace the name of the workbook ...
|
| Split Excel Colomn | 12/17/2007 |
Q: Could u pls help me how to split a single colomn in to multiple colomn .
A: You can select the column, and go to menu Data->Text to Columns. This will launch a wizard, where ...
|
| vlookup | 12/14/2007 |
Q: I have a large file containing the names of pdf files I have in a folder. My task is to create ...
A: If you know which folder are the files, you can use the HYPERLINK formula for that, something like: ...
|
| SUMIF on non-adjacent cells | 12/13/2007 |
Q: I need a SUMIF function on non-adjacent cells but I'm not sure if I need a multiple condition ...
A: With your layout, a formula like this should give the expected result: J2: ...
|
| conditional statements and values | 12/13/2007 |
Q: my problem is that I'm running the formula =if(AA4:AA894>=13,1,2) and there are a few cells in ...
A: As fas as I know, because one of the factors in the comparison is a text, Excel is performing an ...
|
| New to Macros | 12/12/2007 |
Q: ! I consider myself to have intermediate excel skills, so you may have to work with me a little ...
A: I will try to outline the process that I would follow, it will be up to you to make the current ...
|
| excel with acrobate | 12/12/2007 |
Q: is there a way to transform an excel file to an acrobate reader file(protected by a password and ...
A: You can purchase the full version of Adobe Acrobat, and it will integrate with Excel and allow you ...
|
| Adding dashes to SSN | 12/12/2007 |
Q: I am trying to create a macro to add the dashes to a ssn. Ex. 123456789 to 123-45-6789. All in ...
A: You are really close, substitute the row where the actual transformation is done with something ...
|
| Input form | 12/11/2007 |
Q: I am VBA challenged so please forgive. I need code for an input form drop down list I have the look ...
A: I am supposing that you are creating a form in the Visual Basic Editor, and you want to feed a combo ...
|
| SUMIF questions | 12/11/2007 |
Q: I am having some problems making a formula. what i need is a formula that will pull out totals for a ...
A: You can use the SUMPRODUCT formula to achieve multiple conditions, something like: ...
|
| about excel2003 | 12/11/2007 |
Q: My colleague was a bit confused yesterday, so I ask you on her behalf and I hope in a better ...
A: I also took the question too literally, that was also an interesting challenge :) Regarding the ...
|
| SumIF Formular | 12/10/2007 |
Q: I have a table of data and I would like help in writing a formular. The table looks like this: ...
A: Unfortunately, to do what you want to do cannot be achieved with a simple formula and the current ...
|
| SumIF Formular | 12/9/2007 |
Q: I have a table of data and I would like help in writing a formular. The table looks like this: ...
A: I have tried to come as precise as possible, so the formula is a bit more complex that I usually ...
|
| Excel macros | 12/8/2007 |
Q: I have matrix of 600X450 filled with 5 digit nos.I club 3 consecutive cells of each row in all 450 ...
A: OK, if I have understood correctly, this code should use the ID instead of the row number: Sub ...
|
| Sumif Error | 12/7/2007 |
Q: I am using the following formula SUMIF ('[Data Prod Analysis - Feb2007.xls]New ...
A: Sorry, my fault, the first post was misleading, SUMIF cannot be updated with closed workbooks. ...
|
| Excel macros | 12/7/2007 |
Q: I have matrix of 600X450 filled with 5 digit nos.I club 3 consecutive cells of each row in all 450 ...
A: I see that you are using Excel 2007 (450 columns don't fit on 2003), so I am not completely sure if ...
|
| Sumif Error | 12/6/2007 |
Q: I am using the following formula SUMIF ('[Data Prod Analysis - Feb2007.xls]New ...
A: The formula can work in closed workbooks, but it needs to referenciate the full path. In your case, ...
|
| Sorting by blocks | 12/6/2007 |
Q: In a large spread sheet I would like to lock groups of, say, 10 rows into blocks, then sort the ...
A: One way of achieving it can be the following: - Use an additional column, and fill it with the same ...
|
| sum function | 12/6/2007 |
Q: I have been trying endlessly to get a simple sum function to work. In my spreadsheet there are three ...
A: I am not sure on what can be the cause of that behavior, but you can check a few things: - Check the ...
|
| Shading between the lines | 12/6/2007 |
Q: I am decently knowledgeable when it comes to Microsoft Excel, but I have had the hardest time ...
A: The question is really good, and in fact I haven't been able to come with a complete solution. I ...
|
| Comparing Excel to Text File | 12/5/2007 |
Q: I have a text file with numbers in the the first column. I also have a excel file with similar type ...
A: Trying to modify text files in place is much harder than it looks, and the alternative of creating a ...
|
| Excel 2003 Plotting | 12/5/2007 |
Q: I am trying to plot real-time data from an excel spreadsheet in to a chart, but the chart is ...
A: You can use conditional formatting to hide the errors, using a formula similar to this in the menu ...
|
| Macro Error in Command Button (Run time error '1004') | 12/5/2007 |
Q: I created one macro which is working fine WHEN I USING KEY BOARD SHORTCUT KEY (Ex.Ctrl+M). But, the ...
A: You may try by explicit the sheet where you want to select the range. For example, change the line: ...
|
| Excel 2003 Plotting | 12/4/2007 |
Q: I am trying to plot real-time data from an excel spreadsheet in to a chart, but the chart is ...
A: If you don't want to plot zeros, you can change the formula to: ...
|
| Comparing Excel to Text File | 12/4/2007 |
Q: I have a text file with numbers in the the first column. I also have a excel file with similar type ...
A: I would need a few more details to make a functional code, but my suggested process would be: - Open ...
|
| Copy active cell | 12/4/2007 |
Q: Is there a way I can copy into cell "A1" from an active cell just by cliking on it. I have a colume ...
A: You have to use macros, and you can use the Worksheet_SelectionChange event to achieve this. To do ...
|
| Excel question | 12/3/2007 |
Q: Is there an easy means of inverting a column of data without sorting? I've searched for a command ...
A: You may use an additional column, with a formula similar to: =INDIRECT("A" & COUNTA(A:A)-ROW()+1) In ...
|
| Stocktake workbook | 12/2/2007 |
Q: I have a 'stock take' spreadsheet with no formulas, as an example i have a range of different ...
A: I am not completely sure on what you try to achieve, but here is the overview of two techniques that ...
|
| 1. Comparing and editing 2 spreadsheets - 2. Exporting MS Outlook Data to Excel | 12/1/2007 |
Q: Question#1. Is there a procedure, Excel formula or software to compare 2 spreadsheets, identify ...
A: To your first question, there are some software developed available for what you need, for example: ...
|
| Excel or Access | 12/1/2007 |
Q: i would like to create a student database (personal detail & results) for easy reference and ...
A: If the data can be represented in a single table, Excel can be a easier solution, but it depends on ...
|
| Paste into merged cell | 11/29/2007 |
Q: I am trying to copy text from a Word document into an Excel merged cell and get an error that the ...
A: I find merged cells very inconvenient, and try to avoid them except for the presentation of data; ...
|
| moving data between sheets in 1 workbook | 11/28/2007 |
Q: I have one workbok and three sheets in it. I would like to move whole column (which is built from ...
A: If you just want the numbers and formats, you can do the following: - Select the column that you ...
|
| Arrays with dates | 11/27/2007 |
Q: I need a count of different criteria. I have a list of claims, but need a count of all claims with ...
A: You can use SUMPRODUCT, that is not an array formula by itself, but uses arrays as parameters. In ...
|
| how make a list | 11/27/2007 |
Q: How can i take this list and put the email in column a and the name in column b in excel? ...
A: You can use the text to columns feature to help you here. Step by step it would be: 1. Paste the ...
|
| Excel formula | 11/27/2007 |
Q: When using the fill down option, I need to obtain data from another worksheet, but only from every ...
A: You can use INDIRECT for that, and craft the range using the ROW function. Something like: ...
|
| dynamic ? | 11/26/2007 |
Q: I wish you a nice day. I ve tried on line help without good results.. May I ask you how to built a ...
A: It looks like the final dot got included in the address, as this input box is plain text I have ...
|
| blank cell getting ignored | 11/21/2007 |
Q: I am copying certain data from one workbook to another workbook and appending it.my problem is when ...
A: The reason may be on the offset that you do while pasting overwrites the offset with the active ...
|
| Excel adding 30 days to date | 11/20/2007 |
Q: I have a cell column that is formatted as date mm/dd/yyyy hh:mmAM/PM. I need to have another row of ...
A: You have got the right formula, the problem may be that the date is not a date, but a text. There ...
|
| excel | 11/20/2007 |
Q: i need to gerenate a series of dates that are 5 days apart. Except, if it would fall out on Tuesday, ...
A: Supposing that your starting date is on cell A1, you could use a formula like this on cell A2: ...
|
| Excel Formula using solver | 11/19/2007 |
Q: I have figured out the constraints, but need the formula in a particular cell to complete this ...
A: I have uploaded one workbook with a solution based on solver, with two pages, the one with the ...
|
| Excel Formula Help | 11/19/2007 |
Q: Wondering if possible... I have a spreadsheeet with one column showing dates. For example, one of ...
A: You can do it using conditional formatting. Select all the cells that you want to check, go to menu ...
|
| Excel Formula using solver | 11/19/2007 |
Q: I have figured out the constraints, but need the formula in a particular cell to complete this ...
A: I suppose that the central formula of the solver will be the total shipping costs, and that you will ...
|
| Macro writing | 11/19/2007 |
Q: My problem is: I have three columns. Col-A contains some reference value for Col.-B. Col-B has ...
A: I am not sure how column C affects the process, following the explanation and the example I couldn't ...
|
| excel | 11/17/2007 |
Q: if i put some text in A1 and some other text in A2. In A3 write a fromula which compares A1 and A2: ...
A: This formula should return the values based on the conditions, there is a mix of IF formulas with ...
|
| V look up and H Look Up | 11/16/2007 |
Q: Can you tell me what results do the foll formulae give: Thanks ...
A: If you want to see what the formulas do, the best way is to use the menu Tools->Formula ...
|
| I'm stumped !! | 11/15/2007 |
Q: ....here's my problem that couldn't be solved by another AllExpert....hope you can help ! > Thanks ...
A: You can use goal seek for this. For example, if your 5 numbers are in range A1:A5, enter the ...
|
| Drop down selection and a date | 11/15/2007 |
Q: Is it possible for me to select a selection from a drop down menu and when I select this, a date on ...
A: Sorry, I didn't understood correctly. In this case, you can use macros to achieve it, with a code ...
|
| Drop down selection and a date | 11/15/2007 |
Q: Is it possible for me to select a selection from a drop down menu and when I select this, a date on ...
A: You can do that by using names. Select the destination cell on the other worksheet, go to menu ...
|
| Filter Data | 11/15/2007 |
Q: I was hoping you could help me. I have dates which run from 18/04/2005-31/05/2006...What I would ...
A: Your approach covers part of the solution that I am proposing, but you may want to use an additional ...
|
| IF Statement with two nested VLOOKUP | 11/15/2007 |
Q: I have to write a formula and I'm stuck. I have two columns, and need to look up to cells to ...
A: In this case, you can use a logical comparison and add the result to the column parameter of the ...
|
| attitudes chart | 11/14/2007 |
Q: We're producing a report on peoples perceptions towards the environment. I need to make a chart to ...
A: First, I think that the graph choice is pretty good, it will allow people to picture the tendencies ...
|
| formula for counting numbers repeated in a range of cells | 11/14/2007 |
Q: I have random numbers in a column A, I HAVE selected cells A1 TO A16 and now i want to display the ...
A: In that case, I am afraid that it cannot be done in one cell. If possible, what I would suggest is ...
|
| excel 2003 auto color change | 11/14/2007 |
Q: few of our spreadsheets change color automatically when reopened. The original colors are different ...
A: Probably it is due to changes on the standard color palette. You can modify it using menu ...
|
| Sharing of an ADD-IN | 11/13/2007 |
Q: I am working on an Excel document in which I have added a few Addins (Including the CALENDER addin) ...
A: You can use the Workbook_open event to activate the add-in, something like: Private Sub ...
|
| Various formulas for Pay Rates | 11/13/2007 |
Q: Other than finding the sum for total hours worked, total cost per pay rate, etc. what other formulas ...
A: Sorry, I don't know what you are asking for. If you need formulas, I need more data to be able to ...
|
| formula for counting numbers repeated in a range of cells | 11/13/2007 |
Q: I have random numbers in a column A, I HAVE selected cells A1 TO A16 and now i want to display the ...
A: In this solution, in order to simplify the formulas, I have put the first most repeated number in ...
|
| "Contains" formula | 11/13/2007 |
Q: Is there a simply way to use the "contains" formula in excel. All I want to do is to return the ...
A: For one cell, you can use the SEARCH formula, that returns the position on a string where you can ...
|
| graph | 11/8/2007 |
Q: please explained stacked column?
A: It is a type of column graph, that represent each element in a single column, composed by the sum ...
|
| Quantity Sum by Item Description (VLOOKUP won't do) | 11/7/2007 |
Q: Here is my situation. For simplification, I have two columns. Column A, has Items and Column B ...
A: You could use the SUMPRODUCT formula, combined with the SEARCH and some logical ones. For example: ...
|
| Navigating sheets with dropdown lists | 11/7/2007 |
Q: your suggestion for the 'Worksheet_change' code works perfectly if I have a single validation list. ...
A: You don't need an additional sub, you can modify the current If function adding additional checks, ...
|
| Finding width of DropDown item | 11/7/2007 |
Q: How to find width of item of MSForms Dropdown box.Please help me. ANSWER: I am not sure what you ...
A: You can use the shape properties of the combo box to resize the width of it, something like: Sub ...
|
| VBScript | 11/6/2007 |
Q: How do we compare rows from 2 excel worksheets and put results in another worksheetusing vbscript ?
A: It depends on what you want to compare inside the row, one way of doing it is the following: - Parse ...
|
| Excel comments | 11/6/2007 |
Q: When I add a comment to an Excel cell and later reopen that spreadsheet - the comment moves and ...
A: I am not able to replicate the error, and I don't know what may cause it, but in fact I rarely show ...
|
| Finding width of DropDown item | 11/6/2007 |
Q: How to find width of item of MSForms Dropdown box.Please help me.
A: I am not sure what you are trying to achieve. The width of the control can be shown by right ...
|
| Read from excel that is converted from html | 11/6/2007 |
Q: I Saved an html page which had tables and textboxes in it, I opened it in excel. It shows me the ...
A: What I use to do when importing formatted data, like html, is to stripe it of all the format, as it ...
|
| Combin feature | 11/5/2007 |
Q: I have a list of X numbers: eg. 1 2 3 4 5 I would like to get all the possible combinations from ...
A: Ok, I haven't been able to come with a full code that will accept any amount of numbers, but this ...
|
| Combin feature | 11/3/2007 |
Q: I have a list of X numbers: eg. 1 2 3 4 5 I would like to get all the possible combinations from ...
A: I had to use macros for this, the following code will generate the combinations, in a different ...
|
| Date generating | 11/3/2007 |
Q: I have a workfolder in Excel which basically has seven pages, one for every day of the week. Now I ...
A: If I have understood correctly, a script like this should do what you require: Sub PrintDates() ...
|
| Request | 11/3/2007 |
Q: I have a question about excel, I have two folders, Folder A and Folder B. Each folder has an Excel ...
A: In this scenario, I would suggest to use a pivot table (menu Data->Pivot table and pivot chart ...
|
| comparing columns and VLOOKUPS | 11/2/2007 |
Q: I have 2 lists of data. One list is a "master" list, and the other list is a weekly export from our ...
A: In this case, VLOOKUP is a good option. In fact, it doesn't compare two sets of data, but just one ...
|
| Dates that change upon pasting or coping | 11/2/2007 |
Q: I have been working with a spreadsheet and the dates change if the sheet is saved as copy to new ...
A: The workbook has the 1904 date system active, that is the reason why it changes when copied to a new ...
|
| Date generating | 11/1/2007 |
Q: I have a workfolder in Excel which basically has seven pages, one for every day of the week. Now I ...
A: You will need macros to do that, I could help with the code if you can post what is the logic that ...
|
| Inserting the value of a cell which is text into a formula | 10/31/2007 |
Q: I have found some VBA code on the net which prints out the name of all my work sheets into the first ...
A: You don't need to type it every time, Excel has some nice features that will do the work for you. ...
|
| Infinity | 10/31/2007 |
Q: Is there a function whereby you can include infinity in to you excel spreadsheets without getting a ...
A: It depends on what you want to do with it. If you want to use it in other formulas, it is not ...
|
| Excel plotting | 10/30/2007 |
Q: Miguel, Welcome back from vacation. I posed the below query to two allexperts associates who were ...
A: In that case, I would use the OFFSET formula, combined with the COLUMN one, something like: ...
|
| Excel plotting | 10/30/2007 |
Q: Miguel, Welcome back from vacation. I posed the below query to two allexperts associates who were ...
A: The plus sign will not work in this case, but you can have multiple ranges in the values section by ...
|
| Inserting the value of a cell which is text into a formula | 10/30/2007 |
Q: I have found some VBA code on the net which prints out the name of all my work sheets into the first ...
A: You can use the INDIRECT formula for this scenario. If the column with the sheet names is column A, ...
|
| Give excel sheet a FORM look | 10/19/2007 |
Q: I was just wondering if you could help me in one more thing. My excel sheet has about 10 columns of ...
A: There are things that you want the worksheet to do that are not possible without using VBA. You ...
|
| Matching two fields in excel then copying the data over | 10/18/2007 |
Q: I have a query regarding Excel Macros. I have some knowledge but I am struggling with this. I have ...
A: My suggestion would be to use the VLOOKUP formula instead of macros, something like: ...
|
| Growth Formula | 10/17/2007 |
Q: I am trying to find a formula which will work out the growth, as a percentage, of specific data ...
A: Usually you will need 24 months of data to measure a growth over a 12 month period. With that, you ...
|
| Worksheet comparison | 10/17/2007 |
Q: Miguel, I found your VBA code on here for the "SearchNonPresent". This works great for finding ...
A: This code doesn't present exactly what you are asking, but it will search which elements on sheet1 ...
|
| Lookup with multiple conditions | 10/17/2007 |
Q: I pulling my hair out!! I am trying to match incoming calls to the times that adverts were played on ...
A: This may be a little too much, but using the same formula inside an INDEX one, that may do the ...
|
| Conditional Formatting | 10/16/2007 |
Q: Let's say I have a worksheet with 12 columns. I would like for the cells in column D to be shaded a ...
A: If the values are going to be different, I wouldn't hard code them, but hide them on the worksheet ...
|
| Lookup with multiple conditions | 10/16/2007 |
Q: I pulling my hair out!! I am trying to match incoming calls to the times that adverts were played on ...
A: In this case, because the radio stations are numbers, you could use the SUMPRODUCT formula. With ...
|
| find and replace | 10/15/2007 |
Q: I've an excel list of last and first names separated by comma only. Example Joe,Smith and I would ...
A: Excel has a feature that can help in this case, select the column with the data, go to menu ...
|
| Excel | 10/15/2007 |
Q: I use an 'X' in the column to state whether it is awaiting spares or awaiting labour etc. However I ...
A: This is really strange, as the formula you are using should bring the value that is shown in the ...
|
| Complicated VBA program | 10/12/2007 |
Q: I have a spread sheet of water level in a tank over a period of time. As the tank empties I am able ...
A: You can try this macro, that should fit with the requirements. If that is not the case, please let ...
|
| need help | 10/12/2007 |
Q: i have two files one with all of our customers over the past 5years so it's quite large but i have ...
A: For the formulas below, I have put the value (150) in cell A1, and the different percentages in ...
|
| Sinchring two sheets | 10/11/2007 |
Q: I have a problem and hope you can help me... I have two sheets, both of them have a common column ...
A: In this case, I would propose doing it with a macro, that checks which names are shown in sheet1, ...
|
| growth formula | 10/11/2007 |
Q: cell A1 = 10% (this represents a growth rate) cell A2 = 100 ( a year zero dollar figure) Cell A3 = ...
A: The second formula (=-FV(A1,A3,0,A2)-A2) just does that. It shows the future value based on the ...
|
| Excel Drop Down Menus | 10/11/2007 |
Q: I have created a two page document in Excell. On page #1 I have successfully created a drop down ...
A: It is possible, you need to use Excel names. To do so, select the range with the data on page #1, ...
|
| growth formula | 10/10/2007 |
Q: cell A1 = 10% (this represents a growth rate) cell A2 = 100 ( a year zero dollar figure) Cell A3 = ...
A: You can use the FV to return the future value of the investment, and if you only want the growth, ...
|
| Column in one sheet to rows in another | 10/10/2007 |
Q: I'm setting up a database with two worksheets. The first worksheet is a list of fields in column B ...
A: Looking at the code, I would suggest a different approach. Substitute the line Range("A5").Select ...
|
| Column in one sheet to rows in another | 10/10/2007 |
Q: I'm setting up a database with two worksheets. The first worksheet is a list of fields in column B ...
A: This is one way of returning the first unoccupied row on Sheet3. myRow = ...
|
| Excel Compare Compare - Like Values | 10/9/2007 |
Q: Miguel, the VB script you have me works wonderful. I am grateful. Sub SearchNonPresent() Dim ...
A: If the only condition is that the value on column A is the same, you can change this line: If ...
|
| Count number of matching entries in range with range | 10/9/2007 |
Q: Greets, I have a spreadsheet that tracks compromised hosts around the world. On the daily ...
A: In this case, I would add a column in tab 1, and bring the continent to the daily table. For ...
|
| More than one condition in a formula | 10/9/2007 |
Q: Good Day! Heres my question. In Cell L2, I have a drop down list of the following items (please ...
A: This formula should do what you expect: OR(AND(L2="SECURED",COUNTA(E2:J2)<6),AND(L2="LOST ...
|
| Excel subtract # workdays not present | 10/8/2007 |
Q: I have a table - sample shown below: Date month# miles Monday, October ...
A: In this case I would go back to the SUMPRODUCT formula, adding this time the WEEKDAY formula to ...
|
| Vlookup Function | 10/8/2007 |
Q: I'm new to the Vlookup function so I'm not sure if I can use it for my current task. I have two ...
A: The function VLOOKUP won't help too much in this case, it would be better suited to return the ...
|
| Excel 2000 | 10/8/2007 |
Q: I am trying to create a macro that will (among other things) navigate to a file directory, then ...
A: I am not sure if this will fit with what you would like to do later, but at least this code may give ...
|
| Excel auto fill colors | 10/7/2007 |
Q: I am trying to semi-automate a risk assessment spreadsheet used by US Army aviation units. The ...
A: You are right, there is not enough information to give a detailed answer, but at least I will try to ...
|
| Comparing Columns | 10/5/2007 |
Q: I saw one of your responses. Works great for me, but I'd like to expand on the solution. I have ...
A: This is one way of doing it, copying and pasting the entire row. It is not too elegant, but it ...
|
| Comparing Columns | 10/5/2007 |
Q: I saw one of your responses. Works great for me, but I'd like to expand on the solution. I have ...
A: If I have understood correctly, you want to extract the records on the column A of sheet2 that are ...
|
| year-to-date comparisons | 10/4/2007 |
Q: I have a table - sample shown below: Date month# miles Monday, October ...
A: I am happy to hear that it worked! Regarding the follow up, you are very close. Instead of adding a ...
|
| Sum total when a condition in one column permits the counting of another column | 10/4/2007 |
Q: I'm looking for a sum number when a condition in one column must be met before the other column is ...
A: This one should work: =SUMPRODUCT(--('Updated Contract Register'!D4:D74="01. Human ...
|
| Sum total when a condition in one column permits the counting of another column | 10/4/2007 |
Q: I'm looking for a sum number when a condition in one column must be met before the other column is ...
A: The SUMPRODUCT formula should do the trick. The requisite is that all the arrays involved has to ...
|
| year-to-date comparisons | 10/3/2007 |
Q: I have a table - sample shown below: Date month# miles Monday, October ...
A: For the first part, I am supposing that the format of the data is Date, not text, so you can use the ...
|
| too many follow ups | 10/2/2007 |
Q: Ok, for example, Name 2 weeks ago Last week This week % increase XXXXX 4 11 19 ...
A: No, for that one you can use the formula on the first post, but instead of using B2, using C2. ...
|
| percentage increase and decrease | 10/2/2007 |
Q: I would like to know the formula used to calculate the % increase and decrease over a range of ...
A: Ok, if I have understood correctly, you would like to know the increase between the sum of all the ...
|
| percentage increase and decrease | 10/2/2007 |
Q: I would like to know the formula used to calculate the % increase and decrease over a range of ...
A: One way of measuring the % increase is to take the difference between the two numbers, and divide it ...
|
| Excel 2003 Error | 9/30/2007 |
Q: Can anyone please help me? I'm trying to run a excel spreadsheet with vba to connect to oracle ...
A: It doesn't look like the error is on Excel or XP, but on the ODBC configuration to Oracle on the ...
|
| sumproduct over multiple worksheets | 9/28/2007 |
Q: Miguel I am trying to use the sumproduct formula to add time for several different people across ...
A: In this case I would recommend using a pivot table based on Multiple Consolidation Ranges. If it is ...
|
| nesting vlookup or something comparable | 9/27/2007 |
Q: I have a table that is an inventory list for several stores. Column A is the boutique number, Column ...
A: Even if doesn't seem adequate, the SUMPRODUCT formula can fit on this case. You can use it in a way ...
|
| Excell Offset/Match function | 9/27/2007 |
Q: I currently working on a small excel "search" function. I have worksheet of data that is categorized ...
A: Can you put one example of a link? The hyperlink formula works easily with web addresses, but if ...
|
| countif function | 9/26/2007 |
Q: I have ~40 names down column A and ~30 names across row 1. The 30 names across the top have given a ...
A: You can use the MATCH function to find the name, and the OFFSET function to set the range to count ...
|
| Excell Offset/Match function | 9/26/2007 |
Q: I currently working on a small excel "search" function. I have worksheet of data that is categorized ...
A: As you know the column where the hyperlink data is, you can use the HYPERLINK formula on top of the ...
|
| sum of multiple cell | 9/19/2007 |
Q: how can i add/sum sheet1 of b12 cell and sheet3 of d8 cell
A: You can put the name of the sheet before the reference, separated by a exclamation sign. Something ...
|
| textbox highlight | 9/18/2007 |
Q: Miguel: This seems awfully basic, but I can't find it anywhere. When I open a form which has data ...
A: You can use a piece of code like this to force a selection: With TextBox1 .SelStart = 0 ...
|
| Max "If" Function | 9/18/2007 |
Q: I want to show the maximum number that appears in column Z if a certain criteria is met in column c ...
A: You can use the MAX function as an array formula, for example, type the following formula: ...
|
| Please help | 9/18/2007 |
Q: I will try to make this simple. I want to create a part of an Excel Spreadsheet that allows me to ...
A: I am not sure what can be wrong, probably it is my assumption. I have assumed that you wanted the ...
|
| Please help | 9/17/2007 |
Q: I will try to make this simple. I want to create a part of an Excel Spreadsheet that allows me to ...
A: In the following formulas, I will assume that the start time is on cell A1, and the end time on cell ...
|
| skipping rows | 9/16/2007 |
Q: I have a small question, i have 2 work sheets and the first contains a whole set of data, in the 2nd ...
A: I don't know what you mean by skipping rows, do you need to return the position of the match or the ...
|
| VLOOKUP return | 9/15/2007 |
Q: In a spreadsheet I inherited the following VLOOKUP formula is used: ...
A: You may try copying and pasting, not cutting. The cut operation normally carries the references ...
|
| Assigning Values | 9/14/2007 |
Q: In Excel 2003...I have a column which was values of "yes" and "no" in it. How can I assign numeric ...
A: The way that I usually do this is by creating a table with the values assignment, with the value to ...
|
| Assigning Values | 9/14/2007 |
Q: In Excel 2003...I have a column which was values of "yes" and "no" in it. How can I assign numeric ...
A: One way of doing it is to have an additional column with a formula like this: =IF(A1="yes",1,2) This ...
|
| Selecting a range dynamically | 9/14/2007 |
Q: my excel sheet has many columns in which i want to select a column where one of its row value equals ...
A: One way of doing it is by cutting and inserting the full column, something like: Sub MoveColumns() ...
|
| vba | 9/14/2007 |
Q: Miguel: I have 6 digit ID numbers in col A starting in row 2. I would like vba that would hide all ...
A: Something really similar to what you need, except for the part of the selected cell, can be achieved ...
|
| columun format | 9/13/2007 |
Q: I have workbook that contain few work sheets, one of these work sheets called TOTAL. I set up a ...
A: One way of achieving it is to clear first the range, and then run the macro that you are currently ...
|
| Lookup Question | 9/12/2007 |
Q: I have a workbook with multiple worksheets all formatted the same. I am looking for a way to search ...
A: I think that you will need to use a macro. The following one can give you some ideas, it will ...
|
| arranging data | 9/12/2007 |
Q: I have two spreadsheets that are 365*48 matrixs. In the 3rd sheet I want to put spreadsheet 1 as a ...
A: You can try this macro, it should |
