| Subject | Date Asked |
|
| Excel Formula - A doozie | 10/3/2009 |
Q: I am trying to find the correct formula to convert a time, for example 9:00 am, to simply the number ...
A: Sorry for the delay, I've battling the flu. Time values in Excel are really just the decimal part ...
|
| lots of ifs | 9/15/2009 |
Q: I was wondering if you can help me with the following puzzle. I have two columns with dates. In the ...
A: The easiest solution would be to select all the date cells and use the Replace command (Edit menu) ...
|
| excel 2007 Conditional formatting for dates | 8/19/2009 |
Q: I'm using Excel 2007. I'm building a tracking spreadsheet to track training dates. As people ...
A: You will need to set up three cases to do this. In the Conditional Formatting dialog box, set it up ...
|
| Excel formula problem | 8/19/2009 |
Q: I have a long list showing Item No. in A column, Shop No. in B column and Sales Qty. in C column. I ...
A: You will need an array formula for this. The formula in cell F5 would be: ...
|
| sumif with partial keyword | 8/18/2009 |
Q: col. A col. B col.C Col.D AN90308 150.000 903 280.000 AN90408 125.000 904 325.000 ...
A: If the first line is on row 1, the formula for cell D1 would be: ...
|
| Excel - D.O.B | 7/15/2009 |
Q: I have the 2003 version of excel on which i have created a data base with names, adress, contact ...
A: Sorry about the delay. If you sort the dates low-to-high, the data will be sorted ...
|
| Data Validation in Excel with Tables | 7/12/2009 |
Q: Is there anyway to create a dropdown list in excel with the data validation function using a table. ...
A: It is probably one of two things: 1) Your table reference is not a single column or row. If so, ...
|
| How to enter a formula in an excel macro without using the string format | 7/9/2009 |
Q: I am trying to calculate polynomial coefficients in an excel macro. I can write ...
A: Use ActiveCell.Formula instead of ActiveCell.FormulaR1C1 to enter a formula with cell references or ...
|
| #N/A of Vlookup | 6/2/2009 |
Q: I would be much appreciated if you can help. My question is when using Vlookup, if any formula to ...
A: Yes, the ISNA function can make this happen for you. It tests for an #NA error result and can be ...
|
| Excel Formula | 6/1/2009 |
Q: Country: United States Douglas, I have a massive spreadsheet that pulls most of its information ...
A: You can use the INDIRECT function to convert a text string into a reference. Something like this: ...
|
| Formula 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: Most Excel error messages tell you what is wrong, like DIV/0 or REF, but a VALUE error means it ...
|
| Inserting rows in several worksheets | 5/21/2009 |
Q: Wondering if i can insert a row at say row 20 on sheet 1 and Excel will automataically insert a row ...
A: This can be done by selecting multiple worksheets before performing the row insert. Hold the ...
|
| Making a named range dynamic | 5/19/2009 |
Q: In Excel 2000 I have a named range currently defined as =Eowercape!$25:$45,Eowercape!$2:$22 I ...
A: I've seen multiple area ranges and dynamic ranges, but I've never seen anyone combine them before. ...
|
| If Then and Between | 5/18/2009 |
Q: I think this is simple but I can't find the answer. I am looking for 3 values dependent one cell ...
A: Sorry for the delay. I mis-read the problem, not seeing 5956 and 5959. They looked the same to my ...
|
| If Then and Between | 5/17/2009 |
Q: I think this is simple but I can't find the answer. I am looking for 3 values dependent one cell ...
A: A nested IF statement can do this. Something like: ...
|
| Time Calculations | 4/25/2009 |
Q: I am trying to make a schedule and the problem I am having with the formula is when someone works ...
A: Sorry for the delay. The problem probably is that you are not really entering the quitting time as ...
|
| Subtraction of year in MS Excel | 4/24/2009 |
Q: we have to subtract the year by 25 in Msexel it is in the formate of dd/mm/yyyy in this we want same ...
A: You can "take apart" and reassemble dates in Excel using the YEAR, MONTH, DAY and DATE functions: ...
|
| SET DUEDATE IN EXCEL | 4/23/2009 |
Q: Smith, I am now having a problem with setting due date in Microsoft Excel. I'd like to explain in ...
A: Your formula would be: =A1+1 Dates are really integer values. Change cell the A1 format to General ...
|
| Excel ActiveWindow.ScrollRow | 3/15/2009 |
Q: Is there a way to have in a macro the "ActiveWindow.ScrollRow = xxx" pointed on a named row rather ...
A: Yes this is, but you need to pass the named row's row number to the command as part of the process: ...
|
| excel ranking | 3/14/2009 |
Q: What it the formula to show ranking from lowest to highest? If I just use the rank formula is ranks ...
A: RANK can go both ways, depending on how you set the third argument. =RANK(number,ref,order) If ...
|
| Getting Excel to find items with a particular symbol or word and then paste it in a different column | 3/13/2009 |
Q: I'm starting with an Excel list where people's names, emails, and such are listed vertically. Rather ...
A: The FIND function can be used to identify characters in a string. Example: if cell A1 contained ...
|
| Previous 90 Days Formula | 3/10/2009 |
Q: I am working on creating a pilot logbook in excel. In order to calculate recent experience, I need a ...
A: This should do what you want: =SUMIF(range of dates, ">="&TODAY()-90,range of landings) SUMIF ...
|
| auto rounding?? | 2/24/2009 |
Q: i am in an online accounting class and i notice when i am entering the numbers that they are being ...
A: Excel is not actually changing the values, it's only displaying the number rounded to the format ...
|
| Conditional Formatting across mult. tabs | 2/23/2009 |
Q: I am working on a file for fantasy baseball. I have individual tabs for each of the positions and ...
A: Yes, this is possible, but without seeing your file, I can only give you a general answer. The ...
|
| IF statement help | 2/21/2009 |
Q: I am trying to create an IF staement formula and am having difficulties and I need to find an answer ...
A: Give this formula a try: ...
|
| looking for a value in a matrix based on 2 criteria | 2/12/2009 |
Q: I would like to look up the value in a matrix based on 2 criteria. For example: Location/ Week 1 ...
A: There are multiple ways to do this, but using MATCH and INDEX looks like the best solution for data ...
|
| multiplying blank cells | 2/12/2009 |
Q: Cell A1 has a number in it. Cell B1 is blank. When I multiply A1*B1 the result is reported as 0. ...
A: The easiest way to do this is set the cell number format so zero values don't display. Example: If ...
|
| EXCEL - SUMIF | 2/11/2009 |
Q: I USED YOUR FORMULA LISTED IN A RECENT POSTING, THANK YOU VERY MUCH ...
A: SUMIF only totals up one column. You can work around this by adding a column that totals up the ...
|
| XL , IF FUNCTION ,EVALUATING WRONG | 2/7/2009 |
Q: Please help ! ! I spent hours trying to figure this out ? if B2=B3 and B2=H2 the result should be ...
A: I suspect one of the cell values is actually "1", a text value, instead of 1, a number. The way to ...
|
| formula conversion | 1/19/2009 |
Q: an excel file in my office has a cell with a formula of =+IF(A1>0,(A1*37.5)/A1+12.5*A1,0). i can ...
A: Your formula is more complicated than it needs to be. The part that is (A1*37.5)/A1 will always be ...
|
| Want to nest more than 7 if statements | 1/10/2009 |
Q: I want to nest more than 7 if statement at a time, but i can do only 6, plz help me. the formula is ...
A: The short answer is, you can't. Excel has a limit of seven nested IF statements. However, there is ...
|
| Calculating results in multiples of number | 1/9/2009 |
Q: I would appreciate it if you can assist me with a formula that can round amounts up to multiples of ...
A: Excel has a function that will do this: CEILING. In your case, the formula would be: ...
|
| Data Range for Charts | 1/9/2009 |
Q: I know that the 'Data range' for charts can be selected within the 'Source Data'. Which is ...
A: The only way I know to make this a little easier is to select the data and labels before activating ...
|
| Find and replace using wild cards | 1/7/2009 |
Q: I want to replace a bunch of formulaes in a spreadsheet. The formulaes are of the same format ...
A: That technique won't work because Excel can't remember what the * was used for in the Find step and ...
|
| Excel Forumula Help | 12/7/2008 |
Q: My problem consists here. I have a cell with the values of this in the cell O10A2X4C1. ( this cell ...
A: A second text for the O11 text must be added to the formula: ...
|
| Excel 2003 Formulae | 12/6/2008 |
Q: I am trying to find a quick way of changing sign conventions. eg Column A consists of 100 rows of ...
A: You can use Paste Special to do that. First, enter the value -1 into a cell. Now copy that cell ...
|
| Excel color function | 12/4/2008 |
Q: Can I change a cell color or font color using formula bar? [Example =IF(D5=abcd,cellcolor=red,"")] ...
A: The short answer is, you can't, at least directly in a cell formula. However, that can be done with ...
|
| Formula | 11/14/2008 |
Q: You kindly supplied me with a solution to a problem I was having trying to separate numbers only ...
A: Yes, you left out the "" argument in the formula: ...
|
| military time trouble | 11/12/2008 |
Q: Using Excel 2003 SP2 I'm setting up a turn-around-time worksheet and am trying to get the time to ...
A: You need to enter the colon (13:10) to make it work. Time values are numbers between zero and one. ...
|
| Forumla | 11/11/2008 |
Q: I have a column in my database consisting of numbers & text combined in one cell, such as ...
A: Give this a try. It's all one big formula but I've spaced it out so you can read it. It works with ...
|
| Formula help in excel | 11/11/2008 |
Q: I am a begineer in excel & I would like to copy a value from sheet 1 in exccel to another sheet in ...
A: There are lots of pasting options available if you use Paste Special (Edit Menu) instead of Paste. ...
|
| Help with complex conditional formulas. | 10/30/2008 |
Q: Now I know that for the most part, conditional formulas are meant to be fairly simple in structure, ...
A: You have identified the correct functions to use, but some assembly is required. The basic form of ...
|
| count | 10/21/2008 |
Q: In my example there is 2 sales person.Jhon & Smith. I want to count the particular person how many ...
A: A simple change to the above formula will count instead of sum: ...
|
| Auto disable macros | 10/19/2008 |
Q: is there a way to auto disable macros is a specific data on a cell is ZERO? if the data is 1 macros ...
A: If you are talking about changing the global macro security level (Tools menu, Macro submenu) with ...
|
| count | 10/17/2008 |
Q: In my example there is 2 sales person.Jhon & Smith. I want to count the particular person how many ...
A: I'm assuming you want the monthly totals for each person. You will need an array formula for this ...
|
| updating data from daily work sheet into month ending worksheet | 10/17/2008 |
Q: I have created a daily worksheet relating to productivity, in addition I've created a month ending ...
A: Yes, you can fill and/or copy these types of formulas around but you need to look at how the cell is ...
|
| XL ARRAY formula, | 10/15/2008 |
Q: I have a spreadsheet with 5 different large sheets containing a lot of data which is added to every ...
A: All you need to do is change the reference to specifically address the other sheet. The are a ...
|
| Excel 2000 Formula | 10/8/2008 |
Q: I recently solved an issue in Excel2000 based on this answer to a similar issue: ...
A: ANDs and ORs could be used, but here's a neater, cleaner (and cooler) way to do it: ...
|
| Summing up values by day | 10/7/2008 |
Q: I have 2 colums: 1 contains dates and the second columns contains values. Example: 1/2/2007 34.5 ...
A: SUMIF will do this for you. Put the list of dates you want to get totals for in column and then use ...
|
| Pasting a row to a column | 9/20/2008 |
Q: Is there a way to copy a row (cells A1 to D1), and paste it to a column (cells A1 to A4) Thanks ...
A: Yes, this can be done with the Transpose option in Paste Special. Copy cells B1:D1, select cell A2 ...
|
| sum if using a wildcard | 9/20/2008 |
Q: column A contains dates in the following format month/day/year. column B contains numbers. I want ...
A: SUMIF only handles one condition so it won't directly handle your two conditions, year and month. ...
|
| link path definition | 9/19/2008 |
Q: I try to create a link which provide value, where the link path is determine from another cell, ...
A: The REF error means Excel is now treating it as a reference, but it isn't finding the file ...
|
| Separating Decimals and Whole Numbers | 9/18/2008 |
Q: I'm in the process of creating a spreadsheet for my nightly bar counts and I can't quite figure out ...
A: Yes, Excel has a function that will take care of both parts of the number split for you: INT, the ...
|
| price increase formula | 9/9/2008 |
Q: I'm struggling and hopefully you can help me! I have a price list and I wish to increase it by ...
A: The way to set this up would be to put the percent amounts in cells with the "increase by" text and ...
|
| Arithmetic Calc using Concatenated Text | 9/8/2008 |
Q: Trying to perform an arithmetic calculation using a concatenated formula I have attached a screen ...
A: I think I see what you are trying to do, build a reference for AVERAGE to use, however, in that ...
|
| Spreadsheet | 9/6/2008 |
Q: I am looking for a quick way to match my debit and credit amounts within a sheet. One column contain ...
A: Can you send me a copy of the file? I can give you a more efficient answer if I can see exactly ...
|
| Spreadsheet | 9/5/2008 |
Q: I am looking for a quick way to match my debit and credit amounts within a sheet. One column contain ...
A: I'm going to assume the credit numbers are positive and the debit numbers are negative. SUMIF ...
|
| conditional formatting using null values | 8/1/2008 |
Q: I have a sheet for entering data. I would like to make it so that typing data into a row, when ...
A: You will need to add a second condition to check if B1 is blank or not to cover this situation: ...
|
| conditioning format | 7/31/2008 |
Q: I have an expiry date of say 31/12/2008. I want the expiry date cell to change colour to say green ...
A: Yes, this can be done. Here's an example for cell D6: Call up the Conditional Formatting (CF) ...
|
| IF /AND statement help needed | 7/19/2008 |
Q: Below is an example of the data: Original Purchase Price $139,800.00 Bedrooms 3 Bath 2 AC/SF 1870 ...
A: If three out of ten tests passed yield a "qualifies" result, then you can't use AND in this manner. ...
|
| Help required with formula | 7/15/2008 |
Q: In a database where I have got 20 venue names from A1:A20 and on each column(B1:K20)details is there ...
A: You can use MATCH and INDEX to achieve this. Assuming your Venue names are in cells A2:A21, the ...
|
| find next empty cell formula | 7/13/2008 |
Q: i am trying to create a formula or function located in worksheet "A" to find in worksheet "B" the ...
A: Here's what I've come up for your sheet "A" formula. (I'm assuming "A" and "B" are the actual sheet ...
|
| Retrieval multiple data from multiple sheet to one sheet | 7/3/2008 |
Q: I need to do some huge task in excel wherein i have some doubts. In a Excel file, i have more ...
A: You can use the INDIRECT function to quickly "assemble" the references you need. Example: If you ...
|
| Ensure date entry into 2 columns that are related to each other | 7/3/2008 |
Q: I have created an excel sheet that has a list of file names in column B and the status of those ...
A: Data Validation can look at other cells for criterion, but it is only actually activated when you ...
|
| Excel formula | 7/2/2008 |
Q: I have this formula in cell A1 IF (BP3=248,"Full", BP3/8) In cell A5, I want to shown the result ...
A: kirupairajah, I'm glad I was able to answer your question and that you rated my answer, but I do ...
|
| array using counta function | 6/16/2008 |
Q: Good day Douglas, I'm sure i'm close but I do not understand why it does not work. In column Q, ...
A: Yes, you are on the right track, but it's not intuitively obvious what you need to do at this point. ...
|
| conditional formatting | 6/13/2008 |
Q: I have conditionally formatted a cell and have managed to copy this down the column. I am using 2 ...
A: I've never heard of that happening and I can't think of a reason why it would. I would need to see ...
|
| conditional formatting | 6/12/2008 |
Q: I have conditionally formatted a cell and have managed to copy this down the column. I am using 2 ...
A: It sounds like it's cell reference issue in the conditional format formula. Your references to ...
|
| Sum column based on criteria of other columns (index functionality) | 5/22/2008 |
Q: I have an Excel file with 4 columns. I need to sum the data in the 4th column for every record that ...
A: You are on the right track. The formula should be: ...
|
| Sorting? | 5/21/2008 |
Q: I have created an excel spreadsheet with a list of all the helicopter parts that I have in stock. I ...
A: Good morning SGT Adams, Excel has a custom list / sorting feature that might be what you need. ...
|
| Vlookup | 5/20/2008 |
Q: Using Vlookup...i need to compare 2 seperate spreadsheets that contain data. If one sheet has a list ...
A: Your formula would look something like this: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) A1 is the ...
|
| Excell work | 4/26/2008 |
Q: I am going to send one excel sheet to x, y and z by mail. X has to fill only x column, y has to fill ...
A: You can protect the cells you don't want to users to enter data in. It's a two step process. First ...
|
| If condition | 4/13/2008 |
Q: but not able to get the output. Could you pls help me to sort out the same. ...
A: If you change the plus signs to ampersands and add "" as the false part of the IF statements and it ...
|
| conditional formatting | 4/12/2008 |
Q: I have gotten a gantt chart schedule second hand. It automatically puts the days in, in addition it ...
A: Let me give you a quick lesson with WEEKDAY first. It has three forms: =WEEKDAY(A1,1) or ...
|
| lookup 2 conditions | 4/11/2008 |
Q: Greetings Douglas Smith, Please help me with this condition where i have to lookup 2 conditions to ...
A: This can be done with an array formula. With you data on Sheet1 in cells A2:C16, and your results ...
|
| Excel- pay more for more work | 4/10/2008 |
Q: I would like to produce a spreadsheet that calculates the more you will work the more you will get ...
A: A lookup table can handle this task. First, create a table with the hours and pay rates. Example: ...
|
| Need Formula to Project Income and Expenses | 4/9/2008 |
Q: I have two worksheets, an income and an expense worksheet, each with 12 months of data. I would ...
A: Excel has a number of forecasting functions available: TREND, LINEST, GROWTH, LOGEST My guess is ...
|
| formula help needed ASAP :( | 4/2/2008 |
Q: I am trying to calculate a formula that calculates a percentage. We will have a range say: ...
A: If you put the value to lookup in cell A1, the three numbers in cells D1:D3 and their corresponding ...
|
| Using two different files in one formula | 4/1/2008 |
Q: I have two documents "TILL RETAIL REPORT 2007" and "TILL RETAIL REPORT 2008". I am wanting to ...
A: It looks to me like it does like the $D$4:$D$5 part of your formula. My guess is you want to add ...
|
| How to Match Values Between 2 Sheets & Copy Corresponding Data ? | 3/7/2008 |
Q: Suppose we have 2 sheets Sheet1 and Sheet2. Sheet 1 In Sheet 1 Column A we have some names like ...
A: A combination of the MATCH and INDEX functions will do this. Your formula for Sheet2, cell B1 would ...
|
| Calculating only select cells | 3/6/2008 |
Q: Purpose: calculating the percentage votes of a massive number of people according to the share ...
A: SUMIF will do this for you. The syntax would be: =SUMIF(range of "x" cells, "X", range of ...
|
| validating the value | 3/5/2008 |
Q: how can i have an worksheet that the value would start at "0"? this the scenario: ex. in column A ...
A: The formatting I described above will take care of that. If you enter 12345, it will show as ...
|
| Customer Territory Matching | 3/5/2008 |
Q: Sheet 1 Column A - I have 40,000 zip codes (US) Column B - I have territory codes for each zip code ...
A: You can use the MATCH and INDEX functions to retrieve the Territory Codes: =INDEX(Territory Codes ...
|
| validating the value | 3/4/2008 |
Q: how can i have an worksheet that the value would start at "0"? this the scenario: ex. in column A ...
A: This can be done with a custom number format. It's actually a variation of the zip code format. ...
|
| Average with two criteria | 2/29/2008 |
Q: I have read numerous websites that recommend the use of array formulas but for some strange reason ...
A: The primary reason a #Value! error occurs is that the formula is looking for a value and is finding ...
|
| excel formula | 2/29/2008 |
Q: I have two columns of numbers (same # of cells in each) and need to develop a formula to compare ...
A: You can use a formula like this to identify the closest intersecting value: ...
|
| vlookups... | 2/28/2008 |
Q: I have the following formula: =VLOOKUP($C8,$D$2:$F$280,3,FALSE) My problem is that column D ...
A: The problem in the FALSE term, which tell the formula you want an exact match, which you don't. ...
|
| removing time from date cell | 2/27/2008 |
Q: Do you know of a way to remove the time/zone info from the following data (shown as it appears in ...
A: You copy the formula cells in column B and then either: Use Paste Special with the Values option to ...
|
| removing time from date cell | 2/27/2008 |
Q: Do you know of a way to remove the time/zone info from the following data (shown as it appears in ...
A: I'm assuming that is a text string so a formula will be needed to pick out the parts you want. This ...
|
| Formula to always populate date of previous Friday | 2/9/2008 |
Q: I'm looking for a formula (Excel 2003) that will always return the date of the previous Friday, ...
A: This formula should do the trick for you: =TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,4,5,6,7,1) TODAY ...
|
| Excel Problem Solving calculation | 2/7/2008 |
Q: I have XP 2003. From month to month, I copy a worksheet into a new worksheet within the same ...
A: My guess is the calculation mode is set to manual. Press the F9 function key and see if the sum ...
|
| Organizing List | 2/6/2008 |
Q: I have created a spreadsheet that in one column it has all the part numbers that we have counted and ...
A: You can do this with Data Filtering. Select a cell in table (I usually use a column title cell) and ...
|
| Copy texts from multiple sheets | 2/6/2008 |
Q: I have a workbook with about 100 sheets, in cell B4 of all these sheets i have a specific text ...
A: If you have a list of the sheet names in cells, you can reference cell B4 in all of them using this ...
|
| Formulas with dates in Excel | 1/25/2008 |
Q: I am creating a spreadsheet of testing dates for people. I have entered their name and their ...
A: Conditional Formatting will do this. Select the date cells and then select Conditional Formatting ...
|
| Making an Excel Chart to calculate a hotel bill | 1/25/2008 |
Q: I'm trying to create a spreadsheet that will figure out the total cost of a stay at a hotel but the ...
A: The best way to do this is to build a table with each date and rate and then read it with a lookup ...
|
| excel and dates | 1/9/2008 |
Q: im from the netherlands, im trying to create something in excel but im having problems. in column b ...
A: The SUMIF function can do this. Something like: =SUMIF($B$2:$B$100,"<="&TODAY(),$E$2:$E$100) It ...
|
| Excel | 1/8/2008 |
Q: I am having trouble figuring the formula to show Markup percentage not just percentage of items. We ...
A: If I understand your question correctly, you take the difference between the SRP and the cost price ...
|
| excel formula | 1/7/2008 |
Q: i'm in the process of creating a spreadsheet for a project list. on the spreadsheet is a column with ...
A: Conditional Formatting can do this. Select you date cell and then select Conditional Formatting ...
|
| Excel Address Database | 1/7/2008 |
Q: You helped me in the past with a database of mailing addresses. I can't get that solution to fix ...
A: If your first address starts in cell A1, put this formula in cell B1: =A2 Now select cells B1 and ...
|
| Date Calculation | 11/30/2007 |
Q: I am trying to schedule a projects start date. If my project needs to be completed by 3/1/2008 and ...
A: Jerry, I just realized I didn't quite answer your question. I look at it it a little closer and ...
|
| Excel format and formula | 11/29/2007 |
Q: I have created a form where I have a column of numbers that I have formatted with 0 decimals so that ...
A: Yes this is possible with an array formula, something like: =SUM(ROUND(B3:B13,0)) Use ...
|
| formula creation for repeated numbers. | 11/14/2007 |
Q: i have series of numbers in cell A1 TILL A50 , i want to analyse in cell C17 that in range A1:A16 ...
A: The formula in cell C17 would be: =$C$16&" occurs "&COUNTIF(A1:A16,$C$16)&" times" where cell C16 ...
|
| date formulas in excel | 11/13/2007 |
Q: I have a colum of dates in a1 and todays date in cell b1 with the now() and would like to have the ...
A: You can do this by comparing the cells. The formula for cell B2 would be: =IF(A2=$B$1,"Today","") ...
|
| hello--Help needed | 11/12/2007 |
Q: I have a problem in excel, i try to link two excel sheets. The first is a list of product ...
A: Yes, this is possible with Data Validation. First, you need to name the range your products are ...
|
| MS Excel formula | 11/12/2007 |
Q: "I am trying to get the total of a specific range of one sheet to show up on a different sheet ...
A: The formula would look something like this: =SUM(Sheet1!A1:A100) or if the sheet has a space, dash ...
|
| Excel Help | 11/8/2007 |
Q: I am currently creating a budgeting spreadsheet to calculate monthly bills, due dates, dates paid, ...
A: Yes, this can be done with Conditional Formatting. Select the range of cells in column C you want ...
|
| Excel Lookup Problem | 10/25/2007 |
Q: I am trying to use a simple lookup formula to search through a bunch of dates and then return the ...
A: It could be a number of problems: Dates not sorted Formula not configured correctly for data Dates ...
|
| VLOOKUP | 10/25/2007 |
Q: is there a way to do a VLOOKUP within a cell. for example, i have a bunch of text in a cell that is ...
A: VLOOKUP will not work within a cell, but you can use the FIND function to locate a string of text in ...
|
| Counting numbers less than "X" | 10/24/2007 |
Q: I have a database in which I need to know how many numbers are less than "X" value, A list from ...
A: I'm not quite quite sure I understand your exact situation, but you can use the COUNTIF function to ...
|
| Excel and VBA passwords | 10/24/2007 |
Q: I understand that the passwords to protect sheets worksheets and vba code can be "cracked" with ...
A: Yes, Excel passwords are easy to crack. There are dozens of program on the market to do it. The ...
|
| convertion of time to numbers | 9/18/2007 |
Q: Is there a way to convert time formatted cells into numbers and decimals? 03:00 comes from the ...
A: Time values in Excel are really just decimal numbers. One day is 1, 12 hours is 0.5, six hours is ...
|
| formula: knowing tha data of a cell | 9/14/2007 |
Q: 7/6/2007 7/7/2007 7/8/2007 7/9/2007 total 1 2 7/7/2007(cell ...
A: Try this, if your dates are in cells C2:P2 and the entered numbers are in C3:P3: ...
|
| Convert TIme to Numbers | 9/7/2007 |
Q: I am formulating a sheet to track time. I need to convert time to numbers. Example: 2:15 (2 hours ...
A: Multiply the 2:15 cell by 24 to convert it to decimal number value. Be sure to format that cell as ...
|
| Link to other excel sheet | 9/6/2007 |
Q: Is there a way to use a formula to reference 3 cells, one containing the path, one containing the ...
A: Yes, this is possible using the INDIRECT function. It converts text into a reference. Start with ...
|
| Concatenate question | 9/4/2007 |
Q: I have eight cells that may or may not have a number in them. I would like to be able to concatenate ...
A: Sorry for the delay. Your question ended up in my spam folder. Try something like this: ...
|
| goal seek | 7/31/2007 |
Q: I was trying to use some vba solutions related to Goal Seek. I saw this one which is quite a help : ...
A: You can do this by adding a loop counter and using it with Offset. Before the loop, add the line: ...
|
| Counting out Workdays in Formula | 7/30/2007 |
Q: I need help being able to count out three workdays from a date entered in cell A1 and entering that ...
A: Excel has a function that will calculate this for you called WORKDAY. It is not available unless ...
|
| Adding ISERROR to this formula: | 7/29/2007 |
Q: I have a formula that works great until I need it to recognize bad data in the cell. I want to add ...
A: Sorry for the delay. I changed my email filters and my questions got dumped in the spam folder ...
|
| Excel coumn header changed | 7/28/2007 |
Q: How does excel create the column header label? I have a workbook with worksheets that are labeled ...
A: That's a tough one to find if you don't know about it. Select Options from the Tools menu and click ...
|
| Excel formula | 7/27/2007 |
Q: I have a list of 10 numbers and I want to add the lowest 5 numbers. But I want to exclude zeros and ...
A: Give this formula a try: ...
|
| Excel | 7/26/2007 |
Q: Hmm.. I got 2 workbook called book1 and book2 Book1 is design to be form in the spreadsheet for ...
A: I don't believe this is possible, but check with other experts too. Someone may have a technique to ...
|
| IF for #N/A | 7/25/2007 |
Q: In cell A2 I have an IF formula as follows: =IF(H2=0,1,2) However, in cell H2 the result of another ...
A: You can use the ISNA and OR functions to trap that error: =IF(OR(H2=0,ISNA(H2)),1,2) If H2 is ...
|
| Summing text values | 7/18/2007 |
Q: Doug. I am working on a staffing plan and want to be able to either sum by color of cell or sum by ...
A: Followup: Trying to count by cells color will required a macro. Here's an example: ...
|
| Excel - Timestamping upon Modification | 6/21/2007 |
Q: I have a spreadsheet which I use for managing a project. One of the columns (I)has drop down boxes ...
A: You will need a macro to accomplish this and the macro will need to be attached to the worksheet ...
|
| Conditioning Formula | 6/20/2007 |
Q: I have 3 conditions set up on monitoring status. "Operations Accepted", "Due" with the 3rd condition ...
A: Conditional Formatting goes through the conditions until it finds a TRUE result. It applies the ...
|
| Conditioning Formula | 6/20/2007 |
Q: I have 3 conditions set up on monitoring status. "Operations Accepted", "Due" with the 3rd condition ...
A: Your date test G4<"TODAY" is not testing the date because "TODAY" is a text string and not the ...
|
| divide The workers hours to the job.. | 6/6/2007 |
Q: I was having a bit of a problem. I have a small construction business and I'm trying to figure how ...
A: It sounds like SUMIF might solve your problem. The syntax is: =SUMIF(range of letters, letter to ...
|
| Refering sheets in excel | 6/3/2007 |
Q: I want to link particular cell from sheet 1 - 31 in a consolidated sheet. For example i want to ...
A: If you put the numbers 1-31 in cells in cells A1:A31, you can use this formula to read the numbers ...
|
| Conditional formatting question | 5/30/2007 |
Q: I wanted to ask you a quick question related to conditional formatting. If I have the following row ...
A: Yes, this can be done. Highlight the cells and select Conditional Formatting from the Format menu. ...
|
| Cell sums that require an IF factor | 5/29/2007 |
Q: I have a spreadsheet that calculates averages and sums. If the number is 0, i want it to read 0, ...
A: That is accomplished by testing the divisor (the number on the bottom) to see if it is zero. The ...
|
| excel | 5/27/2007 |
Q: what is a formula and formatting in excel?
A: A formula is programming statement in a cell that calculates or manipulates data in some way. ...
|
| extracting from cells | 5/24/2007 |
Q: I have a Dell with XP and am working with an Excel worksheet. The data in question has cells with a ...
A: Excel has a command to split up data. Select the data cells and the select Text to Columns from the ...
|
| VLOOKUP - Variable table_array | 5/23/2007 |
Q: I am using a VLOOKUP function where the table_array is located in another workbook. Within the ...
A: As you've discovered, Excel thinks you are trying to reference a different instead of looking for ...
|
| Is it possible to subtract 1 from every value? | 5/22/2007 |
Q: Is it possible to subtract 1 from every value on a worksheet, the reason being, we are exporting ...
A: Yes! Enter 1 in a cell and then copy it. Select the cells you want to subtract 1 from. Now select ...
|
| subtracting contents of a column | 4/30/2007 |
Q: I have very little excel knowledge, and I was wondering if it were possible to create a formula that ...
A: Do you mean subtract the total of the numbers in a column from a cell? (I think so.) If so, the ...
|
| question about excel | 4/30/2007 |
Q: Smith, I have a question about excel. I realize it may seem simple, but I honestly can not find ...
A: Here's a link to Microsoft's site that lists all the chart types and their details: ...
|
| Task List.... Our way please | 4/28/2007 |
Q: Greetings Douglas M. Smith, and thank you for making yourself available for questions such as this. ...
A: Sorry about the delay. I read your question too quickly and I didn't see the new question. Give ...
|
| Task List.... Our way please | 4/27/2007 |
Q: Greetings Douglas M. Smith, and thank you for making yourself available for questions such as this. ...
A: Yes, this can be accomplished with conditional formatting, but with multiple conditions, the order ...
|
| Formula Dilema | 4/11/2007 |
Q: I have a formula that has quite a complex matrix. Are you aware of a way to make the ...
A: There is a neat function called INDIRECT that will convert strings into a reference. If your sheet ...
|
| sorting columns in excel | 4/10/2007 |
Q: We have 3,000 lines of information in an excel spreadsheet and we would like to sort it by zipcodes ...
A: Yes, you should be able to sort on the zip codes. Select a cell with a zip code in it and click the ...
|
| formula | 4/10/2007 |
Q: Can you show the number value of each cell in a formula? Currently it will show cell number. ...
A: Yes, this can be done, sort of. Select the A3:A8 reference in the formula and press the F9 function ...
|
| email a sheet in a workbook | 3/29/2007 |
Q: How do you email just one sheet out of the workbook? I want the other person to be able to open the ...
A: The only options are to email the entire workbook, from the menu: File / Send to / Mail Recipient ...
|
| round up to $0.99 | 3/27/2007 |
Q: I am creating a spreadsheet for a business and i need all of my products to round up to 0.99. For ...
A: ROUNDUP will roud a number up to a specified number of decimal places. Using it with a little math ...
|
| Excel help with VB/Macros | 3/15/2007 |
Q: I am trying to write this macro for work in Excel 2003. The setup is a master drawing register ...
A: There are some options that will make your programming a little easier. If you use the check box ...
|
| Excel | 3/14/2007 |
Q: I am trying to find out how to make calculations under certain conditions. For example, lets say ...
A: The problem with using VLOOKUP is that is only pulls the first result it finds. There is a ...
|
| Static a formula cell | 3/14/2007 |
Q: Good day and I need your guide, STANDARD COPYS the formula ========================== Original ...
A: Your formula would be: =A1+$B$1 The dollar signs turn B1, a relative reference (changes when you ...
|
| Shading cells | 3/10/2007 |
Q: Downloaded a billing statement from microsofts website. It appears to have a two colored shaded ...
A: This sounds like a Conditional Formatting trick to me. Select a cell and then select "Conditional ...
|
| Making table | 2/23/2007 |
Q: I am trying to make a tabele like below Table 2 by using data table like Table 1. I tried to put ...
A: I don't see that this is possible using only formulas, however, there are other ways to do it. Copy ...
|
| FORMULA | 2/22/2007 |
Q: Douglas I have a dtabase containing supplier and order information in one of the columns i,ve ...
A: An array formula will be needed to retrieve this information. The basic formula would be: ...
|
| Extracting data using a dropdown curtain option | 2/21/2007 |
Q: I work for a shipping company and would like to know how to be able to select a client in a dropdown ...
A: I'm assuming you have your client data in a list format. You can use Data Validation to set up and ...
|
| In a HR Report Can't get to calculate from today to a date in the future. | 2/19/2007 |
Q: Don't want to push my luck but your last formula was just perfect. The final thing to finish my ...
A: Years / Months / Days: =DATEDIF(D4,H4,"y") & " years, " & DATEDIF(D4,H4,"ym") & " months, " & ...
|
| In a HR Report Can't get to calculate from today to a date in the future. | 2/18/2007 |
Q: Here is my scenario. I am building a HR plan for my employees. I have this coding that works from ...
A: The DATEDIF function subtracts first date from the second date so if move the NOW() function to the ...
|
| excel | 2/17/2007 |
Q: Doug, I am putting together a grading report for my students' progress. I am using a grading scale ...
A: Yes it makes sense AND it can be done. In the Condition Formatting dialog box, the first item is a ...
|
| Values in a cell | 2/16/2007 |
Q: I enter a value X in a cell A1. In the cell A2, I want to display the content of the cell HX where ...
A: The INDIRECT function will do this for you. It allows to assemble a reference as text and then ...
|
| merging two files & removing duplicates | 2/15/2007 |
Q: Is there a function within EXCEL that I can use to remove duplicate rows within a spreadsheet after ...
A: Excel used to have a "Data Delete" menu item but that went away in the mid-90's for some reason. ...
|
| Multiple currency conversion | 2/15/2007 |
Q: I only have a very basic understanding of excel :) I have a list of 440 numbers in column A1. They ...
A: G'day Scott, Your question brings back memories from my days in Australia. I lived in Perth when ...
|
| no duplication of numbers | 2/11/2007 |
Q: Mr. Smith, When entering numbers in a column how can I make sure no number has been duplicated ...
A: This can be done with Data Validation. Select the cells you will be entering the numbers into ...
|
| Putting information on two worksheets at a time | 2/9/2007 |
Q: I have to do expenses and want the data to trans. on two sheets. Example John Smith on the first ...
A: You can activiate more than one sheet at a time by holding down the Control key and clicking on ...
|
| Excel timecard - track type of hours | 2/8/2007 |
Q: My question seems to be similar to the {=SUM(IF... questions. The data is laid out by months, the ...
A: COUNTIF has a buddy named SUMIF that should be what you are looking for. It's structure is: ...
|
| Excel 2000 | 2/7/2007 |
Q: I'm trying to create a Macro that will unprotect all password protected worksheets (without ...
A: Here's a routine that will loop through all the sheets in the active workbook and unprotect them, ...
|
| formula help | 2/6/2007 |
Q: what the formula is meant to be doing is if the value is smaller or equal to the offset value (which ...
A: It looks like you have a right parenthesis in the wrong place. It's grabbing the true/false results ...
|
| Row number | 1/28/2007 |
Q: Now I'm stuck because I can't use the row number to manipulate a range of cells i.e. I know that the ...
A: References in the Range function are constructed like text strings and Range then converts it to an ...
|
| Row number | 1/27/2007 |
Q: In Excel VBA, I use Range("a65536").End(xlUp).Select" to find the last row containing data. How can ...
A: This line will get the row number of the last data row after you use your code ...
|
| Text and Numbers | 1/26/2007 |
Q: Is there any way to combine text and numbers together and still be able to calculate the numbers? ...
A: No, combining numbers into text turns them into text. If you want to then do math with them, your ...
|
| If / multiple condition formula | 1/24/2007 |
Q: now lets go anothere step further. Instead of returning "no bob" if those conditions aren't met, I ...
A: Basically, you would just copy the above formula (without the = sign) and paste it back into the ...
|
| lookup and if statements | 1/23/2007 |
Q: I have ratings and ranges. For example, I have ratings 1 through 7 and for each of these ratings I ...
A: Your situation has 28 (7 ratings x 4 ranges) so a standalone formula is going to be a problem. Your ...
|
| Color fill in cell, what formula? | 1/22/2007 |
Q: Hey Douglas, Well i have a sheet where random cells are filled with BLUE color. Now i need to change ...
A: Unfortunately, there's no formula read or set cells colors directly so formulas and conditional ...
|
| Bringing a PDF file into Excel | 1/22/2007 |
Q: I have a .pdf file that I need to import into Excel. How can I do this?
A: It depends on the protection scheme used on the PDF file. On some, you can select & copy or drag & ...
|
| Excel | 1/18/2007 |
Q: How may I delete a file name from the Excel "File" listing, please ? I have deleted the file ...
A: The list of files is the most recent ones that you've opened, regardless of what's happened to them ...
|
| work problem | 12/29/2006 |
Q: I am trying to tune up this formula - =IF(K1091>=0.251,"HIGH ",IF(K1091<=-0.251,"LOW ","OK")). We ...
A: I suspect the problem lies with the value in the cell. The only way I could get 0.00 to show a ...
|
| convert to mph( miles per hour) | 12/27/2006 |
Q: How do I calculate a formula to work out miles per hour some one runs at. for example if it takes ...
A: The trick to doing conversions is to lay out the interim steps and rates. In this case, you are ...
|
| Spaces at the end | 12/27/2006 |
Q: I am having an issue...i am converting some information on telephone company directory. At the end ...
A: There are several ways to deal with this, depending your situation. I'll go through them all for ...
|
| if OR/AND syntax question | 12/12/2006 |
Q: I am attempting to write a nested if statement, but it needs to read in non-excel syntax "if ...
A: Give this a try: =IF(AND(OR(B9=cond1,B9=cond2,B9=cond3),OR(C9=cond1,C9=cond2)),X,0) The B9 and C9 ...
|
| Excel Formula | 12/12/2006 |
Q: I'm attempting a type of SUMIF or IF function. A1 = 2 B1 = C There are three ranges of data ...
A: SUMIF will only handle a single criterion, so a more complicated formula will be needed: ...
|
| Excel IF Statement | 12/11/2006 |
Q: hope you can help me. I am developing an Excel spreadsheet to track progress on projects based on ...
A: The cell D10 formula should be: =IF(D22>TODAY()),D22,TODAY()) In the formula, D10=D22 does not ...
|
| IF Statement Formula in Excel | 12/11/2006 |
Q: I can not get this formula to work- it keeps bringing back an invalid error. It must be something ...
A: Formulas need to have the same number of left and right parentheses, but the formula has 10 left and ...
|
| Advanced ffilter | 12/9/2006 |
Q: I have 2 list 1st one is the stock and the 2nd one is issue when i need the new stock i was made ...
A: You will need to to run the Advanced Filter from the 2nd sheet, referencing the list range on the ...
|
| DIV/0 problems | 11/25/2006 |
Q: I am trying to build a spreadsheet to track and trend recovered mean values. It take me hours right ...
A: There are ways to test for conditions that lead to a divide by zero error. One is to check the ...
|
| How to avoid error | 11/24/2006 |
Q: Let us assume there is cell a1,b1,c1 the formula is in c1. If there is no value in cell A1and B1 the ...
A: You will need to test if cells A1 or B1 are blank in the C1 formula. Something like this: ...
|
| Copy Sheet Tab label to cell | 11/22/2006 |
Q: How can I get the Sheet Tab name into any cell. ( had come across this formula at a time when I did ...
A: I recently answered a very similarhttp://experts.about.com/q/Excel-1059/sheet-name.htm Let me know ...
|
| Excel Help | 11/20/2006 |
Q: I'm an excel novice. I managed to set up a table that shows me credit line balances when you insert ...
A: You can do this with the MAX function: =MAX(your_formula,0) This will return the larger of the ...
|
| Copying excel values | 11/19/2006 |
Q: I'm very pleased to see that you are available just at the time I need your assistance again: How ...
A: You can use Paste Special to do that. Copy the cell, select the paste cell, select Paste Special ...
|
| Convert to time | 11/15/2006 |
Q: I have a time table that is not in time format, it is just numbers 0900, 1000, 1115, etc. I need to ...
A: There's no quicker way to need a long cumbersome formula than to try and do time math in a non-time ...
|
| Feed | 11/14/2006 |
Q: I have a name on spreadsheet 1 and the same name possible on spreadsheet 2. If there is a match I ...
A: I'm assuming you have a table of data of sheet 2 and you want to read data to sheet 1 if there's ...
|
| ISBlank | 11/7/2006 |
Q: I work for a Fire Dept. and in addition to many items we are tracking Blood Pressue is one of them. ...
A: Before I spend any time digging into your beefy formula, let me throw out an alternative that might ...
|
| Formula Question | 11/6/2006 |
Q: I have to calculate what bonus payout is based on actual performance and certain threshholds based ...
A: This can be done with a look up table. First construct a table like this, for example, in cell ...
|
| Lookup problem | 11/6/2006 |
Q: I am trying to fill a form on sheet1 from data on Ranges (sheet2 renamed). The form has a drop dn ...
A: This is doable, but I'm not sure which kind of dropdown box you are using. Is it embedded in a cell ...
|
| Gross Profit Formula | 11/3/2006 |
Q: I am working on turn a $$GP amount into a %GP. exp: i format the cell as a % sales = $55.13 credits ...
A: If you put a minus after the equal sign, that may solve the problem in the formula. Another ...
|
| Change font colour in one column depending on value in different column | 11/3/2006 |
Q: I want to change the font colour in Range A1:A10 to Red when the date is over 30 days old but only ...
A: In the Conditional Format dialog box, select the "Formula Is" option and enter the following ...
|
| Formular to count number of entries in a row | 10/31/2006 |
Q: How do i get excel to count number of entries NOT TOTAL OR SUM eg A B C D E ...
A: To count cells with just numbers, the COUNT function will work. To count cell with both numbers and ...
|
| How to interpret a "#N/A" result from another cell | 10/29/2006 |
Q: I have a formula in the cell A1 which can sometimes show the value of "#N/A" (not available). Then I ...
A: An error message can't be read as text by Excel, but error messages themselves can be read. The ...
|
| Random Numbers | 10/23/2006 |
Q: Mr. Smith, I was wondering if there was a way to generate random numbers based on the probability of ...
A: For the situation you described, you can use the RANDBETWEEN function and copy it down: ...
|
| Entering days of the week | 10/21/2006 |
Q: I would like to have Monday on the first sheet of a workbook and Tuesday, Wednesday . . . in the ...
A: Going across multiple sheets, there isn't a real slick way to do this. You can type a Monday date, ...
|
| excel limits | 10/17/2006 |
Q: We currently use an excel file that has over 1000 lines of data and lots of formulas in it ...
A: Excel's limitations aren't the problem in your situation. I've created spreadsheets many times the ...
|
| i have names entered in excel | 10/17/2006 |
Q: i have names entered in excel like this marv alpert 1 main street Somewhere, NY 10577 but i want to ...
A: Yes, this can be done without a macro, if we do a little set up first. I'll assume the addresses ...
|
| Rounding up to cero | 10/16/2006 |
Q: I was hoping maybe you can help too. I am pasting a series of number in a cell (Actually all cells ...
A: It sounds like a number format issue. It would be easier if I could see the file to make sure. If ...
|
| formula | 10/16/2006 |
Q: USA I have a column for budgeted dollars next column for actual dollars used do i need to columns ...
A: If I read your question correctly, the third column reult would be: 50% under. Correct? The ...
|
| Deciding on 1st place 2nd place etc | 10/12/2006 |
Q: I work with Army Cadets in Canada, and we hold annual challange competitions. I store all the data ...
A: Yes, Excel has a function for this, appropriately enough for Army use, called RANK. If your results ...
|
| Compare two excel sheets | 10/12/2006 |
Q: Can we compare two sheets in excel? I have searched on net.. there are differnt softwares available. ...
A: Early versions of Excel had this feature but it was removed in version 4 or 5 in the mid-1990's. ...
|
| Excel 2003 Question | 9/29/2006 |
Q: That makes sense, but what if the current balance isn't in the same cell on each sheet? My wife ...
A: I have a saying, "an ounce of structure prevents a ton of programming". If that cell is going to ...
|
| Excel 2003 Question | 9/28/2006 |
Q: I'm creating a bill tracking sheet for my personal bills. I have 12 worksheets in the workbook, one ...
A: A simple way to this is to have a cell on your totals sheet where you type in the current month's ...
|
| EXCEL FORMULA | 9/28/2006 |
Q: I,m trying to create a formula that works between two spreadsheets in the same workbook. The formula ...
A: Try this formula on sheet 1: =ROWS(Sheet2!$C$1:$C$30)-COUNTBLANK(Sheet2!$C$1:$C$30) Change ...
|
| Dynamic Hyperlinks | 9/27/2006 |
Q: I cell hyperlinked to the bottom on a ledger (i.e. I62). When that ledger is full, i copy & paste ...
A: I got so wrapped up in the hyperlink solution, I forgot about a simple shortcut to take you the last ...
|
| Combining Columns | 9/27/2006 |
Q: My name is Dave and I'm in the USA. I have a five column spreadsheet with approximately 125000 ...
A: I've set up a solution for you that uses a crossreference table: ...
|
| Excel - concantenate | 9/25/2006 |
Q: United States I have a list of students in the format Last name, and then first name in the next ...
A: The command you want instead of Paste is "Paste Special", located on the Edit menu. It might not be ...
|
| Excel formula, USA | 9/21/2006 |
Q: I want a formula that will do the following: Sum two or more cells, and If the total falls between a ...
A: Yes it's possible, thanks to the VLOOKUP function. First, you need to build a table containing the ...
|
| Excel 2002 - Formula Audit | 9/11/2006 |
Q: I have an Excel 2002 spreadsheet that I want to view all of the formulas in an "empty" section of ...
A: If you just want to see the formulas themselves, hold down the Control key and hit the ` key (it's ...
|
| Droplists within formula | 9/1/2006 |
Q: How do I use a drop list within a formula? Ex: =IF(B1=1,Droplist1,IF(B1=2,Droplist2),””)) ...
A: You can't use data validation in a formula, however, you can use a formula in data validation. ...
|
| Formula | 8/30/2006 |
Q: USA - I work for a trucking company - we do a spread sheet monthly for the top 9 drivers according ...
A: Sorry about the delay. The RANK function should do what you want. If your miles are in cells ...
|
| custom cell format | 8/29/2006 |
Q: I currently have the following custom format set: "ME-06-"0 Where ME= prefix for a procedure we ...
A: To do everything you wanted, it would take a tricky macro. However, for a little extra work, it can ...
|
| Converting data in a large spreadsheet | 8/29/2006 |
Q: I have a large dataset for a bird study. So, for lets say all geese, ducks, etc.. listed in the ...
A: You do this with VLOOKUP. If your table is in cells G1:H500 (sorted by bird species), and list of ...
|
| how to compare the contents of 2 cells | 8/28/2006 |
Q: Normally, we can compare the contents of two cells with IF, but how to compare following. For ...
A: You will need to cover all the possible letter configurations in the formula. In this case, there ...
|
| Using Excel as a database | 8/27/2006 |
Q: (I'm in the UK, using Excel 2002). Hi, I am using Excel as a database and have run out of available ...
A: Column IV (256) is all you get right now. You will have to wait for Excel version 12 for the limit ...
|
| Time calculations in excel | 8/27/2006 |
Q: Doug, have a start clocktime and processing in minutes I want the target cell to show finish time in ...
A: 1440 is the number of minutes in a day, 24 * 60. Dividing the 135 minutes by 1440 converts it to a ...
|
| excel text boxes | 8/22/2006 |
Q: in my office, we created a form in excel in order to kepp records. there is a header and footer on ...
A: There are two ways to get text to stay within a column. One involves using "wrap text". This wraps ...
|
| excell history | 8/20/2006 |
Q: USA. is there any way to view a history/log of every spreadsheet showing any original information ...
A: Yes, this is possible. Select Tracking under the Tools menu and select you options in the dialog ...
|
| If/lookup? | 8/18/2006 |
Q: I'm trying to lookup a particular value within a range and if it is in that range I want the result ...
A: A look up function (like VLOOKUP) is meant to return a value and further, may return a result even ...
|
| Conditonal formula | 8/16/2006 |
Q: I have the below existing formula in one sheet: =(Sum(H6:L6)+Sum(n6:R6))*d6*16%. I would like to ...
A: I'm not sure I understand exactly what you are trying to do. My guess is you have a cell on the ...
|
| Using LOOKUP function to return date | 8/16/2006 |
Q: I have a spreadsheet detailing company sales with hundreds of rows and several columns. The columns ...
A: It could be a couple of things. LOOKUP requies your data to be in ascending order. If there are ...
|
| Financial Spreadsheet | 8/8/2006 |
Q: I am running a spreadsheet with around 60 rows X 6 columns. I have entered formula into some ...
A: When you copy / paste, the references might end up pointing to different cells or even off the ...
|
| Pick from lists | 8/7/2006 |
Q: I want to enter 240 countries in a drop-down lists, which are not visible in the spreadsheet. But ...
A: You can do this with Data Validation. If the list of countries in on the same worksheet, you can ...
|
| Conditioning Format - different cells | 8/5/2006 |
Q: Can I do conditioning format in Column A if the data in Column B meets certain condition?
A: Yes, this is possible. There are two modes for Conditional Formatting. The default is "Cell Value ...
|
| Area under a graph | 8/3/2006 |
Q: Trinidad. Is there any way that I can use Excel to calculate the area under a chart? I'm talking ...
A: No, you would need to perform that calculation with the data used to generate the chart. Using ...
|
| one formula for a column of random dates | 8/1/2006 |
Q: Is it possible to just use "ONE" formula to add months to a single column of random dates? If so ...
A: There are multiple ways of doing this. Dates are really just integer numbers (example: today, ...
|
| Moving between unlocked cells | 7/31/2006 |
Q: I am using Excel 2000, is there a way to set up a protected sheet so that a user can move between ...
A: That's the normal behavior when protection is turned on, with tabbing to the unlocked cells being ...
|
| Excel File option | 7/29/2006 |
Q: Smith I want to ask you about one Excel file which I received by e-mail, in that Excel file there ...
A: The feature is called "Group and Outline" and it's under the Data menu. It's used to collapse and ...
|
| IF AND OR | 7/20/2006 |
Q: I have 3 numbers in 3 cells (A1 B1 C1). I need to write a formula that will describe one of the two ...
A: A clarification, if you change a sheet name on a sheet using the sheet name formula, recalculating ...
|
| Get sheet name | 7/20/2006 |
Q: Today I have a trouble when I want toget sheet name in Excel. I have 10 sheets with the same layout ...
A: Mailan, A clarification, if you change a sheet name on a sheet using the sheet name formula, ...
|
| Linking across sheets in Excel | 7/18/2006 |
Q: I want to get data in many sheets into summary sheet. What formula I have begin sheet, sheet 1, ...
A: Not all functions in Excel can work with 3D references. Here's an unofficial list of the functions ...
|
| Paste Special - Transpose | 7/18/2006 |
Q: I have a very large database of mailing addresses that unfortunately are all typed in one very long ...
A: Let's say your data starts in cell A1. Put the following formulas in the designated cells: C1: ...
|
| Need help with a formula | 7/16/2006 |
Q: "I need to calculate the apropriate discount using the if function with an absolute reference to the ...
A: The Discount formula would be: =IF(Quantity>1,Unit Price * Quantity * $I$1,0) The formula for ...
|
| Automated Time Stamp | 6/15/2006 |
Q: United States. I keep a phone log of customers who call. The cells breakdown as follows: ...
A: There's a keyboard shortcut that enter the current time in a cell: Control Shift : For ...
|
| finding the value based on | 6/15/2006 |
Q: I want to implement following thing: I have Column A:Project Type User can select the project type ...
A: To limit the column A cells to specific values, you need to first create a list of those values. ...
|
| macro? | 6/12/2006 |
Q: A spreadsheet in sheet 3 of an excel document has certain rows shaded. When I import the document ...
A: Yes, it's going to take a macro to tag which rows are shaded. This one loops through the cells in ...
|
| Drop down lists | 6/12/2006 |
Q: I'm from Sydney, Australia...my question is...I am making up a quoting type speadsheet which has 2 ...
A: Sorry about the delay. If you try to do this with forms or control, it will require some macros. ...
|
| Drop down list | 6/11/2006 |
Q: I am trying to design a form in Excel 2003 for purchase orders. In the column where the “Product ...
A: Give this Data Validation workaround a try: I'll name a cell in the first workbook "ListLink" and ...
|
| Find last filled cell in a column | 6/6/2006 |
Q: How do I find the last filled cell in a column automatically? Once I,ve done that how do I use it in ...
A: This will find the row of the last filled cell in column B: ...
|
| CountA Formula with an indefinite row range? | 6/2/2006 |
Q: ) I'm currently writing up a spreadsheet that will consistently be updated bymyself and others, by ...
A: If you don't anything else on row 5 past column AV, you can just change AV5 to IV5 (or some other ...
|
| Excel Formula | 5/24/2006 |
Q: =SUM(Sheet1:Sheet20!A1) works if I have a set number of worksheets. I add a worksheet to my ...
A: It would take a macro to handle this automatically but I have low-tech option to consider. I have a ...
|
| Address function for cell range | 5/20/2006 |
Q: I know I can use the concatenate function to join 2 address functions and create a cell range (I saw ...
A: You are on the right track and your formula is very close to being correct. The problem the cell ...
|
| keystoke command for 'dragging out' in excel | 5/20/2006 |
Q: I use the 'drag out' feature quite often in excel. Whether it is a date that I am 'dragging out' to ...
A: You have a couple of options: For formulas, select the cell range and then use Control-r to fill ...
|
| Tabulating check box results | 5/10/2006 |
Q: I have designed a quiz in Excel (2002) that I would like to have the students check when they find a ...
A: If you use the check box object from the Forms toolbar, this is easy to set up. (Using the check ...
|
| Value List | 5/9/2006 |
Q: How so I create a valuelist and enter into my spread sheet with the drop down option? I have created ...
A: To add a dropdown list to a cell requires Data Validation. Select the cell(s) you want and the ...
|
| EXCEL 2000 use sumif to get data that did not exactly match the criterion | 5/7/2006 |
Q: ColA ColB ColC 1 Luzon1 Jan 4562 2 Luzon2 Jan 413 3 Luzon3 Feb 12 4 North1 Jan 4654 ...
A: SUMIF only works with one criteria, but you have two, so an array formula would be needed. ...
|
| Excel Formula | 5/4/2006 |
Q: USA I have first and last name in one field separated by a space. Want to move last name to separate ...
A: First select the cells with the names and then select "Text to Columns..." from the Data menu. In ...
|
| rounding | 5/4/2006 |
Q: I use a formula such as =A20*B20 I need the answer to be rounded up to the nearest Multiple of 10. ...
A: The CEILING function should do the trick for you: =(CEILING(A20*B20,10) The 10 tells it to round ...
|
| Count data occurances if range criteria is met | 5/3/2006 |
Q: Followup To Question - United States I need your help; I think I have tried every formula known ...
A: Do you see the 1's in the formula? They are returned if your values are found and stored in an ...
|
| Count data occurances if range criteria is met | 5/3/2006 |
Q: United States I need your help; I think I have tried every formula known to man. I have a two ...
A: You could nest additional tests in to formula like this: ...
|
| Count data occurances if range criteria is met | 5/2/2006 |
Q: United States I need your help; I think I have tried every formula known to man. I have a two ...
A: I'm a little confused on your layout but I've got a formula for Sheet2, column J that you should be ...
|
| Parsing a text string | 5/1/2006 |
Q: A4 C4 The Kinks Kinks, The =MID(C4,FIND(", ",C4,1)+1,LEN(C4)) & " " & ...
A: I am unable to reproduce the carriage issue you described. The problem might be your cell ...
|
| Excel formula for form organization | 4/28/2006 |
Q: I will try to keep this short. I am creating a form for a teaching syllabus at my English academy. ...
A: The formula in cell A1 would be: =INDEX($E$1:$E$20,C1-10,1) Adjust the column E range as needed. ...
|
| Excel date formula | 4/28/2006 |
Q: from USA. I am trying to get a formula to calculate service time. So let's say I use todays date ...
A: You can do math with dates like regular numbers, because they really are regular numbers. Today ...
|
| vlookup | 4/27/2006 |
Q: This is one of the given sample formula that was given to me that I should be using. ...
A: 1) This formula is doing a comparison of two looked up values to see which one to use. The value in ...
|
| Formula help | 4/27/2006 |
Q: USA- Using Micro Excel 2000 I need help calculating a formula. My current cell says 60. I need to ...
A: The formula structure would be: =(60+11)*1.5*1.06 Substitute the cell references for the values to ...
|
| Is there a maximum size of a spreadsheet? I think I might have hit it. | 4/26/2006 |
Q: I'm in the USA. I'm using Excel 2004 version 11.2 on Mac OS X 10.4. I exported a large database ...
A: Yes, you've hit the farthest right column of Excel (for now). IV is the 256th column. You will ...
|
| Formula for cemetery plot layout | 4/23/2006 |
Q: My husband is the sextant for a cemetery in Ct. He has to layout a new section. He needs to convert ...
A: Here's the basic formula to display inches as feet and inches. Two separate operations are ...
|
| Using Wildcard and not equal to when comparing two columns of text | 4/21/2006 |
Q: USA Douglas, I have a worksheet with 9 columns. The second column named PON and 9th column named ...
A: Wildcard characters are mainly for searching and filtering. They also work in functions like ...
|
| round function | 3/31/2006 |
Q: I have a spreadsheet i am working on in excel, and have come accross a problem. I have a column ...
A: Sorry about the delay, I was off-line this weekend. You have a zero for the ROUND argument (last ...
|
| HELP!! | 3/30/2006 |
Q: Douglas, Thanks for this. brilliant, however now where the cells are empty in sheet 1 the following ...
A: I figured you were in England because of the date format. The following formula format can be used ...
|
| Multi-dimensional cell values: using comments | 3/28/2006 |
Q: I'm preparing a timesheet for a team project and I'd need some help on table design. I have ...
A: I'm sorry about the delay. I've been having trouble accessing the AllExperts site. I'm not quite ...
|
| Microsoft Excel | 3/23/2006 |
Q: I have a database that includes first and last names. The first and last names are in the same cell. ...
A: I'm sorry about the delay. Sometimes AllExperts notifications get lost or end up in my spam folder. ...
|
| Douglas,
I'm new at making... | 3/22/2006 |
Q: Douglas, I'm new at making up macros. I just made one up and it runs fine, but I must have messed up ...
A: It sounds like it could be you are trying to call the macro from a file (Personal.xls) that isn't ...
|
| Auto Filling | 3/21/2006 |
Q: If in cell C2, I have 4PM, how can I fill in the cell C3 6PM and in the cell C4 8PM (two hours ...
A: 24 hours in Excel is equivalent to 1, so one hour is 1/24 or 0.416667 and two hours is 2/24 or 1/12 ...
|
| water mark | 3/21/2006 |
Q: I wish to add a water mark as a background to the page (for example: page one, or draft etc). Hoe ...
A: You insert a picture or graphic as a background to a sheet by selecting Sheet from the Format menu ...
|
| sumif formula | 3/11/2006 |
Q: this solution worked very well. Just one more quick question if I may. I want to apply a similar ...
A: For cell A6, use the "Formula Is" option and your formula would be: =AND(M6>=5,M6<=25) The trick ...
|
| sumif formula | 3/10/2006 |
Q: I am trying to write a sumif formula, in excel, to work out this problem: I have a range (G20:G34). ...
A: Try this for counting the numbers: =COUNTIF(G20:G34,">=5")-COUNTIF(G20:G34,">9") This will add ...
|
| How do I delete duplicate listings on my speadsheet | 3/9/2006 |
Q: how do I delete duplicate listings on my speadsheet? I have 16,000 addresses and many appear more ...
A: You can use the Advanced Data Filter to do this. First, select a header cell of your list and then ...
|
| split 2 words in one cell to 2 cells | 3/8/2006 |
Q: I have a first name + family name in colomn A1 (for example, Bill Clinton). I wish to split the two ...
A: Select the cells in column A and select "Text to Columns" from the Data menu. Now select the ...
|
| Date in excel shouldn't change | 3/5/2006 |
Q: How can I enter a date function into a cell in excel that it should enter todays date but shouldn't ...
A: The is a keyboard shortcut to enter today's date, Control ; The date will be static and not ...
|
| LOOKING FOR DATA | 2/22/2006 |
Q: A B C 0 ! 100 200 300 1000 ! 2347 2380 2410 2000 ! 2440 2467 2490 3000 ! 2512 ...
A: If you put 1100 in cell B7, the formula to return 2347 would be: ...
|
| Formula QUestion | 2/20/2006 |
Q: I am using Office and windows XP My question is this We have a list in Column A of events that ...
A: You can do the with conditional formatting. I'm assuming that there will only be one entry per row ...
|
| RANDBETWEEN function | 2/7/2006 |
Q: When I use the RANDBETWEEN function with a number range between 1-50 I get the same numbers twice. ...
A: It could be done with a macro but there may be an alternative technique that could solve the ...
|
| Excel Formula Problem | 1/26/2006 |
Q: Douglas,I often save data to excel spread sheets which may look as follows. MI / B2530/ 2/ 10.75 ...
A: G'day Darren, The SUMIF function will do this for you. This version is for a fixed range of cells: ...
|
| XLS CONVERTING TO CSV | 1/25/2006 |
Q: cANADA I have an excel file one of the columns have pn with leading zeros. I need to make this ...
A: One way to check and see what's actually in the csv file is to right click on the file icon and open ...
|
| follow-up question re: multiple ranges & criteria in formulas | 1/24/2006 |
Q: Doug, Thanks the quick and accurate reply to my last question submitted 1-7-06. Your formulas ...
A: In some cases, formulas like that will work without the zeroes. However, without the zeroes, the ...
|
| Creating a dta tracking in excel 2003 | 1/23/2006 |
Q: i am trying to link an excel form so it will give me a database in excel. The principal is that you ...
A: You can call up a data form by selecting Form from the Data menu. You will need to have a cell in ...
|
| Chart axes | 1/21/2006 |
Q: I'll be surprised if you tell me this is possible, Douglas, but it doesn't hurt to try. I would ...
A: Surprise, it possible! I use it once in a while to show things raw numbers and percentages on the ...
|
| Currency Formula Challenge | 1/14/2006 |
Q: USA - I am planning a trip to the UK this fall. I have started an excel spreadsheet to track cost. I ...
A: Quick answer: $F$1 More detailed answer: Cell references can be relative (F1), absolute ($F$1) or a ...
|
| Formula to calculate vacation - USA | 1/13/2006 |
Q: Doug, I am looking for a formula that can automatically take today's date and my employee's start ...
A: Sorry about the delay. If you build a table with the above values you can retrieve them with ...
|
| IF function | 1/10/2006 |
Q: I really appreciate it. My quandary. I need to write an IF function. I want to have a cell (C1) ...
A: We can accomplish this several ways. (I'm going to shorten your company names to A, B, C, D, E.) ...
|
| range/criteria formula question | 1/7/2006 |
Q: Doug, I'm running Excel 2003 with Windows XP Home. Within a spreadsheet, three of the columns are ...
A: Yes, this is possible and it will open a whole new level of power for you. But first, a little ...
|
| Formulas | 1/7/2006 |
Q: When i copy a formula down by double clicking the cross hair in the right corner of the cell it will ...
A: My first guess is that the calculation mode is set to manual. Press F9 to recalculation. Tools / ...
|
| Compare true and false cells | 1/6/2006 |
Q: Excel 2002 I have to compare two columns of true false results. I want to have the result true ...
A: There's a difference between TRUE (logical value)and "TRUE" (text string). Your formula works for ...
|
| EXCELL | 1/5/2006 |
Q: .. [Microsoft Excel 2000 (9.0.2720) I have always inserted rows by placing cursor in a row, and on ...
A: That error message means that you have data that will be shoved off the bottom of the worksheet. ...
|
| Cascading lists/combo boxes | 1/5/2006 |
Q: Is there a way producing lists or combo boxes in excel that cascade? i.e. choosing 'competitor' ...
A: I don't think this is possible without macros for Forms or ActiveX control objects. Data validation ...
|
| Commission spread sheet | 1/4/2006 |
Q: USA Trying to create a spread sheet that is based on graduated percentages of a monthly dollar ...
A: First, create a table of the numbers: 0 0% 0% 0.0% 20000 18% 3% 1.5% 25000 20% 5% 2.5% ...
|
| Trending | 1/3/2006 |
Q: I'm trying to trend some info. We have a phone system that calls my customers for their scheduled ...
A: If you create a table with the dates in the first column, and the next columns containing the ...
|
| large spreadsheets | 1/1/2006 |
Q: ------------------------- Followup To Thanks for the advice.I have spreadsheets that are about ...
A: Probably not, unless you only have a small amount now and all your other processes (including ...
|
| large spreadsheets | 1/1/2006 |
Q: What can be done to speed up loading and saving large spreadsheets ?
A: Having the latest hardware and Excel version helps. Files also load quicker from local drives ...
|
| Excel 2004 for Macintosh | 12/27/2005 |
Q: Adelaide, Australia Excel in Office 2004 for Macintosh: I want to be able to add entries in a ...
A: I'm a little confused about your specific set up but I can throw out general information about date ...
|
| Formulas | 12/26/2005 |
Q: I like to get up at full speed in the shortest time . I understand the basics of excel but rather ...
A: I would recommend you focus on functions, formulas, and non-macro features (conditional formating, ...
|
| chart transfer-excel to word | 12/25/2005 |
Q: Could you please suggest a WORD expert whom i cud ask this?, i see no word expert on the ...
A: The Word experts are listed here: http://www.allexperts.com/getExpert.asp?Category=1058 I would ...
|
| chart transfer-excel to word | 12/23/2005 |
Q: I am writing from Russia. I have a P-3 and i am working on my PhD thesis. From my data i have ...
A: I've never heard of this before. I would make sure the file was pasted in Word as a picture and not ...
|
| conditional formating | 12/22/2005 |
Q: uk. hi. i am using office 97, the cell in the programe i am writing will light up 3 years from the ...
A: You can do date math just like numbers. If you subtract 30 from the TODAY() result, that should ...
|
| Excel Macros | 12/22/2005 |
Q: UK, Excel 2000 SP3 I have created a spreadsheet that imports data from a SQl dbase, creates various ...
A: With all the data you have coming and going, it might not be the macro code bloat (more on that in a ...
|
| Automatic entry of an "X" with a mouse click | 12/19/2005 |
Q: Perhaps you can solve a computer input mystery. I'm working on a spreadsheet that requires massive ...
A: Start by building a table of the cities and their corresponding regions. Then, you can read this ...
|
| Follow up to Dates | 12/17/2005 |
Q: I thought it might be helpful to tell you what I'm doing. I actually have a column of dates in text ...
A: You can use the MONTH function on a date to return the month number (1-12). Likewise, there is also ...
|
| excel formula problem | 12/16/2005 |
Q: that was really helpful. My last problem is the following: With the same data (two columns, one ...
A: Sorry about the delay. You can use SUMIF and COUNTIF to do this: ...
|
| excel formula problem | 12/15/2005 |
Q: My mistake, very poorly worded question. What I meant was: I am trying to figure out how to find ...
A: I thought your original question might be a little more complicated than it sounded. Try this to ...
|
| Dynamic Range names | 12/13/2005 |
Q: just wondering if you have a way of making dynamic range names so that if someone adds data the ...
A: This defined name formula will work if there are no blank cells within the list of data: ...
|
| Excel find | 12/12/2005 |
Q: however I will need to use vlookup to locate the result in C1, which it currently is not doing. I ...
A: If VLOOKUP isn't working, and assuming you have the correct VLOOKUP options and configuration (like ...
|
| Excel find | 12/12/2005 |
Q: I am combining the data from 2 cells (ie) A1 contains Simon and B1 contains 001 I have made cell C1 ...
A: Find has several options that can set to look for different types of things in a cell. If you look ...
|
| Excel spread sheet | 12/10/2005 |
Q: Ecxel 2000. Hi, I'm a first time user of excel. I'm creating a work book to record spending habits ...
A: To reference a cell on a different sheet, include the sheet name in the reference: =Sheet1!B5 ...
|
| Excel - Office 2000- text to columns without losing leading 0 | 12/8/2005 |
Q: I live in the United States. My question is simple (I think). I am trying to utilize the ...
A: Yes, there is an easy solution to this. On the "Step 3 of 3" dialog box in "Text to Columns", you ...
|
| Automatic time and date stamps | 12/8/2005 |
Q: Is there a way to set up Excel so that every file will automatically print out the date, time, and ...
A: Yes, Excel can do this. Select "Page Setup..." from the File menu. In the dialog box, click the ...
|
| Combining two sets of numbers | 12/7/2005 |
Q: Douglas, I have a need in an excel sheet I use on a daily basis to automate one step. I generate ...
A: 1) To display a number like this, you can format it as a zip code (Format / Cells / Number Tab / ...
|
| Combining two sets of numbers | 12/7/2005 |
Q: Douglas, I have a need in an excel sheet I use on a daily basis to automate one step. I generate ...
A: Here are two ways to join the numbers (same result): =CONCATENATE(A1,B1) or =A1&B1 However, this ...
|
| spaces (trim) | 12/6/2005 |
Q: To go short: I have a number in an Excel cel but in the end is a space, (like '135 '). I need to ...
A: This is probably some other non-printing character. To get rid of it: =LEFT(A1,LEN(A1)-1) LEN ...
|
| Dates in cells | 12/1/2005 |
Q: Dates in cells have turned into serial format!How do i change the relevant cells back to represent ...
A: In Excel, dates are numbers. They start at January 1, 1900 (1) and today, December 1, 2005 is ...
|
| Pasting into excel => Easy one (I think) | 11/30/2005 |
Q: When I copy something with a comma or colon in the range (eg SDNM O/N :1318780) excell has suddenly ...
A: This sounds like the work of the "Text To Columns" feature. Find an out of the way cell and enter a ...
|
| URL in cell from another worksheet | 11/29/2005 |
Q: =VLOOKUP(A6,Resources!$A$2:$B$200,2) This little formula puts a URL into a cell in the main ...
A: The hyperlink is not going to automatically be set by the formula. You will need a macro to make it ...
|
| When I type in numbers (ex. 7/3) it enters into excel as a date. How do I change that? I only want the numbers. | 11/27/2005 |
Q: I am a teacher in SC, USA. We are working on a spreadsheet to enter test results. However when we ...
A: Yes, that's Excel guessing what you want to do. If you format the cells as Numbers, say 0.00 ...
|
| Copying & Transposing Formulas | 11/10/2005 |
Q: I am trying to copy the formulas in a row into the columns of another worksheet without retyping ...
A: The reference A$1 freezes the row number. If you copy it down, it stays A$1. Copying it across ...
|
| Merge records | 11/8/2005 |
Q: I am using MS Office XP I hv two questions (1) I need to import near about 100000 records from MS ...
A: (1) To import that many rows into Excel and split it between multiple worksheets will generally ...
|
| functions | 11/6/2005 |
Q: Okay, here is what I am trying to do. I have a bunch of regions in column A and I have percentages ...
A: The following formula will do what you want: =SUM(IF($A$1:$A$5="USA",IF($B$1:$B$5=20,1,0),0)) but ...
|
| Excel extracting | 11/6/2005 |
Q: Could you tell me how to extract a number from a bunch of text in excel. Looking for a formula to ...
A: There are a number of methods for doing this. Here are a couple of them: String functions like ...
|
| Extract Data from one sheet to another | 11/5/2005 |
Q: I have a spreadsheet with say 6 columns. I want to extract some data from this sheet to another ...
A: As you've discovered, you can't extract data to another sheet, however, there is a workaround. If ...
|
| Employee Time Sheet | 10/30/2005 |
Q: I am using Excel 2003, I have made a time sheet using Excel and what my problem is, is I want to ...
A: Generally, the way you handle a situation like this would be with MAX and MIN functions. Example: ...
|
| SUM does not work | 10/28/2005 |
Q: i'm in the usa. work for ford in dearborn, mi. i am using microsoft excel 2000 (9.0.7616 SP-3) on ...
A: Sorry about the delay! The problem was caused if there was a value in column F but no corresponding ...
|
| Daily Interest Calculation | 10/27/2005 |
Q: I've got a friend who has lent money to his church to build a sanctuary. He did not lend them a huge ...
A: I don't have handy to do this but there lots places on the interest to get the information you need. ...
|
| RELATING TO COMBO BOXES IN EXCEL | 10/25/2005 |
Q: I have already set up two Data validation boxes which works perfect, that is, in the first box I ...
A: There is a complex solution for this problem. You will need a macro that triggers on the dropdown ...
|
| Two functions in a single spreadsheet | 10/20/2005 |
Q: Country...Canada, City of Calgary, AB Douglas, I'm working with a spreadsheet for a short term ...
A: This cannot be done directly as the totals can't "remember" what was erased. However, there are a ...
|
| EXCEL BARCODE | 10/20/2005 |
Q: I add 3 of 9 barcode to some of my spread sheets to allow the use of a wedge (keyboard stlye ...
A: I have some experience with barcode scanners and the ones I worked with had settings that would ...
|
| List Box VB | 10/19/2005 |
Q: I wish to create a list box that can have multiple values selected and then copied into another ...
A: This can only be accomplished using a List Box control (on the Control Toolbox toolbar) and it ...
|
| Excel Formula | 10/18/2005 |
Q: I'm in the US to answer your question. Now for mine. I have an excel data base that lists ...
A: You count the number in the ranges by counting all the ages from the top of the range on down and ...
|
| Excel formula | 10/17/2005 |
Q: How are you going? Do you know a formula in Excel that enable us to filter out stray values for my ...
A: You have the right idea, but we need to tweak the syntax a little: ...
|
| Conditional Formatting | 10/4/2005 |
Q: Using MS Excel for Windows versions 2000 & 2003. I was attempting to create a condition of turning ...
A: To get the entire row to change color, you will need to apply that conditional format to all the ...
|
| Pivot Table Funkiness | 9/30/2005 |
Q: U.S.A. I'm running Excel 2003. I have a pivot table with week ending dates across the top. It's ...
A: I don't work with pivot tables much but I suspect it's a data issue, with mal-sorting dates actually ...
|
| EXCEL HELP !!!!!!! | 9/28/2005 |
Q: (Canada) Hi Douglas, I found you just by googling and it seems you may be able to help me. I have ...
A: There's no way to directly count cells based on their color. It would require a macro or a ...
|
| Nested IF functions | 9/27/2005 |
Q: I have a table which contains data scored by a sports team. Imagine Column C contains the name, ...
A: As you're discovered, you can only nest seven IF statements, however, since you have your data in a ...
|
| SUMIF Function 2 Criteria | 9/26/2005 |
Q: Excel 2003 Windows XP Hi Douglas, I use the SUMIF function in the normal way to add up values in ...
A: SUMIF can only handle one criterion. However, an array formula can it. (SUMIF is really an array ...
|
| Lookup colum information | 9/14/2005 |
Q: (USA) Hello, Situation: I want to look at a table with dates across the top and a colum of ...
A: Sorry about the delay but your question snuck in after I had gone "on vacation" on the board. If ...
|
| Date reversal | 9/13/2005 |
Q: I have a sheet where the date is entered as yy/mm/dd and I have tried several things to get the date ...
A: Even if the cell is formatted yy/mm/dd, you still enter it as m/d/y. This order is defined by the ...
|
| =countif formula | 9/13/2005 |
Q: I want to count values within a range but with some conditions. conditions are like follows: ...
A: COUNTIF won't handle this situation, however, if I read your problem correctly, the following ...
|
| Cumulative Total | 9/12/2005 |
Q: Followup To Question - (Country: USA)- A4 is the current (Today's) total. B4 is the MTD total. ...
A: Any solution that involves overwriting the daily number will have the same pitfalls you've already ...
|
| Cumulative Total | 9/9/2005 |
Q: (Country: USA)- A4 is the current (Today's) total. B4 is the MTD total. How can I get B4 to keep ...
A: You could put the monthly total formula in B4: =SUM(C4:AG4) Where C4 is the data for the first day ...
|
| Cumulative Total | 9/8/2005 |
Q: (Country: USA)- A4 is the current (Today's) total. B4 is the MTD total. How can I get B4 to keep ...
A: I'm assuming you plug in a new number into A4 each day, replacing the previous day's number, and you ...
|
| Dates for Every Sunday, Tuesday and Thursday | 9/7/2005 |
Q: I want to make a spreadsheet Top row: the day of the week for every Sunday, Tuesday and Thursday ...
A: The easiest way to do this is enter the first Sunday, Tuesday and Thursday dates in the first three ...
|
| Using Formulas From multiple sheets | 9/7/2005 |
Q: United States Excel 2000 Trying to Refrence a formula on sheet 1 cell A3 in Sheet 2 A6. I need it in ...
A: Directly copying a formula between sheets will only copy the formula. It won't bring specific sheet ...
|
| Dates and formulas | 9/6/2005 |
Q: it's all great apart from D1 which for some reason has a complete random number in it. I followed ...
A: I tested my formula on a range without blank cells. Dates are just integer numbers formatted as ...
|
| Countif | 8/31/2005 |
Q: 1) Excel2003 2)I have in a colum a hugh list of documents tagged by letters some are "G" some are ...
A: You have the right idea but COUNTIF will only handle a single criterion. To handle two (or more) ...
|
| FORMULA in conditional formatting... | 8/19/2005 |
Q: .. I have price data in rows like this (each price takes up a cell): 7 6.75 6.50 2.2 5.43 6 ...
A: This is a very interesting problem. I've put more time into it than I usually do on a question. ...
|
| Addressing '99999' values | 8/16/2005 |
Q: My problem is that in the dataset, the author has used '99999' to denote a failed measurement, or an ...
A: There's no chart option built-in to Excel that will automatically conditionally handle values except ...
|
| Excel Formula for percentages | 8/15/2005 |
Q: I need a formula when working with two quarters, total amounts in a column, I need to know the ...
A: Let's set cell A1 as the previous quarter total and B1 as the current quarter's total. We need to ...
|
| Macro, Make Chart Object Free from Cell Ref. | 8/11/2005 |
Q: I'm using Excel 2000. On Sheet1 is Chart Object "Chart 1". What macro code will make the ...
A: If all you want to do is to convert the chart data ranges to a data array, there's a quick way to do ...
|
| SUMIF | 8/11/2005 |
Q: On Worksheet 2, I have dates in column A and amounts in Column B. Header is budget items, Rent, ...
A: SUMIF only works with one test condition and way you need to express it is somewhat limited. By ...
|
| date format | 8/10/2005 |
Q: I received an e-mail with an excel attachment that was a DIF and I saved it as an microsoft excel ...
A: Time for a quick lesson in how Excel handles dates. The number 70105 is just that, a number (at ...
|
| Excel Average Question | 8/7/2005 |
Q: I am using Windows XP and MSOffice XP (Excel 2002 SP3). I am trying to get an average of 15 ...
A: I'm sorry I don't have enough time to spend on your interesting problem right now. I'm going to be ...
|
| IF AND statement | 8/5/2005 |
Q: Is there any way that I can use an "IF... AND..." statement? I have an application where I need to ...
A: Yes, you can use them in combination. You can only nest IF statements seven deep, but you can have ...
|
| excel to excel data transfer | 8/4/2005 |
Q: 1) microsfot excel 2002 2) link a data from one book to another by using macros. The 11.xls and the ...
A: Your active directory is probably not the same directory the files are in. (Try to open a file and ...
|
| pmt function | 8/3/2005 |
Q: b d f 3 Principal 25000 4 Interest Rate 10 % ...
A: On a regular calculator, this is a very cumbersome calculation. Here are a couple of links to web ...
|
| Protection | 8/2/2005 |
Q: How do I protect an excel wookbook to prevent other people viewing it. I can protect it from change ...
A: G'day Nola, You can protect your file by requiring a password to open the file. To do this, call ...
|
| IF+ AND + MAX | 8/1/2005 |
Q: Here is my question - What is the exact formula for: IF A1 is greater than D1, AND B1 is smaller ...
A: The formula would be: =IF(AND(A1>D1,B1<C1),MIN(E1-F1,G1),???) You didn't specify what the result ...
|
| displaying two digits when applying percentage formula | 7/19/2005 |
Q: I am using Excel 2000 My problem is I am trying to display the results of the following formula in ...
A: I'm a little confused. Was your formula supposed to be: =(680*100)/850 If so, here's the ...
|
| Need help for LOOKUP Dates | 7/17/2005 |
Q: I have list of dates without holidays like following 1-Oct-04 4-Oct-04 5-Oct-04 6-Oct-04 ...
A: The combination of MATCH and INDEX functions is better suited for this: =MATCH(lookupdate,lookup ...
|
| linking excel files | 7/15/2005 |
Q: I have an Excel file on a mapped drive (G) that I would like to link to a copy on my C drive so I ...
A: This operation is going to require a macro. Something like: Sub SaveBackup() BackupDir = ...
|
| Currency calculation problem <> $ and £ | 7/14/2005 |
Q: At the risk of sounding dumb, I don't really understand the point. I used a MsgBox to confirm the ...
A: You can send Excel suggestions to Microsoft via email: xlwish@micrsoft.com ...
|
| Currency calculation problem <> $ and £ | 7/13/2005 |
Q: ' Premises: ' ' Column 5 / Row 3 contains the Starting Budget in US Dollars - The only ...
A: I don't have time to dig through the macro code but I can give you some information on how Excel is ...
|
| Format Cells | 7/4/2005 |
Q: Where I work we have a bonus structure in place as follows: Bonus Structure: # Saves Payout ...
A: If you rework your Saves/Payout list, you will be able to read it with a VLOOKUP formula. 0 0 ...
|
| Separate Windows | 7/4/2005 |
Q: How do I open different excel spreadsheets in separate windows on Internet Explorer 6 like I can do ...
A: Normally, when you click on a web page link, it will open the link in the existing window unless the ...
|
| Excell -- null value | 7/1/2005 |
Q: I'm using Excel 2003. I'm developing a spreadsheet that others will be putting data into, and I ...
A: The ISBLANK function may be what you are looking for. It returns TRUE or FALSE based on whether the ...
|
| Summing Between Worksheets | 6/28/2005 |
Q: Is there a shortcut to sum the same cell in 20 worksheets within the same workbook?
A: Yes there is: =SUM(Sheet1:Sheet20!A1) This will add up cell A1 on sheets Sheet1, Sheet20 and all ...
|
| Data Validation | 6/27/2005 |
Q: My value of C1=A1+B1. I need to monitor that if C1 more than 5,000, a pop-up message will warn user ...
A: Unfortunately, doesn't work on calculated values, only entered values. You can however, flag an ...
|
| EXCEL print area set up | 6/24/2005 |
Q: I have been using excel 2000.In one of my worksheet i have got 8 pages.I want to print Column A and ...
A: First select all of row 1. Now hold down the Control key and select of column A. Under the Insert ...
|
| Excel Formula | 6/23/2005 |
Q: I need to add a column that contains text as well as numbers. Example, entry for cell a2=5, a3=5, ...
A: I'm a little confused. Adding the cells individually: =A2+A3+A4+A5+A6+A7 will cause a #VALUE! ...
|
| Array Formula | 6/22/2005 |
Q: Please help identify the correct array formula that would sum specific numbers in a range that meet ...
A: Here's the standard array formula set up for your problem: ...
|
| CHaracter Count | 6/20/2005 |
Q: How do i find out5 how many characters are in a certain field? I have a list of approximatly 3000 ...
A: You can use the LEN function to count the number of characters in a cell. Example: Cell A1 contains ...
|
| combining choose function with other functions | 6/18/2005 |
Q: I am trying to create a formula that will sum columns based on a date criteria. For example: Col A ...
A: As much as I love to use CHOOSE, it's a bit cumbersome for this task: ...
|
| if, then and ranges | 6/17/2005 |
Q: I am in Australia, working in excel 2000. Our new tax season is fast approaching and I am working ...
A: You've come the right person because I used to write spreadsheet-based income tax softare. Please ...
|
| Excel 2000 | 6/14/2005 |
Q: I am using the basic Average function on a few cells. For example: =Average(C5, F5, H5) However, ...
A: You can use COUNT in an IF statement to check those cells before calculating their average: ...
|
| averaging | 6/14/2005 |
Q: Douglas, You have been a tremendous help literally saving me hours if not days of research. (I ...
A: You can create the equivalent of an an AVERAGEIF function to solve this problem: ...
|
| #DIV/O! | 6/13/2005 |
Q: Doug, Thanks very much for your help on the prior question. It works great. I do have a couple ...
A: Try this for the averaging problem: =IF(COUNT(E46:E49)=0,"",AVERAGE(E46:E49)) To hide a displayed ...
|
| autofill a designated cell with a grade | 6/10/2005 |
Q: A1 1000 Adam CO-100 90 A2 1000 Adam TH-100 95 A3 1000 Adam NT-100 100 A4 1002 Baker CO-100 80 ...
A: Reading the name from student number is pretty straight forward. Getting the grade is a little ...
|
| Cell Color Changes based on condition | 6/9/2005 |
Q: How do I have a cell color change based on a condition. For example ...
A: This is exactly what Conditional Formatting does. Select the cell(s) you want to use and then ...
|
| Formulas | 6/8/2005 |
Q: I would like to figure out how to enter a formula that will assign a number to a value and a cell. ...
A: I'm not totally clear on your situation but I see basically what you are trying to do. You are on ...
|
| Excell spreadsheet | 6/8/2005 |
Q: United States. I am using Microsoft Excel 97 SR-2. I have a multi-worksheet spreadsheet. On one ...
A: This can be accomplished by setting up a lookup table and reading it with the VLOOKUP function. It ...
|
| Excel (Office XP) | 6/7/2005 |
Q: I have found using this formula saves me so much time that I wonder whether it would be worth ...
A: I'm proud of you. Working through the IF statement solution was extremely valuable, especially ...
|
| Moving the contents of a cell or a paragraph to another file(s). | 6/6/2005 |
Q: Hello. I wish that you are doing well. I have the following question in Microsoft World or Excel. I ...
A: You can set up a formula in the "My Documents" file to link to the sentence cells. You will need to ...
|
| Excel (Office XP) | 6/6/2005 |
Q: I have found using this formula saves me so much time that I wonder whether it would be worth ...
A: Bothering me? If I didn't want to be "bothered", I wouldn't be on this board. The Excel community ...
|
| Adding hours over 24 in Excel | 6/4/2005 |
Q: I am a lorry driver and want to work out my "End of daily rest" & "Start of daily rest". I also want ...
A: Time values are really just day fractions. Example: 6:00:00 (6 hours) is really the value 0.25 (6 ...
|
| Excel | 6/4/2005 |
Q: I run a transcription business and use a simple table in Excel (Office XP) to log the work that ...
A: It's only complicated if you don't know how to do it. Fortunately, Excel is well equiped to handle ...
|
| computer shut down, data not saved | 6/3/2005 |
Q: My computer shut down and i was not able to save the data. when i started the computer again, i ...
A: That's strange it didn't give you a recovery option after startup. There is no way to retrieve it ...
|
| Reversing both singular and multiple last name first name seperated with comma | 5/28/2005 |
Q: I have listed below the software I am using, the website, I am exporting the results, and a ...
A: There's nothing more frustrating than working with inconsistent data. Writing a "one-size-fits-all" ...
|
| spread sht for warehouse inventory | 5/22/2005 |
Q: (USA) I'm looking for a spread sheet I can modify to my own needs. The comany I work for have too ...
A: I don't have anything to share that I've developed but if search around the internet, you will ...
|
| Excel doesn't read numbers as numbers... | 5/21/2005 |
Q: I've copied my Verizon cellphone bill to Excel, in order to calculate usege of minutes by each ...
A: Yes, there is an easy to do this. Select the cells and then select Text to Columns under the Data ...
|
| Summary of quantities | 5/20/2005 |
Q: I have another problem that I hope you can assist with: If I have a selection in col B and a ...
A: Sorry about the delay. The bad weather took out my internet connection yesterday. The way I would ...
|
| Excel / Cell Formatting | 5/20/2005 |
Q: I am trying to get Excel to Auto Format a cell based on the value of that cell... For example, if a ...
A: Sorry about the delay. The bad weather took out my internet connection yesterday. Excel can handle ...
|
| INDEX problem | 5/19/2005 |
Q: Is there any way that I can define a range in an INDEX function based on the selection made in ...
A: Once again, you are very close to the solution. The INDEX formula should look something like this: ...
|
| Link mysteriously changed | 5/19/2005 |
Q: May 19, 2005 Dear Sirs, My workers' salaries are calculated on an Excel spreadsheet (Office ...
A: Here's what happened: July65 was open and linked to June65. When you saved June65 as Aug65, the ...
|
| Formula | 5/18/2005 |
Q: I am trying to get the formula that will allow me to join text on a formatted worksheet with the ...
A: It looks like you have the components configured. If you connect them like this: ="LATEST 4 WEEKS ...
|
| ADDRESS of a range | 5/18/2005 |
Q: hope you can assist: Can I use the ADDRESS function to define a range rather than a specific cell ...
A: No, ADDRESS can only be used to reference a single cell. However, you can concatenate a couple of ...
|
| Creating a constant denominator | 4/18/2005 |
Q: U.S.A. In Excel, how do I create a "constant" denominator? For instance, if I have a column of ...
A: The advice you were given was correct, but incomplete. You actually need two dollar signs. Here is ...
|
| Validation in Excel | 4/18/2005 |
Q: I have a column on one worksheet which I want to validate by looking at a list on another. eg List ...
A: Here's a cool trick you can use. First, define a name called "Colors" and instead of having it ...
|
| formula problem in excel | 4/16/2005 |
Q: I am in the US. My problem is in coming up with a formula for hours worked. I have no problem with ...
A: Time values are actually values between zero and one. If you subtract a larger value (11pm = ...
|
| I would like to enter customer... | 4/11/2005 |
Q: I would like to enter customer payments in a spreadsheet and the payments has to be applied to a ...
A: You can do this with a lookup table. Example: List the unit IDs in cells D1:D50 and the customer ...
|
| magic formula involving different "sheets" | 4/8/2005 |
Q: I work for the Forest Service. I have asked everyone around the office for help and have stumped ...
A: The basic syntax you listed will sum up the same range of cells across multiple sheets, however, ...
|
| Remove Hyperlinks in a Range | 4/1/2005 |
Q: Is there a way to remove hyperlinks from a range of about 1,000 entries without having to do it ...
A: You can select the cells, right-click, select HyperLinks, and then click the "Remove Links" button. ...
|
| Not enough memory | 4/1/2005 |
Q: When starting one particular .xls file, which contains macros and is linked to other .xls files, I ...
A: That error message could mean a lot of things as it does not always apply to your RAM. Accordingly, ...
|
| Timesheet Questions | 3/26/2005 |
Q: I'm a private user of Excel and I'm trying to keep track of my hours at work by setting up a ...
A: Time is just a decimal fraction of a day, for example, 6:00am is 0.25. You can work with these ...
|
| excel | 3/19/2005 |
Q: i am quite new with MS excel.I want to learn everythnig about making spreadsheets.So if you can tell ...
A: Here is a link to the Excel Links page of my website: ...
|
| coloms | 3/11/2005 |
Q: I have an excelfile that has 3 coloms and 445 rows of data. I want to split this in 18 coloms and ...
A: You can use the InputBox method to ask for a number to pass to the macro: NumRows = ...
|
| Excel 2000 Password Protection | 3/5/2005 |
Q: I've inherited a simple spreadsheet written by an unknown past employee that is protected by a ...
A: You can try copying the sheet cells to an unprotected sheet. This may or may not get you what you ...
|
| Need formula to track time spent | 3/4/2005 |
Q: USA. I'm keeping a time log spent on my projects. I set up a time column for each project. I need ...
A: It sounds like your spreadsheet is really doing what you want but the following information should ...
|
| Drop down list | 2/21/2005 |
Q: I want to create a drop down list that only allows the user to select one of the items in the list, ...
A: Select the cell(s) you want to apply the list to and select Validation from the Data menu. In the ...
|
| Summing Time | 2/13/2005 |
Q: USA. As EMS Chief for the fire department I am the Quality Improvement coordinator. I use excel ...
A: A time value is really just a number between zero and one. In your case, the subtraction yields a ...
|
| use of the + symbol | 2/12/2005 |
Q: excel 2003 sp1 I have groups of cells into which 1,.5,0,+,- are inputed from a pick list. How can I ...
A: SUM is only going to add up numbers. COUNTIF can handle it though. This formula will tabulate both ...
|
| microsoft excel formula | 2/11/2005 |
Q: USA - Microsoft Excel 2000 I'm trying to create a formula that uses data typed into a cell and finds ...
A: You question pretty much describes what the VLOOKUP function does. =VLOOKUP(lookup value, table ...
|
| excel 2000: how to extract unique values in a column | 2/11/2005 |
Q: I have a large (26,000+ rows) spreadsheet in excel 2000. One of the columns contains names. Each ...
A: You have a number options available to do this. Setting up a pivot table will list both the unique ...
|
| Hello Douglas
What I am... | 1/30/2005 |
Q: What I am trying to do is this. the cell in a1 could be a number between 0 and 28 what I want to do ...
A: The best way to handle this is with a lookup table and to read it with VLOOKUP. Example: put the 0 ...
|
| IF statement question | 1/21/2005 |
Q: =IF((E24-E46)<=5,"OK","ERROR") =IF((E46-E24)<=5,"OK","ERROR") Here is the formula I'm trying to get ...
A: No, but you have the right idea. Try: =IF(OR((E23-E46)<=5,(E46-E24)<=5),"OK","ERROR") If either ...
|
| Count | 1/21/2005 |
Q: Sir, I have the following data: Date Name Count Saturday, January 01, 2005 John Sunday, January ...
A: With more than one condition, you will need to use an array formula. If the date and name to check ...
|
| Adjacent cell entry changes cell formula | 1/7/2005 |
Q: =SUM(T5,U5,V5,W5,X5)-SUM(B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5,N5,O5,P5,R5,S5,Y5) This simple formula ...
A: Off the top of my head, the only thing I can think of that cause something like to happen would a ...
|
| Date computation in Excel to track transaction on day of month | 1/5/2005 |
Q: I have to calculate a date on the fly for each month of the year, but need it to stop counting once ...
A: I'm a little confused about your setup so I'll throw out some general date information. To tabulate ...
|
| Formula for getting values from other sheet | 1/4/2005 |
Q: I am Sanjay from Mumbai, India. The difficulty I am facing while using Excel is as follows : I ...
A: If the part numbers are unique in the lists, MATCH and INDEX can be used. On sheet one, the ...
|
| moving the cursor | 1/4/2005 |
Q: Is there a way to assign a single stroke to move the cursor down one row and to the left three ...
A: Yes, this can be done but you will need a macro to do it. Start to record a macro (Tools / Macro / ...
|
| ? on Formula | 1/3/2005 |
Q: I have a spead sheet that is being recorded in standard time (1.15 = 1 hour & 15 min) so when I add ...
A: This is going to be a little complicated but that's what happens when you mix number formats like ...
|
| Table with two columns and the formula "COUNTIF" | 1/1/2005 |
Q: My question is a simplySupose we have a table with two columns and five rows,with data. The first ...
A: Sorry about the delay. I forgot to set the "on vacation" option. Assume your letters are in column ...
|
| dates | 12/29/2004 |
Q: conditional formatting in xl2000 I'm trying to conditionally format a cell in bold font blue when ...
A: I think the problem lies in how you are entering the formula rather than your logic. Using the ...
|
| Excel Chart | 12/17/2004 |
Q: I have 15 cells in a row that have either "No" or "Yes" written in them. How can I make a bar ...
A: You can't plot the "Yes" cells directly. You will need to have a cell containing the total number ...
|
| Excel Validation | 12/17/2004 |
Q: Using Excel 2000 v9.0 I want to limit the input on a column or cells using “Validation” to either ...
A: The syntax for this (and conditional formatting) is slightly different from cell formula. The ...
|
| Conditional Formatting | 12/12/2004 |
Q: I want to do a conditional formatting for cell M3 in my spreadsheet for due date. E.g. due date is ...
A: The worksheet function TODAY() (no arguments) will return today's date. You can use it in ...
|
| Excel97 Macros | 12/10/2004 |
Q: I've used Excel for years but haven't written macros in it (last time I wrote macros was in Quattro ...
A: VBA has a full compliment of disk/file commands, most of which look like DOS prompt commands, like ...
|
| time entry | 12/3/2004 |
Q: i am trying to convert a cell that has the time in hours and decimals. i want to insert or write a ...
A: Time values are really just a decimal amount from zero to one with time formatting applied to it. ...
|
| excel text import | 11/28/2004 |
Q: I have imported a text to excel. in a single cell are words with spaces between them. I would like ...
A: Yes, TRIM should do what you want. If cell A1 contains a's with three spaces between each letter ...
|
| Cell color calc | 11/21/2004 |
Q: I am using Excel 2000. How do I do a calculation on a range of cells based on the color of the ...
A: You can't directly test a cell's color in Excel. It would require a macro. However, I'm sure ...
|
| Database information | 11/21/2004 |
Q: How do you make the formula fixed when another data has been added to the database between any ...
A: If I understand your question correctly, you have to be very careful when editing linked workbooks. ...
|
| Database information | 11/20/2004 |
Q: I have a database in Excel 2002 file that constantly gets update for additional data and/or for ...
A: Your formulas for cells B2 and B3 would be: =VLOOKUP(B1,[Book1]Sheet1!$A$2:$D$6,2) ...
|
| excel | 11/12/2004 |
Q: i would like to know if there is a way to combine data from a chart in one sheet with a chart in ...
A: Yes, you can combine data from multiple sheets into one chart. One way involves using the Chart ...
|
| Excel to PDF Creates 3 files! WHY? | 11/12/2004 |
Q: I have an Excel spreadsheet with 8 worksheet tabs at the bottom. When I try to print all the ...
A: I'm not a PDF expert, but I was able to duplicate your result (Acrobat v5). I couldn't find ...
|
| Adding cells in different worksheets | 11/6/2004 |
Q: I corrected my spelling (Thanks!). I can get everyone to work except for Los Angeles and San ...
A: Yes, multiple words need to be between single tick marks: 'Los Angeles'!A1 Do a point and click ...
|
| Adding cells in different worksheets | 11/6/2004 |
Q: I am trying to add cells in different worksheets. I am using =Los Angles!B2+San Francisco!B2, etc. ...
A: It sounds like Excel doesn't like a sheet reference (thinks it's another file) and wants you to ...
|
| Excel | 10/29/2004 |
Q: I would like to be able to convert ages of students and their test results into years and months eg ...
A: This can be done but I would advise against using this convention. 7.1 and 7.10 look different but ...
|
| Excel Protection | 10/28/2004 |
Q: Using Excel 2003 on XP Pro. The file in question has 12 tabs, one tab takes specific data and ...
A: Worksheet protection is used to keep users from changing part of the worksheet. Cells can be set as ...
|
| converting column of time | 10/28/2004 |
Q: I have a column of time which users type in "2:00-3:00". The users were lazy to type in the am/pm ...
A: Sorry for the delay, but this is more complicated than I thought. I've put in far more time than I ...
|
| Excel Printing out too many pages. | 10/27/2004 |
Q: Anytime I print out a spreadsheet in Excel it attempts to print out thousands of pages, when the ...
A: A couple of things to check. Press Control-End and see what cell you land in. (This will be the ...
|
| converting and merging fields | 10/21/2004 |
Q: I have 2 columns now in my excel file: 1) Date - data type is Date 2) time - data type is General ...
A: You can use text functions to chop up the date/time text. If you want the results as text, use ...
|
| Consolidating Pivot-tables | 10/20/2004 |
Q: but the range definition is not accepted. Lets say I have a data-table in sheet1 in the area ...
A: I didn't think this was possible but I checked Excel Help and it can be done. On the first Pivot ...
|
| comparing 2 Excel spreadsheets to find missing records in one | 10/19/2004 |
Q: You have been so helpful in the past so I turn to you once again -bless you! I have two office XP ...
A: You can use the MATCH function to cross reference the unique ID numbers and tag the ones that aren't ...
|
| excel2000 | 10/18/2004 |
Q: When I open and close a spreadsheet with some external links without making any changes, I still get ...
A: The only way to do this would be to write a macro to force close the file with the alerts turned ...
|
| formula | 10/18/2004 |
Q: Is it possible to count how many times a coloured number occurs (any number in a green font)in a row ...
A: No, you can't read font properties directly in Excel. It would require a macro to read font colors. ...
|
| selecting the last # in a column >0.00% | 10/17/2004 |
Q: usa in the column each cell contains an average formula. if there is no entries to return an ...
A: This can be accomplished with an array formula that evaluates each cell in the range for it's row ...
|
| Protecting borders and formats only | 10/16/2004 |
Q: I am using Excel in Office 2000 and have very minimal basic knowledge of Excel. I have created a ...
A: This can be done, but not as a drag & drop operation. You copy the cell, then use Paste Special ...
|
| Find and Replace question | 10/15/2004 |
Q: I've spent an hour trying to solve this question on my own and I'm now turning to you (if you can be ...
A: You are on the right track. In the Find/Replace dialog box, click the Options button (Excel 2002, ...
|
| Excel spreadsheet sorting | 9/29/2004 |
Q: from the UK, I have a worksheet with 3 columns. Column A has one of 5 sale types with each type ...
A: You can do this a couple of ways but the easiest would be to create a pivot table. Select the cells ...
|
| Display Message As Alert For Payment Due in Excel 2003 | 9/28/2004 |
Q: It really helps me. However, if I need Excel to prompt me with a pop-up message instead of ...
A: If you want a message to pop up, you will need to write a macro. The macro would need to be ...
|
| page breaks when header changes | 9/27/2004 |
Q: Can you tell me how to make Excel automatically set a new page break when the header changes?
A: The only way I know of to do something like this is with Subtotals (Data menu) applied to a list. ...
|
| Display Message As Alert For Payment Due in Excel 2003 | 9/27/2004 |
Q: I'm preparing a spreadsheet which keeps track all payments due for different products. For example, ...
A: This can be done a couple of ways. Excel has a number of Date and Time functions, one being TODAY. ...
|
| Removing Blank rows or list only rows with 1 or greater in a cell | 9/26/2004 |
Q: I have created a great spreadsheet to allow me to create a good quote. But I have run into a snag I ...
A: If I read your question correctly, you have a list of items on tab "A", you want the list to ...
|
| excel | 9/25/2004 |
Q: USA - column A contains two numbers for every cell. I want to split those numbers so that I have 2 ...
A: Excel has a wonderful feature that can do this for you. Select the number cells and then select ...
|
| delete first empty column | 9/17/2004 |
Q: i have a problem. i import some files to excel97. these files have diferent number of columns i need ...
A: OpenText has an option to take of this in the import process. It happens in the second number of ...
|
| spreadsheet construction help | 9/16/2004 |
Q: Mr. Smith: i have a workbook that i'm trying to construct that compiles the total ...
A: If I read your question correctly, you want to have totals for month1, month1 + month2, month1 + ...
|
| Pop-Up boxes | 9/15/2004 |
Q: Country: USA Version: Excel XP I am working on creating a sales sheet that tracks sales over the ...
A: Yes, several methods are possible but all will require macros, some more than others. I can give ...
|
| Multiple IF statements | 9/15/2004 |
Q: G'day, I'm wanting to create an IF statement with 6 options in a row that is based on what item is ...
A: Excel chokes when you try to nest more than seven IF statements. Fortunately, there's other ways to ...
|
| Formula problem | 9/14/2004 |
Q: I have a formula which is =INDEX(B19:C34,Match(B43,B19:B34,0),2) where I have a lookup table, I ...
A: The best way to trap this is with the ISNA function. It is used to evaluate if there is an #N/A ...
|
| Un apply names | 9/14/2004 |
Q: Once I "Apply Names" to a set of cells, I can no longer copy an paste them to apply to other ranges ...
A: This is a "feature" where sometimes Excel thinks it's smart than we are. I don't think there is a ...
|
| Formulae | 9/13/2004 |
Q: Look, I cannot sink when you have just received a life savers' gold medal in Athens, and you are ...
A: Probably the best way to handle this is with a VBA Do loop. Something like: Do Until Result = ...
|
| Excel Formula - USA | 9/11/2004 |
Q: I use WinXP Pro I have a formula that fills a cell on sheet 7 as that number is entered in ...
A: Copying down will change the row references but not the column references. However, if you change ...
|
| Filters in Excel | 9/11/2004 |
Q: I have a handy dandy little spreadsheet that I've applied filters to but I was wondering if there's ...
A: You can count and total (and nine other things) a filtered list by using the SUBTOTAL function. It ...
|
| Excel - How to access Workbook Properties | 9/10/2004 |
Q: I have a client who asked me HOW to access the info kept in the properties sheet of an Excel ...
A: This information is only accessible through VBA. I've included the VBA help text for ...
|
| Excel Print Preview | 9/9/2004 |
Q: Douglas I am using Excel 2000. Basically I am wanting to print out two pages from two separate ...
A: Wrap the preview command in an IF statement: If ActiveSheet.PrintPreview = False Then End All ...
|
| Command Button | 9/8/2004 |
Q: I have had good success in creating command buttons and writing syntax for them, but I would like to ...
A: Here are a couple of options. This one counts the number of items in column A on Sheet2 to ...
|
| Getting rid of Carriage returns | 9/7/2004 |
Q: I have a client who converted an Access MDB to Excel 97. There a carriage returns within the ...
A: Murray, I'm not sure what point you are at in the process, but here's a few suggestions: I forgot ...
|
| Getting rid of Carriage returns | 9/7/2004 |
Q: I have a client who converted an Access MDB to Excel 97. There a carriage returns within the ...
A: You can use Replace from the Edit menu to do this but there's a trick involved. Unlike Word, which ...
|
| matching two criteria in a range | 8/29/2004 |
Q: I'm using Excel 2000. How to match two criterias (example: outside dia and color code) and find the ...
A: You can do this with an array formula but you will need to fill down the numbers in column so each ...
|
| Writing a formula in Excel | 8/27/2004 |
Q: USA I need to write a formula in Microsoft Excel for Microsoft Office 2000 SR-1 Professional that ...
A: A formula using nested IF statements would be (for a value in cell A1): ...
|
| Excel - looking for help on a formula | 8/27/2004 |
Q: I am working on an Excel spreadsheet for my boss, and believe that the format we have is Microsoft ...
A: You are going to need an array formula to do this calculation. COUNTIF is really an array disguised ...
|
| Date Formatting | 8/26/2004 |
Q: Many thanks - that works a treat! Lastis it possible to use a range of values, so that I can ...
A: You can do things like that: =AND(C4-TODAY()>=0,C4-TODAY()<100) Other logical functions like OR ...
|
| Date Formatting | 8/26/2004 |
Q: I'd like to know what formula to enter in the conditional formating field, to have date fields ...
A: You will need to set up your three "Formula Is" equations like this (C4 is the cell with the ...
|
| Count the Text Data cell | 8/25/2004 |
Q: Mr. Douglas M. Smith I have one query, There is one table containing marks of the subject. The last ...
A: You can use COUNTIF to get the answers. Example: Pass / Fail items are in cells E1:E99: ...
|
| Have a range of figures between... | 8/24/2004 |
Q: Have a range of figures between 0 & 1000 in row A, Require: Any number to round up to the nearest ...
A: Excel has a number of functions to round numbers. ROUNDDOWN should do exactly what you want without ...
|
| keep formula as value in a cell | 8/23/2004 |
Q: From visual basic when a formula is pass to excel I have to write as following .Cells(10, ...
A: Try this: Cells(10, 13).Formula = "=SUM(" & Range(Cells(1, 3), Cells(10, 3)).Address & ")" ...
|
| Excel Spreadsheet | 8/22/2004 |
Q: Could you tell me if there is a way to multiply every entry in a column (in Excel) by, say 120%, in ...
A: Let assume your entries are in column A, starting in cell A1. You can enter a forumula in cell B1: ...
|
| CREATING A FORMULA TO RETURN A NEGATIVE NUMBER OF THE CELL BELOW IT | 8/9/2004 |
Q: I'm in Roanoke, Virginia and using Excel 2000. I have 6 amounts that I have to transfer via online ...
A: I'm a little in the dark about what your spreadsheet looks like so I'm making some educated guesses ...
|
| excell selected rows | 8/9/2004 |
Q: We have 2 sheet in a excel file. There is a total of seven selected rows in sheet1 from sheet2. ...
A: It sounds like you have both sheets selected. If this is the case, anything you do on one sheet ...
|
| Error upon opening an Excel file | 8/8/2004 |
Q: When opening an excel file I get the following remark: 'C:\Program Files\Business ...
A: Look under the Tools menu for "Add-Ins...". There should be an item named something like "Business ...
|
| Excel DSUM | 8/7/2004 |
Q: I use DSUM regularly to sum selective data. However, I don't know how to solve this problem. I ...
A: Here's the first answer I sent. I think my followup erased it: Hi Bob, It's been a while since ...
|
| Sorting or filtering invalid data | 7/31/2004 |
Q: I'm using Excel 2000 to manage a database with about 1400 entries. Two columns, B and C, are ...
A: The easiest way to do this is create a temporary column with a formula that returns a sortable ...
|
| Run Excel macro from a command line? | 7/30/2004 |
Q: Douglas, We run a ton of automated processes via batch file and command line. We run a number of ...
A: I've never fiddled with Excel command line switches before but I did find some information in ...
|
| conditional formatting | 7/30/2004 |
Q: I would like the cell next to a given response to turn either red or green. I would like 7 year old ...
A: This can be accomplished with Conditional Formatting. Assume the result to be tested is in cell A1 ...
|
| Cell color | 7/24/2004 |
Q: Is there a way to know whether the cell is colored or white and to give the result as a number (o or ...
A: There is no direct way read a cell's color. It would require a macro. Something like: Sub ...
|
| Excel - Macros | 7/23/2004 |
Q: Is there a way to copy a macro from one computer to another. I cannot find where macro is stored ...
A: Macros are stored as part of the Excel file they were written in. It is not a separate file. By ...
|
| formatting and toolbox | 7/22/2004 |
Q: Do you have another suggestion for the last line not wrapped around? The row was expanded but the ...
A: Hmmm, that's very strange. The only thing I can think of off the top of my head is that it's a font ...
|
| Rounding in Excel. | 7/22/2004 |
Q: I tried using Paste Special and selected value which worked for most things but it would not work ...
A: You can set a separate format for how the zero value is displayed. Number formats have four ...
|
| formatting and toolbox | 7/21/2004 |
Q: Douglas, I hope you can assist me. I have a cell that has an outline of points a through h, ...
A: A friend of mine (thanks Damon!) gave this link to check out for the toolbar problem: ...
|
| Break Even analysis charts | 7/20/2004 |
Q: Could please give me an example of how you would set up a break even analysis chart using excel ?
A: You will to set a column of inputs and a column of results. For example, cells A1:A10 would contain ...
|
| Worksheet names | 7/20/2004 |
Q: Do you know if there is a way of linking the name of a worksheet to a cell entry. For example if ...
A: There's no way to directly link it but it can be done with a macro: Sub SetSheetName() ...
|
| Rounding in Excel. | 7/19/2004 |
Q: I am trying to round numbers in Excel to one decimal and I want a decimal to be included even with ...
A: This isn't a rounding issue. Saving a spreadsheet in a text format strips out all formatting. You ...
|
| Field filling | 7/19/2004 |
Q: I am runnig Excel version 9.0.3821 I want to auto fill the background pattern on a number of ...
A: Conditional Formatting can be used to change a cell's properties under certain conditions. Select ...
|
| formula to increment cell values | 7/18/2004 |
Q: <from Australia> <Excel 2000 9.0.2720> My spreadsheet is used to track customer shipping data. My ...
A: Writing a macro to this would be pretty tricky but I understand the situation correctly, there are ...
|
| Sum of different values between to dates | 7/16/2004 |
Q: I have a table like this Jan-04 Feb-04 Mar-04 Apr-04 May-04 Product1 30 25 ...
A: I think I have the solution you want: ...
|
| Pasting into hidden cells | 7/15/2004 |
Q: I have one sheet(1) that has been filtered and I want to paste a column from another sheet(2) ...
A: I think you are pretty much stuck with that behavior when pasting to a filtered sheet. You might ...
|
| Sum of different values between to dates | 7/15/2004 |
Q: I have a table like this Jan-04 Feb-04 Mar-04 Apr-04 May-04 Product1 30 25 ...
A: Let's assume your dates are in cells B2:M2 (Jan-Dec) with your products underneath in rows 2, 3 and ...
|
| Excel TODAY formula | 7/14/2004 |
Q: US I know this must be so basic. I am trying to create a row where it is today's date minus the ...
A: Your formula is working but Excel usually assumes that since you are working with dates, you want a ...
|
| Dates not cooperating | 7/13/2004 |
Q: From Massachusetts I am running Excel 2002 and am trying to convert some Web-based tables to usable ...
A: I'm assuming you are using either the file import wizard or text-to-columns to chop up delimited ...
|
| Pivot | 7/12/2004 |
Q: When I use the pivot table to see how many customer bought a product. The product is listed once in ...
A: Pivot tables are more for consolidation in a database-style report format. To get what you want in ...
|
| =now() | 7/3/2004 |
Q: I have used =now() function in a cell to show the current date and system time. However the cell ...
A: It's neither a hardware or software problem. NOW behaves like any other Excel function and only ...
|
| I need locate from a very... | 7/2/2004 |
Q: I need locate from a very large range of dates all dates/people who will have a birthday next week - ...
A: You can use the following formula to mark the rows with a date in the range specified: ...
|
| Changeing colum to rows | 7/1/2004 |
Q: Smith Thank for taking my question! I have a table with no formulas inside just a big table of ...
A: You do this by selecting and copying the cells and then select a cell below these cells where you ...
|
| Sort applicant by job selection | 7/1/2004 |
Q: I have a spreadsheet with a list containing the names of 100 people (in rows). they have applied ...
A: I'm a lttle confused with regard to your data setup. If I could see it, a solution should quickly ...
|
| Business Chart | 6/30/2004 |
Q: I was trying to figure out how to make a chart of the stock market numbers. The things I need to ...
A: Generally, you need to have your data set up in a structured table, with labels. The Chart Wizard ...
|
| Weighted Average | 6/29/2004 |
Q: I was hoping there was a formula in the More Functions area of the Auto Sum button drop down list. ...
A: No, there's no specific function for calculating weighted averages. You have to use other functions ...
|
| Excel Formula with Reference to Another File | 6/27/2004 |
Q: This is a recurring problem I have been having, but take the belwo example. I have two Excel files, ...
A: Check the cell references in the two files. Do they look something like this?: Good file: ...
|
| conversion to base 12 or other solution | 6/22/2004 |
Q: The data being entered is age based 3.11 meaning 3 years 11 months I need to be able to average this ...
A: If years were in column A and months in column B: =(SUM(A1:A6)*12+SUM(B1:B6))/COUNT(B1:B6)/12 This ...
|
| conversion to base 12 or other solution | 6/22/2004 |
Q: The data being entered is age based 3.11 meaning 3 years 11 months I need to be able to average this ...
A: I wouldn't play around with alternate number bases. That's asking for REAL trouble. You can get ...
|
| Forgotten Excel password | 6/18/2004 |
Q: I have forgotten my password for an Excel spreadsheet and was wondering if there was any way I could ...
A: You are going to need some third party help for this. There are lots of commercial password ...
|
| Linking Cells | 6/17/2004 |
Q: I have several worksheets under one workbook in Microsoft Excel 2000. I linked several cells from ...
A: My guess is you are referring to absolute and relative references, $A$1 versus A1. $A$1 will stay ...
|
| Excel formula | 6/17/2004 |
Q: Using Excel 2000. I am working on setting up a template for my boss, I am stuck on one formula ...
A: I'm a little confused about your situation but I'll take a shot at it anyway. My guess is that your ...
|
| formula | 6/16/2004 |
Q: i have a excel sheet with datas. column 1 contains the currency code whereby is it stated either USD ...
A: If the currency code is in cell A1, the exchange rate is in B1 and the sales value is in C1, the ...
|
| Calculating all odd # and All even Number seperately | 6/16/2004 |
Q: I have a spreadsheet that has rows 1 through 2432 filled I would like to add all the odd # to a ...
A: What a coincidence. This is the second question about odd and even numbers I've answered today. ...
|
| Calculating Weekending Date | 6/15/2004 |
Q: Douglas, I am trying to create a spreadsheet that will cover multiple years of data, and I need ...
A: WEEKDAY does not return a date, it return a value of 0-6 or 1-7 corresponding to the days of the ...
|
| Fomulas and functions | 6/14/2004 |
Q: In the Office XP Excel program How do I use a formula or function to find the difference of two ...
A: A formula would need to reference cells to perform the subtraction. If you had 10 in cell A1 and 6 ...
|
| Age Calculation in years | 6/14/2004 |
Q: I need to calculate a persons age in years as of a specific date. How do I do that?
A: Dates are really numbers in disguise. As far as Excel is concerned, today (June 14, 2004) is 38152. ...
|
| 2 dimensional lookup formula | 6/13/2004 |
Q: Douglas, I saw oneof your answers on usig array formula's an dI believe the answer to my question ...
A: You've got me thinking a lot harder than I'm used to on Sunday afternoon. :-) If MATCH worked two ...
|
| creating complex spreadsheets | 6/11/2004 |
Q: What's the best way to create the following table: I have 100 tenants residing in 15 different ...
A: You have a tricky problem. Developing a tool to handle it proper is beyond the scope of this forum ...
|
| creating new macros | 6/10/2004 |
Q: i have tried ur method. it only works well if the date i want to retrieve a number for is in cell ...
A: The formulas you listed should be working. The reference style $F$1:$G$36 locks the reference to ...
|
| formula | 6/9/2004 |
Q: I have a spreadsheet where information about employee are kept. I have a sheet that keeps track of ...
A: To get the name cells to change with Conditional Formatting, change "Cell Value Is" to "Formula Is". ...
|
| Zero out cells based on info in other cells | 6/8/2004 |
Q: .. Here's the scenario... B2 and B3 are unlocked cells used for "input". B2 is for text; B3 is ...
A: I had to read your question several times. Hopefully I got it right. :-) You are right, B3 can't ...
|
| Tabbing order | 6/8/2004 |
Q: Is there a way to control the direction of tabbing from one cell to another. I have a protected ...
A: Well, sort of. If you hold down the Control key and select the cells in the order you want them to ...
|
| Fraction trouble | 6/7/2004 |
Q: For our monthly reports we keep a running total of Positive tests over All tests run. I have made a ...
A: I don't think you can force the fractions from simplifying but there is a workaround. If A1 ...
|
| Excel vba 'Next' statement | 6/7/2004 |
Q: You helped me successfully last year so I am hopping that you can do the same again. I'm despirate ...
A: Just delete the End IF statement. Your two If statements are "self contained" and End If is not ...
|
| Excel | 6/5/2004 |
Q: Sir i am working in money exchange co. in kuwait and i want to use a formula in such a way that if i ...
A: If I understand your question correctly, you want to be able to change either the dollar or dinar ...
|
| Excel 2000 | 6/4/2004 |
Q: I have a spreadsheet that has data going through to column AE. When I go into print preview, it ...
A: The most likely cause is something in column AE that is overrunning into and over the next two ...
|
| creating new macros | 6/4/2004 |
Q: i do not know alot about exel but i know how to use it to a certain extend. i have imported some ...
A: You don't need a macro to do this. Create a table with the dates and numbers. Example: In cells ...
|
| "What if" operation | 6/3/2004 |
Q: I have done a spreadsheet on salary increase proposal and need to perform a "what if" operation. It ...
A: A "what if" calculation involves altering one or more inputs to see what the changes are. In a ...
|
| Turning off Excel automatic errors | 6/3/2004 |
Q: I use terms like 19m to represent 19 million, and want to therefore also enter minus 19 million as ...
A: You can set up a custom number format to take care of this. Select the cell(s) you want this format ...
|
| Literal Formula | 6/1/2004 |
Q: Here is myConsider the following formula AVERAGE(A1:A3). This simply obtains the average of the ...
A: Yes, this is possible with normal Excel functions. The formula for your example would be: ...
|
| ignoring cells with a zero value when doing an average | 5/30/2004 |
Q: I wish to average the data in 12 cells, where one cell is on one worksheet - there are 12 ...
A: A easier way may be change the formulas to not return a zero: =IF(result(Dave's ...
|
| Calculating service engineers payments | 5/28/2004 |
Q: I would like to produce a spreadsheet that calculates a service engineers payments based on the day ...
A: There is an Excel function called WEEKDAY that will do what you need. It evaluates a date and ...
|
| Formulae Protection | 5/22/2004 |
Q: I am working in Excel 2003 and am trying to get a formula in the same cell in which the data is ...
A: Protection can be set for each cell. It fact, all your cells are probably set to be locked. To ...
|
| Data Averaging | 5/19/2004 |
Q: This is the 3rd email I've sent to AllExperts since last week, and I have yet to get a reply. I ...
A: I'm sorry you haven't had a good experience on this board. I'll try and make up for it. You can ...
|
| Why is my formatting not affecting all selected rows | 5/19/2004 |
Q: I copied my data output from the query analyser (SQL Database) and pasted it into an Excel ...
A: Numbers coming in as text is a common issue with external data plus dates can come in all kinds of ...
|
| Hello | 5/18/2004 |
Q: Do you know the formula to seprate stuff contained within a cell. Ie before abc33ac after abc ...
A: There are a couple of way to chop data. One involves the functions LEFT, RIGHT and MID. The ...
|
| Simple Question | 5/18/2004 |
Q: Hopefully this is a simple question for you. I have a products spreadsheet which has to be updated ...
A: This can be done with a lookup table. Example: your prices are listed in column A and the ...
|
| Copying down a formula with a negative increment | 5/17/2004 |
Q: I am trying to copy down a formula with a negative increment. For example, lets take 12 cells from ...
A: You can build text version of the formula and then convert it to a formula. Example: In cell D1, ...
|
| Golf Handicap formula question | 5/9/2004 |
Q: I don't know if you are familiar with golf handicap calculation (or how much time you have to devout ...
A: Your question just snuck in after I went on AllExperts vacation for the week. I don't have time to ...
|
| Custom Headers | 5/8/2004 |
Q: Doug, I using Excel 2000 with Windows 98SE I have a workbook with about 50 sheets and I often need ...
A: 1) Yes, this can be done. The trick is to select all the sheets first. To do this, click on the ...
|
| Writing a complex formula into an excel spreadsheet | 5/7/2004 |
Q: I need to create a spreadsheet for tracking the costs of materials for training classes in an Excel ...
A: Your main problem looks to be translating copy type number in column D to the corresponding cost ...
|
| VLookup Help | 5/6/2004 |
Q: I have a list of numbers in Column A (approximately 15,000 numbers. Column B is a shorter list ...
A: I like to use MATCH in situations like yours. The formula for column C would be something like: ...
|
| formulas | 5/6/2004 |
Q: I am using Excel 2002. I have a workbook that uses VLOOKUP and SUMIF formulas. My question is why ...
A: The #N/A error message is generated by the VLOOKUP function under the following conditions (from ...
|
| Excel spreadsheet formula | 5/5/2004 |
Q: I am using Excel 97 SR2 I do not have alot of experience with the program, but have done a few ...
A: I'm a little confused on the details of the problem but I think I can give you general guidance that ...
|
| Excel functions | 5/4/2004 |
Q: I am running Office 200 XP Pro. I ran into a problem when I tried to insert a new row in the ...
A: Ronald, You asked me this question last week. I've copied below the reply I sent previously. Doug ...
|
| Uploading Excel Data to a Mainframe | 5/4/2004 |
Q: Is there a quick and easy way to do this? If so, I would appreciate knowing how to do this. Thanks
A: The short answer is: "no, not really." Here's a more detailed explanation: Excel can be used ...
|
| Excel File Size | 5/3/2004 |
Q: I use an Excel spreadsheet as a schedule. The file size has been growing to unusable size (11MB) ...
A: Here's a few things to try, in the order you should try them: Go through each sheet in your ...
|
| Strange enterings with Excel | 5/2/2004 |
Q: This is going to sound weird....I do a database with Excel. I also do a lot of cutting and pasting ...
A: Excel isn't going to create things like this without help. The only thing I can think of is that ...
|
| COUNTING | 4/30/2004 |
Q: =COUNTIF(B:B,"open") How would I nest a trim function to stip off a blank space on the end of the ...
A: I'm guessing the extra spaces are in the column B entries. You can add an asterisk after the "n" in ...
|
| Simple Qusetion | 4/29/2004 |
Q: I need to find out the percentage of employees that make between $50K , $75K and $100k. I'm not ...
A: You can do this easily using COUNTIF and the Compensation Level numbers. Assuming the above data is ...
|
| adding rowaa with formulas | 4/29/2004 |
Q: I ran into a problem when I tried to insert a new row in the sheet where an entry had been ...
A: No, there's no setting of built-in function to do this. Formats will copy, but not formulas. A ...
|
| CHARTS | 4/27/2004 |
Q: I am working in one excel document with 9 worksheets that connect data into charts. Whenever I do a ...
A: Can you clarify what you mean by "shrink up"? Are a some of the worksheets pages shrunk up or are ...
|
| formula | 4/26/2004 |
Q: I have windows me and office 98. My question is if you have different slit widths such as 4 ½”, 5 ...
A: You can use Solver to come up with solutions like this. I haven't used it much though. To get ...
|
| How can I make the CODE unique? | 4/26/2004 |
Q: Yesterday I asked you this question : "I want an excel template for my pharmacy .. which I can make ...
A: Excel does not have feature to prevent you from entering a duplicate number like Access does, but ...
|
| excel templates | 4/25/2004 |
Q: I want an excel template for my pharmacy .. which I can make a (CODE) for every thing .. so when I ...
A: You can use the VLOOKUP function to do this. First, you need to create a table of the codes and the ...
|
| CONDITIONAL FORMAT | 4/24/2004 |
Q: if i have data in a row, say cells A1 to A10, can i get the whole row to change colour if say cell ...
A: Yes, this can be done. Select the row or cell range you want to change color and then select ...
|
| writing macros in excel | 4/24/2004 |
Q: i want to write macros in ms excel so that i can solve given task give me procedure thanks for your ...
A: The best way to learn how to write macros (Visual Basic for Appications or VBA) is to get a good ...
|
| lost file | 4/23/2004 |
Q: I am using Excel 97. I lost a file that is my senior research project... I had it on a floppy disk, ...
A: This is a disk issue, not an Excel issue. You will need a disk recovery program (Norton, Symantec, ...
|
| Excel Spreadsheets | 4/23/2004 |
Q: How do I set up automatic numbering in Excel? This is line by line numbering and not page. Thanks.
A: Excel does not directly have this feature. (Access does for records and Word does to bullets / ...
|
| Excel | 4/22/2004 |
Q: What tricks can I use to help make my spreadsheets and processing more efficient using Excel?
A: Can you be a little more specific on what you are trying to do with Excel? List management, ...
|
| excel | 4/21/2004 |
Q: I would like to know if i can obtain by writting somewhere, the search of items. I'll explain, i ...
A: Excel has this feature (AutoComplete) but it is very limited. It only works in data lists and then ...
|
| How to use DCOUNT function | 4/21/2004 |
Q: 1, My mistake, what I want to know is regardin DCount, can we use a formula like ...
A: The formula cannot be used in the DCOUNT formula because the DCOUNT function needs cell ranges and ...
|
| How to use DCOUNT function | 4/20/2004 |
Q: Doug how about using a formula as criteria? the problem is a formula ususally is referring to a (or ...
A: I'm not quite clear on what you are asking but I'll take a shot at a couple of possibilities. You ...
|
| Excel | 4/20/2004 |
Q: I have two questions In excel,I need to link from a spreadsheet and I need to link to a ...
A: The easist way to set up a link to or from a spreadsheet is to put an equal sign in a cell and click ...
|
| How to use DCOUNT function | 4/19/2004 |
Q: When using the Dcount function,the selection criteria is usually an "and" relationship (ie, ...
A: Yes, it is possible, plus you can combine AND's and OR's. Here's how: Criteria on the same line ...
|
| Formatting Cells | 4/19/2004 |
Q: Is it possible to set the length of a cell such that when a paste operation is applied on it then ...
A: You can limit the number of characters to a cell by using Data Validation (Data menu) but that only ...
|
| Numbers exported from a database | 4/17/2004 |
Q: I have a text file of numbers which is exported from a database. The numbers are separated with ...
A: Is the file opening straight into Excel or is the Text Import Wizard asking you how to process the ...
|
| Excel efficiency | 4/17/2004 |
Q: Microsoft Excel for XP I have a master list on Excel, which contains hundreds of entries and I make ...
A: You can use the Advanced Filter (Data menu / Filter / Advanced Filter) to collapse the master list ...
|
| date | 4/11/2004 |
Q: Please inform the Excel formula that gives the number of a certain weekday between two given dates. ...
A: I'm about to head out of town for the week so I won't have time to give you a complete solution, but ...
|
| Excel | 4/10/2004 |
Q: My knowledge of exel is fairly basic so please bear with me. I use excel 2000 at work. I purchase ...
A: I about to head out of town for the week so I won't have time to give as detailed of a solutions as ...
|
| EXCEL FORMULA | 4/9/2004 |
Q: Cell A1 contains characters and dashes, eg: WG-LW-YC-BR-WG Need formula to count the dashes, in ...
A: I took a shot at this and I couldn't come up with a formula for it. FIND and SEARCH will both ...
|
| Highlighting selected numbers | 4/8/2004 |
Q: I have a worksheet with lists of numbers and i wish to enter a selection of numbers elsewhere on the ...
A: You can do this with conditional formatting. (For this example, the first entry cell is A1 and the ...
|
| RE EXCEL | 4/7/2004 |
Q: I WOULD LIKE TO FIND AN EASY WAY TO TRIGGER A MESSAGE BOX WHEN ,FOR EXAMPLE WHEN CELL D10 = THE ...
A: A macro could be written to do this but might be a little complicated. For a non-macro solution, ...
|
| Spreadsheets with Links | 4/7/2004 |
Q: What is the easiest way to remove all links in a spreadsheet.
A: There is no button or quick technique to track down all workbook links because they can be found in ...
|
| Modified time | 4/6/2004 |
Q: Excellent solution! Can this be modified so that pasted or moved cells will have the stamp applied ...
A: Yes, change the AddComment line in Stamper to something like: Range("A6").Value = Date + Time This ...
|
| Modified time | 4/6/2004 |
Q: Is there any way to apply a time stamp to a given cell based on when it was last modified?
A: Try this macro code: Sub TimeStampOn() ActiveSheet.OnEntry = "Stamper" End Sub Sub Stamper() ...
|
| Excel Heading format | 4/5/2004 |
Q: In excell 2000, my column headings are a,b,c sometimes and 1,2,3 others. Is there anyway to just ...
A: The setting for this is located on the Options dialog box unser the Tools menu. Click on the ...
|
| Format for date | 4/5/2004 |
Q: I am using d mmm* (ddd) format for a column of my spreadsheet. I just wonder if there is a method ...
A: Yes, this is possible, but not through normal formatting. Select the date cells, and then select ...
|
| Looking up values. | 4/2/2004 |
Q: From a range of values, I want to know what month and year the largest value occurred, the second ...
A: Here's the formula: ...
|
| Sum like data | 3/19/2004 |
Q: Using Excel97 Have invoices of 8 digits formatted as text (do not want to change format) with ...
A: You can use COUNTIF and SUMIF to do this, but it will go a lot easier if define some range names ...
|
| Using Vlookup to find more than 1 criteria | 3/18/2004 |
Q: I use Excel 2000. I have one table with three columns. One column is the item, the next column is ...
A: VLOOKUP won't work in this situation but we aren't going to let that get in our way. Let's learn ...
|
| Excel header help | 3/18/2004 |
Q: I need to change the top column headers (ie: A, B, C, etc.)of a standard Excel spread sheet to other ...
A: I'm sorry, but that's not possible. There are only two options for those headers, letters (ABC...) ...
|
| Opening My file(s) | 3/17/2004 |
Q: You were exactly right. There were references to another workbook and unless that workbook was open, ...
A: It's the file location that was the problem. If the file wasn't open but was still in the correct ...
|
| Formula to Sort a list | 3/17/2004 |
Q: First of all thanks for your time and help and I'll try and make this as painless as I can for you ...
A: This is going to be a multi-part solution. First, we will need to separate out the quantities and ...
|
| excel | 3/16/2004 |
Q: I have a query result from an access database, dumped to excel, pat_id, age_at_exam . Now I want to ...
A: Excel needs the counts of those categories before it can chart them. You can use COUNT and COUNTIF ...
|
| misbehaving array formula | 3/15/2004 |
Q: In column B under a heading 'letters' I have the numbers 1 2 3 1 in separate cell. In column C ...
A: You are on the right track. The array formula version would be: {=SUM(IF(let=1,cost,0)} The array ...
|
| Opening My file(s) | 3/15/2004 |
Q: I have a problem when opening a couple of my MS Excel Files. The files are password protected and ...
A: It sounds like the file you are opening has links to another file, which can't be located. It ...
|
| Excel | 3/13/2004 |
Q: This is a very basic question for you, I'm sure. I'm doing a spreadsheet, and I'd like to know how ...
A: I hate it when that happens! You want the Data Validation feature. Select the cell(s) to apply the ...
|
| I need a suggestion | 3/12/2004 |
Q: Sir, I want to master excel so that i can put my thoughts and initiatives into action but my ...
A: That's very admirable of you. There many resources available. A good book is invaluable. Any book ...
|
| -------------------------
... | 3/11/2004 |
Q: I have two columns of numbers in Excel that I want to multiply and add together. For example, I ...
A: Select cell C1, press F2 to edit the formula and then press Control-Shirt-Enter instead of Enter. ...
|
| network days and Median? | 3/10/2004 |
Q: I have a spreadsheet that I have made in Excel that calculates our turn around times on patient ...
A: Excel has a built-in MEDIAN function. Here is what Excel Help says it does: Returns the median of ...
|
| Writing for PC Upgrade Magazine's Leasrning Series | 3/9/2004 |
Q: Smith, I am writing on behalf of PC Upgrade Magazine's Learning Series. We are looking for someone ...
A: Yes, I'm very interested. Please send me the details. I once wrote a 12-part series on AppleWorks ...
|
| Insert a check mark | 3/6/2004 |
Q: How do you insert a check mark in a cell. I am using xp office pro Answer - Hi Dick, There are ...
A: I'm guessing you are doing everything except changing the cell font to Wingdings. If the character ...
|
| Insert a check mark | 3/6/2004 |
Q: How do you insert a check mark in a cell. I am using xp office pro
A: There are several ways to do it. The first method involves using a graphic font such as Symbol or ...
|
| associate a text word with a numerical value? | 3/5/2004 |
Q: Can i make a tally of QNS, +, and - by saying =COUNTIF($A$1:$A$50,C1:C3)? Then a %+ by ...
A: Not quite. However, you can count all the items like this: ...
|
| associate a text word with a numerical value? | 3/5/2004 |
Q: i'm working on a monthly sheet to tally our patients positive tests versus negative tests and tests ...
A: You don't need to convert the symbols to numbers to count them because has functions that can count ...
|
| Excel as an Inventory management tool | 3/4/2004 |
Q: Yes, I could set the prices at zero, however I would have to see if there is another method to track ...
A: A couple points to remember if you decide build your own. You don't the users to be able to modify ...
|
| Excel as an Inventory management tool | 3/4/2004 |
Q: Smith, I have recently been given the job of maintaining our offices inventory of contracts, ...
A: Yes, Excel can handle all of that but it would take some work to set it all up. In simplest terms, ...
|
| formula question | 3/3/2004 |
Q: Doug, I'm using Excel 2000 on a PC with Windows 98SE. Q. When the results of a formula yield "0", ...
A: Yes, the are a couple of ways to do this, globally and locally. The global method will hide ALL ...
|
| Naming and Labels | 3/3/2004 |
Q: What is the difference between naming a range of cells and assigning them as a label in Excel?
A: In a nutshell, named ranges are physically named and defined, but labels implied/interpreted. Named ...
|
| Excel as mailing list? | 3/2/2004 |
Q: I'm sure Word is better suited to this, but I sure would like to get it to work with Excel. At work ...
A: Here's detailed information on doing mailing labels in Word from an Excel data source (from Excel ...
|
| undo a saved file | 3/2/2004 |
Q: I had accidentally saved my file before I rename it, so my previous file was overwrite. I didn't ...
A: It is probably lost forever. We've all had that happen (at least) once. The only possibility may ...
|
| Excel formula problem | 2/27/2004 |
Q: I'm using Excel 2000 on a PC with Windows 98SE. Q. In a spreadsheet containing expenses, one of the ...
A: This can be caused by three things: 1) You manually entered the squiggley brackets {}. This makes ...
|
| Excel 97 - Links | 2/26/2004 |
Q: I'm having a challenge eliminating links that I no longer need. Within Edit, Link - the manual ...
A: Tracking down links like this are part science, part art. They can lurk just about anywhere. Try ...
|
| Transferring text from one work page to another within the same workbook. | 2/26/2004 |
Q: I am running Excel 2000 on a Windows 2000 format with an HP Omnibook laptop that has an Intel ...
A: Sounds pretty confusing to me but try to get to the root of the problem. Evaluating text works the ...
|
| Excel formula question | 2/25/2004 |
Q: I have created the following formula, which compares two criteria that have been placed in cells L22 ...
A: You have the right idea. Let's use ISNA instead of ISERROR. It traps only the N/A error. The two ...
|
| adding time (hour) units that exceed 24 | 2/24/2004 |
Q: How can I add hours when the total will exceed 24 hours. It seems that the normal addition returns ...
A: Here's a quick lesson on how Excel handles dates and time. Time is measured from zero to one. Zero ...
|
| inserting rows | 2/24/2004 |
Q: I have a spreadsheet that has over 3000 rows of transaction information and I'm importing this ...
A: You're going to need a a macro to do this: Sub Inserter() ActiveCell.Offset(1, 0).Select Do While ...
|
| Comments merging | 2/23/2004 |
Q: Is there a way to have all of the comments from various cells merged into one cell's comment box ...
A: No, you can't do this directly because there are no worksheet functions that will read a cell's ...
|
| SEARCH | 2/15/2004 |
Q: SUPPOSE THE TABLE * A B C D 1 20 70 120 180 2 25 90 260 270 3 28 120 480 390 4 32 180 520 570 ...
A: If you used a VLOOKUP formula: =VLOOKUP(A6,$A$1:$A$5,1) then result would the closet lower value, ...
|
| Arbitrary date changes in Excel? | 2/15/2004 |
Q: When I open a previously saved speardsheet, all dates are changed by about four years. It has ...
A: Your IT helper is mis-informed. The Windows version of Excel starts counting dates at January 1, ...
|
