You are here:
| Subject | Date Asked |
| Use of Macro in Excel | 11/11/2009 |
| Q: Mt questing related to use of Macro. How can I use Macro in Excel? For what kind of function I can ... A: Umesh, In general, Macros are used to replace operations which can only be done by using the ... | |
| How do I make IF statement is DATE Tue, Fri? | 11/10/2009 |
| Q: Only certain days are ok for training. I have a date in A1. If Day of date is TUE, OK, otherwise ... A: Andy, You should learn to use the excellent Excel function reference to answer this question. ... | |
| Copy values from one sheet to the other | 11/9/2009 |
| Q: I am working with two excel sheets W1 amd W2. I have some charecters in W1 and corresponding values ... A: Terrance, You will use the VLOOKUP function to compare the characters (hopefully you mean WORDS) ... | |
| Excel formula needed | 11/7/2009 |
| Q: I looking for an Excel formula to perform the following: Objective: The spreadsheet will compute ... A: Mark, Your problem is most likely more complex than a simple formula, especially given your ... | |
| Finding a value in a column | 11/6/2009 |
| Q: I have a column of 366 IF statements designed to give me different hyperlinks on different dates in ... A: Andy, Take a look at MATCH and INDEX. MATCH() can look down the column and return the row ... | |
| Filling blank cells according to specified weighting | 11/5/2009 |
| Q: I was hoping you could help. I am looking for a formula that will fill blank ‘result’ cells with the ... A: Rob, I have the impression that once you have this column of heads and tails, you are going to ... | |
| Excel totaling catorgories | 11/3/2009 |
| Q: Bill, Thanks for taking my question. I have sheet names "Spendings" and I would like to have ... A: Kevin, You can use the SUMIF function to do exactly this. I shouldn't have bother to ask about ... | |
| Conditional Format | 11/2/2009 |
| Q: "I am trying to do a conditional format on 1 cell if another cell is between 1 and 99. I think I ... A: Len, Its easy to do this with Excel 2007, as well as with 2003. Select the cells you want ... | |
| Excel Counting the max amount of consecutive positive numbers | 10/30/2009 |
| Q: I have one column in an Excel spreadsheet and would like to count the highest maximum amount of ... A: Ed, You need to add a column of Logical Equations next to your column of numbers. each logical ... | |
| use conditional formatting to track due dates | 10/28/2009 |
| Q: I am using Excell 2007 and have a spreadsheet with dates completed. I want the cell to be green if ... A: Robert, Before I can help you properly, I need to know more about how 'it doesn't work'. What ... | |
| Formula | 10/27/2009 |
| Q: Excel 2004, Mac 10.6.1 My speed in minutes per mile: Distance (4.25) / Time (60) = 14.12 (in F25) In ... A: Don, The formula for minutes per mile is total minutes divided by total distance. I'm ... | |
| Zig Zag Sorting in Excel using Macro | 10/26/2009 |
| Q: I would like to do zig zag sorting in Excel. Current Data: UserID Current_Port New_Port ... A: Ahmadrazhan, I don't follow your logic. Are you sure you have typed your example properly? 1. ... | |
| Excel spreadsheet-put one "record" on a page | 10/24/2009 |
| Q: ...Have an excel spreadsheet generated from a 50th college reunion class survey. What I would ... A: Carrie, I'd do this a different way. There's no need for a word document, among other things. ... | |
| pls need a help | 10/24/2009 |
| Q: "pls help me how to Create a formula in excel which will add .com to the end of the word and www. At ... A: Jenny, Your English is okay except for one problem: you make giant sentences that never end, ... | |
| Excel Time Sheet | 10/23/2009 |
| Q: I'm trying to create an excel time sheet and am intermediate level on Excel. This time sheet must ... A: Russell, I think a set of IF statements will do the trick You'll need on IF statement for ... | |
| Keyboard Settings | 10/23/2009 |
| Q: Each time I open my Excel 2003 I have to reset the default Keyboard settings in order for the 4 keys ... A: Cheri, This isn't an Excel question, so unfortunately, I can't help you this time. This is a ... | |
| pls need a help | 10/23/2009 |
| Q: "pls help me how to Create a formula in excel which will add .com to the end of the word and www. At ... A: Jenny, I can only answer part of your question, because there are too many questions piled up ... | |
| Excel Formulas | 10/22/2009 |
| Q: I am trying to create a workbook for our staffing coordinator here at work. I am wondering if it is ... A: Tim, Sorry, but your question isn't at all clear. What is "the appropriate position and shift" ... | |
| Need date comparison and/or verification formula | 10/22/2009 |
| Q: Bill I'm using Excel XP (2002). I'm working on a report that has different dates on column A, from ... A: Carlos, This isn't too hard. What you want to do is check each date in your long list to see ... | |
| Admin and specific user only per tab? | 10/21/2009 |
| Q: I am wondering if there is a way without VBA to create a spreadsheet where there is a admin tab and ... A: Isabella, All you have to do is PROTECT the workbook, with the Admin sheet formatted as HIDDEN. ... | |
| Funcion IF | 10/21/2009 |
| Q: 1. I have a columna with letter, which can be L or S. I want write a instruccion, that if I find the ... A: Gloria, 1) In the cell where you want the 1 to appear if there is an L (in an adacent cell), or ... | |
| Alternate Column | 10/21/2009 |
| Q: I am using excel 2003, my question is that i have 1000 rows of data with 3 columns, i want to ... A: Anthony, My answer stands as is. I'm sure its correct. Please try it again. If you cannot ... | |
| Alternate Column | 10/20/2009 |
| Q: I am using excel 2003, my question is that i have 1000 rows of data with 3 columns, i want to ... A: Anthony, This is quite easily done (Q.E.D). Assuming that you mean: ".... I want to ... | |
| Macro for data sets analysis | 10/20/2009 |
| Q: I have 'n' sets of data. I need to find out the average each set. i want all these averages in a ... A: Prasad, If N is the number of data sets (not their SIZE), then what is there to change? Just ... | |
| Auto-suggesting store | 10/19/2009 |
| Q: When I hear about a DVD that is recommended, I note that down in an excel list. This way, I can ... A: Endre, There is a way to do this! Its the MATCH and INDEX equation. Unfortunately, if there ... | |
| Formula works in Excel 2003 not 2007 | 10/19/2009 |
| Q: Spreadsheet counts sick days from S or .5S entries. This formula: ... A: Dave, This is a very tricky formula. Its an implied array formula, and it is POSITION ... | |
| Caculate the money/bill denominations of a number | 10/19/2009 |
| Q: Is there a formula or a macro that would caculate the money/bill denominations for any number. ie. ... A: Vince, I worked out a solution for you. Put the original number in cell D5. Make a column ... | |
| Excel question | 10/17/2009 |
| Q: I have a question about excel , I have 5 items which one costumer can buy .. if the comstumer buy 2 ... A: Andy, A simple IF statement will do this. "If qty = 2, price*95*qty%, ... | |
| Sum, 2 conditions, criteria embedded in text | 10/16/2009 |
| Q: I have an employees’ time collection database on sheet 1 in an Excel 2003 file. Sheet 1 contains 3 ... A: Andy, What needs to be done to solve this is to add some additional columns into which you can ... | |
| Count work days between 2 given dates | 10/16/2009 |
| Q: I want to have a formula to count work days for my team. Yes, the build in function NETWORKDAYS of ... A: Edward, If NETWORKDAYS does just what you want for a group, it certainly will do the same ... | |
| Date Range Summary | 10/15/2009 |
| Q: I would like to summarize my daily scrap quantities data into a weekly percentage. Sheet1: In ... A: Nate, If you just want it so you don't see the zero, you can select an option from the View menu ... | |
| Copy live value to static value in excel | 10/15/2009 |
| Q: I am working in office excel 2007 and have run up against a wall. I am pulling data from the web ... A: Rob, I don't understand the difficulty you're having. This seems like a simple copy %26 paste ... | |
| Date Range Summary | 10/14/2009 |
| Q: I would like to summarize my daily scrap quantities data into a weekly percentage. Sheet1: In ... A: Nate, The answer to this is called a multi-criteria record selector. You need to select all ... | |
| Automation | 10/14/2009 |
| Q: Hey Bill, Im using excel 2003. My problem is that I have certain information linked to a number in a ... A: Vish, Read my answer again: There is no row lookup function! Ooops, I said 'now' instead of ... | |
| search for exact duplicate number in 2 workbooks | 10/14/2009 |
| Q: I'm comparing two workbooks to find if the date appears in both workbooks. example: does the date ... A: Alison, I hope you are using the term 'workbook' correctly, or parts of my answer will seem like ... | |
| Automation | 10/14/2009 |
| Q: Hey Bill, Im using excel 2003. My problem is that I have certain information linked to a number in a ... A: Vish, You need one VLOOKUP for each item you want to return. There is now "Row Lookup' function ... | |
| Conditional Formatting w/dates | 10/14/2009 |
| Q: Good morning Bill, I am using excel 2007 and want to mark my overdue dates using conditional ... A: Tim, It sounds as if you put the formula (cell-with-date)<TODAY() into the cell itself, not ... | |
| slow spreadsheet, only sections! | 10/13/2009 |
| Q: I have a fairly large spreadsheet (Excel 2002). Parts of the spreadsheet run slowly (moving from ... A: Leigh, The reason your spreadsheet is acting slowly is because there are a lot of calculation ... | |
| V look up | 10/13/2009 |
| Q: I have a list of values in cell B6 - say A, B ,C and D created using the Data Validation Tool. The ... A: Binoop, There are a lot of problems with your question. 1) how can a single cell [B6] hold ... | |
| Advanced use of the CountIf function | 10/12/2009 |
| Q: I have a spreadsheet containing customer information, one customer per row. 8 of the columns ... A: Tim, You've almost gotten it yourself. here's what I'd do. 1)Add a column of COUNTIF which ... | |
| Name Matching | 10/9/2009 |
| Q: First of all THANK YOU !! I have two lists. one list will say Michael Drayton and the other list ... A: Michael, If you want to see what your formula thinks it is finding, just extract the MATCH ... | |
| Nesting IF, OR, and VLOOKUP | 10/9/2009 |
| Q: I am using Excel 2007 I am wanting to write an IF statement with the functions OR, VLOOKUP, and ... A: Chris, One problem is that you've mis-applied the OR function. Every argument inside the OR ... | |
| Name Matching | 10/9/2009 |
| Q: First of all THANK YOU !! I have two lists. one list will say Michael Drayton and the other list ... A: Michael, There are many ways to approach this problem. The approach you need depends upon ... | |
| nested if statements | 10/8/2009 |
| Q: I sure hope you can help me. I'm trying to write a nested if statement and have not been ... A: Joni, Your question can be edited to show what you want, nice and tightly: If A1 is < 5:46 ... | |
| If, Then Statements | 10/8/2009 |
| Q: I want to create a conditional statement for a cell based on letters entered into another cell. For ... A: Neill, You must be using Excel 2003. Excel 2007 would allow up to 64 levels of nesting, but ... | |
| excel format | 10/8/2009 |
| Q: I am using 97-2003 Excel I am getting this message after using the said formulas in Excel "The ... A: Rajan, All you have to do is split this into two IFs, and then select which one's result to use ... | |
| ranges | 10/7/2009 |
| Q: How do you select a range by typing the range? I hope that makes sense. I am unable to select the ... A: Kacy, If you cannot select a range with the mouse, something is SERIOUSLY wrong. Even if you ... | |
| Excel Formula | 10/6/2009 |
| Q: I need some help with a formula. The VDB formula works great for computing "MACRS" depreciation ... A: Bill, I understand why you need to have this work, [you have hundreds of items all with ... | |
| TIME calculations for billable services | 10/6/2009 |
| Q: I am working on an Excel spreadsheet that translates inputed figures into a billing report invoice, ... A: John, There is not much that is easier than a simple subtraction. However, you've written your ... | |
| TIME calculations for billable services | 10/6/2009 |
| Q: I am working on an Excel spreadsheet that translates inputed figures into a billing report invoice, ... A: John, What you may not know is how to find just the number of minutes in a given time. ... | |
| Excel Formula "VDB" and how to use it to calculate accumulated depreciation | 10/5/2009 |
| Q: I need some help with a formula. The VDB formula works great for computing "MACRS" depreciation ... A: Bill, I'm sorry but I can't seem to help you with this. I'm not a financial analyst so I don't ... | |
| Excel Formula to count number or occurence between two dates | 10/5/2009 |
| Q: Bill, I am familiar with the simple 'countif' [COUNTIF(E2:E23,"x")]function but need to count how ... A: Joyce, You want something called a multiple-criteria record selector". There are several to ... | |
| excel- code replacement in a list | 10/3/2009 |
| Q: Gudday. Im being sent a list of products in excel format, from a customer each week who uses their ... A: Robert, Use the VLOOKUP function for this. It'd be perfect! I've attached my own short ... | |
| excel answer of a result chages function | 10/2/2009 |
| Q: I am trying to work out how i can convert an anwer from a imple true / false statement to include ... A: Stuart, This is very easy with a nested IF statement. ... | |
| Formula to calc call time cost | 10/2/2009 |
| Q: Have call times expressed in minutes and seconds. i.e. 2m30sec or simply 2:30 or 2.30 Need to be ... A: Jason, This isn't too difficult. First, you need to express the time in seconds only. Then ... | |
| Travelling allowance | 10/1/2009 |
| Q: Please give me EXCEL formula to calculate the TA in following conditions DA=22% If Grade Pay (GP) of ... A: Rajan, Thanks for the clarification, that's much better. The logic you want is like this: ... | |
| Matching value in multiple columns | 10/1/2009 |
| Q: My question relates to your early discussion, ... A: Alex, I think I understand part of your question, but I have some additional questions for you ... | |
| IF equation, I think | 10/1/2009 |
| Q: I am using Microsoft Excel 2007. I am a writer, and I have an Excel database of magazines which I ... A: Evan, Its much easier than that. Just use COUNT() to count the number of entries in the date ... | |
| Excel 2003 remove cells containing text | 9/30/2009 |
| Q: I have a column of cells some of which contain text and some numbers. I want to remove all of the ... A: Nancy, You cannot "remove" cells with a formula. TO do such a thing, a formula would have to ... | |
| Travelling allowance | 9/29/2009 |
| Q: Please give me EXCEL formula to calculate the TA in following conditions DA=22% If Grade Pay (GP) of ... A: Rajan, I don't quite understand some aspects of your question. Clearly what you need is a ... | |
| Excel | 9/15/2009 |
| Q: I am trying to calculate my wage per day. I enter starting time in cell A1 then ending time in cell ... A: Rachel, In order to multiply a time by an hourly rate, you must convert the minutes portion of ... | |
| Excel transferring data | 9/13/2009 |
| Q: I am trying to transfer data (by row) to another sheet in an excel file. I have a list in sheet ... A: David, This is not possible using equations. There is no function that detects the presence of ... | |
| VLOOKUP duplicated numbers | 9/12/2009 |
| Q: I have a spreadsheet containing approximately 2000 rows with 10 columns relating to staff records. ... A: Ian, Of course, it is possible to add 0.1 to each occurrence of the number, to make it unique. ... | |
| Excel Formulas | 9/11/2009 |
| Q: I am trying to write a formula and wonder if there is a better way to write it. Column A Column ... A: Amy, Use the SUMPRODUCT function for this. It is designed to take the sum of products, just ... | |
| calulating overdue and underdue dates | 9/11/2009 |
| Q: I am trying to track tasks in Excel(2000) and need a bit of help with my formulas. I have 2 columns ... A: Gareth, You can perform easy arithmetic on dates. Dates are stored as special "serial ... | |
| Splitting excel cells | 9/10/2009 |
| Q: As a complete excel newbie I was wondering if it would be possible for you to let me know how to ... A: Jessica, This is a terribly difficult problem to solve with only equations. It could be done by ... | |
| And / OR | 9/9/2009 |
| Q: I am trying to write this excel formula: if (C2<>"") and (G2<>"") or (H2<>"">) then L1 = 1 else = 0 A: Issa, here's how: =IF(OR(AND(C2<>"",G2<>""),H2<>""),1,0) This would be written in cell ... | |
| Vlookup with IF function | 9/9/2009 |
| Q: I have made an sheet I have to tables, I want that when I change the text in the second table in a ... A: Usman, Sounds like my answer stands: ... | |
| Pivot Table | 9/8/2009 |
| Q: I have to break down a business credit card bill by general ledger account (GL) and store, and right ... A: Martin, It would be easier to spit it up manually and create an equation in the 'allocated ... | |
| SUMPRODUCT formula | 9/7/2009 |
| Q: I have a formula which i need to alter slightly. The current formula is; ... A: Louise, The problem you're having is that you have assumed that putting the search values in ... | |
| Inconsistent formula | 9/7/2009 |
| Q: I have to search value of a column in a another list which has data in it. My problem is some of the ... A: Ravi, I noticed one problem with the formulas you provided. The example you give for ... | |
| excel spreadsheets | 9/6/2009 |
| Q: i have to do surveys on 1000 houses, i have created one sheet for each property copy paste, each ... A: Mark, It sounds like what you want to do is collate information from non-adjacent cells, ... | |
| Find next emptry cell/s in a range and return data | 9/6/2009 |
| Q: Hope you can help please? I have 2 sheets in a workbook (Excel 2007)... In Sheet 1: I have ranges ... A: Nico, If you insist on the structure you've outlined, then there no choice except for a macro. ... | |
| modfied sorting | 9/5/2009 |
| Q: I have about 18,000 Persian words in a column that I want to sort alphabetically, and it's too big ... A: Dave, My first suggestion is to give custom lists another try. Create a list with the first ... | |
| Excel 2003 | 9/5/2009 |
| Q: i am using MS office 2003. In excel for formatting the text (making bold,Italic,underline)it takes ... A: Amol, This is probably a computer issue and has nothing to do with Excel specifically. I ... | |
| Excel | 9/4/2009 |
| Q: I have created a table (E1:T25) indicating the mean temperature of various countries at different ... A: John, Your basic question here seems to be about circular references [CR]. You REALLY ... | |
| Inconsistent formula | 9/4/2009 |
| Q: I have to search value of a column in a another list which has data in it. My problem is some of the ... A: Ravi, Okay, so that's what you are trying to do. What is wrong with the results that you are ... | |
| Inconsistent formula | 9/4/2009 |
| Q: I have to search value of a column in a another list which has data in it. My problem is some of the ... A: Ravi, In order for me to answer this, you need to help me figure out what you're trying to do. ... | |
| subtracing dates in column to report in the next | 9/3/2009 |
| Q: I want to put a date in column(A) and have colum (B) report a date that would be 4 weeks prior to ... A: Joyce, Dates are stored as a serial number. The serial number is the number of DAYS since Jan ... | |
| Excel | 9/3/2009 |
| Q: I have created a table (E1:T25) indicating the mean temperature of various countries at different ... A: John, I read your comment when you made your ratings [thanks for the 10's]. There IS a list of ... | |
| EXCEL 2007 formula evaluation question followup | 9/2/2009 |
| Q: Reached upper limit of followups in previous thread. To recap, looking at the formula in cell F45, ... A: Jim, Wow, that is really odd. I took your spreadsheet and wrote SUM(D45:E45) in one cell and ... | |
| Cell Option | 9/1/2009 |
| Q: I am encountering difficulty in solving an Excel issue. There is a row of 9 cells. I have set the ... A: Rahul, The first thing to do is make a Y-detector. Then, apply its result to cause some action ... | |
| Time difference calculation | 9/1/2009 |
| Q: I have trains travelling from one destination to another with stations inbetween. lets call the ... A: William, You need to re-create the times so this doesn't happen. make a new table according to ... | |
| Locating Cells | 8/31/2009 |
| Q: I am wanting to create a chart. All the cells will have a different numbers in them, because they ... A: Luis, A very tricky arrangement! I would never have guessed from your first question! I ... | |
| Locating Cells | 8/31/2009 |
| Q: I am wanting to create a chart. All the cells will have a different numbers in them, because they ... A: Luis, Perhaps you are using the improper terminology??? A CHART in Excel is a GRAPH... a ... | |
| Week no./procedure matrix | 8/31/2009 |
| Q: I have an Excel 2007 sheet which has week no. as the column headings. The purpose of the sheet is to ... A: Richard, I still don't understand the nature of your table. You have three ROWS per ... | |
| Date change in Excel | 8/29/2009 |
| Q: I have this strange thing that happen to my Excel sheet. I used to open the Excel sheet on two ... A: Stig, This obviously has something to do with the new O/S you installed. It's probably NOT an ... | |
| MAtch text | 8/29/2009 |
| Q: I have list of Names in one column on 1st tab. D. Jain Dharmender Singh Dikshitulu G S V Dinesh Kr ... A: bhavana, You didn't give me any examples of what the corresponding email addresses look like, ... | |
| Move data from cell without moving formula | 8/29/2009 |
| Q: I have a log that I created for logging guest users of a small parking lot. Basically, it logs: ... A: Jonas, To make the rows 'automatically' change order when data is entered (without writing a ... | |
| New question. | 8/29/2009 |
| Q: it's very much appreciated. Well, as you will be able to see from the worksheet I've attached - I ... A: Ray, I'm sorry but the finer points of your new question elude me. Your image might help, ... | |
| LArge excel file | 8/28/2009 |
| Q: Bill, I have an excel file (Excel 2003) that is 144 megs in size. It is slow to calculate. My system ... A: Dave, There can be many reasons why it is slow. First, two major classes of reasons: 1) ... | |
| I need help with solving this problem I've tried a pivot table but can't quitem make it work. | 8/28/2009 |
| Q: ok this is for Commission payout for salesman. We just started using a new system that tracks ... A: Adam, A pivot table might work, but you probably are having trouble getting it to be in the ... | |
| How do I ask a cell to look for two sets of numbers, then if condition is met, plus one. | 8/28/2009 |
| Q: it's very much appreciated. Well, as you will be able to see from the worksheet I've attached - I ... A: Ray, Cool spreadsheet! I love it. Your problem might be easily solved.... its based upon ... | |
| Week no./procedure matrix | 8/28/2009 |
| Q: I have an Excel 2007 sheet which has week no. as the column headings. The purpose of the sheet is to ... A: Richard, Forget the way you've been trying to do this. Let's start over. First of all, ... | |
| Copying Data and Eliminating Blanks | 8/27/2009 |
| Q: I have the following form of data: # Joe A B # # # # # # # # # # # ... A: Russell, There is nothing like over-simplification to make a question (like your original) ... | |
| Excel Formula | 8/27/2009 |
| Q: I am trying to get a formula on one sheet in a workbook to automatically change each time I create ... A: Calfin, No, its not clear yet. How about if you were to say this? If cell F18 on sheet 1 ... | |
| Copying Data and Eliminating Blanks | 8/27/2009 |
| Q: I have the following form of data: # Joe A B # # # # # # # # # # # ... A: Russell, There are several ideas I have but I can't exactly tell which one is best for you. I ... | |
| Copy From One Sheet To Another | 8/27/2009 |
| Q: I have a worksheet One sheet named "Vendors" and the other named "Summary". I am looking to create ... A: Tom, If you insist on a macro, then I can't help you. I don't offer macro/VBA advice on this ... | |
| calendar | 8/27/2009 |
| Q: Sir, I have on tab 1 (Faculty) 29-Jun 30-Jun 1-Jul 2-Jul Trainers Mon Tue Wed Thu ... A: Bhavana, Your question is a little difficult to understand because the attempt you made to ... | |
| Excel Formula | 8/27/2009 |
| Q: I am trying to get a formula on one sheet in a workbook to automatically change each time I create ... A: Caitin, If you have an equation that's =SHEET1!F18 on sheet 1 itself, and you copy sheet 1 ... | |
| Excel Function | 8/26/2009 |
| Q: Bill, I am using Excel 2003. I need to populate a cell based on another populated cell. I have a ... A: Terry, I don't see how an IF statement can return a zero unless that's one of the clauses within ... | |
| Excel Function | 8/26/2009 |
| Q: Bill, I am using Excel 2003. I need to populate a cell based on another populated cell. I have a ... A: Terry, I am not sure I understand what you want. X probably means a number, or maybe a letter, ... | |
| Comparing and Sort/Extracting Data | 8/26/2009 |
| Q: I have 2 sets of customer data. Set A is 5000 records and contains Customer Number and Business Name ... A: Jef, The 'best way' is to use VLOOKUP to find the matching customer name from one table to the ... | |
| Excel function | 8/26/2009 |
| Q: Is there a way in Excel to enter into a particular cell the sum of some numbers - I know how to sum ... A: Dave, Yes there is a formula just like that. I found it on the Wikipedia. It is ... | |
| Help on Macro for copying data from one sheet to another | 8/26/2009 |
| Q: I want help on how to copy data from one sheet to another assuming Sheet 1 is having fixed range to ... A: Mayank Gupta, Although I usually don't provide Macro advice on this website, I'll give you a ... | |
| Automatic population of a cell | 8/26/2009 |
| Q: First of all thanks for help always. You are doing a nice job. I have a specific query regarding ... A: Mittun, The solution depends on if the user has TWO cells in which to enter either a host name ... | |
| IF Function | 8/25/2009 |
| Q: Hey Bill, Im using Excel 2003 and im have an error when i carry out the following function : ... A: Vish, Okay then! Here's the approach I'd use: Use a set of three nested IF statements, ... | |
| IF Function | 8/25/2009 |
| Q: Hey Bill, Im using Excel 2003 and im have an error when i carry out the following function : ... A: Vish, Let me restate your question and see if I have it correct. 1) you have three lists , ... | |
| Excel Counting | 8/25/2009 |
| Q: I have a data-sheet containing thousands of rows. I would like to add all the instances that are ... A: Ryan, What Excel version are you using? if it is Excel 2007, then look at the COUNTIFS ... | |
| IF Function | 8/25/2009 |
| Q: Hey Bill, Im using Excel 2003 and im have an error when i carry out the following function : ... A: Vish, I don't understand how you think your function can work. If Bob_Smith is the name of a ... | |
| Balance per month | 8/24/2009 |
| Q: I have designed a spreadsheet where the balance owing (by my employer) at the beginning of the year ... A: Peter, To find the current month of NOW() or any other date, simply use the MONTH(cell) ... | |
| cost comaprison worksheet | 8/24/2009 |
| Q: I have been assigned to compare two vendors prices for products for my restaurant. The sheet i have ... A: Nick, How do you want the difference in price to be color-coded? Do you mean that if vendor 2 ... | |
| Maintaining Cell References in a Formula During A Sort | 8/24/2009 |
| Q: Is there a way to maintain a cell reference to a cell referred to in a formula during a sort so that ... A: Bill, You can't maintain the references to cells when they sort, as you have observed. ... | |
| date | 8/24/2009 |
| Q: How can I calculate a date in case I have the year, weeknumber, and day? Let's say A1=2010 A2=5 ... A: Wolfgang, Well, you may see it that way, but that doesn't mean the creators of Excel provided ... | |
| shading between two polynomial fits | 8/22/2009 |
| Q: I have two data sets signal amplitude (Y axis) vs time (X axis). I plotted them using scattered plot ... A: Pam, When you say you want to "draw a shade area between THESE two lines", you can ONLY mean ... | |
| Formula to find if last 6 characters in a cell are numbers | 8/22/2009 |
| Q: I have data in cell A1,A2 and A3 as below OLD NO : 664, NEW NO:13, SCHOOL ROAD, ANNA NAGAR WEST, ... A: James, HAH! I totally revised this answer based on that final thought I had (the one in the ... | |
| Balance per month | 8/21/2009 |
| Q: I have designed a spreadsheet where the balance owing (by my employer) at the beginning of the year ... A: Peter, You can do what you want but you need to change the organization of your spreadsheet a ... | |
| Calculations | 8/21/2009 |
| Q: I want to add column A (which has different names in the rows), how do you calculate by the names in ... A: Suzette, Use the SUMIF function. SUMIF can take two ranges and a criterion value. ... | |
| Excel rank correction factor | 8/21/2009 |
| Q: I am using Excel 2007. I have integer values in cells E20, G20, I20, K20, M20, O20. I need to rank ... A: James, I agree... it is such a pain the way Excel chose to implement the RANK function. I also ... | |
| If statement cell range | 8/21/2009 |
| Q: The IF formula I'm using does not look at the cell range I put in. In cell A1 I wrote: ... A: Don, Your formula has an error, the second paren around the range should not be there. It ... | |
| calculating Voids on a weekly basis using Excel 2003 | 8/20/2009 |
| Q: I want to be able to count how many void places I've got in the current week. I have 3 worksheets ... A: Kerith, I noticed your comment but you can write a followup to ask for clarification, instead of ... | |
| date | 8/20/2009 |
| Q: How can I calculate a date in case I have the year, weeknumber, and day? Let's say A1=2010 A2=5 ... A: Wolfgang, This is a very difficult problem, and I don't know of an easy answer. To be honest, ... | |
| Lookup Multiple Rows. | 8/20/2009 |
| Q: The image attach herewith is the picture of sheet1(Picture-1) and sheet2(Picture-2). Picture-2 in ... A: Nabam, This is an EXTREMELY difficult problem, made difficult by several factors: 1) your ... | |
| Automatic population of a cell | 8/19/2009 |
| Q: First of all thanks for help always. You are doing a nice job. I have a specific query regarding ... A: Mittun, I assume that you MUST have two different cells, one for each dropdown list. Next ... | |
| Excel Spread Sheet | 8/19/2009 |
| Q: I have an Excel Spread sheet of my parts inventory for my truck. How can i set up this spread sheet ... A: Rommel, You'll need to research how to interface your barcode scanner to your computer to ... | |
| dependent validation lists with dynamic ranges | 8/19/2009 |
| Q: I am creating a spreadsheet, with three columns. In column a the cells are validated and tied to the ... A: You've created a very brilliant cross-coupled pair of validation lists! I've never seen that done ... | |
| Macro to extract specific values from a list | 8/19/2009 |
| Q: From a list of numbers in a column e.g 13.2, 14, 15, 17, ... A: Mitali, Although my profile says that I don't offer macro advice on this website, I wonder if ... | |
| How do I preserve the '0's in text-number conversion? | 8/19/2009 |
| Q: I am curious how to preserve the '0's in a text to number conversion. For example: I want to make ... A: Brent, An example of one case, without a general problem statement, isn't clear enough for me to ... | |
| INDEX and MATCH formula in VBA? | 8/18/2009 |
| Q: I have the formula: =INDEX(($K$7:$K$19),MATCH(E8&F8,$I$7:$I$19&$J$7:J$19)) What this does is look ... A: Bill, Read my profile: it says that I don't offer macro or VBA advice on this website. ... | |
| Excel 2003 | 8/17/2009 |
| Q: I am using the 2003 version of excel. My question is, If I compare two values from different ... A: Craig, I understand a little better but you didn't answer all my concerns. So the best I can ... | |
| Excel 2003 | 8/17/2009 |
| Q: I am using the 2003 version of excel. My question is, If I compare two values from different ... A: Craig, There is a formula you can use. Its a Nested IF Statement. I'm not 100% sure I ... | |
| Help about convert row into columns | 8/15/2009 |
| Q: I have Data Like 324 Platinum Gold Silver 325 platinum Silver 326 Gold 327 silver 328 329 platinum ... A: Satyabrata, This is a terribly complex problem that is beyond my ability to explain with short ... | |
| Formula Issue | 8/15/2009 |
| Q: I am working on a room booking sytem in Excel 2007. It has two main sheets Gantt (where the formula ... A: James, A really quick thought for you: try putting the portion that returns the #NUM inside an ... | |
| Creating a Graph in a new tab without values | 8/14/2009 |
| Q: Goal: To show a graph by itself in a new tab without showing all the values. I have multiple tabs ... A: Kevin, As you work through the Chart Wizard in Excel 2003, you'll see an option to Insert the ... | |
| countif not working | 8/14/2009 |
| Q: I have 4 values in column A that go down 4 rows. They are: <50% >50% <50% <50% I want to count the ... A: Amy, You've come up with a real puzzler. It seems like what you've done should work and it ... | |
| Average Amount of Product Used | 8/14/2009 |
| Q: I am trying to calculate the average amount of product used weekly. What I would like help with is ... A: Randy, The best way to do this is to keep two different tables, one for USEAGE and one for ... | |
| file password | 8/14/2009 |
| Q: I'd like to find out how to create an excel file password to be required when ever someone tries to ... A: Brian, there are two ways I can think of, but I can't give you the details of either one, because ... | |
| If Statements | 8/13/2009 |
| Q: a = x a = y a = z b < 15 b >= 15 b >= 30 How do you ask if a = x and less than 15, and a = x and ... A: Barbara, Your question isn't clear. You made a nice little table of a & b, but then never ... | |
| Excel Character limit question | 8/13/2009 |
| Q: I am hoping you can help we with an excel problem I have a column with X amount of characters I am ... A: Naomi, You can use the =LEN(text-cell) function to return the number of characters in a cell. ... | |
| Help Please...... | 8/13/2009 |
| Q: I need help... I have this situation: In column A have Dates and in column B have dates to, like ... A: Cosmin, I'm sorry for the delay. You can use the advanced form of SUMPRODUCT to count a column ... | |
| compare dates and conditionally format | 8/13/2009 |
| Q: I have a spreadsheet that lists (by employee) training and the date the training was last completed. ... A: Chris, From the title of your question, it sounds like you already know how to do this: use ... | |
| MODE function | 8/12/2009 |
| Q: I used this formulae to find the mode in a row or column,[INDEX(G8:M8,MODE(MATCH(G8:M8, G8:M8,0)))]. ... A: Ramya, The MODE function only works with numbers so you can't use if for determining the most ... | |
| Excel 2007 | 8/12/2009 |
| Q: I work for a calibration firm and I use a spreadsheet as our "report" for customers. I would like to ... A: Barry, My answer regarding Conditional Formatting will automatically format the number based on ... | |
| Sorting Groups of Rows in Excel | 8/12/2009 |
| Q: I am a summer student at an office and they want me to format one of their payroll sheets. The ... A: Mahala, The only way I can see to do this is to add two columns to the spreadsheet and use them ... | |
| Help Please...... | 8/12/2009 |
| Q: I need help... I have this situation: In column A have Dates and in column B have dates to, like ... A: Cosmin, Use the COUNTIFS() function, and hope that you have Excel 2007 because that's the only ... | |
| graphing numeric data based on text field | 8/12/2009 |
| Q: I need your help please. I have made graphs before but to me the following is complicated and I ... A: Mark, I realize it has been a month since you submitted this question. You have probably solved ... | |
| Pivot Table | 8/11/2009 |
| Q: In the attachment Balance Column is not in Pivot Table. 1- I would like to create Borders against ... A: Khurram, I think you can accomplish this by using INSERT CALCULATED FIELD. This is an option ... | |
| Help with an IF Statement | 8/11/2009 |
| Q: I am trying to write an IF statement which looks for a value in one cell and then applies a discount ... A: Sarah, You need a NESTED (sometimes called a COMPOUND) IF statement. These are easy to write ... | |
| Excel Rate Calculator | 8/10/2009 |
| Q: I’m trying to create an excel calculator for a type of work that I do. I would like to calculate ... A: Lindsay, Unfortunately my expertise does not extend to financial analysis, (even a simple one ... | |
| Cumulative total | 8/10/2009 |
| Q: I do not know if the solution to my question would require a VBA macro. Envision a spread sheet to ... A: Juergen, All you have to do is create a simple formula in column B which adds the number in the ... | |
| Matching Two Columns | 8/10/2009 |
| Q: I'm trying to compare two different columns and count how many rows have different information. ... A: Kevin, This is perfectly clear, and there are several ways to do it. If it were me, I'd use ... | |
| How to update column | 8/10/2009 |
| Q: I would like to update the inventory numbers on B1 once I key in numeric data on A1. For example on ... A: Stephen, It's very difficult to do this, since it requires that Excel have a memory of the ... | |
| Compare part of strings in a table against a list of Keywords | 8/10/2009 |
| Q: I have a list of Keywords as a master reference table in a separate sheet (say Keyword!A2:A1500). My ... A: Nagesh, This is a very complex question, but some time ago another questioner gave me a ... | |
| Excel Formula for Timeclock | 8/8/2009 |
| Q: I am trying to calculate hours worked but having trouble with having the time calculate to the ... A: Tim, You need a custom rounding function. There are several ways to do this, and here is one ... | |
| Excel Question | 8/8/2009 |
| Q: I have four different Excel files that contain the names and addresses of people who voted in four ... A: Mike, This is slightly complicated and you'll have to do a lot of work to get a result. The ... | |
| Excel 2007 | 8/7/2009 |
| Q: I work for a calibration firm and I use a spreadsheet as our "report" for customers. I would like to ... A: Barry, Excel has so many different rounding functions, it's no wonder you don't know which ones ... | |
| excel | 8/7/2009 |
| Q: I know nothing about excel, never used it. Is it a separate program that I must buy? or can I ... A: Joe, Excel as such can only be purchased... it is certainly NOT free. It's fairly expensive, ... | |
| EXCEL Text Lists | 8/7/2009 |
| Q: Bill, I've developed a column of names (30+) on a data worksheet. In the same workbook, on another ... A: Clint, This has been a tough one to figure out but I took another look and an idea hit me... at ... | |
| Charting data | 8/7/2009 |
| Q: I have a set of data with priority level as the y-axis and division as the x-axis. The data is ... A: Margaret, You may have already solved this by now, but in case not, here is a response that may ... | |
| convert four-digit number to military time | 8/6/2009 |
| Q: I import a sheet from a program that gives me a four-digit value for time (i.e., 2330). I need to ... A: Virginia, You need to break the number 'in half' (Hours & minutes), and then use the TIME() ... | |
| comparing content in a cell range | 8/6/2009 |
| Q: I am working on MS Excel 7. This is my problem: I have a spreadsheet with 2 columns (A and B) ... A: Elke, Use COLUMN C to create a COUNTIF equation. Each COUNTIF will take as input the adjacent ... | |
| Formula Help | 8/6/2009 |
| Q: I'm trying to create a formula to give an "Error" when a duplicate time is entered. It should also ... A: Mealnie, The correct way to do an OR test is like this: =IF(OR(D6>J6,C6<K6),"error","") ... | |
| discount | 8/5/2009 |
| Q: I have an invoice program & want to put in a discount of 40%. Would like it after the subtotal,then ... A: Gail, We're not supposed to hand out the answers to homework problems on this site. I tried to ... | |
| Excel 2007 | 8/5/2009 |
| Q: I have a workbook (dashboard) with many sheets. I would like to have my commander to be able see at ... A: Eliot, You seem to have gotten most of it to work already, recognizing that Conditional ... | |
| Excel 2007: Pivot Table - Data Cube - WhatIf Analysis | 8/5/2009 |
| Q: I have created a cube with BIDS (Business Intelligence Development Studio) – MSSQL 2005, and I ... A: Milton, I don't have enough information to help you with a complex problem like this. "It is ... | |
| Automatic Data population | 8/5/2009 |
| Q: Am hoping this will be an easy one. I have an excel spreadsheet (2007) that comprises of 4 sheets. ... A: Jax, What you are saying isn't possible, unless you have the calculation method set to manual. ... | |
| is 8000 a limit for the Solver? | 8/5/2009 |
| Q: In our company we try to solve a task of optimization that requires matrix of about 20 colomns and ... A: Dmitry, I haven't been able to find an answer to this question. The Solver you are using is a ... | |
| excel 2007 | 8/4/2009 |
| Q: I am using Excel 2007 I want to create in interactive table for world cup matches How to use ... A: Ahmad, There are many types of conditions. It's impossible to tell you how to use them all! ... | |
| Automatic Data population | 8/4/2009 |
| Q: Am hoping this will be an easy one. I have an excel spreadsheet (2007) that comprises of 4 sheets. ... A: Jax, All you have to do is create simple cell references between the sheets. If one sheet is ... | |
| Excel Formula for Timeclock | 8/4/2009 |
| Q: I am trying to calculate hours worked but having trouble with having the time calculate to the ... A: Tim, I have a problem with your question... it's based on ONE SPECIFIC CASE. It's not stated ... | |
| Dependent list with criteria | 8/3/2009 |
| Q: I have the following table in Excel: ColumnA ColumnB A 2 B 4 C 6 ... A: Kostas, There is a method to make dependent drop down lists, but I don't think it can be done ... | |
| Excel Micro_output to another worksheet | 8/3/2009 |
| Q: I am trying to write three piece of information into one cell in the output worksheet, for instance, ... A: Hanxue, You can use the =DATE() function to create a DATE from the three parts, day,month & ... | |
| cannot reinstall Excel 2003 | 8/3/2009 |
| Q: I was having a problem where I couldn't open Word or Excel attachments that came in emails. ... A: Ivan, I cannot tell what might be wrong from the little information you have. This is a case ... | |
| graphing in Excel 07 | 8/3/2009 |
| Q: I am currently doing a paper and trying to illustrate the relationship of 2 variables through excel ... A: Twee, There are MANY types of graphs in Excel, especially in the 2007 version! You could ... | |
| Copy Cell Content to another cell based on the Status of another cell in the same row | 8/2/2009 |
| Q: I have a spreadsheet which lists various files that the team works on. All the team members enter ... A: Varun, The portion of your question which makes it very difficult is this: [I have abbreviated ... | |
| sum bill of material by assembly level | 8/1/2009 |
| Q: I have an Excel 2003 spreadsheet provided by my customer. It lists over 900 assemblies with their ... A: James, Ass a column that takes the cumulative value of all the rows where there is a 1 in the ... | |
| Conditional Formatting question | 7/31/2009 |
| Q: Good morning, I have searched and searched and I am still stumped on how to pull this off... (ill ... A: Michael, This is easy to do with Conditional Formatting [CF]. All you have to do is use the ... | |
| SumIF Conditional | 7/31/2009 |
| Q: I'm using excel 2007 and was hoping for some help on a sumif array problem. I'll start with what I ... A: Andy, If you are satisfied with your SUMIF (it is giving you the correct result), you can create ... | |
| copy the data from another sheet | 7/31/2009 |
| Q: i have a workbook with three sheets, on the second one i paste information that i export from a ... A: Javier, I'm afraid that I don't follow your question. There are too many THAT words which I ... | |
| function that update excel 2003 cell | 7/31/2009 |
| Q: Please try to answer my question below. let say A1=2 and B1=5 then i add B1 with A1 in B1 which is ... A: Jack, Generally, you can't do this. It would require Excel to remember the previous values, ... | |
| updating figures in commulative & monthly columns. | 7/31/2009 |
| Q: Bill H., I used to prepare monthly progress report where i have to delete the figures under ... A: Nabam, You CAN'T delete figures that calculations depend upon to produce their answers! As you ... | |
| Split sums | 7/30/2009 |
| Q: I have 36 numbers that sum up to a total. That same total is split into two different subtotals. I ... A: Heidi, Unfortunately there is no easy way to do this with formulas. It's a complex computer ... | |
| Automatically pulling information from one excel sheet into another | 7/30/2009 |
| Q: Bill - First off, thanks for your help with my excel problem. I'm interning at a small/medium sized ... A: Nathan, If you have a master list with all the company names and phone numbers, then each week ... | |
| Product Catalogue Spreadsheet | 7/30/2009 |
| Q: I am working on a product catalogue spreadsheet which contains around 25000 products with each ... A: Antoni, You did a great job guessing the answer, because I think you've gotten it right! ... | |
| updating figures in commulative & monthly columns. | 7/30/2009 |
| Q: Bill H., I used to prepare monthly progress report where i have to delete the figures under ... A: Nabam, If you delete figures that other equations use to calculate, those equations will ... | |
| Drop Down Question | 7/29/2009 |
| Q: Bill, I have created a drop down menu which is working great but I would like to add a description ... A: Alex, Your question is vague: "Do I have to link the Vlook Up to the next cell?"... I don't ... | |
| summarizing data from several worksheets into one | 7/29/2009 |
| Q: I have info in several worksheets (about 100). For simplicity sake lets make it only 3 sheets. I ... A: Pat, There is an easy way to do this, I use it all the time to draw info from various sheets ... | |
| Drop Down Question | 7/29/2009 |
| Q: Bill, I have created a drop down menu which is working great but I would like to add a description ... A: Alex, Build a table of the descriptions and the list of drop-down items. Preferably on a ... | |
| Excel sum does not sum to zero | 7/29/2009 |
| Q: I am using Excel 2003 I am returning a list of dollar amounts from a ledger table in my database. I ... A: Michael, The only explanation for this is that some of the numbers you're summing (it would ... | |
| Presenting information in a different format | 7/29/2009 |
| Q: Bill, Thank you for your ongoing support at Allexperts.com. I have uploaded an image which shows ... A: Azeem, This is a perfect application for the combination of INDIRECT() and ADDRESS(). I use ... | |
| Multiple criteria in sumif function | 7/29/2009 |
| Q: In sumif function can we use more than 2 criterias? As in I want to use >= and <=? and the range ... A: Mitali, No, SUMIF can only be used with ONE criterion. However, you have a couple of options: ... | |
| Excel | 7/28/2009 |
| Q: I have multiple excel sheets linked up to a summary sheet, what I would like to be able to do on the ... A: Kailesh, There are many possible ways to do what you ask, and the answer really depends on more ... | |
| 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: Raymond, You may want a 'simple' calculator, but it may not be simple to create one. First ... | |
| More on SUMPRODUCT and -- | 7/28/2009 |
| Q: Two questions for you, Bill: 1) Did you mean it when you said COUNTIFS? I'm familiar with COUNTIF, ... A: Sarah, 1) yes, I meant IFS. Excel 2007 not only has COUNTIFS, but also SUMIFS and AVERAGIFS, all ... | |
| Excel 07 | 7/28/2009 |
| Q: Is it possible to generate five numbers that can give me a mean and standard deviation value of my ... A: Candice, If you mean that you want to type in a MEAN and type in a standard deviation, and have ... | |
| Pivot Chart | 7/27/2009 |
| Q: I am trying to create a chart to show the min and max. I want Count displayed in the middle, date at ... A: Sara, Why did you title this "Pivot chart", and then make no mention of an associated pivot ... | |
| Highlighted Cell | 7/27/2009 |
| Q: I am keeping employees data on Excel Sheet. Total we have got nearly 1000 plus employees on Payroll ... A: Bhavan, I can't do this for you because I cannot see the conditional formatting formula you have ... | |
| 2 criteria lookup or match | 7/27/2009 |
| Q: Could you please help in making formula for double criteria match. i am using excel-2007 i have ... A: Shuaib, Usually there are several methods to do this, especially in Excel 2007. I'd recommend ... | |
| Excel | 7/27/2009 |
| Q: I have multiple excel sheets linked up to a summary sheet, what I would like to be able to do on the ... A: Kailesh, I assume you are using the word SHEET correctly, therefore all your data is in the same ... | |
| SUMPRODUCT | 7/26/2009 |
| Q: Bill, Do you know of a way to do a SUMPRODUCT on a multiple selection? I'm hoping that my trouble ... A: Sarah, I was unable to make SUMPRODUCT operate with discontiguous ranges. I tried several ... | |
| Drop list | 7/26/2009 |
| Q: i want to make drop list from column , and this colum have repeated names. i make it with validation ... A: Anas, You have to create a new list that doesn't have the repeated names. There are two ways ... | |
| Highlighted Cell | 7/26/2009 |
| Q: I am keeping employees data on Excel Sheet. Total we have got nearly 1000 plus employees on Payroll ... A: Bhavan, There is no excel function which can detect highlighted cells and count them. ... | |
| Leave Calculation. Exact Month and Days | 7/25/2009 |
| Q: Sir, I am HR & Admin person. We mainly depend on Excel Sheet for Employee Data Entry and like. ... A: Bhavan, Do you still need help with this? if you do, please start a NEW QUESTION, [not a ... | |
| SUMPRODUCT | 7/25/2009 |
| Q: Bill, Do you know of a way to do a SUMPRODUCT on a multiple selection? I'm hoping that my trouble ... A: Sarah, I've never tried SUMPRODUCT on non-contiguous arrays, but it should work IF all the ... | |
| SUMPRODUCT | 7/25/2009 |
| Q: Bill, Do you know of a way to do a SUMPRODUCT on a multiple selection? I'm hoping that my trouble ... A: Sarah, I'm sorry that I gave you a simple answer, but I didn't realize you were an advanced ... | |
| Excel formula question | 7/25/2009 |
| Q: =IF(G3>$B8,$C8/12,0) The output is incorrect: Jan Feb Mar Apr May June July Number of ... A: Steve, I'm sorry for taking so long to get back to you, and now, without an answer! I suspect ... | |
| Week number in a month | 7/25/2009 |
| Q: Sir, I have dates =8th July 2009 in cell B4 & 16th June 2009 in B5 How can we calculate that 8th ... A: Bhavana, The first thing you need to do is to define what it means to be the 1st, 2nd, 3rd, ... | |
| Excel formula question | 7/24/2009 |
| Q: =IF(G3>$B8,$C8/12,0) The output is incorrect: Jan Feb Mar Apr May June July Number of ... A: Steve, Unfortunately I can't make head or tails out of your question. Why not forget your ... | |
| #N/A needs to read "No" | 7/24/2009 |
| Q: I am in need of some very quick help. As with any formula, if the data is not there, the result ... A: Ami, Yes, you can change #NA to "No" using an IF statement that's based upon the ISNA() ... | |
| Create a survey with results | 7/24/2009 |
| Q: Please can you advise me on how to go about creating a survey template where user can enter data and ... A: Nola, If you still need help with this, here is a very brief answer. You can provide a ... | |
| to highlight related cells | 7/24/2009 |
| Q: I have a worksheet for managing all documentation at our place. I was trying to come up with a ... A: Sumit, There isn't any equation or function that I know of that reports the cell address of a ... | |
| m.s excel | 7/23/2009 |
| Q: which option is used to store more than one value within the cell in an spread sheet A: Suchith, There is no option for this. It is impossible to store more than one value in a cell. ... | |
| lookups | 7/23/2009 |
| Q: I have two colums of data that relate to each other. for example: col A Col B 45 4 37 3 ... A: Jon, This is a limitation of the VLOOKUP and MATCH functions (whichever one you used). These ... | |
| Performing 2 Dimensional look-ups | 7/22/2009 |
| Q: I have a huge list of information that I need to rearrange into a matrix and I was hoping you could ... A: Grahame, Have you tried applying a PIVOT TABLE to this problem? It seems like a natural ... | |
| Delete Store# | 7/22/2009 |
| Q: Hey Bill, I'm using excel 2003 I needed assistance in deleting rows or cells based on 2 to 3 ... A: Vish, The only way to delete rows is with either a manual (menu-driven, keyboard-operated) ... | |
| Leave Calculation. Exact Month and Days | 7/21/2009 |
| Q: Sir, I am HR & Admin person. We mainly depend on Excel Sheet for Employee Data Entry and like. ... A: Bhavan, Once you know that the serial number which stores the date is "the number of days from ... | |
| SUMPRODUCT | 7/21/2009 |
| Q: Bill, Do you know of a way to do a SUMPRODUCT on a multiple selection? I'm hoping that my trouble ... A: Sarah, Its a shame the XLDYNAMIC site is gone, and with it that most excellent article! I've ... | |
| MS Office Home & Student 2007 | 7/20/2009 |
| Q: When I create a new spreadsheet in Excel and enter a number in a cell, it takes 5 to 6 seconds ... A: Wes, This sounds like something wrong with your computer in general, and not a specific Excel ... | |
| Excel filtering | 7/17/2009 |
| Q: I have two sheets in my workbook. My first sheet contains names and numbers in 10 columns and the ... A: John, Thanks for the clarification. I didn't look at your workbook because what you want to do ... | |
| Excel filtering | 7/16/2009 |
| Q: I have two sheets in my workbook. My first sheet contains names and numbers in 10 columns and the ... A: John, I'm not sure what you mean so I have to ask. Do you mean that you want the results on ... | |
| Multiple Y Axis chart | 7/16/2009 |
| Q: I have a number of data sets in my graph on the Y-Axis. I would like to display various Y axis ... A: Andy, Excel only allows two Y axes. You could consider creating 'fake' axes using graphical ... | |
| mathematical functions using summation, i, k, and n | 7/16/2009 |
| Q: Math functions sometimes start with the Greek letter capital sigma (for summation) with "i=1" (in ... A: Ron, I am quite familiar with the notation you describe. "Do the formula for n as n varies from ... | |
| Excel Multiple files | 7/15/2009 |
| Q: I'm using Excel 2003, I have in one folder more than 10 Excel files, each files contains ... A: Ibraheem, If your 10 files are really that big ("almost reach the limits of data points in ... | |
| What are bin numbers? | 7/15/2009 |
| Q: What are bin numbers? When I am creating a histogram, it asks me for a bin range. What are the ... A: Amit, The Excel help file explains this quite clearly. Here's what it says about Bin Numbers: ... | |
| Looking up and returning data | 7/15/2009 |
| Q: I have an excel workbook with two work sheets. The first work sheet has two columns, both ... A: Kurani, All you need is to use the VLOOKUP function in column B, next to where you type SUN. ... | |
| Select all cells having specific formula only. | 7/14/2009 |
| Q: Bill, How i can select all the cells that contain specific text or formula without pressing down ... A: Nabam, There are several ways to select formulas, but I am not sure if any of them will work ... | |
| date to number | 7/14/2009 |
| Q: how can i convert date input to numerical number jan 1, 2009 as 1, jan 2, 2009 as 2 until 4 years ... A: Anj, The date is serial number that is the number of days since Jan 0, 1900. Knowing this, you ... | |
| vlookup formula with multiple array | 7/13/2009 |
| Q: I want to use the vlookup formula to retrived value to pick value from sheet 1, 2 and 3 in sheet 4. ... A: Srinivas, Its impossible to write an exactly correct VLOOKUP for you, since I don't know the ... | |
| Count entries per month / Excel | 7/13/2009 |
| Q: I am trying to count how many times per month a unit is listed. My criteria will be based on unit# ... A: Dawn, Nice work! I'm glad you got a solution out of my answer. To avoid changing the ... | |
| vlookup formula with multiple array | 7/13/2009 |
| Q: I want to use the vlookup formula to retrived value to pick value from sheet 1, 2 and 3 in sheet 4. ... A: Srinvas, You have to use one separate vlookup per sheet, though you may be able to 'chain' them ... | |
| Goal Seek | 7/13/2009 |
| Q: can we add any or multiple conditions in goal seek A: Sachinkumar, Yes, you can. The goal-seek add-in is a very complex function, which ... | |
| conditional formula | 7/11/2009 |
| Q: I have Excel 2000 and want to get a formula for a cell#1(A1) that will use the data in cell#2(A2) ... A: Dedra, Something like this should work in cell A1: =IF(A2>0,A2+something,A3+something) ... | |
| Automatic data base selection | 7/11/2009 |
| Q: Using Excel 2007, I have a short data base of names with corresponding phone numbers and mailing ... A: Jake, The formula you need is the VLOOKUP function. It looks like this: ... | |
| More information in ONE cell | 7/10/2009 |
| Q: First of all I'm using Excel 2003. And I'm working on a database to register my entire DVD ... A: Endre, I also collect DVDs and have about 600 in my library. My solution to this problem is to ... | |
| multi lookup | 7/9/2009 |
| Q: Need help with Multi column lookup. I have data in Sheet 1 column B, C & D. Column B Column C ... A: Here's your formula broken into the three main parts of an IF statement: IF( ... | |
| Excel Peaks & Troughs | 7/9/2009 |
| Q: Currently use Excel 2007. Have in Column A a set of numbers (starting in cell A2) that will ... A: Russell, The way to do this is to devise a method of detecting a change in slope of the data. ... | |
| Shift | 7/9/2009 |
| Q: I have a date/Time field, I need to find out how many records fall under specific time frame for a ... A: Anil, You can use the COUNTIFS() function (if you have Excel 2007) OR, the advanced form of ... | |
| lookup value multiple times in an array | 7/8/2009 |
| Q: I will explain the problem and then how I started to solve it. I work in a school. Simple case, the ... A: Matthew, If you still need a solution for this, I may finally have developed the germ of an idea ... | |
| Counting times per month / Excel | 7/8/2009 |
| Q: Bill, I just wrote you a question. Please see below. This may clarify. Using the equation ... A: Dawn, I answered your first question before I saw the second question (this question). I don't ... | |
| Count entries per month / Excel | 7/8/2009 |
| Q: I am trying to count how many times per month a unit is listed. My criteria will be based on unit# ... A: Dawn, What you want is to COUNT the number of records that meet a set of criteria. There are ... | |
| Data Validation | 7/8/2009 |
| Q: I use Office Excel 2008 for Mac. I have a payment voucher worksheet with a list of Payees with ... A: Duncan, You can make a 'directed' or 'guided' list of this type by using one of the ... | |
| Tabulated Data | 7/7/2009 |
| Q: I have a number of tables over a number of worksheets. Each table has a list of components used for ... A: Andrew, It seems I still don't follow your explanation. You said: "The end result I would like ... | |
| Dynamic Charts in Excel 2007 | 7/7/2009 |
| Q: Hopefully you can help me out...I am by no means an expert but mainly work my way through the ... A: Vanessa, I wonder if you are properly using the term DYNAMIC CHART. To me, this means that ... | |
| Page display | 7/6/2009 |
| Q: My question seems very simple but I cannot find the answer so I dont know if it is possible. I have ... A: John, Yes, you can. Each page has a triple set of indicators that maximize, close (minimize) ... | |
| Closest match | 7/4/2009 |
| Q: I need to find the closest match in the table based on a value of another cell and then return it's ... A: Petac, Here's an answer which gives the closest match from the table, which is LESS THAN the ... | |
| multi lookup | 7/4/2009 |
| Q: Need help with Multi column lookup. I have data in Sheet 1 column B, C & D. Column B Column C ... A: A very quick look at your formula suggests that instead of: ... | |
| Filtering data | 7/3/2009 |
| Q: I have large chunk data of about 3500 rows in Excel spreadsheet. I carried out the Subtotal fucntion ... A: Tomás, Try the ADVANCED FILTER. IT has a checkbox that makes the filter COPY the selected ... | |
| how can i give a refrence field end filter data in validation list | 7/3/2009 |
| Q: Sheet1 Sheet2 Brands Models Brand Model VW JETTA VW GOLF ... A: Ercan, I don't quite follow your question, but I think maybe I understand you. let me restate ... | |
| Help on Excel | 7/3/2009 |
| Q: My company is sponsoring an organization of Small and medium companies. For a certain amount we can ... A: Rex, Excel is a math and equation-based system, and doesn't have an easy way to do the fuzzy ... | |
| multi lookup | 7/2/2009 |
| Q: Need help with Multi column lookup. I have data in Sheet 1 column B, C & D. Column B Column C ... A: You'll have to provide two input cells for your user interface. The user will have to know where to ... | |
| EXCEL 2007 formula evaluation question | 7/2/2009 |
| Q: See workbook. This is a simple bank balance application. The balance calculation fails when I enter ... A: Jim, Please write a thorough description of your logic. I can't make sense of what you are ... | |
| Referencing and displaying problem | 7/2/2009 |
| Q: I am using excel 2007 and have a two page workbook. I have on one sheet named (cat) a small catalog ... A: Carlos, The list for a data validation drop-down MUST be in a single column or row. I made ... | |
| Cleaning up information obtained in Excel format | 7/2/2009 |
| Q: I've received about 80 various files listing information for a large number of individuals. ... A: Mary, I'm afraid you are mostly stuck with the slow and tedious method, and, since this ... | |
| Push and Pop | 7/2/2009 |
| Q: I have need for storing set of data in excel worksheet (data worksheet) based on one member of set. ... A: Mike, In order for a cell to receive a value, there MUST be an equation in that cell, ready to ... | |
| Lookup and sum | 7/2/2009 |
| Q: i am trying to write a formula that says, look up the value in column A, in Tab X range X and return ... A: Tina, There are several ways to do this. You can use the DSUM() database function. It ... | |
| Creating Charts from Date Ranges | 7/2/2009 |
| Q: I have a list of data which is based on LOOKUPS, so some answers will show as #N/A. For Example: - ... A: Matt, It sounds like what you want to do is specify a date range and have the chart (graph) ... | |
| Dates in Excel 2003 | 7/2/2009 |
| Q: I'm using Excel 2003 and have a simple table with clients public liability certificates with the ... A: Josh, The simplest answer is to not apply the conditional formatting [CF] to the blank cells! ... | |
| Sumproduct function | 7/1/2009 |
| Q: I am using Excel 2003 and I'm having some trouble with the SUMPRODUCT function. The general scope ... A: Mina, I've finally thought of a possible method but can't be sure it'd work without trying ... | |
| Remove/Mark Duplicates in a column | 7/1/2009 |
| Q: I have an excel sheet which has duplicate entries in column A. I want to keep only unique entries ... A: Julia, If you have Excel 2007 there is a built-in duplicate detector in the DATA ribbon group. ... | |
| specifying separated cell ranges | 7/1/2009 |
| Q: In the generic func(arg, arg, ...) where the argument is a cell range and the delimiter between ... A: Tom, LINEST is HARDLY a 'generic' function. LINEST is one of the complex functions from the ... | |
| IF ELSE | 7/1/2009 |
| Q: Below are the two difference If conditions in my macro code ====================================== ... A: Khaja, I don't usually offer macro/VBA advice on this website, but in your case I may be able to ... | |
| Lookup from separate sheet | 7/1/2009 |
| Q: I have an Excel 2007 file with two sheets, call them A and B, each sorted by a different category. ... A: Mr./Ms/Mrs Mysterious X, The lookup table specified in the VLOOKUP must be more than one column ... | |
| excel Chart/Graph question | 6/30/2009 |
| Q: This is a chart/graph type question. I’m using excel 2003 but also have the reader for excel 2007. I ... A: Dave, It seems to me as though a bar chart isn't the best choice. You want to plot too many ... | |
| Excel 2007 Data Table Lookup | 6/30/2009 |
| Q: I am having trouble with a look-up table. Please refer to attached image file. I want to populate a ... A: Paul, Your table looks like its already working! But I'll assume that you just typed in the ... | |
| Excel to notify user | 6/30/2009 |
| Q: I have a user that I support that has a spreadsheet for his group's vacation time. He want to put in ... A: Elena, Just remember, that I can do this much faster than you, possibly. And while I'm doing ... | |
| Lookup from separate sheet | 6/30/2009 |
| Q: I have an Excel 2007 file with two sheets, call them A and B, each sorted by a different category. ... A: Mr/Mrs/Ms Mysterious X, Yes, you can do this quite easily. The thing that makes it possible is ... | |
| Data Validation Dependent List | 6/30/2009 |
| Q: I am using Microsoft Excel 2003. I want a data validation dependent list to display options e.g. if ... A: Lisa, The method you are using, INDIRECT(D15), makes the Data Validation refer to a NAMED ... | |
| copy cells in aspecified form | 6/30/2009 |
| Q: a b 1 x y 2 z m and convet them to this form a b c d 1 x y ... A: Ahmed, Still need an answer? You can TRY this, though I can't say for absolute sure if it will ... | |
| Excel Column to Row | 6/30/2009 |
| Q: =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1)) This formula works for one column, to ... A: Michelle, I may have finally gotten an idea on this, though you MUST have solved it by now! ... | |
| EXCEL FORMULA | 6/29/2009 |
| Q: I have created an attendance spreadsheet in Sheet 1 (names in column 1, 12 months across, Present or ... A: Christine, This is quite easy. On the Master sheet, add a column that sums the attendances per ... | |
| Count Ifs | 6/29/2009 |
| Q: I have four columns: A=Month, B=Name, C=Name, D=Number. I wish to count the number of times a name, ... A: Dwayne, You'll simply have to use TWO COUNTIFS, each one counting the names in one of the ... | |
| Excel issue | 6/29/2009 |
| Q: I have a master list of people with some basic data on them (exam results, attendance, etc). During ... A: Ryan, The principle should work across different workbooks. You'll have to tell Excel the ... | |
| Excel | 6/29/2009 |
| Q: I have a spreadsheet which has two sheets one with 4,000 rows of orders and the other with 1,000 ... A: Sue, There isn't enough detail for me to provide a meaningful answer. I don't know what ... | |
| Excel issue | 6/28/2009 |
| Q: I have a master list of people with some basic data on them (exam results, attendance, etc). During ... A: Ryan, You can put the VLOOKUP function on the master list to call in the details from the other ... | |
| Excel | 6/26/2009 |
| Q: Currently use Excel 2007. I have e.g 50 numbers(but will increase every week)in a column. Some of ... A: Russell, First, let me know if I have understood your problem correctly. Then, if my ... | |
| Excel | 6/26/2009 |
| Q: The following is a basic idea but I am completely open as to how it can be achieved & the format it ... A: Russell, It's taken me a while to come up with the basic idea which will make this work, but ... | |
| Excel | 6/26/2009 |
| Q: Currently use Excel 2007. I have e.g 50 numbers(but will increase every week)in a column. Some of ... A: Russell, If I may re-phrase yourYou have a column of numbers of varying length. The numbers ... | |
| Extracting Data from Database Sheet Based On Date Periods | 6/26/2009 |
| Q: I work in a contact centre, and would like to be able to pull some information from a database sheet ... A: Matt, It would seem that you want to SUM some field in a number of records which fall between a ... | |
| Forecast | 6/26/2009 |
| Q: I am a civil engineer and calculate capacities of foundations with depth. I use the forecast ... A: James, It seems to me that if everything is linear, you can use a simple Y=MX+B function to ... | |
| excel formula | 6/25/2009 |
| Q: I'm trying to count how many calls a person took. I have an excel sheet with the call detail in ... A: Jeromy, You want a list of Unique Names! If you are using excel 2007, there is a menu command ... | |
| excel | 6/25/2009 |
| Q: Hey Bill, I have almost the exact same problem as this article - ... A: Andy, There is a similar type recorder called the Macro Recorder. It will copy every keystroke ... | |
| Excel Dates | 6/25/2009 |
| Q: I am hoping you can help me. I am doing a project for a section of our sales. Basically what I am ... A: Sara, There will never be a last question, but don't worry. I am happy to answer them all. ... | |
| formula challenge | 6/25/2009 |
| Q: How do I keep the formula from changing the first number when I drag it down the column? =B1+A1 ... A: Kay, Yes, this is basic! In fact this is a fundamental skill, which a lot of people just don't ... | |
| Automatic expansion of lists | 6/25/2009 |
| Q: I'm working in Excel 2000 with several two worksheets. What I have so far: One worksheet is a ... A: Jim, Excel can't 'automatically insert a row', populated with the appropriate equations, like ... | |
| excel 2003 formula question | 6/25/2009 |
| Q: I have an Excel 2003 worksheet with employee names, date of hire and date of birth (among other ... A: Holly, Sure its possible. All you need to do is be able to detect each condition, and then put ... | |
| sorting 3 columns of cells | 6/25/2009 |
| Q: I have an alphabetical list of satellite TV programs (column 1) followed by area description ... A: Mark, There is a sorting routine on the Excel 2007 Home Ribbon, on the right, or under DATA -- ... | |
| Excel value compare | 6/24/2009 |
| Q: I am having a problem finding mismatched values in 2 spreadsheets. eg. Spreadsheet A User ID First ... A: Mark, What I would do to correct this mess is to create a temporary sheet that combines both of ... | |
| Listing array data: | 6/24/2009 |
| Q: I have a couple of questions: The first is, if I have an array that only has values in some of the ... A: Eric, Question 1: There are two methods: 1) Use SORT to sort the data table so all the blank ... | |
| charts | 6/24/2009 |
| Q: Bill, I want to create a chart that shows weekly sales over a 12 month period. I tried doing this by ... A: Yehuda, Read my answer again. It tells you just what to do. The 12 values MUST be in adjacent ... | |
| Lookup? Match? Offset? | 6/24/2009 |
| Q: Excel Time Management Spreadsheet Formula Question.... I’m OK with Excel, but never tried a formula ... A: Renzo, It is possible, but it's COMPLEX. The answer involves the MATCH(), INDEX(), and OFFSET() ... | |
| vlookup doubt | 6/24/2009 |
| Q: i had 2 excel sheets one is data base and other is transmittal. the format of data base is: sdno ... A: Rony, I'm sorry, when I read and answered your email, I didn't see the question on AllExperts. ... | |
| Excel Date Format - follow up | 6/24/2009 |
| Q: Were you able to figure this question out [see below]? I need the same type of date format on a ... A: Michelle, The empty date cell you are trying to create can't be done the way you wish... at ... | |
| vlookup doubt | 6/24/2009 |
| Q: i had 2 excel sheets one is data base and other is transmittal. the format of data base is: sdno ... A: Rony, As long as there is ONLY ONE RECORD which has the two matching values SDNO & REV, in the ... | |
| Excel | 6/24/2009 |
| Q: I am pretty new to Excel, so i don't know if this is a Macro or VBA question. Forgive me it it is. ... A: Aleksander, Create a column next to the column you want to change. Then fill it with the ... | |
| cell from one workbook to another | 6/24/2009 |
| Q: i am using ='[BANKRECONCILIATION]JUNE 09!$M$24 to have data entered into bankreconciliation ... A: Teresa, You can use some clever method to CALCULATE the address ... | |
| Excel value compare | 6/24/2009 |
| Q: I am having a problem finding mismatched values in 2 spreadsheets. eg. Spreadsheet A User ID First ... A: Mark, I'm not certain what 'values' you are actually trying to find. It sound like what ... | |
| Isolate differeing data from two spreadsheets | 6/24/2009 |
| Q: A spreadsheet summarising a database is sent to me weekly. It lists details about employees from our ... A: Martin, Yes, its possible. The thing that makes it possible is the Unique Key for each ... | |
| Is It Possible To Extract Data From Networked Hubs Into One Worksheet? | 6/23/2009 |
| Q: Hermanson - Is it possible to extract data from other hubs which are networked? I wanted to link ... A: Alex, It is possible to extract data from closed workbooks which are located on other servers in ... | |
| charts | 6/23/2009 |
| Q: Bill, I want to create a chart that shows weekly sales over a 12 month period. I tried doing this by ... A: Yehuda, Its hard to say what you're doing wrong because you didn't describe the results you got. ... | |
| region per month | 6/23/2009 |
| Q: I am hoping you can help me. I am doing a project for a section of our sales. Basically what I am ... A: Sara, Possibly what is wrong is your use of "MAY" and "557" inside double quotes. The use of ... | |
| complex numerical sort for mixed part numbers | 6/23/2009 |
| Q: I would like to know if there is a formula (Excel 2003) that would allow me to do a numerical sort ... A: Steve, Does "The alpha "0" shall be considered as numeric zero" mean that the LETTER O, should ... | |
| Linking and Conditional Formatting. | 6/23/2009 |
| Q: I have a basic excel spreadsheet. One of the columns has conditional formatting to change colours ... A: Paul, What you want might be possible but I don't understand IT because you use too many ... | |
| Vlookup for text strings only | 6/23/2009 |
| Q: using Excel2007--doing a vlookup between two massive spreadsheets with text only. All I want to do ... A: Zully, A VLOOKUP result of NA typically means that VLOOKUP can't find the text you are looking ... | |
| compare two columns in Excel | 6/22/2009 |
| Q: I want to compare two columns in Excel. First column has 1500 rows with numbers and second column ... A: Anna, You can use a variety of methods. I would use COUNTIF, which would not only tell you if ... | |
| Spreadsheet into Report | 6/22/2009 |
| Q: I have this spreadsheet full of data and I need to set up the report of results by site including ... A: Racine, You can send me the actual spreadsheet, but be aware that I will NOT write the ... | |
| Cross Reference Lookups | 6/22/2009 |
| Q: I have the following spreadsheet: Alpha,Date,Value,Num A,2009-10-10,100,1 B,2009-10-10,99,2 ... A: Milly, If there is only ONE combination of each pair of Alpha & Num, you can use DGET() to find ... | |
| Conditional Formatting | 6/22/2009 |
| Q: I am using conditional formatting on a spreadsheet. I have set it so that cells where value is ... A: Angelique, If your version of Excel is THAT old, I may not be able to see the problem either. ... | |
| linking sheets | 6/22/2009 |
| Q: suppose i have two sheets. in first i put all the raw material and their rate and in second sheet ... A: Nipun, lets call the first sheet the DATABASE and the second sheet the INVOICE. You can use ... | |
| percentage formulas in sxcel | 6/21/2009 |
| Q: Hope this isn't too simple, but I'm extremely basic in my abilities with EXCEL. Using Office Excel ... A: Joe, If you have the question, then it isn't too simple. Write an equation in Column F ... | |
| Excel Formula | 6/20/2009 |
| Q: I have 3 spreadsheets of varying lengths in the same workbook. I need to verify that a ... A: Lois, There is a function (or two) designed exactly for this. You can use either VLOOKUP() or ... | |
| Excel in a Citrix Environment | 6/20/2009 |
| Q: I would like Excel data lists to appear in a Citrix environment. I have created data lists in Excel ... A: Tanya, I'm sorry but I have no idea why you'd have this problem. This question is more properly ... | |
| Multiple Rows to Single Row with Multiple Columns | 6/19/2009 |
| Q: An example of the data I have is: MallCode StoreName A1 Store1 A1 ... A: Brian, Here's what I'd do (if you still need a solution after all this time): Create a grid ... | |
| Sum rows with common data | 6/19/2009 |
| Q: I have a spreadsheet with numbers ranging from 1-100 down column A. There is about 800 lines in ... A: Roshan, You will be surprised at how easy this is! Just use SUMIF() in column C. Use column A ... | |
| Excel Auto-Population | 6/19/2009 |
| Q: I have a multi-page spreadsheet that I use to generate reports. The first page is a checklist that I ... A: Barry, Certainly you can. What you need is to use one VLOOKUP equation for every field ... | |
| Conditional Formatting | 6/19/2009 |
| Q: I am using conditional formatting on a spreadsheet. I have set it so that cells where value is ... A: Angelique, Conditional Formatting (CF) should not behave that way. I'm not sure why you are ... | |
| Excel Solver: Maximizing the minimum value from a selected set | 6/19/2009 |
| Q: I am using Excel 2003 and the Solver addon. First, the context of the problem. Imagine that a ... A: Steven, I'm sorry that I haven't answered this question earlier. Hopefully by now you have ... | |
| shading entire row based on cell value | 6/18/2009 |
| Q: Sorry, I know this isn't all that complicated...in fact, I've done it once before, which is why it's ... A: Tina, Careful, you might hit someone with a flying computer! There are better ways to wreak ... | |
| Import hyperlink into Oracle database | 6/18/2009 |
| Q: I am working on a project to move a large excel spreadsheet into an Oracle table. (I am using ... A: Dale, I am sorry to have neglected your question for so long, and then to come back like this ... | |
| Excell if/vlookup formulas | 6/18/2009 |
| Q: Good morning, I have an Excel file with 2 worksheet, one with information downloaded from SAP and ... A: Sylvie, I don't seem to understand your problem. You say you have entered 3 formulas, each in ... | |
| Tabulated Data | 6/18/2009 |
| Q: I have a number of tables over a number of worksheets. Each table has a list of components used for ... A: Andrew, If you still need an answer to this, I have a few comments, and questions. If I ... | |
| Excel Conditional Formatting | 6/17/2009 |
| Q: Bill, Using Conditional Formatting via the formatting drop down, can I check if the cell is equal ... A: Alan, Yes, you can check for more than one condition. You must use the FORMULA IS mode (that's ... | |
| SUMPRODUCT Formula | 6/17/2009 |
| Q: I am trying to automate a data-gathering Spreadsheet that I spend hours on each month. In a ... A: Luke, I should have caught the mistake you'd made with the date-criterion.... you had ... | |
| Excel Data manipulation | 6/17/2009 |
| Q: Here's my question. I have a database on the 404 people in our battalion. It's our master list and ... A: Marty, People always seem to want to make duplicate copies of their data! I'd ask WHY you want ... | |
| NEED Help with a formula to delete multiple rows of data | 6/17/2009 |
| Q: I need to scrub some data, I've got multiple rows with a single part number, I need to delete rows ... A: Noami, You can't delete rows with a formula, and I don't see how a pivot table would help ... | |
| copy paste format | 6/17/2009 |
| Q: Hi every time i copy paste a column of dates from one workbook to an other the date changes from ... A: Maxine, I cannot come up with an explanation for this. I doubt its a virus! Were the ... | |
| Excel 2007 Formula | 6/17/2009 |
| Q: Column A is the week numbers of the year. ISO defines the week to start on Mon and end on Sun. ISO ... A: Tari, You can begin to approach this problem by asking if the WEEKDAY(date,1) of 1/1/YY is <6 ... | |
| IF & AND Statements in excel | 6/17/2009 |
| Q: I am trying to create an IF & AND statement in excel and finding it extremely frustrating. ... A: Mo, Ummm, I'm Bill, not Tom. Are you sure you are talking to the correct Expert? Given my ... | |
| Cumilative | 6/17/2009 |
| Q: Respected Sir, My question is how can I create a cumulative function in Microsoft Excel 2003,here i ... A: Ravi, A Cumulative equation is simply the sum of the number above it, plus the new number to ... | |
| VLOOKUP Variable Table Reference | 6/17/2009 |
| Q: I need to make the "Table_Array" variable on the VLOOKUP function. I use it to search based on the ... A: Charlene, The method to make an array become variable is to replace the array with an OFFSET ... | |
| shading entire row based on cell value | 6/16/2009 |
| Q: Sorry, I know this isn't all that complicated...in fact, I've done it once before, which is why it's ... A: Tina, You are so close to having it correct. By the time you get my answer, you'll have figured ... | |
| Extract data from table according to a time interval | 6/16/2009 |
| Q: I ve the following data in excel sheet format A B C 00:10 qq ww ... A: EliaJozaif, Create a new table with a column that lists the 20-minute intervals for which you ... | |
| Vlookup | 6/16/2009 |
| Q: I need to lookup a value and then see if it matches a range in the next columns - example: lookup ... A: Jason, Why should the return value be X? There is no value of 0.8 in the table anywhere. I'd ... | |
| IF Function | 6/16/2009 |
| Q: I have a spreadsheet that looks like this: 123 $15.00 123 $280.00 548 $22.25 123 ... A: Misty, Use the SUMIF function instead. Refer to 123 in an external cell; that way you can add ... | |
| IF & AND Statements in excel | 6/16/2009 |
| Q: I am trying to create an IF & AND statement in excel and finding it extremely frustrating. ... A: Mo, I rarely get such a clear question, where the questioner knows exactly what they want and ... | |
| FORMULA | 6/15/2009 |
| Q: Cell F24 IS BLANK Cell G24 IS BLANK Cell AB24 CONTAINS FORMULA =AVERAGE(F24:G24) PLEASE PROVIDE ... A: Dan, I thnk what you must mean to say is that you want cell H24 to behave as follows: 1) it ... | |
| vlookup | 6/15/2009 |
| Q: I need to evaluate a spreadsheet. I need to check for a single text letter in column one. When there ... A: MJ, If you don't know beforehand which letters there will be, then you need to check for them ... | |
| grab every fourth cell's value | 6/15/2009 |
| Q: Bill, Column A is a growing column of data. In column B, I want to grab every fourth cell value ... A: Dave, Try this: =IF(INDIRECT(ADDRESS(C1,1))=0,"",INDIRECT(ADDRESS(C1,1))) I assume ... | |
| Need help with rental charge marco/formula | 6/15/2009 |
| Q: My current spreadsheet is setup like the following: C column: Build Date D column: Date to go on ... A: Kevin, If you still need an answer to this, then here are my thoughts. First of all, the ... | |
| grab every fourth cell's value | 6/15/2009 |
| Q: Bill, Column A is a growing column of data. In column B, I want to grab every fourth cell value ... A: Dave, There are two good ways to do this. I recommend method 1 because it is a bit easier to ... | |
| splitting text | 6/14/2009 |
| Q: I would like to know how to extract the zip code(s) from text like the sample below. Dothan - 36301 ... A: Venkat, You'll need a series of equations in separate cells. Figure out what is the largest ... | |
| Combinations | 6/13/2009 |
| Q: Sir- I am not certain whether or not this is something that can be accomplished in Excel or not. ... A: David, There isn't any Excel function which can generate these specific combinatorial sets. ... | |
| FUN TIMES remove certain text | 6/13/2009 |
| Q: I have the following list of current and prior Exec's and Board members in cell A1: McNealy, ... A: Russ, If you still need an answer to this, I'll need to know: How is a non-board member ... | |
| Data Charts | 6/12/2009 |
| Q: I am making a sheet with individuals and and their scores, from those scores I am making a cell with ... A: J Madden, If you still need an answer to this, I have some thoughts. Your question is, ... | |
| Pivot table (blanks) | 6/12/2009 |
| Q: I have created a pivot table and blank cells have (blank) in them. I want to keep the blank cells ... A: Caroline, The Excel default is to write the word BLANK so you can clearly identify what is ... | |
| Can VLOOKUP work on multiple sheets? | 6/12/2009 |
| Q: I'm trying to get two results from excel. A)To get a VLOOKUP result from multiple worksheets (to ... A: James, Any given VLOOKUP can only operate on ONE worksheet at a time. Since you'd know which ... | |
| Multiple Conditions - How? | 6/12/2009 |
| Q: I (teacher) have created a workbook to capture assessment scores for my learners (9 learning areas). ... A: Ivan, The solution to this is entirely in the realm of Logical Functions. What you need to do ... | |
| Overtime Calculation Formula | 6/12/2009 |
| Q: I have a data like Time In Time Out Regular hours no.hoursworked overtime 08:00am 20:00Pm ... A: Ravi, You need TWO equations, one to calculate regular time and one to calculate over time. ... | |
| Possible Combinations from a list | 6/12/2009 |
| Q: I absolutely stuck!! Any assistance is greatly appreciated. I have a list of numbers 1 through 49, ... A: Michael, I've gotten this question many times before, and haven't been able to find a reasonable ... | |
| Combine if statment with date | 6/12/2009 |
| Q: First of all thanks in advanced for your help. My question is that I have an excel sheet with the ... A: Firas, What you need is to extract the MONTH number from the date, and subtract it from the ... | |
| Convert Text in Formula | 6/11/2009 |
| Q: I want to convert a text that I concataneted to form a formula in a formula, let me exemple it. Cel ... A: Leandro, I don't know how to convert text into a formula, without a very complex conversion ... | |
| Date Range Specifications | 6/11/2009 |
| Q: I'm in desperate need of assistance. I'm setting up a worksheet that shows employees and the dates ... A: Teej, You can probably accomplish this, but the complexity involved depends on how your main ... | |
| SUMPRODUCT Formula | 6/11/2009 |
| Q: I am trying to automate a data-gathering Spreadsheet that I spend hours on each month. In a ... A: Luke, Your SUMPRODUCT formula looks okay, up to a point... it's missing a key term! All three ... | |
| EXEL if statement | 6/11/2009 |
| Q: I need an if statement that will reconize a value of "x" in a chosen cell and and when the X is ... A: Gary, What you're asking is to look up a price from a catalog-like data table, using multiple ... | |
| Nested And Statements | 6/11/2009 |
| Q: I need help with my formula below. When I run it, it performs the first part but not the second ... A: Ruben, Your equation looks correct, except that you have lots of extra () pairs that you don't ... | |
| EXEL if statement | 6/11/2009 |
| Q: I need an if statement that will reconize a value of "x" in a chosen cell and and when the X is ... A: Gary, =IF(chosen-cell="X",another-cell,"") Place this equation in the second 'another cell' ... | |
| Copying Text | 6/11/2009 |
| Q: I am trying to create a master database from a large fundraising event we have each year. People ... A: Natalie, There are several functions which can be used to 'look up' information from one sheet ... | |
| transpose data | 6/11/2009 |
| Q: I download csv files from ebay that show the details of sales I have made. I have to send this ... A: Alex, Yes there is a way to do this. What you need is to pick one column in the first sheet ... | |
| Excel Files Comparison | 6/11/2009 |
| Q: I receive a data file on a daily basis, most of the lines remain the same, but some are added each ... A: Erica, I'd ask why you can't sort them? You can always get them back the way they were before ... | |
| drop down list in excel | 6/10/2009 |
| Q: I am making a spreadsheet and I want drop down menus for three columns consecutively, but I want the ... A: Sarah, Quite Easily Done! {QED) I get this question so often that I have prepared a canned ... | |
| Copying Text | 6/10/2009 |
| Q: I am trying to create a master database from a large fundraising event we have each year. People ... A: Natalie, If your intention is to abandon the 12 different worksheets, I'd suggest a simple cut ... | |
| Calculating stats over time | 6/10/2009 |
| Q: I've created an excel document, in each worksheet I have a number of activities that I have ... A: Eoin, If your data table includes the DATES, then you can create a series of functions which ... | |
| Donor data | 6/10/2009 |
| Q: I work with donor related information and it's all on an excel 2007 worksheet. The data I have are ... A: Sarah, The answer to this depends on exactly how your data is organized. Do you have a table ... | |
| intersect of two lines | 6/10/2009 |
| Q: I am having problem with getting the intersect points of 2 lines. I am using Excel 2007. From the ... A: Hoy Yen, There isn't an INTERSECT function, which is too bad. You'll have to create your own, ... | |
| Sorting By Section | 6/10/2009 |
| Q: I work in a clothing store and want to figure out a way to sort by section. We have divided our ... A: Kyle, Neither sorting nor filtering is an 'automatic' operation... these must be done from the ... | |
| weekly chart and graphs | 6/9/2009 |
| Q: I was hoping you can help me find an efficient way to do a weekly task. Every week I get an excel ... A: Brian, Repetitive tasks are best improved by coding the menu steps into a MACRO. I don't ... | |
| Microsoft Excel Spreadsheet | 6/9/2009 |
| Q: OK, so I have an excel spreadsheet that has multiple worksheets in it. Each worksheet has a ... A: Mike, What I'll do is suggest several methods by which you can create address of the type you ... | |
| Excel dropdown list ... | 6/9/2009 |
| Q: How to have a dropdown list of usernames that when a name is clicked, inserts that name into another ... A: Robin, If you select a name from a dropdown list, and want it to be part of another cell, then ... | |
| Excel 97 macros | 6/9/2009 |
| Q: Macros I wrote in Excel 97 in Windows XP on my Dell Dimension 4600 executed without error. I ... A: Charles, Sorry to say, I have no idea what could be causing this problem But anything involving ... | |
| Calculation | 6/9/2009 |
| Q: I am a novice in Excel formula, please help to see if it is possible to make a calculation table of ... A: Karsten, You can't do exactly what you're asking, and here's why: a cell can either be an ... | |
| sum of the dollar sign format only | 6/9/2009 |
| Q: how I can sum of the dollar sign format cells only excluding the other currencies format A: Bahaa, I don't think there is a way to do this. But you might try this experiment: Use the ... | |
| circular reference problem | 6/8/2009 |
| Q: Here is what currently happens manually. My system runs and calculates profits. It also calulates ... A: Dave, I'm afraid to say that nothing is obvious about your question. What. for example, is ... | |
| Sum Last X number of bars variable | 6/8/2009 |
| Q: I currently use the following formulas to sum the last x number of rows in a single column. (Excel ... A: Dave, This is easy to do using an advanced function called OFFSET. OFFSET defines a range ... | |
| Index and Match not recognizing any value under 1 and Indexing multiple tables | 6/8/2009 |
| Q: I am trying to help a colleague in creating an automatic form where you can drop down to two ... A: Bill, I took your formula apart to analyze it, see below. It looks okay to me, and as you say, ... | |
| Microsoft Office Excel Functions | 6/8/2009 |
| Q: My question is regarding Mircosoft Office Excel 2007.I am a student, and my homework requires me to ... A: Andrew, There is a function which can calculate the NUMBER of combinations, but there isn't an ... | |
| Calculating time worked from clock in & out hours. | 6/8/2009 |
| Q: I've attached the spreadsheet I made with the formula I'm using. The problem is when the clock IN ... A: Craig, You suspect right. Write yourself an IF statement that compares the two times. ... | |
| Excel - finding the right formula | 6/8/2009 |
| Q: I have a set of data that show the results of a test: q1 q2 q3 q4 q5 q6 ... A: Thomas, This is an interesting logic problem. I can see the start of several possible ... | |
| Average Array Formula - multiple criteria | 6/8/2009 |
| Q: I am using the following formula with great success: {=AVERAGE(IF(C2:C1000="John ... A: Joe, I usually avoid array formulas whenever possible! If you have many of these, they run ... | |
| excel lookup | 6/8/2009 |
| Q: i have an excel database which includes a product name in column a and the price of the product in ... A: Chris, Do you still need an answer to this? I have a rather simple way to do it, but I don't ... | |
| Dynamically populate rows based on a string in a field above | 6/8/2009 |
| Q: I wonder if you can help me please. I have a requirement to automatically populate rows if there is ... A: Ashleigh, This is a difficult problem for 'equations only'. A macro/VBA solution would be much ... | |
| Importing updated sheets with moving cell ref | 6/8/2009 |
| Q: I have a spread sheet with 2 pages. Some cells on sht1 are linked to cells on sht2. When receiving ... A: Keith, It is probably better to look up the information from Sheet 2 onto sheet 1 using one of ... | |
| Sorting data | 6/7/2009 |
| Q: Basically I need a data sheet that I can update daily and then sort it as well. This sheet will have ... A: Paul, Sorting a table with equations in it is done all the time and should work perfectly. Your ... | |
| Excel 2007 | 6/7/2009 |
| Q: I am new to Excel and there is part of my assignment where I have to Group Sheet 1 and Sheet 2 ... A: Kim, I suspect you are in school and this is part of a homework problem. Its an odd ... | |
| Pulling information from one worksheet into another | 6/5/2009 |
| Q: I have one worksheet which contains which contains a listing of action items (Col A - Description, ... A: Stephanie, If, as you say, "The idea is to simply be able to update one spreadsheet", then why ... | |
| Curiosity on behalf of an Excel novice | 6/5/2009 |
| Q: Is it possible to derive a formula which would assess all the information within a column to arrive ... A: James, If I may re-phrase your question, it sounds like you have a column of numbers and you ... | |
| Populate matrix from column data | 6/5/2009 |
| Q: I'm trying to convert 3 columns into a matrix where the first column will be the row names in the ... A: Ron, If you actually have the text "R1", "R2"... etc and "C1", "C2".. etc, in columns A & B, ... | |
| Excel Drop Down Lists | 6/5/2009 |
| Q: I have been working on creating a budget and want to create a drop down list to select a month and ... A: Colt, As I say often, with Excel, ANYTHING is possible. Your concept for the monthly ... | |
| simple date calculation exclude weekends | 6/5/2009 |
| Q: I'm looking for a formula (or whatever you suggest) that will return a date but will IGNORE ... A: Jennifer, If you are using Excel 2007, there are two functions built in: NETWORKDAYS () is the ... | |
| Excel Complex Logic | 6/5/2009 |
| Q: Bill, How can excel (Office 2003) be used to implement an exclusive OR of two variables (x,y)? For ... A: Joe, By 'this kind of material", I assume you mean the Logical Functions. I'm not able to ... | |
| Stripping Characters from a Cell | 6/5/2009 |
| Q: Hey Bill, this is my first time on these forums and I greatly appreciate any guidance you can ... A: Steven, I took a second look at what I thought was a touch question, and solved it in less than ... | |
| Excel for Mac Help | 6/5/2009 |
| Q: I have a list of items in one column and a list of associated cost prices in the adjacent column. ... A: Garry, This is a common use for Excel. Its pretty easy to create and you can add lots of user ... | |
| Accumulated Vacation days | 6/4/2009 |
| Q: I have started a new job and I get 0.5 days of vacation time for each week I work. I wanted a ... A: Jimmy, This is pretty easy. Create a column of dates seven days apart. Start with 6/1/09 in the ... | |
| A simple problem that i can not figure out relating to IF. | 6/4/2009 |
| Q: I have a basic problem which while simple on paper seems difficult to implement. I have a column in ... A: Richard, You're right; it's simple on paper but difficult to implement. The process you've ... | |
| Search | 6/3/2009 |
| Q: My boss has asked me to input all of our work library into a spread sheet then set up some sort of ... A: Matt, Look into the FILTER function, or possibly the ADVANCED FILTER. Reading about this ... | |
| creating a timesheet covering the past 2 years | 6/3/2009 |
| Q: I’m new to excel and trying to make a spreadsheet to track and audit my hours at work. I need help ... A: Ruth, You have it already figured out well. Your ideas for the columns you need are right on. ... | |
| tables multiple | 6/3/2009 |
| Q: I have a set of four tables, each contains 4 coloums and 10 rows. The row are the names of the ... A: Jenny, You can apply the RANK function to the results column, to find out who is in first place, ... | |
| Drop Down List Help | 6/3/2009 |
| Q: I'm at my wits end. I do not know if it is possible to do without the use of a macro but I would ... A: Stephen, Yes, this is possible. Apply the ADVANCED FILTER to your database. The ADVANCED ... | |
| adding # plus cell contents to reference a cell | 6/3/2009 |
| Q: I am trying to add two specific cells, that are constant, based on a variable. I'm confusing myself. ... A: Albert, That's a good solution and a clever use of IF to choose between two different VLOOKUPS. ... | |
| Lookup question | 6/3/2009 |
| Q: I would be much appreciated if you can help and solve my problem as per list attached. Thanks A: Fk Wan, It seems to me as though this is a PERFECT application for a pivot table. Put the ITEM ... | |
| Move data from multiple excel worksheets to one worksheet | 6/2/2009 |
| Q: I have an excel workbook that has 80 tabs(worksheets) which include multiple columns. Each tab has ... A: Bob, You can use the INDIRECT(ADDRESS(parameters)) function combination to make this happen ... | |
| formulas on excell professional | 6/2/2009 |
| Q: i have a sheet with various columns of data. on a seperate sheet i want to run a formula that would ... A: Jacques, I have an excel workbook from you that apparently relates to this question. I'm ... | |
| Excel 2007 | 6/2/2009 |
| Q: I maintain a training matrix for 45 employees on sheet 1. On sheet 2,3, and 4 I would like to break ... A: Chris, This sounds like an application for a pivot table. It can summarize your whole database ... | |
| Excel 2007 dropdown rules | 6/2/2009 |
| Q: In Excel 2007, I would like to have a user choose from a dropdown menu a list of machines (I have ... A: Chris, Use a VLOOKUP() with the dropdown selection as the input-value. Build a small table ... | |
| Excel Lookup & return value? | 6/2/2009 |
| Q: Here's hoping you can help. I have a workbook with multiple sheets (only 5 of them). On the first 4 ... A: Jessica, Thanks for answering some of my questions, but you missed the most important ones! ... | |
| Lookup question | 6/1/2009 |
| Q: I am having some trouble with a lookup excel function. I have 2 worksheets: 1: School Name Student ... A: Matt, I suspect that you're not telling me the whole story, so my answer may not be what you ... | |
| adding # plus cell contents to reference a cell | 6/1/2009 |
| Q: I am trying to add two specific cells, that are constant, based on a variable. I'm confusing myself. ... A: Albert, Thanks for the clarification. I understand at least part of what you're trying to do. ... | |
| Excel Function Error - #value | 6/1/2009 |
| Q: I have entered the following formula: =SUM(IF('Non Card Outage Detail'!$C$32:$C$1348=$M$4,IF('Non ... A: Mona, I did a little bit of experimentation with this. It appears as though formulas of this ... | |
| Search & Replace '= | 6/1/2009 |
| Q: Bill, I used an answer provided by you to avoid having to change formulas to absolute references ... A: Tim, I am so embarrassed by this, I don't know what to say. I'm sure that I have used this ... | |
| excel formula | 6/1/2009 |
| Q: please provide formula to calculate below calculation there is 1000 row data in A and B columns, In ... A: Parth, This is a perfect application for the OFFSET function! The OFFSET function creates a ... | |
| Excel Lookup & return value? | 6/1/2009 |
| Q: Here's hoping you can help. I have a workbook with multiple sheets (only 5 of them). On the first 4 ... A: Jessica, Although you addressed your question to "Bob", I'm Bill but will take a crack at it ... | |
| Using ISERROR() function in a if/or statement | 6/1/2009 |
| Q: I have the following formula in cell O5: ... A: Manuel, Yes, I am afraid that your formula is incorrect! You can't use ISERROR on a range. ... | |
| adding # plus cell contents to reference a cell | 6/1/2009 |
| Q: I am trying to add two specific cells, that are constant, based on a variable. I'm confusing myself. ... A: Albert, Please take another crack at explaining this. Avoid explanations which are based on a ... | |
| Column number | 5/30/2009 |
| Q: I need to find the closest match in the table based on a value of another cell and then return it's ... A: Petac, Do you still need an answer to this question? It's been in my stack for a long time, so ... | |
| Column number | 5/30/2009 |
| Q: I need to find the closest match in the table based on a value of another cell and then return it's ... A: Petac, Presumably you are looking across a single ROW for the closest match. Use the ... | |
| Lookup first few numbers in a table and fetch corrosponded value | 5/30/2009 |
| Q: Please help me in finding the solution for my problem. here is the scenario. i have Two tables, ... A: Sami, I don't think you can do what you wish. The numbers from the two tables do not match at ... | |
| Auto update a date after a certain time | 5/30/2009 |
| Q: I am trying to find a formula where the date doesn't change automatically after midnight. I work in ... A: Kurt, Use the NOW() function instead of TODAY(). NOW() includes both the date and the time. ... | |
| formula to calculate pay off DATE | 5/30/2009 |
| Q: if i have the principle, payment amount, and interest rate for a creditor, how do i calculate the ... A: Anita, I'm sorry... but I am not a financial analyst! I looked through the entire list of ... | |
| Follow up question | 5/29/2009 |
| Q: I got your following answer where you said that you would answer the second copy of the same ... A: Dilip, This question has been unfortunately neglected too long. Now that I see it, there is no ... | |
| referencing one data series to another labeled with a gradient | 5/29/2009 |
| Q: Bill, I have performed a sensitivity analysis for a particular parameter in a couple different ... A: Amber, Its a little difficult to grasp your vision without seeing an attached picture, but I ... | |
| Further clarification on an answer you provided previously. | 5/29/2009 |
| Q: At this link, http://en.allexperts.com/q/Excel-1059/2009/2/Excel-2153.htm you provided an answer ... A: Kamal, The term 'shadow form' is of my own making, so it's not surprising you can't find out ... | |
| Colored rows | 5/29/2009 |
| Q: Your line breaks <br>'s are showing up in your text. Not sure how it's happening since usually ... A: Kendra, There is some odd text before youris this directed to me??? , or is there an error on ... | |
| Database | 5/29/2009 |
| Q: Just found this site---GREAT find and thanks in advance!!! I know I have tons of questions, but my ... A: Rosa, A messy database can be a big problem. There are ways to avoid this and give your users ... | |
| Excel | 5/29/2009 |
| Q: good morning. Somehow my Excel app is set to automatically divide all numbers entered in cells by ... A: Jambo, It sounds as though you've applied some odd modification of the percent format to all ... | |
| Excel 2003 | 5/29/2009 |
| Q: I write a formula using the IF function, when it reaches up to 7 statements, the computer will not ... A: Victorino, According to your IF statement, every single value of A26, no matter what it is, ... | |
| Excel/SUMIF | 5/28/2009 |
| Q: I have a follow-up question to this one (http://en.allexperts.com/q/Excel-1059/SUMIF-2.htm). I got ... A: Kate, The expert who answered your question gave you an ARRAY formula as a solution! I never ... | |
| transferring data from one sheet to another | 5/28/2009 |
| Q: I am trying to find a way to populate excel spreadsheets from previous excel spreadsheets that I ... A: Traci, I have developed a neat, automated way to do this for clients of my business, so it ... | |
| Web form into Excel | 5/28/2009 |
| Q: Bill, Thanks for your time and expertise. I am trying to get data from a web form to import ... A: Todd, The link you included sent me to a blank form that had questions for a user to fill out, ... | |
| Excel 2003 | 5/28/2009 |
| Q: I write a formula using the IF function, when it reaches up to 7 statements, the computer will not ... A: Victorino, There is a limit to how many levels of nesting are allowed in an IF statement, and ... | |
| Due Amounts by week | 5/28/2009 |
| Q: I have an excel sheet with due dates that are in a certain column with the amount that should be ... A: Chris, Of course it is possible! But I think I have confused you (because I missed something). ... | |
| Using Drop Downs as Filters in Excel | 5/28/2009 |
| Q: I have a complex list of data points that I need to simplify a search from. Basically, I have four ... A: Michael, There might be a much easier solution than drop down lists and such. Have you tried ... | |
| Data Validation | 5/28/2009 |
| Q: I want to have 2 Data Validation criteria in one same cell so that the user will get message A if ... A: Dolores, What you really want is to use the Whole Number Only model of data validation. You ... | |
| How to segregate a specific word from sentences | 5/28/2009 |
| Q: I just wanted know that is there any way to separate a word from a sentence? i will explain you what ... A: Dheeraj, I THINK that in between the time you sent in your question, and now, that SOMEWHERE I ... | |
| Date & Time Calculation Question. | 5/28/2009 |
| Q: How can i minus One Date to Another in Excel? e.g : entry date: 98-01-11 exit date: 98-04-16 ... A: Nikmat Subtract the two dates with a minus sign, just like you would subtract any two numbers. ... | |
| Using Drop Downs as Filters in Excel | 5/27/2009 |
| Q: I have a complex list of data points that I need to simplify a search from. Basically, I have four ... A: Michael, You can do this with validation-list drop-down boxes followed by a multiple-criterion ... | |
| Consolidating Multiple Company Data | 5/27/2009 |
| Q: I am using a Microsoft Query to pull in the General Ledger account balances for several different ... A: Nancy, I believe that you need to use a multiple-criterion lookup. It would be best if you'd ... | |
| Sorting Data Range with one Drop Down List | 5/27/2009 |
| Q: Data Columns: In A1-Title B1-Date C1-Notes D1-Page Records: In A2-Days of Thunder B2-4/15/2000 ... A: Stephen, It IS a simple task. All you have to do is apply the FILTER (called the AUTOFILTER in ... | |
| Cell Referencing | 5/27/2009 |
| Q: I'd like to be able to reference a cell "value" using a formula like the following. =P(1+M2) the ... A: Craig, There are several methods that Excel provides which allow you to compute an address. ... | |
| excell | 5/27/2009 |
| Q: i want a programme that help me ,when i enter a data in sheet with name of a person i want that date ... A: Vijay, Do you still need an answer to this question? It's been in my stack for a long time, so ... | |
| Days and Times spanning midnight | 5/27/2009 |
| Q: I have been looking at this for a while. Row J is Start Time Row K is End Time Row L is Duration, ... A: Alan, The key to understanding how to calculate with dates and times is to understand the ... | |
| Color fill tow cells from one value. | 5/27/2009 |
| Q: I've created a spreadsheet for my golf tournament to keep track of each golfer's scores. When I ... A: Ron, Evidently you have applied Conditional Formatting [CF] to the score cell to make the color ... | |
| Due Amounts by week | 5/27/2009 |
| Q: I have an excel sheet with due dates that are in a certain column with the amount that should be ... A: Chris, Close but no cigar! You have: =SUMIF(E2:E93,(E2:E93<=WEEKNUM(TODAY(),1)),B2:B93) ... | |
| VLOOKUP Function | 5/27/2009 |
| Q: I've a small query for you. I'm really in need of your help! I want to pull up data with keyword ... A: Sirajuddin, You almost have it correct.... but you can't use an "OR" in the way you've tried. ... | |
| YES or No Formula in Excel | 5/27/2009 |
| Q: My workbook has two sheets. In Sheet1, I have column A as 'Occupation' and Column B as 'SkillSet'. ... A: Mark, The fastest and easiest way to do this is to use a PIVOT TABLE. To make this solution ... | |
| Fill color in cell when adding text | 5/26/2009 |
| Q: my questions is, how do I make the color of the cell change when I input text, any text. For ... A: Ron, I am SO sorry! I made a mistake on your answer and typed the totally wrong thing! Instead ... | |
| Dynamic range for Spin button | 5/26/2009 |
| Q: I have a tab on my spreadsheet where A5-A202 is reserved for item numbers 1-198. A5 permanently has ... A: Sep, Sorry, but I have been unable to assign anything but a given number to the min and max ... | |
| using a data validation list to pull data | 5/26/2009 |
| Q: Bill, Hello! I am trying to make a job easier, but I need a little help. I have to fill out an ... A: Paul, There are several functions you can use to find a matching name in a list, and to then ... | |
| Due Amounts by week | 5/26/2009 |
| Q: I have an excel sheet with due dates that are in a certain column with the amount that should be ... A: Chris, You can do this by directly interrogating your database... you don't need a separate ... | |
| Fill color in cell when adding text | 5/26/2009 |
| Q: my questions is, how do I make the color of the cell change when I input text, any text. For ... A: Ron, This is QED (quite Easily Done) using Data Validation. Select the cells in question and ... | |
| Excel formula | 5/26/2009 |
| Q: I have a database of apartments, the database runs Col A floor, Col B suite, Col C tenant, Col D ... A: Kim, There isn't a way to sum a column based directly upon the cell colors in the column. ... | |
| Pivot tables | 5/26/2009 |
| Q: I am using Excel 2007 and have made a pivot table. How do I add a formula to a field so that it ... A: Ilse, I'd have to see this pivot table in order to fix it for you. You can send it to my ... | |
| Excel Time Converstion | 5/26/2009 |
| Q: Im sure its an easy fix. D3 is the on duty time IE: 06:30 H3 is the off duty time IE: 15:30 I3 is ... A: Jared, This seems really easy, am I missing something? Put 08:00 in cell K3. The ... | |
| Regarding macros | 5/26/2009 |
| Q: Can you please tell me how to retain particular columns in excel using macros? Let us consider i ... A: Sravani, I don't know what you mean by "retain". "Retain" when you do what, under what ... | |
| EXTENDED "IF" FUNCTION ? | 5/26/2009 |
| Q: I am relatively new to excel In cell F1 I have the value 0.5151 In cell F1 I have the value 0.0253 ... A: Dave, What you want to do is called a NESTED or COMPOUND IF statement. This is one of the more ... | |
| Column Graphs In Excel 2003 | 5/25/2009 |
| Q: Hey mate I need to make a column graph in excel to record each idviduals sales for every month in ... A: Jarrod, I can't give you a complete graphing tutorial on this website, but I can get you ... | |
| Multiple Count Statements with Dates | 5/25/2009 |
| Q: Good Afternoon, I would like to create a count based on two columns (Account & Date) and a sum ... A: Aaaron, The answer is to use COUNTIF() and SUMIF() with a reference to the cell containing the ... | |
| copy cells in aspecified form | 5/25/2009 |
| Q: a b 1 x y 2 z m and convet them to this form a b c d 1 x y ... A: Ahmed, All you do is COPY the cells in row 2, and PASTE them into row 1. But I bet you ... | |
| Excel Autogenerating blocking chart | 5/25/2009 |
| Q: I'm hoping you can help answer my question. I work for a franchise company's marketing department ... A: Steffanie, This is a REALLY cool and interesting problem that I would LOVE to work on for you ... | |
| Populating a dropdown list with data from another file depending on Cell value | 5/25/2009 |
| Q: I am making an invoice sheet for me and i am struck at 1 place. I need your help. I am using Excel ... A: Apoorva, Do you still need an answer to your question? It's been in my stack for a long time, ... | |
| Excel - automating the update to closed workbooks | 5/25/2009 |
| Q: tricky one this - how/ can I automate the updating of links to another workbook that is closed ... A: Martin, You MIGHT be able to make this work if every link to a cell in a closed workbook used a ... | |
| button to sort data | 5/25/2009 |
| Q: I have a spreadsheet that i have designed for our lawn bowling club to record tournament results. I ... A: Stephen, Ordinarily I don't offer macro advice on this website, but this one is so easy I'll ... | |
| Create a horizontal list from vertical array | 5/24/2009 |
| Q: First of all I appreciate you giving your valuable time to read and attend to my query. Thanks! I am ... A: Dilip, Nice of you to attach an image, but you didn't make any reference to it in your text. ... | |
| Excel formuas in sorting data | 5/23/2009 |
| Q: I need help in setting up a work sheet in which there is a master tab and from the master tab, I ... A: Dominic, I never received a file named PETS from you. But I can answer your question ... | |
| Excel Column to Row | 5/23/2009 |
| Q: =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1)) This formula works for one column, to ... A: Michelle, Do you still need an answer to this question? It's been in my stack for a long time, ... | |
| Current Data for A Stock Graph | 5/22/2009 |
| Q: I am tracking the value of a pair of mutual funds. I have made a sheet (FundData) that calculates ... A: Dave, This can be done but it may not be easy if you aren't very experienced with Excel. ... | |
| Excel Column to Row | 5/22/2009 |
| Q: =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1)) This formula works for one column, to ... A: Michelle, Your OFFSET equation demonstrates an advanced knowledge of Excel! Congratulations on ... | |
| Comparing Workbooks | 5/22/2009 |
| Q: I have just taken over a new position and my predecessor did not have the chance to give a clear ... A: Anthony, Your predecessor has left you a huge intractable MESS. Whatever you do is going to be ... | |
| TIME AND RESOURCE TRACKING | 5/22/2009 |
| Q: First I want to say thank you for taking the time to help me. I am new to excel but I feel ... A: Your intention is clear and your project is complex. Yet it can be done. I'm concerned about ... | |
| Chart question (actual vs. budget) | 5/22/2009 |
| Q: I have a data like this: The heading is A1 = "Name" , B1 = "Actual", C1= "Budget"" , the data is: ... A: Mart, This is easy to do so you don't need a sample. Besides, use Experts don't get to see your ... | |
| Polynomial trendline cofficients extraction | 5/22/2009 |
| Q: Is there a way to capture in a cell the equation coefficients generated by a polynomial (fourth ... A: Hamant, Unfortunately, I don't think there is a way to get these values. There is no data ... | |
| Filter & Protection | 5/21/2009 |
| Q: Developed worksheet which is a daily log for employees. Eight cells in the worksheet take the last ... A: Deb, I received your workbook. I'm not sure which is the area that you are having troubles ... | |
| Spreadsheet equations and functions | 5/21/2009 |
| Q: I am trying to create a spreadsheet to track employee hours and minutes for the pay period. I have ... A: Kim, In Excel 2007, there is a TIME format which reads "37:30:55". This would be the format to ... | |
| EXCEL MARCO for find/Replace - surrounding text | 5/21/2009 |
| Q: I am looking for a macro or other function in excel that will allow the replacement of a function ... A: Chris, I can't swear that this suggestion will work, but it may get you started. First, I ... | |
| EXCEL MARCO for find/Replace - surrounding text | 5/21/2009 |
| Q: I am looking for a macro or other function in excel that will allow the replacement of a function ... A: Chris, Try something like this: =IF(ISERROR(your-function-here),0,your-function-here) ... | |
| Graphing | 5/21/2009 |
| Q: I am using the xy graph to plot some lines in. I have a datasheet that contains values or formulas. ... A: Ben, I'm not sure I understand your problem. Values in a cell, and formulas in a cell, are the ... | |
| Count the number of times an entry appears within a date range | 5/21/2009 |
| Q: Using Microsoft Excel 2003 on a PC, I am attempting to count the number of times an entry appears ... A: Carolyn, Okay, I have received your workbook. Now I need some explanation. Please tell me ... | |
| If(and) formula | 5/21/2009 |
| Q: The formula I am using to show the incentive earned by a person is ... A: Rogers, You may send your file to me at ExpertAtExcel@AOL.COM. However, I won't write ... | |
| workday function | 5/20/2009 |
| Q: This should be simple, but I can't quite figure it out. The logic of what I am trying to do is ... A: Patrick, You certainly could create a table of holidates (how do you like that word?) and in a ... | |
| pivot table | 5/20/2009 |
| Q: is this possible? ID BNAME 2819 Transaction Price 2819 Unit Size Factor 2819 Indicator ... A: Manish, The easiest way I see to do this IS to make a pivot table! To do this, you need to add ... | |
| Count the number of times an entry appears within a date range | 5/20/2009 |
| Q: Using Microsoft Excel 2003 on a PC, I am attempting to count the number of times an entry appears ... A: Carolyn, If you can send me your workbook, or a piece of it, I can probably figure out the ... | |
| sales per day | 5/20/2009 |
| Q: I am hoping you can help me. I am doing a project for a section of our sales. Basically what I am ... A: Sara, This sounds like a perfect application for a pivot table, which would give you the ... | |
| pivot table | 5/20/2009 |
| Q: is this possible? ID BNAME 2819 Transaction Price 2819 Unit Size Factor 2819 Indicator ... A: Manish, Well, no, it's not possible. But it is certainly possible to create some kind of ... | |
| data transfer between worksheets | 5/20/2009 |
| Q: I just started making expense and account reports for monthly spending and I need to copy the ending ... A: Amber, This should be simple enough, as you said. If you are hand-typing the cell reference, ... | |
| Excel 2000 dynamic range | 5/20/2009 |
| Q: The static definition of my range is =Eowercape!$25:$45,Eowercape!$2:$22 I would like to replace ... A: Frank, It's a known behavior that named dynamic ranges created using the OFFSET function don't ... | |
| link | 5/20/2009 |
| Q: I have many excel files in the same folder - 1.xls 2.xls 3.xls and so on. All of them contains a ... A: Plamen, I'm not entirely sure you can do this, but I have a suggestion that you can try, unless ... | |
| excel form | 5/20/2009 |
| Q: I am trying to create a employee scheduling form, where the schedulers can pick from a list of 100 ... A: Sue, Your brilliant application of a named dynamic range should work perfectly. What makes you ... | |
| Formating Cell Equation | 5/19/2009 |
| Q: Ok so i have made a spread sheet in which a large row computes an equation for various number ... A: Zach, Use =IF(MOD(N,1)=0,"integer","not integer") to determine if a number is ... | |
| Count the number of times an entry appears within a date range | 5/19/2009 |
| Q: Using Microsoft Excel 2003 on a PC, I am attempting to count the number of times an entry appears ... A: Carolyn, The DCOUNT function will work nicely, thought there are other functions too. You ... | |
| excel form | 5/19/2009 |
| Q: I am trying to create a employee scheduling form, where the schedulers can pick from a list of 100 ... A: Sue, I have never heard of a limit on the number of names in a data validation list, at least, ... | |
| Need help with complicated formula? | 5/19/2009 |
| Q: I have created 2 drop down menus in Excel, each with a limited amount of possible answers. What I ... A: Danielle, There are several ways to do this. The method I'd recommend is extendable to ... | |
| Excel question | 5/19/2009 |
| Q: How can we make all the gridlines disappear except for the data we have selected. A: Keshav, You can turn off the gridlines but the exact method depends on the version of Excel you ... | |
| Problem with two dimentional lookup formula | 5/19/2009 |
| Q: I am using Excel 2003. In my workbook I have two worksheets titled “Parameter” which are Basics ... A: Swastik, Thought your question isn't quite as clear as it could be, I'll take a stab at an ... | |
| excel formula | 5/19/2009 |
| Q: can we write a formula which prompts for a user input as formula component. eg ==PMT(5,b2,c10) Here ... A: Tatavarty, You can write a formula which prompts for user input. Use an IF staement that ... | |
| excel form | 5/18/2009 |
| Q: I am trying to create a employee scheduling form, where the schedulers can pick from a list of 100 ... A: Sue, While I am not sure how to make Excel email the form, I am sure how to create the form in ... | |
| Don't know how to configure formula to calculate | 5/18/2009 |
| Q: I am using Excel 2003. Within my workbook, I have a spreadsheet for every month (Jan - Dec) to ... A: Jennifer, I don't see a workbook from you, but your question is clear enough without it. ... | |
| Excel 2003 | 5/18/2009 |
| Q: Bill, I am trying to create a formula in which it will take into consideration holidays. The ... A: Melissa, An upgrade to Excel 2007 would immediately solve your problem because holiday ... | |
| Excel 2003 IF statements | 5/18/2009 |
| Q: I am creating a spreadsheet to track attendance at 8 visits a month on average or 24 visits per ... A: Jason, Without knowing the precise details of your logic, I can't write the equation for you ... | |
| Showing Duplicates | 5/18/2009 |
| Q: I may be over thinking this, but here goes: I need to check a spreadsheet for duplicate phone ... A: Matt, Next to the column of phone numbers, create a column of COUNTIF() statements, each of ... | |
| Excel 2000 dynamic range | 5/18/2009 |
| Q: The static definition of my range is =Eowercape!$25:$45,Eowercape!$2:$22 I would like to replace ... A: Frank, There are several approaches to this. Its been a long time since I have seen or used ... | |
| Setting an Excel field to 17 characters | 5/18/2009 |
| Q: I have a particular column in an Excel spreadsheet who's values range from 5 - 24 characters long. ... A: Shane, The best idea I can come up with is to use a LEFT() statement to create a new column with ... | |
| drop down box & check box coversion | 5/18/2009 |
| Q: How can I conver Drop Down Box & Check Box results to constant values so I can dump it into Access A: Eric, You can convert any result that is showing as the 'answer' to a formula to a solid value, ... | |
| help creating a cloumn | 5/18/2009 |
| Q: I have very little experience with excel and i would appreciate any help. I basically need help ... A: Philip, You've answered your own question! Just create a formula that divides each of the 6 ... | |
| Formatting in Excel | 5/17/2009 |
| Q: I have a Excel 2007 spreadsheet that contains a column of parts and on each row is a list of ... A: Joey, All you need to do is apply a Conditional Formatting (CF) rule to each entire row. ... | |
| if then put statement | 5/16/2009 |
| Q: This should be very simple. If A1-B1<=50.00 then I want to place the result in C3. If ... A: Bill, In order for a value to appear in a cell, there MUST be an equation in that cell which is ... | |
| Spreadsheet equations and functions | 5/15/2009 |
| Q: I am trying to create a spreadsheet to track employee hours and minutes for the pay period. I have ... A: Kim, I can't address a Microsoft Works question directly, as I am an Excel expert and don't even ... | |
| creating unique id | 5/15/2009 |
| Q: Sir, I had created Receipt spreadsheet but I want unique receipt No.( R09-001... R09-002... ... A: Arvind, This is a somewhat difficult problem, which I believe can best be solved with a macro. ... | |
| Excel Copy and Paste | 5/15/2009 |
| Q: I know there is a way to copy a formula in a cell and paste it to another cell keeping that formula ... A: Elizabeth, It sounds like you want to have the formula in A3 be =A1+B1, and the formula in A4 ... | |
| Excel Workbooks!!! | 5/15/2009 |
| Q: ! So here what's up... We have several Employee Workbooks where each employee does their work each ... A: Christopher, This isn't too difficult, but perhaps you have found an answer elsewhere by now. ... | |
| excel | 5/15/2009 |
| Q: Is there a formula when you have a column of number you enter different numbers everyday that it ... A: Drew, Yes, there is. You can use MIN() and MAX() of the column to continuously display the ... | |
| multiple IF's | 5/14/2009 |
| Q: I know you can't go past 7 in one formula which is where I run into a problem. I have 11 options ... A: Sam, There is certainly some way to 'get around' the 7-level IF limitation. But I would ... | |
| Drop Down menus | 5/14/2009 |
| Q: I have an Excel workbook named "Costs-Invoicing" containing the following worksheets - Costs, Data, ... A: David, Instead of copying the Data worksheet to another workbook intact, try doing a COPY on the ... | |
| nested IF or CHOOSE statement? | 5/14/2009 |
| Q: -------------------------------------------------------------------------------- I have the text in ... A: Sal, This requires a nested IF statement with a compound conditional tests. The ... | |
| If(and) formula | 5/14/2009 |
| Q: The formula I am using to show the incentive earned by a person is ... A: Rogers, If you upgrade to Excel 2007, you can get 64 levels of IF nesting, which should cover it ... | |
| Multi Criteria Lookup with Wildcard | 5/14/2009 |
| Q: I am using the following formula to do a multi criteria lookup: ... A: Thomas, Do you still need an answer to this question? It's been in my stack for a long time, ... | |
| Add Year to date total at a given month | 5/14/2009 |
| Q: I have a workbook which includes a data sheet and a summary sheet. The data sheet contains the ... A: Hanno, Do you still need help with this? if so, here it is! But actually, I have a ... | |
| formulas | 5/14/2009 |
| Q: I have entered data into a spreadsheet and am trying to input a formula that gives a watterbottle if ... A: Connie, You can use ages or date-of-birth, either way. The IF statement is the correct one to ... | |
| Formula Question | 5/13/2009 |
| Q: Here's my problem: =IF((B1-C1)<=50,0,IF((B1-C1)<=100,5,IF((B1-C1)<=200,10,"Greater than 200"))) I ... A: Latoya, Sorry I wasn't clear. here's what you need: ... | |
| Count a occurence of a word in 'n' number of columns | 5/13/2009 |
| Q: Good Day Bill, Can you help me with this problem. I have a worksheet with 200 to 400 rows of ... A: Jeff, If you wish to do a COUNT on a database using multiple criteria, the [2007-only] function ... | |
| Formula Question | 5/13/2009 |
| Q: Here's my problem: =IF((B1-C1)<=50,0,IF((B1-C1)<=100,5,IF((B1-C1)<=200,10,"Greater than 200"))) I ... A: Latoya, When you say "IT" I can only assume that you mean "the difference between C1 & B1". ... | |
| Excel 2003 - formular question on populating information automatically into another worksheet? | 5/13/2009 |
| Q: I want to be able to type in a unique ref. Number into a destination worksheet, where it picks up ... A: Keith, I don't see the difference between your original question and your re-stated question. ... | |
| Two Combo box hyperlink | 5/13/2009 |
| Q: I have 6 tabs in a excel sheet On top of every tab I want to put a two combo box 1)1st combo box ... A: KK, Your question is a bit self-contradictory, for in the first part you say that combo box 1 ... | |
| Finding averages using calculations | 5/13/2009 |
| Q: I am trying to establish an average from some data. Basically, there are a number of patients (2328) ... A: Ben, I must be missing something because it seems like you could just take the average of the ... | |
| Filtering Data Using Combo box | 5/13/2009 |
| Q: I have an excel sheet which contains data with labels in top rows. I want a combo box in the top of ... A: Adnan, You requested a professional quote for this. Please send a more detailed description of ... | |
| Multi Sheet input | 5/12/2009 |
| Q: I am in the process of updating a lot of my company's files from lotus to excel. I have most of it ... A: Jarrod, Excel is so much more powerful than Lotus 1-2-3 (which I used to use before Excel came ... | |
| Which formula should I use? | 5/12/2009 |
| Q: Bill- I'm using Excel 07 with Xp. My payroll needs to input a # in a column that represents the ... A: Vicky, An UP counter or a DOWN counter is simply a formula with adds 1(UP) or subtracts 1 ... | |
| Which formula should I use? | 5/12/2009 |
| Q: Bill- I'm using Excel 07 with Xp. My payroll needs to input a # in a column that represents the ... A: Vicky, I can provide an explanation of my technique, but it is quite complex and you'll have to ... | |
| 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: Maki, When I come across a problem like this, the first thing I check is the help file. It ... | |
| 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: Maki, The easiest way I know of to do this is to use the ADDRESS function buried inside an ... | |
| Excel | 5/12/2009 |
| Q: Sir, I am new to Excel & need your advise. I have dates horizontally, & in veticials I have lab ... A: Melvin, I am not sure what you're asking, a picture would be helpful, and ESPECIALLY a clear ... | |
| excel formula | 5/12/2009 |
| Q: In PF if ur basic is grater than 6500 then it takes max 541,and if it is less than 6500 then it take ... A: Pramit, Sounds like a simple IF statement is all you need here. lets say the number you care ... | |
| Which formula should I use? | 5/11/2009 |
| Q: Bill- I'm using Excel 07 with Xp. My payroll needs to input a # in a column that represents the ... A: Vicky, You asked your question two different ways and they are not the same question! So I ... | |
| Auto filtering | 5/11/2009 |
| Q: I have a spreadsheet that for some reason will not show all the parts when I click on the auto ... A: Debbie, If your database is LARGE you might be running into the autofilter line number limit. ... | |
| Over time Hours within a time range | 5/11/2009 |
| Q: I've been working on this for way too long, and got stuck. I am trying to allocate hours between ... A: Roman, This problem involves rigorously determining what all the possible combinations of begin ... | |
| Conditional Formatting | 5/11/2009 |
| Q: I have a spreadsheet set up with/ a column labeled Apr-June, & then a date completed column. Right ... A: Janet, The formulas used for conditional formatting (in the conditional formatting dialog box ... | |
| Complicated Lookup | 5/11/2009 |
| Q: I have spreadsheet that contains dates in Column A2-A25 and the data for these dates in B2:D25. I ... A: Thomas, All you need to do is apply my favorite function, VLOOKUP(). For each value you want ... | |
| Excel 2003 - formular question on populating information automatically into another worksheet? | 5/11/2009 |
| Q: I want to be able to type in a unique ref. Number into a destination worksheet, where it picks up ... A: Kerith, All you need to do is apply the VLOOKUP function to this problem. The unique ref no ... | |
| Filtering Data Using Combo box | 5/11/2009 |
| Q: I have an excel sheet which contains data with labels in top rows. I want a combo box in the top of ... A: Adnan, My suggestion would be to use an ADVANCED FILTER, and feed the combo-box choice ... | |
| Data in excel is only recognaized as a number when cell is activated | 5/11/2009 |
| Q: I have a file containing data of staff.The ID is a number ex: 123456. When I try to use this value ... A: Iman, There are many things that could be wrong. Your 'number' might be TEXT. Perhaps you ... | |
| 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: S.K, Umm, I never answered your question before , as far as I can tell, so it wasn't me who ... | |
| Cleaning spreadsheet of addresses | 5/10/2009 |
| Q: I have a file comprised of 2 columns of about 1,500 addresses. Each address appears over 4 ... A: Angus, This is a little tricky (as you know) but I think I see a method. You'll need to add ... | |
| networking database | 5/8/2009 |
| Q: My goal is to input a contacts name, their need, and what product or service they sell. From that ... A: Mark, This is a very complex project that requires much up-front thought. Well, you already ... | |
| Opening an Excel 2007 Document with Excel 2003 | 5/8/2009 |
| Q: What is process to open a document prepared in Excel 2007 if the computer it is sent to has only ... A: S, All you have to do is save the workbook from Excel 2007 as an Excel 2003 type. In 2007, ... | |
| Using time-related function | 5/8/2009 |
| Q: I want to create some kind of monitor within my spreadsheet that watches whether a target price is ... A: Raffi, I apologize, I hadn't seen your followup question. I think you must have missed a most ... | |
| excel formula find file name | 5/7/2009 |
| Q: I'm looking for a formula that will return the file name of the saved file. For example: the sheet ... A: Tony, In order to answer this followup, I'd need to know what the PATTERN for all possible ... | |
| Writing a Sumif or a Sum Product formula based on three criteria | 5/7/2009 |
| Q: Please can you look at the following set of data: Date of Visit LEA No of Children / participants ... A: Peter, What you need to use is the advanced form of SUMPRODUCT(). This form is NOT documented ... | |
| data time range difference | 5/7/2009 |
| Q: I am struggling with a formula. In the attached image i have time intervals given in colum C and D ... A: Stefano, I'm a little bit confused, because if the times are a few seconds out side the limits, ... | |
| Combination of numbers | 5/7/2009 |
| Q: Is there an easy way in excel to take an array of numbers and find the combination of those numbers ... A: Cat, Not only isn't there an easy way, there isn't even a fairly difficult way. There is an ... | |
| excel formula find file name | 5/7/2009 |
| Q: I'm looking for a formula that will return the file name of the saved file. For example: the sheet ... A: Tony, You can do this with a combination of CELL("filename") and the text functions. ... | |
| If Formula | 5/7/2009 |
| Q: I have to move a "-" sign which now sits at the beginning of a number to the end of the number and ... A: Heidi, The reason you can't do it with an IF statement is that your number isn't a number at ... | |
| Conditional formatting | 5/7/2009 |
| Q: I'm sure the answer is very simple but I cannot get it to work. I want to conditionally format a ... A: John, Well its not quite THAT simple, but its not too difficult either. However, you said ... | |
| Line Graphs | 5/7/2009 |
| Q: I have a simple Profit & Loss Line Graph and am wondering if it is possible to have the one axis ... A: Dom, You need to use an IF statement to replace the zero or blank values of the graph lines ... | |
| is there a formula to automatically sort data | 5/7/2009 |
| Q: Bill, I have a new problem ffrom my boss. What he now wants me to do to my spreadsheet is sort the ... A: David, There IS a formula-based method to do this, but the equations are REALLY complex and ... | |
| time formula | 5/7/2009 |
| Q: Bill, what is the formula to obtain the age of a person or time spent in a project. Thanks, Sam A: Sam, Those are two different questions. The AGE of a person is measured in YEARS, and time ... | |
| FHA 203k Max Mortgage Work Sheet | 5/6/2009 |
| Q: I need a 203k mas mortgage work sheet in an excel format. I have a copy of one that is lock. It has ... A: Rick, I'm not a mortgage analyst or broker, so I have no idea what you want. A "203K mas ... | |
| splitting and address from the final space | 5/6/2009 |
| Q: Being in the utility industry I'm seeking to split addresses for filtering. I found a formula for ... A: Steve, There is a process to do this, in fact I use something like this in a homework problem in ... | |
| multiply conditions in a formula | 5/6/2009 |
| Q: First I want to thank you for your help in this matter. I have an Excel (2007) sheet in which I ... A: Tom, A simple nested IF is indeed the answer, as you surmised. If you are getting error ... | |
| Text Manipulation involving spaces | 5/6/2009 |
| Q: I'm using Excel 2003 and want to separate members' initials from their surnames. I have a list of ... A: John, There is a way to do this but it is a fairly complex process. I use a similar problem as ... | |
| excel formula needed | 5/6/2009 |
| Q: I wish to use =SUM(A1/B1) and have it return a blank or zero if A1 and B1 contain zeroes. Right now ... A: Dennis, This is easy to solve and REALLY easy if you have Excel 2007. In 2007, just ... | |
| Formula to calcuate time range | 5/6/2009 |
| Q: I am trying to calculate hourly range for a particular time. For eg: 4/1/2009 7:25 AM comes between ... A: Nagesh, If you want to output a time RANGE, whose time numbers can be used by other ... | |
| Memory Hog Eqyution | 5/5/2009 |
| Q: I am using Excel 2007, but will ultimately be saving the file in Excel 2003. I have a very complex ... A: Steve, There are several things you can do to simplify this. below I have included your ... | |
| Excel question for three conditions and three results | 5/5/2009 |
| Q: I would like to ask you if you know if with Excel you can formulate 3 different conditions with ... A: Luis, A simple nested IF is the answer you want. =IF(weight>18,358,IF(weight>8,290,250)) ... | |
| COUNTIF | 5/5/2009 |
| Q: You've helped me a few times in the past and I'm hoping you can help again now. I am trying to use ... A: Simon, If you have Excel 2007, look at the COUNTIFS() function. It does exactly this. ... | |
| Calculating interest | 5/4/2009 |
| Q: This email is not urgent, just to let you know. The reason why this isn't urgent is because this is ... A: Jeroen, You can use an IF statement to check the present value of the bank account. if it is ... | |
| sorting data in excel | 5/4/2009 |
| Q: I have a column with data of mixed digits and numbers. Also some are shorter in length. I need the ... A: Aha, So, sort the column in ascending order! Use the DATA --> SORT menu to choose the column ... | |
| Trouble writing a customized round function | 5/4/2009 |
| Q: I'm trying to write a formula that will adjust numbers according to the following conditions: If ... A: Jason, I think I have an answer for you. Though I know nothing about Golf, I do know about ... | |
| excel time sheet | 5/4/2009 |
| Q: I'm using excel as a time card. Example: Monday Alex 8:00 17:00 .5 8.5 I use the following formula ... A: Raymond, You can embed your entire time calculation inside an IF staement, whose conditional ... | |
| formula to shrink a string too long error and ignore blank cells | 5/4/2009 |
| Q: After messing around with my spreadsheet formulas I found an if command that does what I wanted to ... A: David, You can modify your IF statements to include a check for blank cells. This will make ... | |
| workday function | 5/1/2009 |
| Q: This should be simple, but I can't quite figure it out. The logic of what I am trying to do is ... A: Patrick, It is indeed simple, if I understand your question properly. Can your question be ... | |
| Data Validation | 5/1/2009 |
| Q: I have 15 cells. Each cell needs a number of 1-15. No numbers can be duplicated. How can I set up a ... A: Gary, I just tried this and it works perfectly. 1) Create a column of the 15 numbers. My ... | |
| IF function using MONTH and YEAR | 5/1/2009 |
| Q: My end goal is to reference the date of an invoice, and based on it's date, put the amount in the ... A: Diana, All you need is a compound conditional test based on the month and the year. Such a ... | |
| modify a spreadsheet to tally ranked votes | 5/1/2009 |
| Q: I want to modify a spreadsheet that tallies votes to be able to tally ranked votes. We run a ... A: Cris, I don't have quite enough visibility as to what your existing vote structure looks like. ... | |
| Exacting the minimum values for each day | 5/1/2009 |
| Q: I have an Excel spreadsheet (Excel 2000) which has 3 columns. The 1st column holds the date, 2nd ... A: Jiwei, You can do this by applying the DMIN function to your data set. The DMIN function ... | |
| Absolute formula in a large formula | 5/1/2009 |
| Q: I want to determine the ten top values in a column range of data, the data contains both positive ... A: Ann-Marie, This shouldn't be too difficult! It sounds like what you are talking about is ... | |
| HLOOKUP | 4/30/2009 |
| Q: Bill, When I modify an existing, working HLOOKUP, such as going in to the function dialog box and ... A: Russ, It sounds like you have the cell formatted as TEXT. Try formatting another cell as a ... | |
| Excel Question | 4/30/2009 |
| Q: I'm working on a spreadsheet where I'm providing variable percentage changes for current year sales ... A: Ingrid, If the -9.2 % is wrong, what should it be? What result do you want there instead? ... | |
| Using time-related function | 4/30/2009 |
| Q: I want to create some kind of monitor within my spreadsheet that watches whether a target price is ... A: Raffi, I'll provide you a small part of the answer and let you play with it. This is how you ... | |
| HLOOKUP | 4/30/2009 |
| Q: Bill, When I modify an existing, working HLOOKUP, such as going in to the function dialog box and ... A: Russ, Not sure what you mean by "....the entire cell is evaluated as a constant..." Do you ... | |
| Excel Question | 4/30/2009 |
| Q: I'm working on a spreadsheet where I'm providing variable percentage changes for current year sales ... A: Ingrid, I thought you might be subtracting values, which is why I wanted to see your formula. ... | |
| Finding a sum based on a single criteria in multiple columns | 4/30/2009 |
| Q: I am making a calendar on an excel spreadsheet to track vacation and sick time. This is roughly what ... A: Laetitia, This is somewhat difficult due to the irregular structure of your calendar. But ... | |
| Using time-related function | 4/30/2009 |
| Q: I want to create some kind of monitor within my spreadsheet that watches whether a target price is ... A: Raffi, I'd love to help you but I need to understand it better. I think I see what you're ... | |
| formula to shrink a string too long error and ignore blank cells | 4/30/2009 |
| Q: After messing around with my spreadsheet formulas I found an if command that does what I wanted to ... A: David, Here's a quick little bit of help, I hope. You can use an IF statement based on an ... | |
| Execel - simple cash low payout | 4/29/2009 |
| Q: Bill wondering how to calcualte simpe payout in excel year - 1, 2, 3 4, 5 Cash flow - -500, 200, ... A: Bob, Since I am not a financial analyst, I don't know the terms you're using. If you'd define ... | |
| Merging spreadsheets | 4/29/2009 |
| Q: I have several separate spreadsheets that contain employee information, I would like to merge them ... A: Amy, Yes, its possible. But without more information, I can't say exactly how. My GUESS, ... | |
| Convinience of a drop-down calendar | 4/29/2009 |
| Q: In Excel 2003: Is it possible to insert a drop-down calendar for cells where a date is required? ... A: Pat, A full-blown drop-down calendar is not a standard Excel function. The drop-down boxes ... | |
| Question | 4/28/2009 |
| Q: I am using XP with Excel 2002. I have a spreadsheet that has multiple columns and rows. (10 Rows ... A: Jim, A "Logical Value" refers to the values TRUE and FALSE. In certain cases, these have the ... | |
| Excel Pivot Tables | 4/28/2009 |
| Q: I have a spreadsheet that was set up with headers on both the rows and columns. I know that Pivot ... A: Holli, Sorry for the delay in answering this, and I realize you may have gotten another answer ... | |
| Auto-populating sub-sets of data from a Master report | 4/28/2009 |
| Q: How do I auto-populate n number of worksheets (reports) based upon a value (parameter) contained ... A: Peter, There are two ways I can think of to do this. One way uses manual (keyboard, menu driven ... | |
| Formula about | 4/28/2009 |
| Q: what i need is this , if we have two columns which are full of data like this A B ... A: Sherif, This is a simple application of VLOOKUP. Pus a VLOOKUP in each cell of Column D. ... | |
| If formula | 4/28/2009 |
| Q: I would like knock out a formula to split a string of text separated by commas. So at each comma I ... A: Niki, Yes, its quite possible, though you don't use an IF statement. By the way, if you ... | |
| cross referencing names across multiple worksheets | 4/28/2009 |
| Q: eack week being a different worksheet. the worksheets are labeled p1w1 and so on. I need this data ... A: David, Okay, I've confused you (as I thought I might) and you have confused me back. So ... | |
| Excel looking up and returning ranges | 4/28/2009 |
| Q: I have a range of NUMBERS horizontly. The numbers in the range can be changed. By changing them they ... A: Lucasz, I seem to understand what you need, and perhaps it has been so long that you have ... | |
| cross referencing names across multiple worksheets | 4/27/2009 |
| Q: eack week being a different worksheet. the worksheets are labeled p1w1 and so on. I need this data ... A: David, Hmmm, this new information may change things. I thought you knew how to find the top ... | |
| Highlight Changed Rows | 4/26/2009 |
| Q: I am using Excel 2003. Is there a way to highlight each row in a spreadsheet in which any data in ... A: Mark, I can only offer the germ of an idea, untried and untested, to do this. It might work or ... | |
| copying dates | 4/26/2009 |
| Q: I am using excel 2000. When I copy dates from one spreadsheet to another, they decrease by 4 years ... A: S, I don't see how this could happen. Perhaps you should explain your exact method for copying ... | |
| Lists | 4/25/2009 |
| Q: i have 2 lists of data, they are under 2 headings of season 14, and season 15. There are 17 names in ... A: Steve, All you have to do is NAME the two ranges of episode names "14" and "15". These are TEXT ... | |
| Converting 4 digit Military to decimal | 4/24/2009 |
| Q: How do I go about converting military time such as 1630-0800 to equal 8.5 rather than 8.3? ... A: Dwight, Do you really have the time as 1630 instead of 16:30? What do you mean by 1630-0800? ... | |
| postion of a value in a list | 4/24/2009 |
| Q: - excel 2003 I have a number (X) that needs to be compared to three other numbers in a list so that ... A: Greg, A nested IF is indeed the answer. Perhaps you need a tutorial on this topic, so I will ... | |
| If Statement using a tab | 4/24/2009 |
| Q: basically i want to create an if statement that chooses the cell in the most recent tab from another ... A: Aaron, It MIGHT be possible if you could predict the name of the most recent tab in the other ... | |
| cross referencing names across multiple worksheets | 4/24/2009 |
| Q: eack week being a different worksheet. the worksheets are labeled p1w1 and so on. I need this data ... A: David, There are a number of pieces to this problem. 1) In order to know what the top and ... | |
| lost information pls help | 4/24/2009 |
| Q: I have a big problem. I was in one of our excel files last night finishing off some information for ... A: Jannita, As I said, this is not an Excel question.... Do you know how to use SEARCH from ... | |
| Worksheet auto-population from Master Sheet redux | 4/23/2009 |
| Q: How do I auto-populate n number of worksheets (reports) based upon a parameter contained within the ... A: Peter, I've read this over (and over) and I don't quite see what you're driving at. The ... | |
| Data Chart | 4/23/2009 |
| Q: Month Month/Year Result Feb→ May 2010→ Feb 2010 Mar→ June 2010→ Mar ... A: Winston, So if you took my advice and read about the functions I've named: MONTH(), ... | |
| Modify cell reference increments? | 4/23/2009 |
| Q: I have a worksheet with several related items on each line. I extract several cells from this ... A: Mark, There is no way to modify the increment that the references update by. But there is a ... | |
| lost information pls help | 4/23/2009 |
| Q: I have a big problem. I was in one of our excel files last night finishing off some information for ... A: Jannita, This isn't really an Excel problem, it could be true of any file. It sounds to me ... | |
| EXCEL LOGIC PROBLEM | 4/22/2009 |
| Q: I have a currect formula writen up that is working for me but i would also like that after this ... A: James, All you need to do is apply Conditional Formatting to the cell whose color you want to ... | |
| Stretching a set of data | 4/22/2009 |
| Q: I'm using excel 2007. I have a set of 45 non-consecutive numbers. I would like to stretch these ... A: Adrian, When you say "stretch these numbers to 100", it sounds as if you wish you had 100 ... | |
| Lookup function | 4/22/2009 |
| Q: Should be pretty straightforeward. I need a lookup function in Excel to return the smallest value ... A: Todd, Use MATCH instead of VLOOKUP. Mode -1 or +1 will behave the way you want. Read about ... | |
| Column vector into matrix | 4/22/2009 |
| Q: I am having trouble finding a formula that would convert column vectors into a matrix. For example, ... A: Aya, If the original data is static and not constantly changing, you could easily do this with a ... | |
| Excell Help | 4/22/2009 |
| Q: Sir, I want to know the procedure to automatically change the address instead of change in value. ... A: Sudhir, I don't see the pattern in what copies to where, given your example. In the ... | |
| Vlookup/If statement | 4/22/2009 |
| Q: I have a excel file that has 5 worksheets. 4 of the worksheets is a timeline. What I want to do is ... A: Eldy, Without having any idea of what your data values are, how they are organized, or what this ... | |
| Work Date variations | 4/22/2009 |
| Q: I have (2)coulmns, expected due date and actual due date. I want to find the difference in the ... A: Chris, You can arrange is to that the difference is always positive. Just embed both ... | |
| Excel Cell Formatting - Time hh.mm.ss | 4/22/2009 |
| Q: I certainly hope you are able to assist me with this question as it has stumped many over the past ... A: Kiym, I am certain I can help you with this, because I completely understand how the Date & ... | |
| Auto populate cells from a master sheet | 4/21/2009 |
| Q: I am working in a spreadsheet containing many sheets. Sheet 1 is my "MASTER" sheet which contains ... A: Randy, Either the cell where the formula is is formatted as TEXT, or there is an 'invisible' ... | |
| Looping through rows in excel to compare data | 4/21/2009 |
| Q: I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. ... A: Bimal, Its not the processing time I am worried about.... that likely won't be very long. It's ... | |
| Updating a spreadsheet automatically with forms | 4/21/2009 |
| Q: I have created a easy work order form to print out and give to employees for a public works ... A: Amber, You can apply a FORM menu command to your database which will automate this somewhat. ... | |
| Changing Excel Start of Day time | 4/21/2009 |
| Q: I am looking to change Excel's start of day from 12am to 5am. I have a TV Broadcast spreadsheet ... A: Mark, If you do reset the start time for a day, the sort order will be exactly the same as if you ... | |
| Auto populate cells from a master sheet | 4/21/2009 |
| Q: I am working in a spreadsheet containing many sheets. Sheet 1 is my "MASTER" sheet which contains ... A: Randy, (=VLOOKUP(B3,Master!A3:N6,Master!B1)) Look carefully.... does your equation ... | |
| Looping through rows in excel to compare data | 4/21/2009 |
| Q: I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. ... A: Bimal, =============================================================== This followup answer ... | |
| Excel If Statement? | 4/21/2009 |
| Q: I need to create a formula that will add up values in a column if the cell next to it hilds a ... A: Jake, All you need is a stack of SUMIF() functions. However, your image refers to ... | |
| Inventory Count results | 4/21/2009 |
| Q: Refering to example below. Results required is If 1ST COUNT =SYSTEM COUNT THEN 0 and IF 1ST COUNT IS ... A: John, If you wish to contract my professional services to help you, write to my business email ... | |
| Looping through rows in excel to compare data | 4/21/2009 |
| Q: I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. ... A: Bimal, This shouldn't be too hard. Since you can sort the database by donor name, you can ... | |
| User Interface - Scroll Bar or Graph to drive split of year into months | 4/20/2009 |
| Q: I need to build some variability into the seasonality of my financial model for a home building ... A: Steve, Excel line charts have always had to capability to drag any individual data point on the ... | |
| Excel 2007 | 4/20/2009 |
| Q: I want to modify the What-ifAnalysis.jpg spreadsheet to have the ability to play what-if analysis ... A: Lori, What you need to do is investigate the use of the SCENARIO. With a scenario, you can ... | |
| formulas | 4/20/2009 |
| Q: I'm new to this and need abit of help please, i would like to know how to get a formula, if i start ... A: Jason, Okay, here's a REAL answer. Knowing that time is a special serial number, you can test ... | |
| text sequence - days of the week | 4/20/2009 |
| Q: I would like to create a sequence for days of the week, where the lead cell can be changed and with ... A: Doerhe, I assume what you mean is the you want to type MON in the lead cell, and have the rest ... | |
| Auto populate cells from a master sheet | 4/20/2009 |
| Q: I am working in a spreadsheet containing many sheets. Sheet 1 is my "MASTER" sheet which contains ... A: Randy, It looks as though what you really want to do is LOOKUP various shipping label parameters ... | |
| Auto populate cells from a master sheet | 4/20/2009 |
| Q: I am working in a spreadsheet containing many sheets. Sheet 1 is my "MASTER" sheet which contains ... A: Randy, I'd ask you for some additional details.... certainly you don't want to make all your ... | |
| Inventory Count results | 4/20/2009 |
| Q: Refering to example below. Results required is If 1ST COUNT =SYSTEM COUNT THEN 0 and IF 1ST COUNT IS ... A: John, This sounds like a simple IF statement.... so what's the problem? I note that you ... | |
| Counting cells within a month having some remarks | 4/20/2009 |
| Q: In my worksheet of Enquirers, I have dates on one column and Member number on another column. All ... A: Michael, For the second part of your question, "I want to find out how many Enquirers who made ... | |
| lists | 4/18/2009 |
| Q: I would like to use the "IF" Formula to add a list to a box.(Excel 2007) For example I have made a ... A: Nikolai, There are several ways to do this, though none of them involve IF. Here's my ... | |
| Dynamic sumif formula | 4/18/2009 |
| Q: I'm trying to create a sumif formula that finds the column the information is in that it needs to ... A: Ross, I love this kind of problem, it's fun to solve! Instead of INDIRECT, I'd use a ... | |
| Database | 4/17/2009 |
| Q: How do I create a form that I can circulate to my staff that will populate a database housed on my ... A: Lee, There are several ways that you can do this. Which way you choose is mostly dependent on ... | |
| V-Look Up | 4/17/2009 |
| Q: I have never used a VLOOKUP formula before. I would say I am an beginner excel user. I need to ... A: Norma, Since there is very little information in your question, all I can do is give you my ... | |
| Resolving cell contents to a list | 4/17/2009 |
| Q: I want to resolve 2 rows of information into a neat column of titles: Row1: Title1 Title2 ... A: Kevin, If this isn't a constantly changing list, you can do this with the TRANSPOSE operation, a ... | |
| setting a control limit/alert? | 4/17/2009 |
| Q: I am analyzing a HUGE amount of data relating to monthly breakage rates of products in excel. ... A: John, The ability to change cell appearance based on cell values is so important that Excel has ... | |
| Conditional formatting w/dates | 4/17/2009 |
| Q: Bill, I track training in our organization using Excel 2007 and want to use conditional formatting ... A: Charles, Everyone else wants this also, so Excel has a 'function' called CONDITIONAL FORMATTING ... | |
| Frequency function with dynamic criteria | 4/16/2009 |
| Q: I would like to create a formula that will create a frequency table based on a selection criteria. ... A: Kim, At last I have thought of a way to accomplish this! 0) the technique will be to replace ... | |
| Linked Spreadsheets | 4/16/2009 |
| Q: I have linked a Microsoft Excel chart to an Excel spreadsheet. The problem is that I want to send ... A: Trelissa, By now you may have figured this out but I'll give you my answer anyway. Excel ... | |
| Commision Calculation on Collections | 4/16/2009 |
| Q: I am using Excel 2000, I have a contingency commission report to create. One calc is getting me ... A: Franz, It would seem that you have to keep a database of checks received, so you know what ... | |
| Matching two columns to return value in 3rd | 4/16/2009 |
| Q: I have a list with three columns headed: Business Unit, Country, Headcount I have another list with ... A: Alex, Here's what I think you want: BU Country Headcount A Belgium 200 A France ... | |
| If/Then Statement | 4/16/2009 |
| Q: Good Morning Bill. I'm not experienced with Excel at all but I'm trying to learn. I'm referencing ... A: George, The images that the site allows are usually too fuzzy to read in any detail. Plus of ... | |
| Looking up Values in a Column and returning all the adjacent columns value | 4/16/2009 |
| Q: I have a spreadsheet with ±6000 rows of Cartridges and in the adjacent column I have what printer it ... A: Justin, Such a simple result, you'd think it would be so simple to accomplish. But's its VERY ... | |
| excel formula help | 4/16/2009 |
| Q: I don't know if you can help, but I'm having a nightmare trying to get Excel functionality do what I ... A: Fraser, You should have written sooner instead of getting so frustrated! It isn't that hard. ... | |
| Delete duplicate rows in excel | 4/16/2009 |
| Q: I need to have a Macro that will check a worksheet for duplicate rows and delete them. 3 issues ... A: Shreya, If you are convinced that your answer just has to be a macro, I can't help you because I ... | |
| return multiple data from dropdown list | 4/15/2009 |
| Q: I am trying to insert a function on an Excel 2003 worksheet that, depending on the value selected ... A: Terry, I'm not entirely sure what you mean, but it sounds like you want to look up data from a ... | |
| Lookup on 2 elements | 4/15/2009 |
| Q: I have a report that I need populate by looking at 2 elements on the report and corresponding ... A: Criag, VLOOKUP doesn't lend itself to nesting, so you need to use another method. I'm ... | |
| Excel drop down list | 4/15/2009 |
| Q: I have created a drop down list that works fine. If it is not too complicated I would like the list ... A: Taylor, Actually this is somewhat complicated but can be done. A special type of form-box is ... | |
| Totaling hours based on project number from multiple worksheets | 4/15/2009 |
| Q: Created a timesheet document using multiple worksheets for each week of the month. Columns for ... A: Sharon, Yes this is quite possible. The answer I'd use is based on the SUMPRODUCT() function, ... | |
| Nested IF(AND(OR #VALUE! error | 4/15/2009 |
| Q: I am trying to create an IF function using AND, OR and the end result is returning a #VALUE! error. ... A: Linda, It looks like you don't have the parentheses correct. At the end of your combined ... | |
| significant figures (significant digits) | 4/15/2009 |
| Q: I often create data tables that contain analytical results to a certain number of significant ... A: Steve, The closest I could recommend is to use SCIENTIFIC format, like 1.23E04 or 1.256E-04. ... | |
| If/Then Statement | 4/15/2009 |
| Q: Good Morning Bill. I'm not experienced with Excel at all but I'm trying to learn. I'm referencing ... A: George, You can use the advanced from of SUMPRODUCT as a sophisticated COUNT function. You ... | |
| Summing up | 4/15/2009 |
| Q: Bill, basically I want to Countif a value (year = 2009)appears in Column A and a value (Employee = ... A: Joe, What you want is called a "multi-criteria record counter". If you have Excel 2007, ... | |
| Comparing array data to a list | 4/14/2009 |
| Q: I have 2 sheets in 1 workbook. On sheet 1 is a list of data. On the second is an array. I want to ... A: Bill, You can make this happen exactly as your words describe it. There are several steps ... | |
| looking up information in a grid | 4/14/2009 |
| Q: I am trying to create a spreadsheet to quote jobs. The price will be based on number of colors in ... A: Shelly, You use two MATCH functions which look across the columns and down the rows, and feed ... | |
| Trying to identify incorrect entries in excel | 4/14/2009 |
| Q: I am busy with mobile number lists for an sms campaign. Each mobile number entry must contain 10 ... A: Anthea, The answer depends on if these are actual NUMBERS or are they TEXT. It sounds like they ... | |
| Excel copying to another sheet | 4/14/2009 |
| Q: I need to copy cells from 1 sheet to another If the reference cell before the description cell is ... A: Thomas, Now that I look at your question (again), I see that something about it doesn't make ... | |
| Excel Question about creating a copy formula | 4/14/2009 |
| Q: In a workbook, I have several worksheets and I need help figuring out how to solve this problem. In ... A: Katina, I'm sorry that I upset you by either talking above, or below, your skill level... I ... | |
| Line Graphs | 4/14/2009 |
| Q: I have a simple Profit & Loss Line Graph and am wondering if it is possible to have the one axis ... A: Dom, Its possible if you to split your data into two columns. One column will be POSITIVE ... | |
| Find Duplicates | 4/14/2009 |
| Q: I have a spreadsheet I need to find duplicates between 2 columns. Columns with data are H5:H1399 ... A: Steve, I read this question to mean that you want to know if each entry in Col H exists in Col ... | |
| Average days | 4/13/2009 |
| Q: I would like to get the average number of days for every 6th date in a column. Right now I find the ... A: Carol, I'm certain that I don't understand your question. You said: "Right now I find the ... | |
| Time keeping in excel | 4/13/2009 |
| Q: ok i have a cell that has 7:43am in it and the next cell have 4:48pm in it how do i set up my excel ... A: Misty, Your question has two parts but both parts seem to ask the same thing. All you have ... | |
| Cell formula automatically updating, should not | 4/13/2009 |
| Q: I have a formula that is automatically updating on me. Make M1 = SUM(A1:E1), N1 = SUM(F1:H1), and ... A: Don, When I first read your question, I thought that you must be doing something wrong, this ... | |
| Making invoice with Excel | 4/12/2009 |
| Q: I need help with making excel into a simple invoicing machine. I want excel to pull up records about ... A: Robert, This is a very broad question so I'll give you a lot of top-level guidance, instead of ... | |
| Excel text formula | 4/11/2009 |
| Q: I've got this excel sheet full of data which unfortunately I can't send you because it contains data ... A: Doris, Don't worry, its easy and there are two ways you can do it. 1) The SORT method ... | |
| IF formulas | 4/10/2009 |
| Q: An answer to yourGood afternoon. I am working on a spreadsheet that I need to be able to change ... A: Jon, A DIT (aka Do-If-True) is just like any equation any where in any cell, except you don't ... | |
| If Condition | 4/10/2009 |
| Q: I am working with a large data,for every line I need to enter the currect date and currect ... A: Parmesh, Record two simple macros that performs the keystrokes you use, and assign them to the ... | |
| countif | 4/10/2009 |
| Q: I am going to have a workbook that has US States listed throughout a range. On the right side I want ... A: Steven, Make the reference to the data column an ABSOLUTE reference so it will not change when ... | |
| IF formulas | 4/10/2009 |
| Q: An answer to yourGood afternoon. I am working on a spreadsheet that I need to be able to change ... A: Jon, You need to know two things: how to build a nested (also called a compound) IF statement, ... | |
| Sum Numbers only in cell array | 4/9/2009 |
| Q: I have foound many formulas that provide me parts of what I need. I am looking to try and combind ... A: Larry, I must say I don't understand your equation relating to CHAR(1). That is neither the ... | |
| Excel formulas | 4/9/2009 |
| Q: I have an excel spreadsheet consisting of 16 criteria columns. In each column underneath there is a ... A: Tania, Y, N and NA are all perfectly okay. I think all you need to know is the number of N's. ... | |
| Sum Numbers only in cell array | 4/9/2009 |
| Q: I have foound many formulas that provide me parts of what I need. I am looking to try and combind ... A: Larry, You can fix any NA's by asking if there is an error with an ... | |
| Sum Numbers only in cell array | 4/9/2009 |
| Q: I have foound many formulas that provide me parts of what I need. I am looking to try and combind ... A: Larry, You may not be able to do this all in one mega-formula, but it can be done. If all ... | |
| Allocating various costs across several months | 4/9/2009 |
| Q: The system prevented me from sending you another response, so I am sending you another question. In ... A: Alex, Although "I can make Excel do anything", sometimes it takes a while to think of the way to ... | |
| Due Dates to Chart | 4/9/2009 |
| Q: I have 1 column that identifies an item and a 2nd column that lists a start date that expires every ... A: Bonita, When you say a "chart", do you mean you want a graphical display like a bar or line ... | |
| Matching two columns to return value in 3rd | 4/9/2009 |
| Q: I have a list with three columns headed: Business Unit, Country, Headcount I have another list with ... A: Alex, All you need to do is apply VLOOKUP, which is designed to look up items from one table ... | |
| How to use Vlookup | 4/9/2009 |
| Q: I know how to use vlookup I want use vlookup on many sheets i am able to use normal vlookup but if ... A: Viabhav, This is really very difficult to do. Instead of VLOOKUP, you have to use a ... | |
| formula for summing values under merged cells | 4/8/2009 |
| Q: I need help in summing the values under merged cells. Basically I have 3 columns(B1,B2,B3) under ... A: Srikanth, I thought there was something you weren't telling me! So your REAL solution is ... | |
| Match Formula ? | 4/8/2009 |
| Q: I have two sheets (these are in different workbooks) and both contain data, of which one column (on ... A: Paul, Instead of a MATCH formula I'd use a VLOOKUP formula here. You should be able to put the ... | |
| formula for summing values under merged cells | 4/8/2009 |
| Q: I need help in summing the values under merged cells. Basically I have 3 columns(B1,B2,B3) under ... A: Srikanth, The formula is +B3+C3+D3 or =SUM(B3:D3) But what you REALLY want to know is how ... | |
| 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: Sahil, To prevent the random numbers from changing all the time, set the workbook calculation ... | |
| Auto fill | 4/8/2009 |
| Q: My question is this: On sheet1 I have 3 columns: Invoice No Partial Value Exchange Rate 1 ... A: Vinay, Is this a 'living document' or a one-shot deal? The answer entirely depends on this. ... | |
| IF AND THEN formula | 4/8/2009 |
| Q: I've actually asked this question of another AllExperts Expert but, he was unable to help and as I ... A: Jon, Thanks for the kudos! Well, this time I might not have the answer you want. In order ... | |
| Count Formula | 4/7/2009 |
| Q: I am trying to write a formula to the how many 'Leads' there are to a certain criteria match. My ... A: Louise, Your formula looks fine, except that I can't see for myself what the defined names are. ... | |
| Scanning ID Barcodes into Excel and have them show Names | 4/7/2009 |
| Q: I recently received two USB barcode scanners for a project at work. What I'm trying to do is pair a ... A: Matthew, All you have to do is make a database that has everyone's name in it, with their ... | |
| Allocating various costs across several months | 4/7/2009 |
| Q: I use MS Excel 2002. I am working on a business travel costing spreadsheet where I need to allocate ... A: Alex, I have a solution in mind which I am sure will work. Its slightly difficult to explain ... | |
| Excel sorting | 4/7/2009 |
| Q: I have a excel based schedule that I need to sort there are 2 consecutive rows that = 1 item. 1st ... A: Cameron, This will probably teach you not to create a database that has linked records across ... | |
| Allocating various costs across several months | 4/7/2009 |
| Q: I use MS Excel 2002. I am working on a business travel costing spreadsheet where I need to allocate ... A: Alex, I didn't receive a workbook from you, unless you used a totally different name and didn't ... | |
| printing highes value on the chart | 4/7/2009 |
| Q: i need some help in chart, i have 5 values which will make "S" type line in excel chart, some times ... A: Shuaib, This is a very difficult problem. I'm not sure I know an answer! The problem (as ... | |
| Excel looking up and returning dynamic ranges | 4/7/2009 |
| Q: I have a range of NUMBERS horizontly. The numbers in the range can be changed. By changing them they ... A: Lukasz, I have found it very difficult to sort through your spreadsheet and figure out what you ... | |
| Allocating various costs across several months | 4/6/2009 |
| Q: I use MS Excel 2002. I am working on a business travel costing spreadsheet where I need to allocate ... A: Alex, This could get very tricky IF your span of dates is allowed to be large! Can the span of ... | |
| VLOOK UP | 4/6/2009 |
| Q: I am using VLOOKUP to get value from another worksheet. I am using below formula and it works fine. ... A: Abhay, Your solution is almost correct. All you have to do is convert the date in Cell A1 to ... | |
| Look up artwork in a workbook | 4/6/2009 |
| Q: How do I prepare and write a formula to look up a particular picture in a workbook? I would like to ... A: Roger, I've seen this done so I know its possible. I don't thoroughly understand it yet, but I ... | |
| 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: Sirajuddin, This is so easy to do, you'll save tons of time! All you have to do is apply 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: Sahit, If you are using Excel 2007 you can use RANDBETWEEN to generate the numbers. if you are ... | |
| Excel Filter | 4/4/2009 |
| Q: I would like to use the excel fliter with the restriction that it cannot hide the rows adjacent to ... A: Vinay, The Autofilter doesn't and won't work that way. Leaving blank cells in a database ... | |
| Automatically Hiding Rows | 4/4/2009 |
| Q: I have a drop list (using validation) in a cell with Yes, No and N/A as options. If a person selects ... A: Shane, yes, its quite possible. But it can only be done with a macro. Since I don't offer ... | |
| Excel 2007: Excel User Interface display | 4/4/2009 |
| Q: Is it possible to load a worksheet with only a range of cells say A1 to J30 without displaying the ... A: Nissan, Those are all things you can turn off in the EXCEL OPTIONS button at the bottom of the ... | |
| Random Question Generation in MS Excel | 4/4/2009 |
| Q: I am handelling an online exam. The exam paper contains 20 questions on each subject. A question has ... A: Mangesh, Your problem is with getting 20 random questions, correct? Here's what I suggest. ... | |
| Display Different Pictures Based on User Response | 4/3/2009 |
| Q: I have a "library" of 24 small pictures (.jpeg or .bmp) on Sheet 2. On Sheet 1, I prompt the user ... A: John, I've seen this done but have never done it. The best I can do is tell you there is a ... | |
| Referencing a tab name based upon a cell | 4/3/2009 |
| Q: I am working on an excel sheet where I have a master tab called "The List". On the list I list ... A: Marc, This is easier than you think! Take a look at the ADDRESS function. It should do just ... | |
| Visual Basic Excel | 4/3/2009 |
| Q: I am trying to create a formula that automatically increments by "1" when I copy to the next sheet ... A: Bob, My approach to this would be to use the CELL("filename") function to return the name of the ... | |
| Logic Functions | 4/3/2009 |
| Q: I am attempting to find a formula that will allow me to only count a cell if in that same row ... A: Emmanuel, So here is your problem statement (with a few [edits]): "I [am] looking [for] a ... | |
| countif(and | 4/3/2009 |
| Q: Have a column of dates, and would like to count number that fall within specific range. ex: Dates ... A: J, I'm curious why you said the question wasn't answered. I gave you a very specific and clear ... | |
| Merge & Match | 4/3/2009 |
| Q: I am being driven mad by this problem! Vlookups are confusing me! I have two sets of customer ... A: Roy, The solution to this is indeed the proper application of VLOOKUP. There are many ... | |
| excel conditional formatting | 4/3/2009 |
| Q: I would like to create a planner that would highlight certain cells when dates are entered in other ... A: Rick, The title of your question contains the answer. Use Conditional Formatting to achieve ... | |
| Scheduling | 4/2/2009 |
| Q: I am trying to make a schedule of machine runs, which happens only on Mondays and Thursdays. Ideally ... A: Agnes, Just make your own calendar! It can look any way you choose. Change the row heights ... | |
| Turn Around Time Calculation (TAT) | 4/2/2009 |
| Q: This is regarding Turn Around Time Calculation(TAT I want to calculate Team TAT but problem is our ... A: Sandeep, When writing a question to an Expert, please keep in mind that we don't know anything ... | |
| Logic Functions | 4/2/2009 |
| Q: I am attempting to find a formula that will allow me to only count a cell if in that same row ... A: Emmanuel, Yes, this is an application for Logical Functions. But I am afraid that I don't ... | |
| Sumif - array? | 4/2/2009 |
| Q: See Snagit attachment. I'm trying to sum the number of days served in column H for any row in which ... A: Dave, You said: " I am curious why you can't enter the text/etc itself versus a cell number for ... | |
| Re: Calculating quartely costs | 4/2/2009 |
| Q: I have the following quarters for the year and length of each quarter: Q1 01-Jul-08 - 30-Sep-08 92 ... A: Pinky, I was afraid you'd say that! This is becoming a complex problem, but of course I can ... | |
| Sumif - array? | 4/2/2009 |
| Q: See Snagit attachment. I'm trying to sum the number of days served in column H for any row in which ... A: Dave, What you are trying to do is select certain records based on multiple criteria, and ... | |
| Re: Calculating quartely costs | 4/1/2009 |
| Q: I have the following quarters for the year and length of each quarter: Q1 01-Jul-08 - 30-Sep-08 92 ... A: Pinky, I'm working on an answer for you but have encountered a question. Actually two ... | |
| Conditional sum of visible cells in a filtered list | 4/1/2009 |
| Q: I have the following formula which is being used to do a conditional sum of a filtered list. ... A: Ashtosh, You don't understand, because that is NOT exactly what is happening! I took your ... | |
| Program Guide | 4/1/2009 |
| Q: I work as a volunteer at a community ( not for profit ) Radio Station. Seems I spend an inordinate ... A: David, Clearly, you need at least two major things: a database of all the programs and a ... | |
| Merge column data from two spreadsheets | 4/1/2009 |
| Q: I have two spread sheets with some columns in common. I would like to merge/combine the updated ... A: Angie, You can use the VLOOKUP function to retrieve information from Spreadsheet 2 (SS2) onto ... | |
| excel date calculation | 3/31/2009 |
| Q: I have a starting date. I then used a workday function to calculate the next series of dates ... A: Kristine, It may not be simple, but I have thought of a way. I'm developing this 'live' as I ... | |
| Sales Data | 3/31/2009 |
| Q: Bill! I think I've asked you a couple of questions before. This one's a doozy. I have a spreadsheet ... A: Cassandra, Your pivot table method might work. Try exploring the MATCH() and INDEX functions ... | |
| Sales Data | 3/31/2009 |
| Q: Bill! I think I've asked you a couple of questions before. This one's a doozy. I have a spreadsheet ... A: Cassandra, There are two ways to do this and they are radically different. I'll just give an ... | |
| Sales Data | 3/31/2009 |
| Q: Bill! I think I've asked you a couple of questions before. This one's a doozy. I have a spreadsheet ... A: Cassandra, The easy way to do this is to use a FILTER (its called an AUTOFILTER in Excel 2003) ... | |
| Sumif Statement 2 conditions, then sum | 3/31/2009 |
| Q: I have a column of years(Y),a column of month(M)and a column of Item Numbers(I). When column Y=2007 ... A: Eric, If you have Excel 2007, use SUMIFS to do this. The function reference describes this ... | |
| sumif with a cell used as criteria | 3/31/2009 |
| Q: I create complicated finanical models in excel 2003. I want to sum numbers across a row for a range ... A: Mark, I think what I would do here is create a column which sums all the rows, and then use a ... | |
| Tracking Metrics | 3/30/2009 |
| Q: I am putting together a spreadsheet to track supplier metrics Column N - All cells in this ... A: Jon, Of course there is a way to do this! Its not exactly the way you've guessed, though. ... | |
| barcode scanner-to-excel | 3/30/2009 |
| Q: I want to make the following. I want to scan barcodes from products and the data are passed on an ... A: Dimitris, You'll have to check with your barcode scanner supplier to see what kind of output ... | |
| Excel - calculating time values which cross midnight & deducting breaks | 3/30/2009 |
| Q: I've got a spreadsheet which is essentially a timesheet for employees. It covers a week and I have ... A: Marmot, In cases like this, where there are lots of different possibilities to detect (did they ... | |
| Complet formulas for dynamic lookups | 3/30/2009 |
| Q: Current formula Uses calc to figure out row range The column is hardcoded but I want the formula to ... A: Yolanda, What you want to do is quite possible, but I don't have enough information to get you ... | |
| Excel Stumper | 3/30/2009 |
| Q: I am working on a sheet that will help calculate Pipe weights, but I have a complicated formula I ... A: Gwen, Thanks for your reply. So here are the steps you need to take to make this work. 1) ... | |
| Excel formulas | 3/30/2009 |
| Q: Bill, I have 20 worksheets in 1 workbook. All 20 sheets are identical, with formulas, except for a ... A: Johan, Something like this MIGHT be possible, but not the clever way you have tried. ... | |
| Large worksheet / extracting partial data | 3/30/2009 |
| Q: I have a huge worksheet with Sales data. I want to automate extraction of the data for various ... A: Rachna, Have you considered or tried making a Pivot Table as a means of creating a report? ... | |
| Large worksheet / extracting partial data | 3/30/2009 |
| Q: I have a huge worksheet with Sales data. I want to automate extraction of the data for various ... A: Rachna, It sounds like you just want to do this ONCE. After that, new incoming data will go to ... | |
| Macro Help | 3/29/2009 |
| Q: I am creating a macro for a bunch of people who run the same report but receive different length of ... A: Staci, Its unlikely that you need a macro to do what you want. Which is probably good news if ... | |
| Perpetual rows | 3/29/2009 |
| Q: I am using a set of documents provided to me by my boss. These two worksheets are linked together ... A: Magnetite, It's difficult to know what you did. Your description isn't precise, and uses ... | |
| Excel - Numbers custom format | 3/29/2009 |
| Q: I would like to know how can I display number in ten thousands, using the custom format for number. ... A: Ofer, In your example, wouldn't you want the number to be 1,234 instead of 12,34? It's the ... | |
| Help with IF formula | 3/28/2009 |
| Q: Enclosed is the information for which I am seeking assistance, please can you help? My Excel 2003 ... A: Maja, Its easy to do this> But you also need to specify what you want to happen if the number ... | |
| Follow up from Excel Issue | 3/28/2009 |
| Q: This is a follow up from http://www.allexperts.com/user.cgi?m=6&catID=1059&qID=4845477 could not ask ... A: Dan, I'll refer you to my original answer which discussed DYNAMIC RANGES. This is what [I ... | |
| Excel Stumper | 3/27/2009 |
| Q: I am working on a sheet that will help calculate Pipe weights, but I have a complicated formula I ... A: Gwen, Yes, I'd like to have a look at your workbook. Send it to EXPERTATEXCEL@AOL.COM and be ... | |
| NEED HELP With Formula to Look up data and replace | 3/27/2009 |
| Q: I am NOT an EXCEL GURU not even close... I NEED help.. Here's the situation I have a spreadsheet ... A: N, I'd like to help but I don't understand what you want. Please ###spell check### your ... | |
| tab and copying formulas | 3/27/2009 |
| Q: i have a spread sheet with 48 tabs. i want to make a summery page of the infomation on each tab. i ... A: Rodney, One really good way to do this is to create the addresses from which you want to pull ... | |
| VALUEIF | 3/27/2009 |
| Q: Here’s my situation- In worksheet number one, I have two columns, Column A has model numbers and ... A: Gary, In order to be able to do this, there can be only ONE INSTANCE of each model number on ... | |
| If statement with multiple columns | 3/27/2009 |
| Q: I have two columns of data,and am trying to find values in one column that are greater than 0, and ... A: Dierdre, If you are using Excel 2007, you can use the AVERAGEIFS() function to do this! Read ... | |
| combining date & time from two columns | 3/27/2009 |
| Q: I have excel 2007, for monitoring air quality reports we get many lines of data each day ... A: Renee, You'll LOVE how easy this is! All you have to do is ADD the DATE and the TIME with a ... | |
| Excel Stumper | 3/27/2009 |
| Q: I am working on a sheet that will help calculate Pipe weights, but I have a complicated formula I ... A: Gwen, Of course this can be done! It will take several steps but you can make a nice user ... | |
| Extract value from R9C15 from multiple sheets | 3/27/2009 |
| Q: I have a workbook. One week of data on each sheets, 52 sheets. I want to extract the value of a ... A: Marion, You're in luck! There is a way to create a 3D range through all your worksheets! Once ... | |
| formula | 3/26/2009 |
| Q: I have been looking at formulas to see if it is possible to pull a range of data(on one line) from ... A: Peggy, Yes, its possible. All you have to do is create LINKS (also called hyperlinks) to the ... | |
| Excel IF statement for dates | 3/26/2009 |
| Q: I currently have an excel IF statement to check the dates on our training records for each ... A: Sherry, Your solution is to make a master summary sheet which brings in a copy of each persons ... | |
| Excel 2007 date calculation and formatting | 3/26/2009 |
| Q: I am using Excel 2007 and I am trying to calculate dates automatically what I am looking for is this ... A: Pat, Excel always knows what date and time it is. Just put the TODAY() function in a cell and ... | |
| Excel 2007 date calculation and formatting | 3/26/2009 |
| Q: I am using Excel 2007 and I am trying to calculate dates automatically what I am looking for is this ... A: Pat, Yes you can! You can do just about anything in Excel. I can't tell you EXACTLY what ... | |
| excel formula | 3/26/2009 |
| Q: count cell when criteria is met until a blank cell. i have a range of values in column A COLUMN A ... A: Solay, The easiest way to do this is with a column of IF statements which check for the ... | |
| Finding number of characters in a cell and... | 3/26/2009 |
| Q: Please help me!!! It will be very helpful for me in my daily work.... I use MS Office 2007 and have ... A: Siraduddin, This can easily be done with a compound (nested) IF statement. If the text is in ... | |
| convert text to .xyz | 3/26/2009 |
| Q: I have data in excel and save it to text file but i need to save this data to file .xyz.How can i do ... A: Rosli, I have never heard of a file extension of XYZ that means anything. So the only ... | |
| RE: Counting Rows/Entries | 3/25/2009 |
| Q: Bill, I just realized the format of my previous example was difficult to comprehend once submitted. ... A: Josh, Lets start over. There's no example with this followup though the text refers to a ... | |
| Hyperlink | 3/25/2009 |
| Q: Is it possible to use a hyperlink in the following way? I want the hyperlink to look at a cell on a ... A: Jill, Sure! Put a VLOOKUP equation in the cell which refers to the cell on the other sheet. ... | |
| Vlookup within a range of dates | 3/25/2009 |
| Q: I am trying to solve an apparently simple problem... but I am missing the right formula it seems. I ... A: Patrick, Hopefully your database contains actual YEARS and DATES rather than text phrases like ... | |
| VLookup (Excel 2000) | 3/25/2009 |
| Q: We have 4 different price lists (different product ranges), with different layouts. I need to import ... A: Gretchen, Is it your goal to ELIMINATE the four separate price lists and replace them with a ... | |
| RE: Counting Rows/Entries | 3/25/2009 |
| Q: Bill, I just realized the format of my previous example was difficult to comprehend once submitted. ... A: Josh, Yes, this is possible. But its annoyingly complex! You'll need to master MATCH() and ... | |
| Graph Designing | 3/25/2009 |
| Q: We are operating DO-228 and Jetstream 4100 tupes of fleets. Now we are using manual system for Load ... A: Pawan, Are you attempting to create a type of "limits chart", that would show weight & moment ... | |
| Inventory Reduction | 3/24/2009 |
| Q: File 1: INVENTORY.xls Columns A,B,C,D Descriptions ID#,DESCRIPTION,AMOUNT,UNIT Row 1 ... A: Robin, All cell references [that I have ever seen in 20+ years of using Excel] are of the form ... | |
| Excel 2000 | 3/24/2009 |
| Q: My company uses a quoter tool that was built with Excel 2000 that we want to do some additional ... A: Dora, You have to use the decimal number validation, NOT the "custom". Custom doesn't provide ... | |
| Formula Question for MS Excel 2003, Using SUMIF | 3/24/2009 |
| Q: The Basics: My company uses an excel sheet called the GPS (General Production Schedule) that list ... A: Ian, What you need is to have those ranges become DYNAMIC so they adjust to the data ... | |
| Consolidate Data From Other Files | 3/24/2009 |
| Q: I would like to consolidate data from all the excel files in a specific folder into one table in ... A: Zohar, You can create links to any cell in any other workbook, which will retrieve the value of ... | |
| Excel 2000 | 3/24/2009 |
| Q: My company uses a quoter tool that was built with Excel 2000 that we want to do some additional ... A: Dora, You can use the data validation dialog boxes to accomplish this. All you have to do is ... | |
| Problem with my formula | 3/24/2009 |
| Q: I am having a problem with one of my formulas, it is returning a #VALUE! error. My formula is; ... A: Louise, Your formula looks correct, but there is one important thing I can't tell. All of ... | |
| Inventory Reduction | 3/24/2009 |
| Q: File 1: INVENTORY.xls Columns A,B,C,D Descriptions ID#,DESCRIPTION,AMOUNT,UNIT Row 1 ... A: Robin, This is a very tricky thing to do in Excel. I assume that you will next want to ... | |
| Excel | 3/24/2009 |
| Q: I have an excel spreadsheet which contains the name and address details in the same field but ... A: Naomi, This is a very common problem that has numerous solutions. 1) First thing to try is ... | |
| Installing Microsoft Office | 3/24/2009 |
| Q: From: I recently installed a larger Hard Disk in my computer and transferred all programs from the ... A: Henk, Generally you can't just 'transfer' all your programs from one hard disk to another. I ... | |
| Formulas in workbook | 3/24/2009 |
| Q: I have several worksheets and I was wondering if it was possible to create a formula to ... A: Robert, If you desire ONE formula to read ALL your worksheets, it is POSSIBLE but that might be ... | |
| drop menus and matrix | 3/23/2009 |
| Q: I'm working on setting up a spreadsheet to track and manage internal Non Conformance Reports for the ... A: Drew, Certainly it is possible! What you need to do is make a matrix of all thew ... | |
| Identifying cells with text and numbers | 3/23/2009 |
| Q: Good Afternoon, I am working with data that has been converted from a .pdf to an .xls document. In ... A: Sean, I'm glad you have a working solution! However, I still think that sorting your data ... | |
| Identifying cells with text and numbers | 3/23/2009 |
| Q: Good Afternoon, I am working with data that has been converted from a .pdf to an .xls document. In ... A: Sean, I'm not quite sure of your data structure (despite your good effort to describe it). Do ... | |
| Extract text from a cell | 3/23/2009 |
| Q: I am trying to write a piece of VB or a formula that will enable me to extract a text from a ... A: Abhijit, The way to do this is to try to FIND() each of the 10 numerical characters, and then ... | |
| Vlookup | 3/23/2009 |
| Q: I am using a vlookup function and want use an the cell above my formula to define the table_array ... A: Brian, Let's say you have that reference typed into cell B10. What you are probably doing is ... | |
| excel 2003-data formatting | 3/21/2009 |
| Q: Bill, greetings from Highlands Ranch. I have a 9 digit number in each cell of column A, going down ... A: Bob, I'd suggest that you save the Excel file as a TXT type (using SAVE-AS). This will give you ... | |
| Extract text based on return carriage | 3/21/2009 |
| Q: I am trying to extract text from a single cell that has been used for weekly text updates. Every ... A: JJ, I don't quite see how you can have the data you describe, because when you enter a RETURN ... | |
| Linked Drop Down Boxes | 3/20/2009 |
| Q: I do not know if my goal is possible. What I would like is to creat a drop down box in one column ... A: Christina, Its not only possible, it's QED (that's Quite Easily Done). I want to make sure I ... | |
| Excel: Re-organise table data into list | 3/20/2009 |
| Q: I'm stuck on creating a worksheet which will read data in the following format: PRODUCT1, 02, 05, ... A: Dave, Would you mind sharing your workbook with me? I'd like to see the results. If you will, ... | |
| Comparable Sales Spreadsheet | 3/20/2009 |
| Q: I have created a spreadsheet with residential property information in it. Information for each ... A: Donald, Its difficult to make Excel retrieve multiple rows that meet multiple (or even single) ... | |
| Having excel determine which weights to assign to each category to match desired percentages | 3/20/2009 |
| Q: Bill. The solution to this problem might require a macro, but I thought I'd ask you first. I have ... A: Sheldon, I don't think this can be done without a macro. I tried several crafty and clever ... | |
| Create summary from a sheet that takes data from sheet that's calculated based on a drop-down cell | 3/19/2009 |
| Q: I have one master sheet that has all my formulas that I calculate (formula sheet). In addition, I ... A: Shradda, This is called a SCENARIO. I haven't used this feature very much so I can't give you ... | |
| dropdown | 3/19/2009 |
| Q: i have 3 drop downs, want to force the user to use the second dropdown if the first dropdown have a ... A: Octavio, Here is one of my methods for doing this. 1) make a table whose column ... | |
| Excel: Re-organise table data into list | 3/19/2009 |
| Q: I'm stuck on creating a worksheet which will read data in the following format: PRODUCT1, 02, 05, ... A: Dave, Holy cow, you got it to work? Congratulations are in order! Were there any problems? ... | |
| Excel | 3/19/2009 |
| Q: I am writing a bespoke Excel application which analyses results from a monthly survey. I will be ... A: Brian, I can think of several possible solutions. 1) when you protect a sheet, Excel offers you ... | |
| Sorting and listing data | 3/19/2009 |
| Q: I'm looking to create a spreadsheet where you can click on a customer or account manager and it will ... A: Geoff, If by "total LEVEL of sales" you mean a SUM of sales for the selected customer OR account ... | |
| Excel: Re-organise table data into list | 3/19/2009 |
| Q: I'm stuck on creating a worksheet which will read data in the following format: PRODUCT1, 02, 05, ... A: Dave, I'm sure I can come up with a solution, but first I must understand the nature of your ... | |
| NCAA tournament bracket | 3/19/2009 |
| Q: I hope you are a sports fan, but if not I think you will get the basis of my question. I have a ... A: Nick, Examine the dialog box contents for the data validation list in one of the copied sheets. ... | |
| repeat a value until new value is seen | 3/18/2009 |
| Q: The title of my spreadsheet is "binchk-all kathy entities 3-9 IN PRO", and I will be sending you a ... A: Scott, Well, there is sort of a way to do this, but of course it's not 'automatic'. Its a ... | |
| Counting duplicates and listing the rows they appear in. | 3/18/2009 |
| Q: Excellent site you have :) But I have a question i can't find the answer to; I have an excel sheets ... A: Per, The reason you have this problem is because you have designed an unworkable data structure. ... | |
| Make changing data static | 3/18/2009 |
| Q: Using Excel 2003, I am attempting to create an internal audit management system. Not the best ... A: Steven, This is a particularly vexing problem for Excel, but perhaps in your case there is a ... | |
| Excel 2003 | 3/18/2009 |
| Q: I am trying to populate a form by placing a distinct indentifier. For example on sheet 1 I have a ... A: Chris, For each value that you want to pull, use a VLOOKUP() function to retrieve it. The ... | |
| Excel - countif using ranges | 3/18/2009 |
| Q: I am trying to save time making a chart. I have tried to be clear in my explanation - I apologise ... A: Sarah, You're looking for a multiple criteria record-summer/counter. Luckily there are several ... | |
| function question | 3/18/2009 |
| Q: I am trying to make a function where excel searches a column, on a separate worksheet, for a ... A: Damien, Use the VLOOKUP function. It's specifically designed for this! If you have ... | |
| Trying to Calculate Date of Retirement | 3/18/2009 |
| Q: I am working with Excel 2003 and I need to calculate the date of retirement of staff. I have all the ... A: Peju, I assume you know their birthdays, right? Otherwise it's impossible. So here's what ... | |
| Select a the most in the right value of a row array | 3/18/2009 |
| Q: Lets say I have the following for row 1: A1 B1 C1 D1 E1 F1 G1 3 2 6 ... A: Leonardo, It can be done using a nested IF statement. Each conditional test will be ... | |
| Summary of random non-congruent list | 3/17/2009 |
| Q: Bill, Macintosh Excel 2008 - I have a time sheet that employees enter daily that includes a Project ... A: Jay, There are several ways to do this. If a pivot-table meets your definition of a ... | |
| Conditional formatting to match any cell in a range | 3/17/2009 |
| Q: I am working in Construction Project Management, and I have a worksheet that is tracking changes. I ... A: Robert, There might be a way to prevent typos from ever occurring. If you don't like this idea ... | |
| read cell, search for the content in other sheet | 3/17/2009 |
| Q: I have two sheets looking something like: A B C D 1 101 red 0,5 2 123 blue 1,8 ... A: Peter, I have a feeling that this can be done WITHOUT a macro, but I am not sure because I ... | |
| Formula to calculate additional rent % of sales after a guaranteed fixed sum is met | 3/17/2009 |
| Q: I need the results of the test in a number format that represents the amount of additions rental 20% ... A: Milton, Here is an answer for you. Its very simple and I am sorry it took so long. What ... | |
| Advanced Excel sum of number of employees based on time of day | 3/16/2009 |
| Q: I have a question regarding schedules for employees. I am in charge of scheduling 300 employees in ... A: Jack, I thought so! Luckily, the main part of my answer is still completely correct. Start ... | |
| Filter of my own | 3/16/2009 |
| Q: I have two sheets, first is like application for invoices, second contains product name, ean codes ... A: Dario, There is a way to make this happen but its VERY complex. I can outline it for you in a ... | |
| Advanced Excel sum of number of employees based on time of day | 3/16/2009 |
| Q: I have a question regarding schedules for employees. I am in charge of scheduling 300 employees in ... A: Jack, Its Quite Easily Done (QED). Select a cell that represents the time of day that you ... | |
| I am having dificulty applying your fix | 3/16/2009 |
| Q: So you need to devise a test for a POSITIVE INTEGER. And then IF the test passes (is TRUE) you'll ... A: Milton, Other people's worksheets are rarely 'self-explanatory". Which number (and I want ... | |
| excel two criteria sumif dispite reading help cant get it to work | 3/16/2009 |
| Q: I have been playing with the array formulas in help and believe the following is the best, but can ... A: Brett, There is certainly a way (or two) to do what you want, but before I write a big ... | |
| xl in office 2000 | 3/16/2009 |
| Q: I want to populate a cell with the value of another cell only if it that cell has a positive integer ... A: Milton, So you need to devise a test for a POSITIVE INTEGER. And then IF the test passes (is ... | |
| Calculate balance after autodrafts | 3/16/2009 |
| Q: Excel 2000 I am tracking my personal accounting in a spreadsheet and have a number of ... A: Joshua, I would have the 'calendar' of dates already set up to show the days of the month on ... | |
| Sales cascade model | 3/16/2009 |
| Q: I'm struggling to develop a formula that addresses the following problem (which I believe is called ... A: Andy, I propose a slight change to the main table you're working with. Instead of having ROW 1 ... | |
| Returning rows with data | 3/16/2009 |
| Q: I have a spreadsheet for a customer that has a list of products and services and prices. He wants to ... A: Dan, I've given your workbook a brief look, and it looks as though it works properly. When I ... | |
| Breaking down an address column | 3/15/2009 |
| Q: I am trying to break a single address field into individual pieces and place those pieces in ... A: Charles, What if someone enters and address as 1234 Carl Lake Shore (and omits the 'Drive"? ... | |
| Excel 2007 version | 3/15/2009 |
| Q: I recently visited Ancestry.com. A file I was looking at attached itself to my Excel 2007 as a new ... A: Wynneth, You said: "I guess I should have 5,000+ lines of information. " What? Where? In an ... | |
| Excel 2007 version | 3/14/2009 |
| Q: I recently visited Ancestry.com. A file I was looking at attached itself to my Excel 2007 as a new ... A: Wynneth, I can't seem to figure out what happened to you, but this isn't really an Excel ... | |
| Lookup matching two values. | 3/14/2009 |
| Q: I have two tables each with three columns; SKU, Plant, Order Quantity. One table is complete with ... A: Michael, I think what you are asking is to add up the total ORDER quantity in each row that ... | |
| using excel to calculate YTD ! ? | 3/14/2009 |
| Q: I need help finding a formula that will continue to add to the total amount weekly so I can ... A: Tony, The Y-T-D is an accumulating total. It is the total from each day added together. The ... | |
| Two columns into one in Excel | 3/13/2009 |
| Q: I have this problem but couldn't solve. I am sure you being expertise in the filed could definitely ... A: Laura, This actually quite difficult to do unless you write a simple macro to do it. You ... | |
| excel help | 3/13/2009 |
| Q: I have a database that allows user to search for sounds in words. I have also have lip shape images ... A: Collins, Excel may not be your best bet to make your application work. The 'animation' you ... | |
| Comparing multiple columns and conditions in Excel. | 3/13/2009 |
| Q: I'd like to know if it is possible to create a formula that will compare conditions in multiple ... A: Kelly, Yes, there is a way. There are actually several ways. For Excel 2007: Use the ... | |
| Auto Populate Cells | 3/13/2009 |
| Q: Ok, so I will try to explain this is as best I can: I am working with two worksheets: Daily and ... A: Ron, I think you need to take another crack at phrasing your question. There are too many ... | |
| count the number is instances of two conditions | 3/13/2009 |
| Q: I'm working on a spreadsheet to calculate a proposal win rate. I want to calculate the number of ... A: Kristin, Yes, there is a way. There are actually several ways. For Excel 2007: Use the ... | |
| dashes!!! | 3/13/2009 |
| Q: i finally figured how to add dashes to numbers in a cell and all the way down the column now how do ... A: Annie, I need to know what these numbers ARE. Are they TEXT or are they NUMBERS? Pick ... | |
| Cell Fill Color | 3/13/2009 |
| Q: I have worksheet with columns of suppose 1st Jan to 31 july and the first 4 columns with activity, ... A: Sarwar, I cannot send you a fully-working schedule system for free. It will take me many ... | |
| Random Generator | 3/12/2009 |
| Q: I am creating a random name generator. column A - have the cells numbered column B - First names ... A: Veronica, I think what you mean is that you are trying to create a random number generator ... | |
| Time converting Macro | 3/12/2009 |
| Q: I saw this question on this website but it didn't have an answer and this is exactly what is ... A: Cyndee, You also are probably misusing the word MACRO. You need a FORMULA, not a MACRO. Look ... | |
| Count unique data in unfiltered rows | 3/12/2009 |
| Q: I am using the following formula to count data in visible rows when I filter ... A: Chris, Please start over as I don't understand what you're getting at. There are several ... | |
| Time Conversion i Excel 2007 | 3/12/2009 |
| Q: Please help! How can I convert 36 hrs and 30 mins (36:30 in [h]:mm format), to 36.50 (number with 2 ... A: Alan, I think the best way to do this is to take the TIME number apart, do a calculation, and ... | |
| SPREADSHEET DESIGN | 3/11/2009 |
| Q: I am an accountant for a holding company with 2 main business segments and with 1 segment having 7 ... A: Tina, The key to making a report such as this work properly and easily is the organization of ... | |
| Excel 2003 Formula | 3/11/2009 |
| Q: Currently I am running MS Office 2003 on Windows XP. I am working on a new spreadsheet that will ... A: Chad, Oops, I knew I should have re-read your question! ... | |
| Count unique data in unfiltered rows | 3/11/2009 |
| Q: I am using the following formula to count data in visible rows when I filter ... A: Chris, I hate to point this out, but the formula you supplied may be behaving differently than ... | |
| Lookup table results don't compute | 3/11/2009 |
| Q: I've created a spreadsheet that tracks my stocks and mutual funds performance. I copy and paste the ... A: Bernie, Sounds like quite an involved spreadsheet, you should be an expert too! I see one ... | |
| autosum in vista excel | 3/11/2009 |
| Q: I have a VERY long column of data and I want to sum every 15 values. I cannot work out how to get ... A: Jack, First off I want to point out that when you say you want to add 0-15, 15-30, 30-45 you ... | |
| Calling Variables in other sheets and workbooks | 3/11/2009 |
| Q: Can you help me? I have a sheet named 'Attached Files' in workbook 1. I have the file names where ... A: Dan, If you have working formulas that retrieve the correct cell references, then you probably ... | |
| Frequency of Dates | 3/10/2009 |
| Q: I have dates for when people arrived and departed. I would like to know if there is a way to find ... A: Brandon, Do you mean, if you have 10 years of data, you want to summarize all 10 May 1sts, all ... | |
| Find_Sum | 3/10/2009 |
| Q: I'm wondering if Excel has a formula that would find a combination of numbers that makes up a ... A: Binh, Excel does not have a function to do this. Its actually a terribly complicated computer ... | |
| Frequency of Dates | 3/10/2009 |
| Q: I have dates for when people arrived and departed. I would like to know if there is a way to find ... A: Brandon, Its easy to do this, assuming the the structure of your data & people table is nice and ... | |
| Date formatting | 3/10/2009 |
| Q: I would like for you to convert these dates (82508, 101408)using the mid function formula (excel. ... A: Greta, 1) I asked you to enter =ISTEXT(date-cell) and ... | |
| conditional formating and matching multiple | 3/10/2009 |
| Q: Right now I have two different tables on the same sheet. Across the top in row one is the employee ... A: Laura, Its possible but somewhat involved. Read about the MATCH and INDEX functions and be ... | |
| Date formatting | 3/10/2009 |
| Q: I would like for you to convert these dates (82508, 101408)using the mid function formula (excel. ... A: Geeta, I can't do this without knowing what "format" these dates are inright now. Are they ... | |
| Need to assign points to population in a group based on their rank in that group | 3/10/2009 |
| Q: I have a list of people ranked (the lower the rank the better) and I need to assign 5 points to the ... A: Chris, I hope you know about the RANK function, because you should apply this first. Put it in ... | |
| Get the minimum value | 3/10/2009 |
| Q: I am trying to get the minimum value of column values based on the value of another cell. ie Get ... A: Chris, Create another column to change the numbers with an associated N into blank cells, and ... | |
| "date" formatting in mixed cell | 3/9/2009 |
| Q: I have the following formula in several cells... ="text "&C1553&" more text. Even more text ... A: Elliot, Each time you &-in a number, embed the number inside a TEXT function. The second ... | |
| Calling Variables in other sheets and workbooks | 3/9/2009 |
| Q: Can you help me? I have a sheet named 'Attached Files' in workbook 1. I have the file names where ... A: Dan, Would you kindly tell me which portion of your statement is the actual question? You ... | |
| dashes!!! | 3/9/2009 |
| Q: i finally figured how to add dashes to numbers in a cell and all the way down the column now how do ... A: Annie, I'm guessing you made a formula which uses a number in another column, and now you want ... | |
| Hide/show cells for specific users | 3/9/2009 |
| Q: Hope you are in good health. i have excel sheet in which i define ranges for data entry for 3 ... A: Shafi, You can't password protect different parts of the same sheet with different passwords. ... | |
| Cell Fill Color | 3/9/2009 |
| Q: I have worksheet with columns of suppose 1st Jan to 31 july and the first 4 columns with activity, ... A: Waheed, The answer is CONDITIONAL FORMATTING. That is the ONLY way to change the color of a ... | |
| Returning rows with data | 3/9/2009 |
| Q: I have a spreadsheet for a customer that has a list of products and services and prices. He wants to ... A: Daniel, It sounds like what you need are DYNAMIC RANGES, which expand automatically to ... | |
| editing data | 3/8/2009 |
| Q: i have 7 numbers in a cell. 500 cells in a column. I have to insert a 0 between the 1st and 2nd ... A: John, Sounds crazy but here goes! Use a series of steps (which may require more than one ... | |
| Excel 2004 v11.3 for Mac | 3/7/2009 |
| Q: Mr. Hermanson, I am attempting to add functionality to an on-going Excel project. I have multiple ... A: David, It would seem that I recently answered a very similar question like this. What's up with ... | |
| Link different sheet from Drop down List | 3/7/2009 |
| Q: I need to create an index from a drop down list found in developers tab. Well i can do that portion, ... A: Sami, You want to JUMP to another sheet? You don't want to retrieve Sami's infor? The ... | |
| Excel Protection | 3/6/2009 |
| Q: We have an excel worksheet at work that we need to protect just certain cells that contain formulas. ... A: Jessica, In Excel 2003 & 2007, when you protect a sheet, a menu appears which lists all the ... | |
| Excel Variable Use/Formula Reference in Cell | 3/6/2009 |
| Q: HELP I am trying to SUM column K(Row# = Value in N3):K(Row# = Value in O3) FORMULA ... A: Dan, There is an Excel function that does EXACTLY this! Its called SUMIF(). SUMIF uses a ... | |
| percentages | 3/6/2009 |
| Q: my question is how do i can i figure out the percentage incrase in the number of subscribers for the ... A: Abdul, Its quite easily done (QED)! I apologize for thinking this was homework. I ... | |
| percentages | 3/6/2009 |
| Q: my question is how do i can i figure out the percentage incrase in the number of subscribers for the ... A: Abdul, Is this a homework problem? Do you understand the meaning of a "percentage ... | |
| Number format in concatenate | 3/6/2009 |
| Q: How do I keep the number formatted in the concatenate result as shown the original? For ex: ... A: XiDau, You change the NUMBER into TEXT with the TEXT function and then concatenate it into your ... | |
| Counting colors | 3/6/2009 |
| Q: I need a formula that will allow me to count colors. In column f3:F233 I have a list of varied ... A: Stuart, Are these colors actually cells formatted as the colors, or are they the words RED, ... | |
| Count If function with filter | 3/5/2009 |
| Q: I am using Excel 2007 and am wondering how to apply a "Countif" function to filtered cells. When I ... A: Nate, I created a quick workaround for this, but I don't know if you'll like it. With the ... | |
| Conditional Formatting, Cell Upon Cell | 3/5/2009 |
| Q: A co-worker wants to color-code a summation field, so that if ALL of the cells in a row are GREEN ... A: Doug, There is no function which detects the color of a cell and reports a value: so you can't ... | |
| Values determined by other cells. | 3/5/2009 |
| Q: I have a spreadsheet where I'm tracking mulitple quotes recieved from vendors for work requested. ... A: Krista, The problem you have is that you are trying to make one poor cell do two different ... | |
| recommended appropriate function for the following? | 3/5/2009 |
| Q: I have the following sheet: - i would like to categorize column 2 "days since log" into the last ... A: Dianna, Since "I can make Excel do anything", this is quite possible. In fact, you'll be glad ... | |
| Cell Fill Color | 3/5/2009 |
| Q: I have worksheet with columns of suppose 1st Jan to 31 july and the first 4 columns with activity, ... A: Waheed/Sarwar (not sure of your name!, Yes there is a way. Its called CONDITIONAL FORMATTING. ... | |
| Excel Count Individuals not visits | 3/5/2009 |
| Q: first of all thank you for your time. The problem I have is as follows: Lets say the first column is ... A: Simon, You'll notice that when you make a pivot table and you put the NAME in the row area, it ... | |
| VLookup | 3/4/2009 |
| Q: Created a simple 4 column spreadsheet in cells AA4:AD64 (not visible without moving page to the ... A: Kay, It is unlikely that your second formula (the one on the new sheet)returned the correct ... | |
| Count multiple columns in excel | 3/4/2009 |
| Q: Bill, I'm attempting to count data in 2 separate columns from a different worksheet. I attempted ... A: Rusty, Write a followup that includes the failed equations, and the results they reported. I ... | |
| recovery of data | 3/4/2009 |
| Q: sir, I have opened an excell sheet which is already exist,for example "A" and Deleted the inner ... A: Guru, Sounds like you made a serious mistake and destroyed your original file. You should be ... | |
| MS Excel | 3/3/2009 |
| Q: Mr. Hermanson, I am interested in adding some functionality to an existing Excel project and need ... A: David, What you mean is this: For every letter in the first position (Col G), you want to know ... | |
| Simple Excel Chart Line graph | 3/3/2009 |
| Q: I'm trying to create a simple chart and I KNOW it has to be possible but it's giving me trouble. I ... A: Lisa, Each of your 'groups' is called a data series. There are several techniques you can ... | |
| Transfer One Worksheet's Data Across Multiple Worksheets, Sequentially | 3/3/2009 |
| Q: I'd like to know if there is a formula to transfer individual cell data from a master worksheet to ... A: Bryan, Its an interesting idea to try to make a formula from a text string! You can certainly ... | |
| Needs to find missing records | 3/2/2009 |
| Q: I will get data from a machine at every one hour. I have some historical data @ hourly basis i.e., ... A: Srikanth, If a record is missing, how can you find it????? Its gone, right? There is no record ... | |
| Borders in excel 2003. | 3/2/2009 |
| Q: I want the BORDERS TO REMAIN whenever i move a range of cells containing datas with borders from ... A: Nabam, Perform a COPY by selecting the cell region and then hitting CONTROL-C, as you ... | |
| Returning rows with data | 3/2/2009 |
| Q: I have a spreadsheet for a customer that has a list of products and services and prices. He wants to ... A: Daniel, Have you tried to apply VLOOKUP or MATCH & INDEX to this problem? It seems like a ... | |
| Excel | 3/2/2009 |
| Q: Cust.ID Month Yr sale type product bus.line sale amt. other 1350 12 2002 sale coffee ... A: Mohammad, Because this sounds like a homework problem, I'll teach you how to solve it, but I ... | |
| Conditional formatting -> Charts | 3/2/2009 |
| Q: This may be a short and sweet no, but I am wondering if it is at all possible to create / update a ... A: Aaron, You could apply a FILTER or ADVANCED FILTER to your data table. Use the same criterion ... | |
| chart problem | 3/1/2009 |
| Q: I am currently facing a problem regarding chart (bar) the table columns are as bellow: USD AMT ... A: Sami, I can't tell you how to do it right until I know how you are doing it wrong. What is ... | |
| pasting and linking highlighted cells into another sheet that sums values | 2/28/2009 |
| Q: I am trying to setup the accounting portion of a small business that will allow me to copy and paste ... A: Al, It sounds like what you really want to do is to create an employee database which will ... | |
| Remove text in paranthese from cell | 2/27/2009 |
| Q: I have a column as below and I need to seperate out the text in parenthese from the cells that have ... A: Stephen, You're on the right track using SEARCH() and LEFT(). Add RIGHT() and LEN() to the mix ... | |
| Excel | 2/27/2009 |
| Q: I have designed a form in an excel program (name: new customer service call) and would like to have ... A: Richard, Your followup question is very confusing. Specifically I don't understand what ... | |
| Transfer One Worksheet's Data Across Multiple Worksheets, Sequentially | 2/27/2009 |
| Q: I'd like to know if there is a formula to transfer individual cell data from a master worksheet to ... A: Bryan, I can think of several ways that MIGHT be applicable to your situation. They all seem ... | |
| copying large ranges from one worksheet into another | 2/26/2009 |
| Q: I have 3 workbooks, each having similar data for different years comprising of over 20,000 records. ... A: Henny, It won't take long to highlight if you know how to use the END key on your numeric ... | |
| CARRY OVER FORMULA | 2/26/2009 |
| Q: MY SPREADSHEET IS VERY BASIC. IN CELL L20 IS TTL DUE. I NEED THIS AMOUNT TO CARRY OVER TO A NEW ... A: Shirley, If I understand you correctly, the mistake you seem to have made is assuming that the ... | |
| EXCEL | 2/25/2009 |
| Q: I have imported an XML document into EXCEL it works great no problem, everything is good until, when ... A: Philip, What is the full file name of the document -- I am especially interested in the ... | |
| Two data series Excel Graph - Actuals/Projected | 2/25/2009 |
| Q: I would like to create a bar graph for two series with each one having different colors. The data ... A: Alex, Thanks for clarifying your problem. I think I understand what you're doing incorrectly. ... | |
| format | 2/25/2009 |
| Q: I have a database in excel in a set order, I have now changed my autoresponder for sending out my ... A: Derek, I'm not exactly sure what you mean. I don't know what an autoresponder is or does or ... | |
| Query reg: Ms Excel | 2/25/2009 |
| Q: I have data like this EmpNo. Loan Amt. Date 101 3000 01/02/2008 105 5000 ... A: Raghu, By now you must have solved this! If not, read on! It COULD be done with ... | |
| Help Wanted | 2/25/2009 |
| Q: I am working with two worksheets in Excel. I need to update several columns in spreadsheet 1 with ... A: Jide, I'm sure I can help you since I am an expert in VLOOKUP and all the other reference ... | |
| Two data series Excel Graph - Actuals/Projected | 2/24/2009 |
| Q: I would like to create a bar graph for two series with each one having different colors. The data ... A: Alex, So what's the problem? Select the data set, invoke the chart wizard, and follow the ... | |
| Simple formula | 2/24/2009 |
| Q: I need to combine multiple lines in excel that have the same description but different quantities. ... A: Jade, There are many ways to do this. But none of them will allow you to destroy or delete the ... | |
| Searching in Excel | 2/24/2009 |
| Q: I know how to find and search for numbers and even words in Excel, but I am wondering if it is ... A: Jeff, Its not only possible, its easy! For each word you want to search for, create a column ... | |
| Query reg: Ms Excel | 2/24/2009 |
| Q: I have data like this EmpNo. Loan Amt. Date 101 3000 01/02/2008 105 5000 ... A: Raghu, I'm not sure I understand. Given your example data, do you want next to 101 the number ... | |
| Sick Leave Accrual | 2/23/2009 |
| Q: I am looking for a formula that will accrue sick leave at a rate of 0.25 hours per month, based upon ... A: Karen, I have a couple of ideas for you. Would it be accurate enough to take the number ... | |
| Extracting text in a varying number of rows, from specific text to another specific text | 2/23/2009 |
| Q: I am Using Excel in the Office and Student package. Hi, I have a huge list from which I want to ... A: Simon, Okay, I'll at least get you started. 1) add three columns to the right of your text. ... | |
| Extracting text in a varying number of rows, from specific text to another specific text | 2/23/2009 |
| Q: I am Using Excel in the Office and Student package. Hi, I have a huge list from which I want to ... A: Simon, Thanks for the answers. I'd like one more answer, I think you missed an important ... | |
| Extracting text in a varying number of rows, from specific text to another specific text | 2/23/2009 |
| Q: I am Using Excel in the Office and Student package. Hi, I have a huge list from which I want to ... A: Simon, This could be really difficult or it could be easy, depending on the answers to several ... | |
| Charting Question | 2/22/2009 |
| Q: I have a chart in excel as shown in the image. At 0 on the horizontal axis I just want to add a ... A: James, If you don't need to be so exactly precise as the numbers you gave, you can add a line ... | |
| FIFO and LIFO | 2/22/2009 |
| Q: I am FIFO and LIFO retarded I think. I have view several websites and also review my book several ... A: Kellie, Where have you been reading about FIFO and LIFO that has you so confused? FIFO means ... | |
| Multiple check boxes | 2/21/2009 |
| Q: I have create a check box which gives the cell a value of true when ticked. However when I drag the ... A: Phil, This is a VBA-related question which I cannot answer. My profile explicitly says I don't ... | |
| Drop downs and Lookups | 2/21/2009 |
| Q: I am a novice Excel user and am so far very happy that I have been able to write simple formulas and ... A: Kris, I makes perfect sense and is clearly stated. And there is a simple solution, which will ... | |
| excel sheets | 2/20/2009 |
| Q: I am entering data onto an excel spreadsheet, names, lunch choices, class choices for three ... A: Janet, I think what you really mean is that everyone who chooses class A would have their info ... | |
| nested if statements? | 2/20/2009 |
| Q: I work in a call centre and we are currently using Excel to track work orders/tickets that we open ... A: Krista, Sounds like you are on track. In addition to removing the extra parens, don't ... | |
| nested if statements? | 2/20/2009 |
| Q: I work in a call centre and we are currently using Excel to track work orders/tickets that we open ... A: Krista, The thing to keep in mind is the structure of the IF statement. It goes like this: ... | |
| Hyperlinking(?) a moving destination cell | 2/20/2009 |
| Q: Using Excel 2003 on a PC at work, and OpenOffice.org Calc on a Mac at home, I have created a ... A: Jacobo, I found a solution to this and tested it. It works! I used Excel 2007, so the ... | |
| Having Realtime in excel without having to select F9 | 2/20/2009 |
| Q: I asked the following questions which was answered as Yes, However please would you be so kind as to ... A: Allan, Thanks for the 10! This is from the FAQ for experts on the site. ... | |
| counting items in an unfiltered table | 2/20/2009 |
| Q: I have a table (33 columns wide, x rows deep, where x is in the thousands). Each cell in the data ... A: Kenneth, It is even more confusing now. I knew your original question was out of context ... | |
| vba tip | 2/19/2009 |
| Q: I want you to show me an easy way to do a problem in excel. I have a data where along a column there ... A: Mekdim, You could use VBA for this but if so, I can't help you because I don't offer VBA advice. ... | |
| Data Manipulation in Excel | 2/19/2009 |
| Q: I was wondering can you help me with a small problem I am having in Excel. I'm trying to create an ... A: Dominic, Did you say a pivot table DOES create the results you want? You said: "I had an ... | |
| Data Manipulation in Excel | 2/19/2009 |
| Q: I was wondering can you help me with a small problem I am having in Excel. I'm trying to create an ... A: Dominic, As you have found, VLOOKUP & MATCH only return the first instance of the search item ... | |
| Cell show formula instead of Value | 2/19/2009 |
| Q: Good day Bill, Some cell are showing the formula while some are showing the results. The Ctrl-~ ... A: Danie, Perhaps those formulas really aren't formulas at all. Perhaps there is a hidden ... | |
| counting items in an unfiltered table | 2/19/2009 |
| Q: I have a table (33 columns wide, x rows deep, where x is in the thousands). Each cell in the data ... A: Kenneth, This is quite easily done! I want to make sure I understand exactly what you ... | |
| Merging two Spreadsheets that share an identical fiels | 2/18/2009 |
| Q: I am using excel 2003 in XP and I have two spreadsheets, one has a list of particular group of ... A: Leo, The answer to your question is the application of VLOOKUP. You need one instance of ... | |
| Linking 1 to many in worksheets | 2/18/2009 |
| Q: I have a workbook with two worksheets. The first worksheet contains a list with a single reference ... A: Alex, I suggest a slightly different approach. A Hyperlink won't quite behave this way. ... | |
| Reference formula only from another sheet | 2/18/2009 |
| Q: I have an IF function being used in my "Master" sheet. I have about twenty other sheets with the ... A: Nick, I am still confused by your explanation. You seem to have a fundamental misconception of ... | |
| Graph problems | 2/18/2009 |
| Q: I am trying to make my graph concentrated into the print area so that when I print I can get the ... A: Lalita, If your graph is on a sheet where you can see cells and there are other things besides ... | |
| Reference formula only from another sheet | 2/18/2009 |
| Q: I have an IF function being used in my "Master" sheet. I have about twenty other sheets with the ... A: Nick, This is too vague to give you a specific answer. can you be more precise about what you ... | |
| Auto-updating work book | 2/18/2009 |
| Q: I have created a work book for maintenance scheduling. Presently for a user to access the work sheet ... A: Dan, No, I am sorry but Excel doesn't work that way. Those are the limitations as I understand ... | |
| find a data | 2/18/2009 |
| Q: and thank you in advance for your help. Well my question is the follow; First: I want to find a data ... A: DEMC, You can add a COUNTIF(column, data) for every column in the other worksheet! If you are ... | |
| Excel | 2/18/2009 |
| Q: I have designed a form in an excel program (name: new customer service call) and would like to have ... A: Richard, It sounds like what you are trying to do is: A) fill out a form for a given ... | |
| Inserting PDF file into Excel 2003 | 2/17/2009 |
| Q: My users are no longer able to insert a PDF document into Excel 2003. Have tried Insert, Object. I ... A: Timothy, I confess I am not an expert on PDF files. But I would think that INSERT OBJECT would ... | |
| Inserting PDF file into Excel 2003 | 2/17/2009 |
| Q: My users are no longer able to insert a PDF document into Excel 2003. Have tried Insert, Object. I ... A: Timothy, The phrasing of your question leads me to believe this USED to work, and now it ... | |
| searching in vba | 2/17/2009 |
| Q: I have made a database filled with information on different contracts with every customer my company ... A: Jason, If your heart is set on this being a VBA macro, you'll have to ask a different expert. ... | |
| Please help if you can | 2/17/2009 |
| Q: Basically, i have a spreadsheet, with a drop down box, it has all the data in there that i need. ... A: Steve, Yes, a VLOOKUP is the answer. The first step is to read the help file for this ... | |
| Excel search | 2/17/2009 |
| Q: I've been trying to figure this out for quite some time now and have tried several functions but I ... A: Anne, You CAN and should split up the multiple entries. Four thousand rows is tiny compared to ... | |
| Find Specific Value for Chart | 2/17/2009 |
| Q: Bill, When I right-click on my chart and click on source data, my field values are the following: ... A: Alex, A chart is always based on values in cells on a sheet somewhere. "Looking up a specific ... | |
| Excel search | 2/17/2009 |
| Q: I've been trying to figure this out for quite some time now and have tried several functions but I ... A: Anna, When you read about VLOOKUP again, you will see that the function reference (or help file) ... | |
| Looking up values based on a date range | 2/17/2009 |
| Q: First off, thanks for volunteering! I have a report that I fill in every night at the end of the ... A: Spencer, It seems as though you didn't read my previous answer at all. I thought I asked you ... | |
| Looking up values based on a date range | 2/16/2009 |
| Q: First off, thanks for volunteering! I have a report that I fill in every night at the end of the ... A: Spencer, Thanks for the question and I appreciate your attempt at clarity by sending me the ... | |
| Excel 2003: Looking up data from a table which may have multiple values | 2/16/2009 |
| Q: I work for an airline and I am trying to use Excel to keep track of my flying hours. I have made a ... A: Jonathan, Keep me in mind... I'd love to develop this application for you. Perhaps you could ... | |
| excel formula | 2/16/2009 |
| Q: I need formula to find the total full legth recquired for given numbers of cut length, for example ... A: Siraj, This isn't too hard to do, but my solution does NOT maximize use of the raw material. ... | |
| Round off any value with condition in excel 2003. | 2/16/2009 |
| Q: i want to roundup a number say 20.50, 21 or 29.99 to 30 but want 20.01 and 20.44 i.e. less than and ... A: Nabam, This can be done quite easily, but first I need to be sure I understand the rules. ... | |
| Excel 2003: Looking up data from a table which may have multiple values | 2/16/2009 |
| Q: I work for an airline and I am trying to use Excel to keep track of my flying hours. I have made a ... A: Jonathan, It is possible but it is extremely difficult to do, and even more difficult to ... | |
| drop down menu in cell | 2/16/2009 |
| Q: The only quirk with it is that for one column called "status", I would like there to be only 3 ... A: Ann, You'll love this! Its so easy. It's called "data validation". I'm not sure which Excel ... | |
| Punchlist spreadsheet | 2/15/2009 |
| Q: I am a contractor wanting to set up a punchlist on Excel. My plan is to have sheet 1 in the format: ... A: Matt, The easiest way to do this is to use a FILTER, also called an AUTOFILTER in Excel 2003. ... | |
| Conditional formatting within chart limits | 2/13/2009 |
| Q: Bill, I'm using conditional formatting successfully to show pilots when their airplane's weight is ... A: Tom, I am still trying to understand. Since I am scientific by nature, I know something ... | |
| Comparing and extracting data | 2/13/2009 |
| Q: The Custom made ERP Software that my office uses does not cater to our Donors Reporting requirements ... A: Rushdi, I see your image clearly. It seems to me that some line have no code at all! Like the ... | |
| Changing cell text colour if it contains a certain symbol | 2/12/2009 |
| Q: i have a Excel spreadsheet wich has two columns with a total on both, another cell shows me the ... A: Nolan, Use CONDITIONAL FORMATTING to do this. Since I don't know what version of Excel you are ... | |
| Cell cannot be blank | 2/12/2009 |
| Q: I have a form that I created in Excel. 1. Some of the cells I do not want the user to leave blank ... A: Anne, You said: "I copied the if statement into the cell in the template." Okay, so ... | |
| Pivot Table | 2/12/2009 |
| Q: sir please you checked the attachment. In pivot table last column Balane is not in pivot table. How ... A: Khurram, You know what the steps are. You wrote them in your question to me! Just use the ... | |
| Using Excel to Match People to Classes | 2/12/2009 |
| Q: I thought I was an excel "genius" but it turns out I'm far from it! I work in Admissions at a ... A: Ally, This is a massive logic problem, not easy to solve by any simple set of equations. I ... | |
| finding which numbers total a specific sum | 2/11/2009 |
| Q: I am trying to use excel to reconcile some of our incoming bank wires. For example, we may have 50 ... A: Gre, So you want to add all possible combinations of 50 numbers (and you don't even know how ... | |
| Cell cannot be blank | 2/11/2009 |
| Q: I have a form that I created in Excel. 1. Some of the cells I do not want the user to leave blank ... A: Anne, You can make a message appear that says "Cannot leave XYZ blank". This could be ... | |
| Removing | 2/11/2009 |
| Q: I have a file (2003) that was created as an export from Outlook Contacts. The business address in ... A: Linda, So lets find out what these characters are. Then we can replace them with spaces. If ... | |
| lookup based on 3 criteria | 2/11/2009 |
| Q: I am trying to pull a value based on 3 criteria, two in columns and one in row. Please see ... A: Wendy, Clever girl! You've got part of it right! The part that looks for the column number ... | |
| excel formula | 2/11/2009 |
| Q: In a given numbers in col A, how to find the repeat numbers in set of 16 numbers, say for example ... A: Siraj, This is quite easy. Add a column next to the column of numbers to be checked. Fill the ... | |
| Generate Random Number in multiple of 5 | 2/11/2009 |
| Q: Bill, i want to generate a random numbers in multiple of 5 say, between 10 to 200. such as 20, 80, ... A: Nabam, Do you have Excel 2007? There is a RANDBETWEEN function that makes it easy to create the ... | |
| Comparing two columns and more | 2/11/2009 |
| Q: I'll be short: I have two sheets in the same workb ( like in pic ); each sheet has 5columns & ... A: Marcel, This is easy to do. I would add three columns. One for the final result, and one ... | |
| Pricing Spreadsheet | 2/10/2009 |
| Q: I am trying to create a new spreadsheet to make costings of our products easier. I have listed in ... A: Zuber, You have made this super-duper-extra complicated by your choice of data structure. ... | |
| Date Format | 2/10/2009 |
| Q: I have a text format of 02.11.09 in my workbook Column A1. How can I convert this to UK date format ... A: Abhi, Look through the date formats to find this format. I think it exists but am not sure. If ... | |
| IF function help | 2/10/2009 |
| Q: here is my existing formula for cell F10: =IF($D10="","",IF($D10="Black",$C$3-$C$5, IF($D10="Blue", ... A: Paul, If you want to know if a value (number or text) exists in a big range like E10 to E100, ... | |
| Forecasting | 2/10/2009 |
| Q: I am trying to predict expected levels of traffic congestion on many routes across a highway ... A: Luke, If you have enough data already, you can use the forecasting functions, which include ... | |
| IF function help | 2/10/2009 |
| Q: here is my existing formula for cell F10: =IF($D10="","",IF($D10="Black",$C$3-$C$5, IF($D10="Blue", ... A: Paul, You asked: "I'd like to make cell F10 blank if the value in cell range E10-E110 are the ... | |
| Importing data from a text file | 2/10/2009 |
| Q: I have a text file that I am importing into Excel 2003. One of the columns of data contains cells ... A: Nancy, When you import the data there is a dialog box somewhere along the way that asks you if ... | |
| Pivot Table | 2/10/2009 |
| Q: sir please you checked the attachment. In pivot table last column Balane is not in pivot table. How ... A: Khurram, The best way is to add a field in your data table called balance. Just perform the ... | |
| 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: Siraj, This is quite easy to do. In each cell of Column C you will have an IF statement ... | |
| pivot table row fileld labels | 2/9/2009 |
| Q: Hey there! Is there a fast way to fill in pivot table row labels? I have three nested row variables: ... A: Nate, To clarify, you want to fill in these 'labels' in another area of the worksheet, where you ... | |
| Linking multiple spreadsheets | 2/9/2009 |
| Q: I have a master spreadsheet with several tabs displaying an array of data. I also have several other ... A: Stile, I assume that all these spreadsheets are in the same WORKBOOK because you only used the ... | |
| #REF! Error | 2/9/2009 |
| Q: My spreadsheet contains formulas for YTD. However, since there isn't data for future dates yet, it ... A: Tim, I would think that your attempt to use IF with an embedded ISERROR would be successful. I ... | |
| Multiple Lookups | 2/9/2009 |
| Q: I have a department hierarchy that I'm battling with. A B Kam Fernando Kam Adel Kam Ryan Ryan Tracy ... A: Graeme, Your question is perfectly clear, you want to expand a hierarchy that's listed flatly. ... | |
| efficient use of raw product | 2/9/2009 |
| Q: how to effectively assign approx 500 individual items of different lengths & types (structural steel ... A: David, Your question is about how to arrange your database, but your bigger question behind the ... | |
| Random Selection with Greater Odds | 2/7/2009 |
| Q: I know how to use the Random function on excell but I need a twist added. My kids roll dice to do ... A: John, Are you using Excel 2003, or 2007? 2007 has a new RANDBETWEEN function that would let ... | |
| excel - lotus | 2/6/2009 |
| Q: I have received an Excel spreadsheet and when I try to put a date in say E65 and then enter - the ... A: Donald, A friend suggested that you look in the TOOLS-->OPTIONS (2003 ) or BUTTON:OPTIONS ... | |
| simple question on efficiencies | 2/6/2009 |
| Q: a colleague of mine and i were having a debate on efficiencies. I Was wondering if you could clear ... A: Nicolas, My feeling is that a pivot table would be much more efficient. The pivot table ... | |
| complex Averaging for Excell 2007 | 2/6/2009 |
| Q: I am using workbook sheets as mini databases and want to create a formula in a single cell in a ... A: Karen, I thought this is exactly what I suggested: "It would be better to take the average of ... | |
| complex Averaging for Excell 2007 | 2/6/2009 |
| Q: I am using workbook sheets as mini databases and want to create a formula in a single cell in a ... A: Karen, Piece of cake! That is, assuming your table is nice and neat. If the doubled ... | |
| If/When Formula | 2/5/2009 |
| Q: I don't think this is as high level as you are capable of but sure higher than what I can do. I have ... A: Danielle, This is easy to do with a simple IF statement. If the only two possibilities for ... | |
| excel - lotus | 2/5/2009 |
| Q: I have received an Excel spreadsheet and when I try to put a date in say E65 and then enter - the ... A: Donald, First thing I'd do is erase that code. It does look like Lotus code but I doubt it is ... | |
| sum data range | 2/5/2009 |
| Q: I have the following problem in summing data range: Column1 Column2 Difference of time (2-1) ... A: Stefano, I've given you several weeks to solve your problem. From the wealth of information and ... | |
| sum data range | 2/5/2009 |
| Q: I have the following problem in summing data range: Column1 Column2 Difference of time (2-1) ... A: Stefano, Your question was clear, and I understood what you meant. My answer applies, did you ... | |
| sum daily data by week | 2/5/2009 |
| Q: .. i am trying to build a table that has unique visitor data by day for multiple web sites for ... A: Robyn, If your data is PERFECTLY REGULAR with absolutely NO skipped days, than you could build a ... | |
| Formulas | 2/5/2009 |
| Q: I have a excel form I use to calculate bonuses. My problem is because my formula is: ... A: Melissa, read about the IF statement in the help file so you understand the parts of it. It has ... | |
| VLOKUP PROBLEM | 2/4/2009 |
| Q: I have a list of out numbers which combine letters and numbers. i.e. CA0403FB as well just numbers ... A: Mark, There are several things which could be wrong. Most likely is that you have used the ... | |
| Formulas | 2/4/2009 |
| Q: I have a excel form I use to calculate bonuses. My problem is because my formula is: ... A: Melissa, You're correct, there are several ways to test for other conditions of cells. My ... | |
| Automatic Tab Reference Change | 2/4/2009 |
| Q: I am currently working on a model that inputs inventory numbers based on the weekly forecasts. What ... A: Anuj, There is an easy way to do this, all you have to do is learn the ADDRESS and INDIRECT ... | |
| using a vlookup text result in an IF statement | 2/4/2009 |
| Q: Bill... GENERALLY: The result of a vlookup formula (a text word) used in an IF statement is not ... A: Philip, First of all, you are using an overly complicated IF statement. ... | |
| Excel Question | 2/4/2009 |
| Q: I read your earlier answer about emailing a worksheet from excel, I need to do the same thing, but ... A: Teresa, I never made an earlier answer about emailing from Excel. Must be the other Bill on the ... | |
| MS Excel | 2/4/2009 |
| Q: Sir- My question involves adding functionality to an ongoing spreadsheet project. I have multiple ... A: David, What you are trying to do is to count the number of records in a database which match ... | |
| sum data range | 2/4/2009 |
| Q: I have the following problem in summing data range: Column1 Column2 Difference of time (2-1) ... A: Stefano, This is very easy to do with a single IF statement. You'll like it! I don't need to ... | |
| Excel Comparison | 2/4/2009 |
| Q: I have a spreadsheet that I would like to search down a column (item name) and find duplicates, then ... A: Clint, As I said, a complete formula-based solution is difficult. All I can provide is an ... | |
| Summing, skipping rows, sheets | 2/3/2009 |
| Q: I think what I need is not so terribly complex, but while a power-user in general, I'm a noob with ... A: Kade, I usually create blocks of references to discontiguous cells using the ADDRESS function ... | |
| Comparing Data | 2/3/2009 |
| Q: I have a spreadsheet where each column represents available inventory from various vendors. There ... A: John, The proposed structure of your database will make it VERY VERY difficult to use. ... | |
| Comparing and extracting data | 2/3/2009 |
| Q: The Custom made ERP Software that my office uses does not cater to our Donors Reporting requirements ... A: Rushdi, There isn't enough information to provide a definitive answer to this. But what ... | |
| Average formula | 2/3/2009 |
| Q: I'm trying to use Excel 2002 to record the profit/loss of my stock portfolio. Let's say I purchase ... A: Bull, It seems to me that a multiply and divide is pretty simple. All you have to do is create ... | |
| Mirroring information in sheets | 2/3/2009 |
| Q: I've been doing basic Excel for a few years now but I'm sorry to say I have a hard time ... A: Patricia, My suggestion is to use an ADVANCED FILTER, which can be set up to COPY all the ... | |
| Comparing Data | 2/2/2009 |
| Q: I have a spreadsheet where each column represents available inventory from various vendors. There ... A: John, The solution depends on how you want it to work. Do you want A) to see all the ... | |
| Excel Comparison | 2/2/2009 |
| Q: I have a spreadsheet that I would like to search down a column (item name) and find duplicates, then ... A: Clint, There are several ways to do this. If you are using Excel 2007, they have made it very ... | |
| Mirroring information in sheets | 2/2/2009 |
| Q: I've been doing basic Excel for a few years now but I'm sorry to say I have a hard time ... A: Patricia, I do need some clarification before I can give you a solution which I know will work. ... | |
| Average formula | 2/2/2009 |
| Q: I'm trying to use Excel 2002 to record the profit/loss of my stock portfolio. Let's say I purchase ... A: Bull, The AVERAGE of your stock portfolio can be done many ways. Here I suspect you are ... | |
| References to ([LIST LAST ROW]+1) | 2/2/2009 |
| Q: I'm by no means a newbie to Excel, but I'm stumped with this 2003/XP problem. In a list, all rows ... A: Greg, It seems like you have already explored all the options, so I don't have much more to add. ... | |
| True or False | 2/1/2009 |
| Q: I am supose to indicate True or false for a column at the same time I do not have to use the IF ... A: Duncan, All you have to do is write any logical equation into the cell(s). If you are comparing ... | |
| FIFO perpetual inventory record | 1/31/2009 |
| Q: I can do the work on paper however my brain cannot grasp the concept of putting into a formula for ... A: Kerry, Luckily I understand the jargon (FIFO) in your brief question, but little else. I ... | |
| excel | 1/31/2009 |
| Q: have a range of information for each individual (A2:I2) on individuals such as Name, Address and so ... A: Asha, Apply VLOOKUP to the problem. For each piece of information you want to pull across, you ... | |
| Counting violations of minimum spacing between similar values in a columns of data | 1/30/2009 |
| Q: I can export to Excel 2007 a sequence of orders to be processed sequentially. Each row shows an ... A: martin, At long last I have thought of a way to accomplish this. if you still need to know, ... | |
| Excel: Copying ranges between worksheets | 1/30/2009 |
| Q: I am using a worksheet for forecasting (Sales Forecast). Within that worksheet, which is used like ... A: Chris, There are many ways to make this occur, and surely you have thought of (and disregarded) ... | |
| cells seperator formatting in excel 2003. | 1/30/2009 |
| Q: How i can format 12345678 as $1,23,45,678.00? i already tried this through control Panel>Regional ... A: Nabam, This requires you to create a CUSTOM NUMBER FORMAT. (I assume you didn't mistype ... | |
| Splitting out dates between a start and end date | 1/29/2009 |
| Q: I have a set of data that shows a delivery start and delivery end date eg: column: A ... A: Ben, All you have to do is use an IF statement to display an incremented date unless that date ... | |
| Excel matching data | 1/29/2009 |
| Q: First thanks in advance for your time. I have setup in rows my list of employees. First name, last ... A: Dimitris, By now you may have solved this. But if not, I have some answers and comments. ... | |
| Month and Day extraction into 1 cell. | 1/29/2009 |
| Q: I am interested in extracting the month and day from a date into ONE cell. For example, if A1="Aug ... A: Philip, If your column "1" list is really as you say, that is "Jan 1", Jan 2", then those are ... | |
| Drop-down Menus | 1/28/2009 |
| Q: Stephens, Will Excel allow me to place both conditional formatting and validation in the same cell ... A: Brit, Each secondary list will require a cell in the main list. So if you have 10 cells that ... | |
| Drop-down Menus | 1/28/2009 |
| Q: Stephens, Will Excel allow me to place both conditional formatting and validation in the same cell ... A: Brit, Yes, this is possible. But before I go on, perhaps you don't mean to be talking to me. ... | |
| Month and Day extraction into 1 cell. | 1/28/2009 |
| Q: I am interested in extracting the month and day from a date into ONE cell. For example, if A1="Aug ... A: Philip, As you probably know, any cell can only contain ONE value. So you can't put both the ... | |
| Excel matching data | 1/28/2009 |
| Q: First thanks in advance for your time. I have setup in rows my list of employees. First name, last ... A: Dimitris, I understand your first table setup but not what you are trying to do with it. ... | |
| Excel pie chart | 1/27/2009 |
| Q: My pie chart has jagged looking lines instead of the smooth lines that I printed last year. ... A: Carol, On first thought, it seems like this isn't an Excel problem, but more likely to be a ... | |
| Average | 1/27/2009 |
| Q: Please refer to the attachment, I would like to khow how to input the formula in the last table ... A: Bralee, Your English is PERFECT, and EXCELLENT but there isn't enough of it. I would never ... | |
| Average | 1/27/2009 |
| Q: Please refer to the attachment, I would like to khow how to input the formula in the last table ... A: Bralee, I'm afraid I don't quite understand exactly what you want to average. You sent me a ... | |
| Joining Two Tables based on Multiple Matches | 1/26/2009 |
| Q: I hope I can explain this properly and that you can help! I have two spreadsheets which I am ... A: Tracey, A HA! So if there are several transactions for the same product, you could take the SUM ... | |
| IF AND logic series help | 1/26/2009 |
| Q: I'm trying to figure out the bug in my equation in excel 07 =IF(AND(G4=1,AS6=1)=TRUE,'WEEK ... A: Carl, Without knowing the logical word statement of the problem you are trying to solve, all I ... | |
| Sum Text By Category and Period Selected | 1/26/2009 |
| Q: Good Morning Mr. Hermanson, how are you? I'm a brazilian 23 years old guy, that works a lot with ... A: Paulo, If you want help with VBA, Tom Ogilvy on this site can expertly advise you. I even ... | |
| Joining Two Tables based on Multiple Matches | 1/26/2009 |
| Q: I hope I can explain this properly and that you can help! I have two spreadsheets which I am ... A: Tracey, There's not enough information in your question to be certain of an approach that will ... | |
| count consecutive blank cells | 1/25/2009 |
| Q: PLEASE FIRST TRY TO UNDERSTAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need ... A: Shadhin, Your formula for total count of all blank rows is ingenious but needlessly complicated. ... | |
| calculating from the next sheet | 1/24/2009 |
| Q: =VLOOKUP("CARRICKFERGUS DEPOT",Sheet2!$BG15:$BH26,2)I am creating a master table and want it to give ... A: Chay, I'm not sure what you are attempting to do with your 'equation'. The syntax you have ... | |
| merging columns | 1/24/2009 |
| Q: I have a workbook with 8 worksheets in it. I want to add a new sheet and dynamically populate the ... A: Paul, Well, every one of your followup answers has made the problem more complicated. Its ... | |
| copy paste excel | 1/23/2009 |
| Q: i have created a formula in column B and C to equal D. I need to now delete column B and C from the ... A: Elizabeth, Excel 2000 is old enough that I am not sure I recall EXACTLY how to follow my ... | |
| copy paste excel | 1/23/2009 |
| Q: i have created a formula in column B and C to equal D. I need to now delete column B and C from the ... A: Elizabeth, If you are going to eliminate the data on which an equation is based, you have to ... | |
| spreadsheet | 1/23/2009 |
| Q: I have 35 drivers. the first page is the list of all driver and what they will do the next day, (3 ... A: Dawn, So what you see is the formula itself, and not the value you expect? You actually ... | |
| Sum Text By Category and Period Selected | 1/23/2009 |
| Q: Good Morning Mr. Hermanson, how are you? I'm a brazilian 23 years old guy, that works a lot with ... A: Paulo, It sounds to me like you should sort your database by the category and month. However, ... | |
| merging columns | 1/23/2009 |
| Q: I have a workbook with 8 worksheets in it. I want to add a new sheet and dynamically populate the ... A: Paul, Its unclear if you want to do this once, using menu operations (I call this 'by hand') or ... | |
| Import Graphs from excel to word | 1/23/2009 |
| Q: I am a LabView programer. I program and automate tests and generate reports. Currently my software ... A: Tiri, I'm sorry but my expertise does not cover working between office applications. Look for ... | |
| Calulating only numbers between 16-50 in a column of random numbers | 1/22/2009 |
| Q: I have a spreadsheet in Excel 2002 sp3 that I need to filter out some information. In column K ... A: Cindy, All you need to do is create a window comparator test for column K. Add a new column ... | |
| Leaving a Cell Blank until others are Filled | 1/22/2009 |
| Q: This is probably a simple question for you. I have a "Total Sum" (formula within a cell) let's call ... A: Keith, You can create a test to determine if both cells are blank, based upon the ISBLANK() ... | |
| excel - file slows down | 1/22/2009 |
| Q: I have a specific file that when you scroll up/down it reaches a point that it slows down completely ... A: Lee-Anne, Perhaps there are a lot of colors, formatting, or graphics at that point in your ... | |
| Excel form with 3 levels of questions | 1/21/2009 |
| Q: Please let me ask for your help. I am exploring methods for optimizing the efficiency of a large ... A: David, All your answers indicate LESS, rather than more, complexity in your database, so that's ... | |
| Multi Criteria Sum | 1/21/2009 |
| Q: I hope you can help me, I have been searching all over the net for a solution. Everything I find ... A: Sean, This is easy if you know about SUMPRODUCT(). There is an excellent article on this at ... | |
| correlation/relationships in data | 1/21/2009 |
| Q: this is a very last minute thing, so if possible, could you reply ASAP...Sorry to be rude! Basically ... A: Katie, I would think that plotting wave height vs wave period would show a correlation, or not. ... | |
| Percentage Formula | 1/21/2009 |
| Q: I am using a formula such as this =A1/$A5*100 to give me a percentage for a row of a table. I can ... A: Stuart, You can fix this issue with a little trick! All you have to do is create the first ... | |
| Database Question | 1/21/2009 |
| Q: I am wanting to know how to set up this scenario, I think excel will work - I have a common ... A: Richard, I know it will work too, but I don't have the expertise required to advise you on ... | |
| Year To Date Accumulation | 1/21/2009 |
| Q: I'm trying to get an average accumulated total for YTD (see Average Score in lower image in ... A: Tim, I'd suggest you use an IF statement to test if you have zero data for each month, and if ... | |
| Cutting excel sheets with graphs | 1/21/2009 |
| Q: Could you please assist me with this problem? I import data from a mainframe (Natural Adabas) ... A: Bruce, A chart is based on the underlying data that it is charting. I don't know of a way to ... | |
| Excel Charting Question | 1/20/2009 |
| Q: Fellow "expert" here (on things Japanese) trying to take advantage of our own services. I am trying ... A: Steve, Do you still need help with this? probably not! But the answer seems simple. Your ... | |
| Creating a Rolling Average | 1/20/2009 |
| Q: Bill, My data is set up like this: Year-Month Year-Month FORMULA ... A: Michelle, At first I couldn't figure out a way to solve this. maybe you have an answer ... | |
| formula | 1/20/2009 |
| Q: I really hope that you can help me with this: i am trying to keep up with the current volume. the ... A: A, This is a job for VLOOKUP! So read up on that function so you understand the rest of my ... | |
| Data Validation | 1/20/2009 |
| Q: I have set a calendar broken down into 26 pay periods with each cell as one day. I have set each ... A: Brian, I have an answer for you at last! The first time I read your question, I didn't ... | |
| Create Ledger Automatically | 1/20/2009 |
| Q: i have table in excel like this Date Particulars Debit Credit 1/1/09 Cash 2000 ... A: Khurram, You say your problem is solved... which of my suggestions did you use? In any case, ... | |
| average formula with multiple ranges | 1/20/2009 |
| Q: I have a sheet with a lot of data and I would like to average out about 13 ranges. I would also like ... A: Jon, I am not a mathemetician, but, is the average of averages a legal mathmatical operation? ... | |
| Conditionally finding the oldest date within a range | 1/20/2009 |
| Q: I have sent you a simplied version of what my spreadsheet looks like to Spreadsheet & Subject is ... A: Luke, Theres an easy method to do this, and actually there may be several methods. My top ... | |
| lookup csv files | 1/20/2009 |
| Q: I'm trying to look up values in a Microsoft Office Excel Comma Separated Values File (.csv) and move ... A: Adam, What error message do you get? #NA, #VALUE, #DIV/0, #NAME ??? The message you get ... | |
| Automatic Scoring | 1/19/2009 |
| Q: Hey I am doing a survey in which I have the option Strongly Agree, Agree, Disagree, or Totally ... A: Caroline, Well, it's not as simple as you think. Option Buttons exist in Excel, as do a ... | |
| average formula with multiple ranges | 1/19/2009 |
| Q: I have a sheet with a lot of data and I would like to average out about 13 ranges. I would also like ... A: Jon, You can average multiple, non-adjacent ranges with one AVERAGE function. Just stack in the ... | |
| Data validation list | 1/19/2009 |
| Q: Bill, I want to set up data validation lists on column B such that they shows 1,5,10,20 times ... A: Raymond, This is so easy, you'll laugh. All you have to do is create a column of formulas ... | |
| Create Ledger Automatically | 1/19/2009 |
| Q: i have table in excel like this Date Particulars Debit Credit 1/1/09 Cash 2000 ... A: Khurram, There are several ways to do this. The easiest way isn't entirely automatic. It ... | |
| Data Connections / Merging | 1/19/2009 |
| Q: I have a spreadsheet with two pivot tables which reference two different SQL database views from two ... A: Donal, The first method that comes to mind is to pick one of the two tables and write VLOOKUP ... | |
| Advanced Filters | 1/19/2009 |
| Q: We have a simple spreadsheet set up where we track our incoming goods: Product, Delivery date, and ... A: Derek, Congratulations on the progress you've made so far. It seems like it is almost working! ... | |
| Counting text using a formula from a drop down list | 1/19/2009 |
| Q: I am trying to count text from a drop-down list using a formula but to no avail. I have tried ... A: Emma, I am not sure what you mean. Are you trying to put a formula into a drop down list, or ... | |
| Finding relevant data | 1/18/2009 |
| Q: and thank you for this service! I am trying to reference data from any cell in one column, so that ... A: John, The VLOOKUP function is just what you need to solve your problem! Read about it in ... | |
| Forecasting future trends | 1/17/2009 |
| Q: I have created a budget sheet which I enter projected spend vs. actual spend and projected incomings ... A: Simon, You're right on track with your actual vs projected table idea. All you need is to learn ... | |
| Semi-complex excel calculation - Help needed | 1/17/2009 |
| Q: i have an online book selling website. I need to calculate my profit margin based on the cost of ... A: Tyler, All you need is a series of nested IF statements. An IF statement has the form: ... | |
| multiple list menus | 1/17/2009 |
| Q: I am trying to set up a worksheet for time tracking. We have several charge numbers we can use each ... A: Mike, If there is ONE description for each job number, looking up the description from the ... | |
| Excel VBA Project size limitation? | 1/16/2009 |
| Q: Here's a toughie: I have this very, very large VBA project (36 forms, 2 modules and 107 class ... A: Matthieu, I had another thought on this after I read your final comment. Have you tried ... | |
| Using search and count for multiple text in same cell | 1/16/2009 |
| Q: On my raw data tab (where I extract for my report and use array calcs), I'm trying to calculate ... A: Tammy, I have a lot of questions about your data set. Your sample shows 7 characters, then ... | |
| Filling gaps in data | 1/16/2009 |
| Q: Data to be processed: 2 columns say A and B Rows({A}) = 12001 {A} = ... A: Chetan, Maybe there is a way to do this after all. I assume what you mean is this: This is ... | |
| several values per cell ? | 1/16/2009 |
| Q: Usually there is 1 Value per Cell. I want to put several values per cell : is it possible ? Thank ... A: Bernard, Its not possible to put several actual values per cell. However, there are several ... | |
| Custom Filters in Excel | 1/16/2009 |
| Q: I have a weekly report I have to run that has in the neighborhood of 6000 rows of data. What I need ... A: Kevin, Sorry for the belated answer but this just dawned on me. You are using 2003, right? ... | |
| Excel | 1/16/2009 |
| Q: I am creating a feedback tracker, I want excel to pick up the date when data was entered in a ... A: Uditsalvan, The best way to do this is with a macro that's set to run when another cell in the ... | |
| Excel: Counting certain cells that meet criteria: Overdue donations | 1/16/2009 |
| Q: Hoping you are able to help. I am working on what I thought was a simple problem, but after working ... A: Erwin, Nice to hear from you , again! Thanks for posting here. What you are attempting to ... | |
| Sorting 00/00 dates in Excel | 1/15/2009 |
| Q: I have a spreadsheet containing a date column. The date column contains valid dates such as ... A: Katie, There are several ways to make this happen, but I don't exactly know what should happen. ... | |
| Filling gaps in data | 1/15/2009 |
| Q: Data to be processed: 2 columns say A and B Rows({A}) = 12001 {A} = ... A: Chetan, I'm sorry to say that even though you have tried to provide detailed information on your ... | |
| lotto | 1/15/2009 |
| Q: "I have a question regarding to how to create a lottery for parking spaces or seats in an ... A: Noren, It actually is very unlikely that RAND() will have duplicates. Have you ever actually ... | |
| filter item missing | 1/15/2009 |
| Q: I have a 2003 xl worksheet with 3 combined lsits on to check for duplicate entries intially I ... A: Alan, Some comments to your comments: I've copied your last followup into this email and my ... | |
| filter item missing | 1/14/2009 |
| Q: I have a 2003 xl worksheet with 3 combined lsits on to check for duplicate entries intially I ... A: Alan, You answered your own question in your email to me: the autofilter cannot show more than ... | |
| Excel form with 3 levels of questions | 1/14/2009 |
| Q: Please let me ask for your help. I am exploring methods for optimizing the efficiency of a large ... A: David, There is a lot to consider when creating something like this, and a lot I don't know, so ... | |
| filter item missing | 1/14/2009 |
| Q: I have a 2003 xl worksheet with 3 combined lsits on to check for duplicate entries intially I ... A: Alan, Perhaps the name is outside the range of the database you selected before you applied the ... | |
| Copy cells to different sheet IF meet criteria | 1/14/2009 |
| Q: I am a little frustrated (and under-educated) with Excel. I had previously asked this question on ... A: Erika, Many people want to do this, so Excel provides a method. However, it is a manual method ... | |
| Need help with formula | 1/13/2009 |
| Q: So glad I found this site. I only know basic Excel and very little VBA. Hopefully I ask so as not ... A: Mary, If you REALLY need to MOVE, not copy, and you are utterly convinced you need to MOVE, ... | |
| Excel Formula | 1/13/2009 |
| Q: Bill, I am using an Excel 2003 Workbook to calculate 12 month rolling totals for my production ... A: Joe, I have a couple of suggestions but they aren't terribly good. The main problem is to ... | |
| Time Sheet Formulas | 1/13/2009 |
| Q: Good Afternoon Bill, Thank you for offering your expertise. At one time I could have figured this ... A: Stuart, Well, the reason I didn't offer new formulae to you, is that you've put a lot more time ... | |
| cell matching | 1/13/2009 |
| Q: i have four columns A,B,C and D. All the values in A can be found in C but not all values in C can ... A: Eric, Its easy to do this. All you need is to fill column B with the VLOOKUP() function. ... | |
| Charting: Auto Scale & Blanks | 1/13/2009 |
| Q: 2 Questions: i) The 'Auto' function for the Value (Y) axis scale adjusts the maximum scale ... A: Jason, i) have you tried to use the manual Y-axis scale, where you enter the minimum number for ... | |
| Help Required | 1/12/2009 |
| Q: I am trying to creat it that if i type in a code it will auto fill the description price etc. I ... A: Caroline, This isn't too hard and you will learn one of the most important functions Excel has ... | |
| Lookup next value (cont.) | 1/11/2009 |
| Q: Sorry Bill, unable to access your followup or work out a way to reply on it. I've copied your help ... A: Shane, I have done some work on your question but it is very difficult to explain in words. ... | |
| Time Sheet Formulas | 1/10/2009 |
| Q: Good Afternoon Bill, Thank you for offering your expertise. At one time I could have figured this ... A: Stuart, You can nest two IF statements together if you want. One whole If statement can be the ... | |
| Hidden macros | 1/9/2009 |
| Q: We have some file that are much larger then they should be. for example a few small tabs of data ... A: Sam, I'm not a macro expert, so perhaps this question can be re-directed to Tom Ogilvy for ... | |
| Changing data ranges for dynamic charts | 1/9/2009 |
| Q: Objective: View time-series data with a chart to visually spot for anomalies. Then hone in on a ... A: Jason, Yes, this is possible, and its not that difficult. You'll have to learn the ... | |
| Help me in Excel | 1/9/2009 |
| Q: I am Puneet from India. I have an excel sheet where I want my team member to be able to fill ... A: Puneet, The are two ways to prevent someone from entering information in a row: 1) hide the ... | |
| wild cards? maybe... | 1/9/2009 |
| Q: I have this HUGE spredsheet that is several hundres lines long. Column C is 1 of about 24 different ... A: Janet, If I understand you correctly, then you want to change all the F's which correspond to a ... | |
| Excel formula with conditionals | 1/8/2009 |
| Q: I have a challenging formula to build: Cell1 Cell2 Cell3 Cell4 Cost ... A: Dbrousset, You've got the right idea but you have mis-written all but the first conditional ... | |
| Looking for a formula | 1/8/2009 |
| Q: I'm trying to come up with a formula that will find matches of values and pull the value of a cell ... A: Christie, This is a perfect application for the VLOOKUP function, which is designed to do ... | |
| Formula | 1/8/2009 |
| Q: I have a list of approximately 250 different names. these names can appear multiple times in the ... A: Valerie, It sounds to me like you want to make a list of unique cities from your list, one ... | |
| Combining rows based on date. | 1/8/2009 |
| Q: I think what I am trying to do is pretty basic but for some reason I am having trouble finding a ... A: Elizabeth, I'm not familiar with Google spreadsheets, so I'd ask you how the data gets on three ... | |
| Charting Frequencies | 1/8/2009 |
| Q: I have a list of data in Column A and want to know if there is a way to graph the frequency of the ... A: David, The way to do this is to make the data being graphed change according to your ... | |
| Conditional Formatting | 1/7/2009 |
| Q: I have six columns wide (by vendor) and 500 someodd rows down with parts, in the corresponding ... A: Glen, I'm sorry that I can't help you diagnose this problem. I am still using Excel 2003, ... | |
| Data by date | 1/7/2009 |
| Q: I'm trying to create a calendar in an excel spreadsheet that shows when certain charges were made. I ... A: Alex, I often use the advanced form of SUMPRODUCT to select records matching multiple criteria. ... | |
| Conditional Formatting | 1/7/2009 |
| Q: I have six columns wide (by vendor) and 500 someodd rows down with parts, in the corresponding ... A: Glen, I'm not exactly sure of the structure of your data or why you're having trouble (perhaps a ... | |
| Excel Cell Manipulation | 1/7/2009 |
| Q: I have an issue where someone has created an excel spreadsheet, and in each cell, they included ... A: Barry, I'd solve this with a series of functions in several columns. If you get ambitious, ... | |
| If/Then statements in excel macro | 1/7/2009 |
| Q: I need a very simple macro to place specified text in an empty adjacent cell if there is text in the ... A: Jess, This is NOT a 'macro', you're misusing that word. You want a simple equation. Try ... | |
| Follow on Question: Merging and Updating Spreadsheets | 1/6/2009 |
| Q: Bill, Thanks for your reply; the operation represents a new step we are taking to make a process ... A: Jeff, This is actually an incredibly complex problem, way beyond the scope of the free advice I ... | |
| Excel formulas | 1/6/2009 |
| Q: Ok, I have an Excel project that's stumping me and found your website (very helpful btw). Here's ... A: Kathleen, There are several ways to do this. That is, if I understand your question correctly. ... | |
| Hide columns with zero values in Column Chart in Excel 2003 | 1/6/2009 |
| Q: Is there anyway in Excel 2003 to hide columns in a Column Chart that have a zero or blank value? ... A: Debbie, I don't know for sure how to do this, or even if there is a way. I can suggest several ... | |
| Follow on Question: Merging and Updating Spreadsheets | 1/6/2009 |
| Q: Bill, Thanks for your reply; the operation represents a new step we are taking to make a process ... A: Jeff, This is better, but still not clear enough. Your bullet points don't indicate a sequence ... | |
| MSEXCEL SHEET LINKING | 1/6/2009 |
| Q: To tranfer data from one sheet to another sheet i linked master sheet cell to the target sheet ... A: Sri, You can feed the sheet names into ADDRESS functions, and then feed that into the INDIRECT ... | |
| Cell Look up | 1/5/2009 |
| Q: I am trying to figure out how to assign a cell to be equal to another cell, but I can't explicitly ... A: Blaine, You're in luck! There is a pair of Excel functions designed for exactly that purpose. ... | |
| Merging and Updating Spreadsheets | 1/5/2009 |
| Q: Each day we are issued a workbook that is used by an external facilities management company for ... A: Jeff, It sounds quite possible.... anything is possible in Excel! Is this something you have ... | |
| What range does a cell fall within - cont'd 2 | 1/4/2009 |
| Q: The system wouldn't take another follow-up so I've started this thread to carry forward. You're ... A: Robert, I'm glad you got it working. If you have any more questions don't hesitate to ask. ... | |
| Conditional data | 1/4/2009 |
| Q: Your last response was correct (...assume it is possible for one of the numbers to be found in T2, ... A: Alex, Ah-HA! What you are really trying to do is to create a two-criterion (column A & B) ... | |
| What range does a cell fall within | 1/4/2009 |
| Q: I'm looking for a function that is kind of the reverse of VLOOKUP. I have been using nested IF & AND ... A: Robert, The VLOOKUP approach will (probably) work also. There is nothing unusual about that use ... | |
| What range does a cell fall within | 1/4/2009 |
| Q: I'm looking for a function that is kind of the reverse of VLOOKUP. I have been using nested IF & AND ... A: Robert, Well, how about this idea? Break your table in half. Make a nested IF for each ... | |
| What range does a cell fall within | 1/4/2009 |
| Q: I'm looking for a function that is kind of the reverse of VLOOKUP. I have been using nested IF & AND ... A: Robert, Would it be possible to calculate the wavelength from the frequency and then round down ... | |
| formula explination | 1/3/2009 |
| Q: Need help on how this formula works please =if(A18="","",INDEX(Source1!A2:B700,MATCH(A2:A700,0)2)) A: Eddie, Let's break this formula into pieces and discuss each piece. Its an IF statement with a ... | |
| Conditional data | 1/3/2009 |
| Q: I have 2 separate spreadsheets and have been trying to make this work, but no luck. I want column c ... A: Alex, "...but I should have mentioned that I want the cell in table 1, column c to search all ... | |
| using excel to generate output for a narrator voice | 1/3/2009 |
| Q: | have come across an idea, and have a cancer patient willing to try anything to gain a voice back. ... A: William, Okay, well.... Excel is a column and row based matrix. So you can have entries in a ... | |
| Conditional data | 1/2/2009 |
| Q: I have 2 separate spreadsheets and have been trying to make this work, but no luck. I want column c ... A: Alex, My answer will assume that the two tables you speak of are different sheets in the same ... | |
| Comparing two excel files | 1/2/2009 |
| Q: i am working on excel and i had a huge problem in comparing excel files. i.e, i had an Excel file ... A: Jagesh, Your question isn't clear. I don't know what you mean by a MATCH, I cannot tell what ... | |
| matching data | 1/2/2009 |
| Q: I am not sure excel is the right medium for this, but I have 2 lists of words that I want to match ... A: Tonya, So you need a two-way match, correct? Let me restate your"I have two lists, List ... | |
| Excel duplicate finder | 1/1/2009 |
| Q: I have more than 50 thousand name,address and phone numbers in one sheet and it keep on increasing ... A: I suggest the AUTOFILTER approach as the next option. Select your whole database and apply and ... | |
| auto increment | 1/1/2009 |
| Q: I maintain a exercise log with following information here is what i want to do. if I add anything in ... A: Pravin, What you need in Column E, the COUNT column, is a logical test that detects the ... | |
| partial matches with iterations | 12/31/2008 |
| Q: Bill- I had to submit a 'new' question due to too many follow-ups. Hmmm, I never thought of those ... A: Cindy, One of the other Experts on the site has been following our conversation and has ... | |
| partial matches with iterations | 12/31/2008 |
| Q: Bill- I had to submit a 'new' question due to too many follow-ups. Hmmm, I never thought of those ... A: Cindy, Wow, that is a really an interesting use for this. I had no idea what you were up to! ... | |
| partial matches with iterations | 12/31/2008 |
| Q: I have two columns of data with each cell containing a string of letters. The strings are anywhere ... A: Cindy, I'm impressed! You're going to go for it! Yes, contact me again through this site. ... | |
| If then using 5 scenarios..help!! | 12/31/2008 |
| Q: I am doing a maturity model matrix that has 7 core areas to be rated. I need a formula so I can ... A: Natalie, You did a great job but I am the one who blew it! There is a mistake in my VLOOKUP ... | |
| Adding different worksheets | 12/31/2008 |
| Q: I have several worksheets with dates (ex. 12-4-08,12-19-08 etc.) I need to add F30 together in all ... A: Dee, There are several ways to do this. The easiest is to simply start creating a formula in ... | |
| Excel duplicate finder | 12/31/2008 |
| Q: I have more than 50 thousand name,address and phone numbers in one sheet and it keep on increasing ... A: There is a really neat trick that is easy to do and can cause all the duplicates to light up in a ... | |
| partial matches with iterations | 12/31/2008 |
| Q: I have two columns of data with each cell containing a string of letters. The strings are anywhere ... A: Cindy, I was afraid you would provide the answers you did! Your problem is as complicated as ... | |
| Adding different worksheets | 12/30/2008 |
| Q: I have several worksheets with dates (ex. 12-4-08,12-19-08 etc.) I need to add F30 together in all ... A: Dee, There is not nearly enough information here to give you any kind of useful answer. Are ... | |
| If then using 5 scenarios..help!! | 12/30/2008 |
| Q: I am doing a maturity model matrix that has 7 core areas to be rated. I need a formula so I can ... A: Natalie, 1) When you create the statement (CHOOSE), its easiest to click on the cells which ... | |
| Conditional Formatting | 12/30/2008 |
| Q: I have a spreadsheet with 28 columns of data populated from other sources. The first column is a ... A: Jake, I am not sure I have enough information, but your answer implies that you are using a ... | |
| Conditional Formatting | 12/30/2008 |
| Q: I have a spreadsheet with 28 columns of data populated from other sources. The first column is a ... A: Jake, I can't answer this because I don't know what you are doing to "update your data". What ... | |
| Signs, subtracting, percentage? | 12/30/2008 |
| Q: I want X to equal 8.3 and ? to equal 4.15 and for the total to show up as a percentage. For this ... A: Rosio, Your problem statement is very unclear, vague, and imprecise. I cannot tell what ... | |
| If then using 5 scenarios..help!! | 12/30/2008 |
| Q: I am doing a maturity model matrix that has 7 core areas to be rated. I need a formula so I can ... A: Natalie, You'll be happy not to die because this is really simple. 1) create a column with ... | |
| partial matches with iterations | 12/30/2008 |
| Q: I have two columns of data with each cell containing a string of letters. The strings are anywhere ... A: Cindy, Something like this can be done, but first you have to define what is meant by a "partial ... | |
| sorting and filtering | 12/30/2008 |
| Q: This is one of number column in Access: Some with 4 digits, some with 5 and some with 6. How to put ... A: Bruce, Is this an ACCESS question or an EXCEL question? In Excel this is a simple matter of ... | |
| lookup | 12/29/2008 |
| Q: I am using lookup functions in a spreadsheet and I was wondering if it could be used to bring up ... A: Ryan, A single VLOOKUP can only return a single looked-up result. Implied in your question ... | |
| Remove Duplicates using array formula | 12/29/2008 |
| Q: I'm trying to recreate a dynamic list of excel values using only a formula. I don't want my ... A: James, It is possible and will involve some very complex formulas. There is no formula which ... | |
| slow calculations in Excel in network folders | 12/24/2008 |
| Q: I have designed several workbooks that are housed in shared folders on our network. I've used ... A: Vesta, I don't answer Macro-related questions (usually) because I'm not an expert on them. ... | |
| Time increasing, then cell value increasing | 12/24/2008 |
| Q: i have two cell in excel A1 and A2.In A1 cell i will enter a time and A2 cell is empty. When i will ... A: Khurram, All you need is an equation in A2 that takes the time in A1, multiplies it by 24, and ... | |
| slow calculations in Excel in network folders | 12/24/2008 |
| Q: I have designed several workbooks that are housed in shared folders on our network. I've used ... A: Vesta, Here's a brief answer: YES, there IS a way to write a macro to make the worksheet ... | |
| Combine rows using criteria | 12/23/2008 |
| Q: Please see the attached screenshot. What I'm needing is for example if O5 is between the dates in ... A: Kathie, This question has been on my list for a long time, and I am sorry that I haven't been ... | |
| Combine rows using criteria | 12/23/2008 |
| Q: Please see the attached screenshot. What I'm needing is for example if O5 is between the dates in ... A: Kathie, What you are trying to do can be done, but I am not sure exactly what that is. When you ... | |
| Excel Formula Required | 12/23/2008 |
| Q: I am trying to match the text from Column D from worksheet 1 to a list on worksheet 2 (which is a ... A: Frank, What you want is to apply the VLOOKUP() function, embedded inside an IF() statement, ... | |
| Generating sub lists from a master list | 12/23/2008 |
| Q: I have a master list of drawings from which I would like to generate sub lists related to several ... A: John, The idea of the pivot table was to build the entire matrix that you spoke of. Pivot ... | |
| slow calculations in Excel in network folders | 12/23/2008 |
| Q: I have designed several workbooks that are housed in shared folders on our network. I've used ... A: Vesta, VLOOKUP can be slow, but only if you have thousands of them which all need to calculate ... | |
| Copy/ Paste Question | 12/22/2008 |
| Q: I have a formula in cell E36 that is using data provided from formulas in cells A30 - A35 and I want ... A: Kim, The details of what you need to copy where, and what the formulas need to refer to, are not ... | |
| Lookup next value | 12/22/2008 |
| Q: Have a problem that is proving a pain to develop an autofill. I have water data so its by columns ... A: Shane, Since I now have two identical questions from you, I'll use the oldest one to let you ... | |
| Copy/ Paste Question | 12/22/2008 |
| Q: I have a formula in cell E36 that is using data provided from formulas in cells A30 - A35 and I want ... A: Kim, If your question only relates to one formula, there is an easy way to do this. Just copy ... | |
| Value displayed as Number | 12/22/2008 |
| Q: Is it possible to display a number (value) as a letter in Excel. X would be summed as 10, M as 0. ... A: Stefan, This is easier than I'd thought. All you need is a simple three-level IF statement. ... | |
| macros | 12/22/2008 |
| Q: for example i have a drop down list with month week and day,i would like help with populating ... A: Ngoza, Perhaps you are misusing the word MACRO because I don't believe a macro is needed to do ... | |
| Generating sub lists from a master list | 12/22/2008 |
| Q: I have a master list of drawings from which I would like to generate sub lists related to several ... A: John, Certainly this is possible in Excel, except I am not sure what you mean when you say "... ... | |
| counting with a combination of AND and OR | 12/22/2008 |
| Q: Uncle! I am having trouble coding a formula that combines several criteria. I want to count the ... A: Robert, You're having trouble because COUNTIF won't ever work that way! I never use ARRAY ... | |
| Excel Spreadsheet formula | 12/22/2008 |
| Q: I have an Excel spreadsheet with my calculations and question on the sheet that I need to send to ... A: dale, Usually I can answer questions (if they are stated clearly) without seeing the ... | |
| how to work out to check between two sheets on specific that date? | 12/21/2008 |
| Q: I post this Q to other expert alas this person has 'maxed out' which I have no idea what's does it ... A: Liz, Okay, I see your images, and they are very clear! Most images I get are so blurry that I ... | |
| Lookup next value | 12/21/2008 |
| Q: Have a problem that is proving a pain to develop an autofill. I have water data so its by columns ... A: Shane, I thought this might be the case. I have two additional questions (while I think of a ... | |
| Value displayed as Number | 12/21/2008 |
| Q: Is it possible to display a number (value) as a letter in Excel. X would be summed as 10, M as 0. A: Stefan, Yes, it is possible. But I don't understand from your sample what the conversion is. ... | |
| Searching for Text within a Cell in Another Sheet in Excel | 12/19/2008 |
| Q: I need to repetitively check a column of short hostnames in one sheet to see if the string exists ... A: Scott, If you need to check every string in sheet 2 (M strings) against every string in sheet 1 ... | |
| Formula Assistance - Trimming Commas | 12/19/2008 |
| Q: In cell A1, I have data that will have a random amount of commas in the text string, for instance: ... A: Michael, Tom, another expert on the site, noticed your question and provided this amazingly easy ... | |
| Lookup next value | 12/19/2008 |
| Q: Have a problem that is proving a pain to develop an autofill. I have water data so its by columns ... A: Shane, This sounds so easy, with no Index or VLOOKUP functions required. Perhaps I don't ... | |
| Excel Data Sharing | 12/19/2008 |
| Q: I have two dbf's in excel that contain information that I need to extrapolate. I can narrow one of ... A: Matthew, I thought that's what DBF meant... pretty lame for an expert who claims databases are ... | |
| Combining multiple excel worksheets into one workbook | 12/19/2008 |
| Q: How do I combine multiple excel worksheets into one excel workbook? A: John, Simple! See the little gray box in the upper left corner, at the junction of the ... | |
| how to work out to check between two sheets on specific that date? | 12/18/2008 |
| Q: I post this Q to other expert alas this person has 'maxed out' which I have no idea what's does it ... A: Liz, If the other equations in those small tables in your picture are like the equation you ... | |
| Unique Values in a Tie | 12/18/2008 |
| Q: Im a teacher trying to rank my students grades. The situation i have is that i have a static list ... A: Payton, The trick is to doctor the ranks so the tie is eliminated. This might not seem ... | |
| Vlookup | 12/18/2008 |
| Q: Here is a formula I am using to calculate a certain date +X months based on a rating that it has ... A: Jason, Thanks for the clear explanation. It is always better to explain your overall problem ... | |
| refering Data List s in code | 12/18/2008 |
| Q: I am having six Data lists on a worksheet. Whenever a user selects a cell from the list a ... A: Vijay, My profile says that I don't do macros on this site. But why do you need a macro to ... | |
| Vlookup | 12/17/2008 |
| Q: Here is a formula I am using to calculate a certain date +X months based on a rating that it has ... A: Jason, Yes, you have confused me. There are some missing pieces of your description that make ... | |
| Calculating with numbers that include text | 12/17/2008 |
| Q: I want to compare two columns of data that are entered as text eg 5a, 6b, 7a etc. I want to compare ... A: Tony, All you need to do is extract the numbers from the text using LEFT(), or possibly RIGHT() ... | |
| Warning, Alarms or Notification Message if a certain date expires. | 12/17/2008 |
| Q: I'm really a newbie in terms of Excel so please bear with me and my question. I'm working in a ... A: Shahriz, You'll be happy to know this is easy to do. All you need to know is how to use ... | |
| Excel Formula | 12/16/2008 |
| Q: We were going over peoples reports and the way they go about them within our dept. The point was so ... A: Brian, You can create what's called a 3-D range across any number of sheets. Then name the ... | |
| Slash command | 12/16/2008 |
| Q: Bill, In a previous question a person asked how to activate the Slash / key for commands in Excel ... A: George, That had to be the other Bill on this site. I never answered a question like this. ... | |
| Time Diffference | 12/16/2008 |
| Q: 12/4/08 12:08 PM 12/10/08 7:05 AM To generate time difference between above two dates in HH:MM ... A: Mahesh, Please re-explain your question. Your example is showing two different DATES as well ... | |
| Changing data - Column headings to row data | 12/16/2008 |
| Q: I have a timesheet that was originally created with 26 tabs for each pay period every two weeks, the ... A: Barb, My first answer had all kinds of advice. How about reading it again? The main idea ... | |
| Changing data - Column headings to row data | 12/16/2008 |
| Q: I have a timesheet that was originally created with 26 tabs for each pay period every two weeks, the ... A: Barb, Unfortunately, this is not going to be easy. But, it CAN be done by equations, which will ... | |
| in-cell data validation drop down bigger | 12/15/2008 |
| Q: I have a cell that has vertically oriented text ( rotated 90 degrees ) to which I want to add an ... A: Tom, If you already had the answer, then why did you ask me? Is this some sort of test? The ... | |
| Linking Multiple Cells in Excel | 12/15/2008 |
| Q: I have an attendance workbook; each sheet has all employees, each day of the month and columns which ... A: Critty, I suspect your problem can be eliminated by consolidating all the sheets into one ... | |
| Excel Counting | 12/15/2008 |
| Q: I am not really sure if this question can be answered without a macro or not but I am trying to ... A: Tim, Just about anything can be done without a macro! Especially this. I think what I would ... | |
| Excel Monthly Production Tracking | 12/14/2008 |
| Q: I have a real challenge in front of me. I own a small manufacturing company, and we produce several ... A: David, I kind of hate to suggest this, but for my professional services I have developed a ... | |
| paste special function lost between separate sessions of excel | 12/14/2008 |
| Q: For some reason, only on my HP laptops (I have a small business and have HP desktops, Dell desktops ... A: Linda, I'm sorry to say that this seems mysterious to me also. The only thing I can think ... | |
| Finance Formulas using Excel | 12/13/2008 |
| Q: You are considering an investment in two projects, A & B. Project A will caost $60,000. Project B ... A: Jill, This sounds like a homework or exam problem. Is it? I never hand out the answers to ... | |
| 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: David, The simple answer is that Excel is perfectly suited to this type of thing. You don't ... | |
| Embedding Files into spreadsheets | 12/12/2008 |
| Q: I need to embed pictures and pdf files into my excel spreadsheets. I would like to have the picture ... A: Corbin, This is so easy to do, I don't understand why you're having trouble with it. But, ... | |
| Time Diffference | 12/12/2008 |
| Q: I need to calculate time difference between 2 different date formates irrespective of which date is ... A: Mahesh, INT performs ROUNDING, a little-known fact! So don't use INT. TRUNC() is better. ... | |
| Excel-accounting | 12/12/2008 |
| Q: I'm making general ledgers for accounting on excel and i have separate columns for the thousands, ... A: Evan, Of course you can. You'll have to put a couple of equations at the bottom of each ... | |
| 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: Kevin, I believe your problem lies in the fact that your IF conditional is NOT a logical ... | |
| Excel - create a list of names from location | 12/12/2008 |
| Q: I have a worksheet that contains up to 2,000 rows of records which include columns for dates, times, ... A: Robert, EEEEK! This is not easy. I was afraid you wanted something like this. I'm going ... | |
| Excel - create a list of names from location | 12/12/2008 |
| Q: I have a worksheet that contains up to 2,000 rows of records which include columns for dates, times, ... A: Robert, Your problem statement is self contradictory. Please rewrite it so it isn't. You ... | |
| If function | 12/11/2008 |
| Q: I need a formula that will replace a specific text with a specific number. I use alot of codes. So, ... A: Ashley, An IF function is not the answer here, for the reasons you have already seen... its too ... | |
| Comparing columns | 12/11/2008 |
| Q: I need small help: I know that it is possible that Excel finds two exact text strings, but is it ... A: Hey, I don't know of an easier way then your suggestion, but you will still have some problems ... | |
| RANK | 12/11/2008 |
| Q: i need to rank data that has subtotals within the data. I need to rank both the subsections and the ... A: Dave, This is just a guess. I don't use SUBTOTALS very often. Have you tried collasping the ... | |
| Deleting leading apostrophes | 12/11/2008 |
| Q: Bill, I have a spreadsheet with data preceded by an apostrophe. This, I've learned, is a Lotus ... A: Bob, You've solved 99% of your problem. Use your equation, as it works fine. Then select and ... | |
| pulling a max value from continuously refreshed data | 12/11/2008 |
| Q: I have a number loaded into cell C38 form an external data source. This number is updated ... A: Chris, Believe it or not there is a way to do this. But, you have to set the number of ... | |
| Is there a way to make an excel cell use a formula that is located in another cell? | 12/11/2008 |
| Q: I am wondering if there is a way to make an Excel "cell" use a formula that is located in another ... A: Jamie, You are correct: there is no way to make a reference to a formula the way you wish. The ... | |
| Follow up to forecasting question | 12/11/2008 |
| Q: I have a follow up question about your excellent solution below from earlier this month: Your ... A: Simon, If all the worksheets are the same in structure, then MAYBE there is a way to make only ... | |
| Return value from other coloum than check is in | 12/11/2008 |
| Q: I have two sheets. In sheet 2 I have a unique number in A1 and would like B2 to be filled in. I ... A: Thomas, Read the description of VLOOKUP again. The problem is that you can't look to the LEFT ... | |
| Vlookup - table array contains only part of strings | 12/10/2008 |
| Q: -Column A contains whole bar code number (combination of numbers & letters,15-23 characters ... A: Goran, Rules are never given by EXAMPLE. You have to be able to state the rules without using ... | |
| excel 2003 automated query | 12/10/2008 |
| Q: First of all, sorry for my English. I really need to see the light in the following problem: I have ... A: Carlos, Your English is perfectly clear, no need to apologize. However, your question is ... | |
| excel formula | 12/10/2008 |
| Q: -I send out an e-mail report everyday for problems with specifc product order#'s which includes how ... A: Li, My idea was to create a pivot table where all the names would be displayed at the same time! ... | |
| Vlookup - table array contains only part of strings | 12/10/2008 |
| Q: -Column A contains whole bar code number (combination of numbers & letters,15-23 characters ... A: Goran, There is some rule in your head (but not in mine) that governs what you mean by the ... | |
| Return value from other coloum than check is in | 12/10/2008 |
| Q: I have two sheets. In sheet 2 I have a unique number in A1 and would like B2 to be filled in. I ... A: Thomas, The VLOOKUP function was designed to do exactly what you want! Read about VLOOKUP in ... | |
| Excel cell linking of text over multiple sheets | 12/9/2008 |
| Q: I have a master worksheet with all of our projects listed on it including details like staff, date, ... A: Ginny, It is very difficult to make a two-way link work the way you wish; in fact it is ... | |
| Charting work | 12/9/2008 |
| Q: Hope you can help and hope my request makes sense. I would like to develop some sort of graph to ... A: Gwen, The obvious answer is to graph the bottom line of the percent sums, which you called ... | |
| 2 argument If(And statement | 12/9/2008 |
| Q: I am having troubles figureing out a problem that I am having with conditional formatting. What I am ... A: Peter, The key to answering your question is this: put into words, in normal English sentences, ... | |
| Arrays? | 12/9/2008 |
| Q: I am trying to calculate a figure based on values between a date range and a number of critera. I ... A: Louise, You have almost got it correct, you're so very close! Basically all you need to do is ... | |
| Excel Charting | 12/9/2008 |
| Q: I emailed you the details at your account. Please let me know if you have any questions. Thanks ... A: Mike, What is it that you want to do? I haven't the slightest idea what you're after, besides ... | |
| Excel linking worries | 12/8/2008 |
| Q: I am creating an excel file that has all our forecasting information for multiple geographies. I ... A: Eliza, The link is tied to the VALUE or CONTENT of a cell, not to the cells LOCATION. So when ... | |
| Pulling Data from .jpg | 12/8/2008 |
| Q: Do you know of any software, or a procedure that can lift data from a .jpg file, so that it can be ... A: Mary, Your only hope is some sort of OCR (optical character recognition) OR perhaps a bar-code ... | |
| Subtracting one line in a column from another line in a different column | 12/8/2008 |
| Q: I would like to keep track of payments made and my spread sheet is set up so that the payment is ... A: Barbara, Well, this seems extremely simple so I must not understand the scope of your question. ... | |
| Excel Formatting Issue | 12/5/2008 |
| Q: This one seems complicated...I have a about 200 sheets (tabs) in an excel workbook. The last sheet ... A: Stephen, You can use the FORMULA MODE of Conditional Formatting to refer to cells other than the ... | |
| excel formula | 12/5/2008 |
| Q: -I send out an e-mail report everyday for problems with specifc product order#'s which includes how ... A: Li, -In excel there is a macro program which converts all the data pasted from the word attachment ... | |
| 2 argument If(And statement | 12/5/2008 |
| Q: I am having troubles figureing out a problem that I am having with conditional formatting. What I am ... A: Peter, Your explanation is better, but I still have some questions. In your question, you ... | |
| Excel: Variables and Greater/Less than | 12/5/2008 |
| Q: I have a spreadsheet detailing around 500 individual cases. I need to count how many of these are a ... A: Paul, I have also had problems using those >= (and other) symbols inside a function. I've ... | |
| Vlookup to an external excel file | 12/5/2008 |
| Q: I am pretty good with excel, and this problem has been bothering me for a while. I have a vlookup ... A: Kamil, It certainly does NOT work for me. I get errors which say the link cannot be updated. ... | |
| 2 argument If(And statement | 12/5/2008 |
| Q: I am having troubles figureing out a problem that I am having with conditional formatting. What I am ... A: Peter, I think you do not really mean "conditional formatting". CF is a way of changing the ... | |
| custom 'rounding' | 12/4/2008 |
| Q: I have a list of prices to which I want to apply a currency conversion factor, say 1.25 and round ... A: Pierre, My answer would always give you to the .99, that's what an integer is. You'd never get ... | |
| Excel: Variables and Greater/Less than | 12/4/2008 |
| Q: I have a spreadsheet detailing around 500 individual cases. I need to count how many of these are a ... A: Paul, I'd have to say I have never heard of DLOOKUP. I am using Excel 2003 so maybe this is ... | |
| formula needed | 12/4/2008 |
| Q: I have a spreadsheet with 53 rows and 20 columns. Column B has a different number for all 53 ... A: Tim, This sounds too easy, so I must not understand what you really mean. The confusion is ... | |
| Date functions | 12/4/2008 |
| Q: Using the =now() or =today() function, I would like to get the next couple of days. That I know how ... A: Karen, I basically wrote the IF statement for you, in my previous answer. So, it should be the ... | |
| Create Hyperlink To Workbook | 12/4/2008 |
| Q: I have prepared a simple worksheet with Validated Cell ranges. I send this workbook as a template to ... A: Kedar, It sounds like you want my exact product. This isn't something I am willing to give away ... | |
| Nested/Multiple IF statements | 12/3/2008 |
| Q: Could you help to solve this fancy multiple if equation. ... A: Prasanna, There are actually so many problems with this I hardly know where to start! The only ... | |
| Excel Count | 12/3/2008 |
| Q: How do I count the number of cells in column B with a value <0 if it meets search criteria in column ... A: Bill, This can be done with the COUNTIF function. But, I am not exactly certain what you ... | |
| functions | 12/3/2008 |
| Q: I have an excel spreadsheet template with 3 columns. Column A is a list of accounts, column B is ... A: Karen, Become familiar with the ADVANCED FILTER. You'll see an option that allows you to copy ... | |
| Graph showing the +- flow | 12/3/2008 |
| Q: I'm trying to create a graph/chart of a cash flow. If I start with a value of 2200 and the next ... A: Bridget, Do you still need this information? I apologize for letting your question linger. ... | |
| Date functions | 12/3/2008 |
| Q: Using the =now() or =today() function, I would like to get the next couple of days. That I know how ... A: Karen, You have to write a special equation to make it skip the weekend dates. Luckily ... | |
| Excel email formulas | 12/3/2008 |
| Q: Bill - My son emailed me a spreadsheet with 3 worksheets; but the formulas did not get transferred. ... A: John, I've never heard of this happening before. There isn't a setting that controls this What ... | |
| IF functions | 12/3/2008 |
| Q: I have a sheets that contains power consumption rates for winter peak time between 17:00 and 19:00. ... A: Arno, It's difficult to say how to fix it, because I don't know how it's acting. What IS it ... | |
| Compiling a text list from cells based on the contents of other cells | 12/3/2008 |
| Q: I have a spreadsheet where Column A is a list of the characters in a play. Columns B-Z are titled ... A: Anne, I can provide you a fairly complete solution for only $50 USD. Can you afford that? ... | |
| random assignment | 12/2/2008 |
| Q: I need to randomly assign 3000 fish to each of 3 experimental treatments. They have to be selected ... A: Daniel, It is probably almost that simple. The random numbers will be between zero and one, ... | |
| IF functions | 12/2/2008 |
| Q: I have a sheets that contains power consumption rates for winter peak time between 17:00 and 19:00. ... A: Arno, very good! You just have some extra parentheses which are confusing poor TRUNC. ... | |
| Cell Formatting / Display | 12/2/2008 |
| Q: Mr. Hermanson, I am using data validation list to create a part number configurator(cell A1). I have ... A: Frank, This is possible, but only via a running macro. In order for a cell to appear as ... | |
| lookup function not working | 12/1/2008 |
| Q: I am using a lookup function that has worked for quite some time. But now it doesn't seem to work ... A: Rich, You have undoubtedly messed up the VLOOKUP function by adding & deleting (and ESPECIALLY ... | |
| if statment contains this then | 12/1/2008 |
| Q: I have a sheet that has values such as "Text 2M 0156", where this one has a 2m in it others could be ... A: Alex, What you want to do is called PARSING the text string. You can use various Excel TEXT ... | |
| Vlookup to return all matching values | 12/1/2008 |
| Q: I have a list of vendors and people assigned to those vendors. Tammy 10056 PRECIOUS Tasha ... A: Ryan, Before I give you an answer, I'd like to know if the Master Table is constantly changing, ... | |
| Create Pivot-Table from 12 sheet Excel Calendar w/Screen Print | 12/1/2008 |
| Q: I have a 12-Sheet Excel Calendar workbook created with each worksheet designated as a separate month ... A: Rachel, Blank rows calls, or columns in a pivot table cause no problems. Even the AVERAGE ... | |
| Forecasting Year total | 12/1/2008 |
| Q: I have sent my spreadsheet via email to . I think what I want to do is fairly simple but I am not ... A: Simon, Your problem has a very elegant solution. parts of it are complex and parts are easy. ... | |
| Conditional formatting and Year | 11/29/2008 |
| Q: I would like to add conditional formatting to a row of cells based on whether the value in Column ... A: Diane, Use Conditional Formatting (hereafter referred to as CF) in the FORMULA IS mode. The ... | |