| Subject | Date Asked |
|
| Conditional Formatting | 11/17/2009 |
Q: Hey Richard, I have a color sorting question for excel 2003. I've applied conditional formatting(i.e ...
A: You need pretty tricky VBA code in order to sort by color in Excel 2003. I don't have such code ...
|
| fx function > | 11/16/2009 |
Q: in column A1 is 12. I want column A2 to display anything over 8; in other words 4, how do I do it? ...
A: Yes, you did phrase it very simply the first time. Garbage in = garbage out, right? :) You can ...
|
| If function | 11/12/2009 |
Q: How do you add a third argument for an IF function. I can handle the baisc IF function but can not ...
A: Use the AND or OR functions. =IF(OR(A2=5,A2=8),X,Y) Easy enough? Let me know if you have any ...
|
| USING "IF" | 11/12/2009 |
Q: Please help. I am trying to write a single formula that will return a "YES" or "NO" answer based on ...
A: Try using the AND or OR functions, like: =IF(AND(A1>A2,B1>B2),"Yes","No") Let me know if you have ...
|
| fx function > | 11/12/2009 |
Q: in column A1 is 12. I want column A2 to display anything over 8; in other words 4, how do I do it?
A: =12-A1 Easy enough? Let me know if you have any other questions or comments. Also, be sure to ...
|
| Assigning numeric value to letter and tallying | 11/11/2009 |
Q: I am making a spreadsheet of RSVPs for our company's Holiday party. I have two columns to be ...
A: That's exactly what the COUNTIF function was designed for. At the bottom of the column, just say: ...
|
| Excel if/then question | 11/11/2009 |
Q: I would like my cell to perform a calculation if there is data in another cell it checks, however ...
A: It's not your formula. It's the fact that you can't compare a numeric value with an empty string. ...
|
| Dates change in Excel | 11/11/2009 |
Q: I am using Excel 2003. I keep a mail list spreadsheet and have columns indicating dates when a ...
A: Regardless of how the data LOOKS in the cell, you need to ENTER it as a real, full date for Excel to ...
|
| columns across sheets | 11/10/2009 |
Q: I'm hoping you can help me with something that seems like it would be pretty simple. I'm managing a ...
A: I would copy/paste-link the headers from sheet to sheet. That's the easiest way, yes. Let me know ...
|
| Calculating Overtime | 11/10/2009 |
Q: I'm trying to do up a spreadsheet for employees ovetime, it depends firsly on what level they are at ...
A: I just recorded a video tutorial on calculating employee overtime pay. Check this out and see if it ...
|
| Is lookup my best function? | 11/9/2009 |
Q: I am trying to relate two worksheets that only have one column in common. I set the following ...
A: Use absolute references for your cell ranges. Instead of A1, say $A$1. See this tutorial: ...
|
| Conditional Count based on the value of a cell rather than a specified number | 11/8/2009 |
Q: I am trying to count the number of cells in an array that are less than the value of a named cell ...
A: Try this: =COUNTIF(A1:A10,"<"&B5) Good old string concatenation trick. Let me know if you have ...
|
| Excel changes my formatting when I open my file | 11/5/2009 |
Q: I have been working on a specific file in Excel 2007 for work. There are numerous sheets and I ...
A: Thomas, I don't know what the problem is off the top of my head - without seeing it in action, it's ...
|
| Excel spreadsheet 11X17 | 11/4/2009 |
Q: I wanted to create an exel spreadsheet that is big enough for Ledger size paper.I have some ...
A: On the PAGE LAYOUT tab of the Ribbon, click on SIZE and then you'll see LEDGER as one of the ...
|
| Counting Blanks in Multiple columns with a variable range | 11/4/2009 |
Q: Using Office 2003 on XP. Basically I get a report of records showing missing data and I need to ...
A: Sure, just use the COUNTBLANK function for each column, then you could paste-link that value into a ...
|
| merging spreadsheets | 11/2/2009 |
Q: I am trying to merge 2 spreadsheets %26 followed the instructions on about.com. Only 2 of my ...
A: Well, I wouldn't try to merge them using Excel. I would merge them in Access. If you still have the ...
|
| Calculating time in MS Excel | 10/30/2009 |
Q: , hope you can help, i am calculating time sheets, times in and out, on a daily basis. I need to be ...
A: This tutorial should answer yourThere are many different techniques in Excel you can use to break ...
|
| Conditional Formatting | 10/30/2009 |
Q: I am trying to do a conditional format on 1 cell if another cell is between 1 and 99. I think I need ...
A: You can't apply conditional formatting to a DIFFERENT cell without some VBA programming. Excel has ...
|
| Tab Color Change based on Cell Value | 10/28/2009 |
Q: I would like the worksheet tab to change to red if in column "B" there is a value less than 2. How ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| accumulative calculation | 10/28/2009 |
Q: what im trying to do is add a set amount to the months. ie. 31 days in Oct so I want to add $5.00 to ...
A: How about putting the actual date in column A, and then in column B you could say: =DAY(A1)*5 This ...
|
| Can Ilink drawing objects with cell values? | 10/28/2009 |
Q: I use MS Excel 2003. Is there a way to link a certain parameter of a drawing object in Excel to a ...
A: Yes, it is possible with VBA code, however a detailed explanation of exactly how to do this would be ...
|
| Percentage Formulas | 10/27/2009 |
Q: I know how to do the formulas to show a percentage increase (122*1.3) to show a 30% increase. I ...
A: If you have a value in A1 and you want to find 30% of it, you'd say: =A1*0.30 So to subtract that ...
|
| Alerta on Excel | 10/27/2009 |
Q: I need to set up an alert which can indicate to me when a document is overdue. I have the date it ...
A: In order to create a popup alert, that would involve some VBA programming, which I cannot go into ...
|
| Rolling stats? | 10/27/2009 |
Q: I am creating a spreadsheet to track YTD rolling averages of my employees' performance measurements. ...
A: Why not just use the AVERAGE function? This will take into consideration the number of data entries ...
|
| Count by Condition | 10/27/2009 |
Q: How are You? I am facing a challenge while i am tring to Count Coloumn A Text Value by placing a ...
A: I don't think you looked at the tutorial I directed you to. At the bottom of Column B, say: ...
|
| vlookup | 10/26/2009 |
Q: Excel 2002 I have worksheet(a)which has a column of text from which I need to extract from each cell ...
A: I tested it, and it seems to work OK for me - assuming I understand your question correctly. I put ...
|
| if, else statement | 10/25/2009 |
Q: Richard, I have an equation that references a few cells to get an answer. That's not the problem. ...
A: You could use the ISERROR() function and say: =IF(ISERROR(A1),0,A1) Let me know if you have any ...
|
| calculationg overtime | 10/24/2009 |
Q: I'm trying to set up an excel spreadsheet for calculating regular time, overtime (at time and a ...
A: It's always easiest to calculate things like this using multiple columns. It's just so much easier ...
|
| Header / Footers in Workbook | 10/24/2009 |
Q: I have a workbook with many sheets in it. I need to insert the same header and footer for each ...
A: Very good question. You can apply the same header to multiple sheets by GROUPING the sheets first, ...
|
| Splitting Data from one row to multiple | 10/23/2009 |
Q: I have my staff's hours like below Project Name Nov Dec Jan Feb PUD John 60 ...
A: That's just a matter of simple math. First, you'll need to make a SUM total for each employee. You ...
|
| Count by Condition | 10/23/2009 |
Q: How are You? I am facing a challenge while i am tring to Count Coloumn A Text Value by placing a ...
A: Use the COUNTIF function. You can use the SUMIF and COUNTIF functions to add up or count values ...
|
| Excel Formula Dilemma | 10/22/2009 |
Q: I have a compliancy report that lists how many times YES (column A), how many times NO (column B) ...
A: I'll assume your data looks like this: A,B,C 5,3,1 4,3,6 5,4,3 etc. If so, you just need to add up ...
|
| 2nd Axis in Excel | 10/21/2009 |
Q: I am trying to add a second axis to a bar chart in Excel 2007, but when I do, the graph ...
A: This all depends on which chart type you selected when you made your chart initially. There is the ...
|
| Admin and specific user only per tab? | 10/21/2009 |
Q: I am wondering if there is a way without VBA to create a spreadsheet where there is a admin tab and ...
A: No, you cannot do this without programming. Excel only supports ONE password for a workbook that has ...
|
| Excel Spreadsheet Reduces in Size | 10/21/2009 |
Q: I have an Excel spreasheet which on Friday (16/10/2009) was showing a file size of approx 15,000KB. ...
A: That's odd. Perhaps Excel compacted some of the data on one of your recent saves. Excel will ...
|
| Excel 2003 time calculation | 10/20/2009 |
Q: I want to display total time as a number, not as a time. For instance, right now I have the simple ...
A: This tutorial answers your question exactly: ...
|
| Excel 2007 formula | 10/20/2009 |
Q: I am trying to enter a formula using =IF; it needs to meet 2 numerical criteria; if > 14 (cell C8) ...
A: That shouldn't be too hard: =IF(AND(C8>14,C10<5),1,0) Easy enough? You can use the IF function to ...
|
| Now() function | 10/15/2009 |
Q: I am using excell 2003 working on my spreadsheet I got stuck in one point; in cell A1 putting ...
A: I'm sorry, but I can't think of any. If any other readers have an idea, please post them here. Let ...
|
| Excel | 10/15/2009 |
Q: Hope all is well. Thanks for your time. I was wondering how do I enter a shaded text behind ...
A: Stacy, go to the Excel Help and type in "watermark." You get this: Mimic a watermark in Excel ...
|
| If/Then question | 10/14/2009 |
Q: I am trying to construct a billing spreadsheet that if I put a number in a certain column, it needs ...
A: How about using the ISBLANK function to determine whether or not to perform your calculation? ...
|
| Change Cell Color When Matching Values Are Found In Different Worksheets | 10/14/2009 |
Q: I wish Conditional Formating worked across multiple sheets. What I have is four spread sheets in ...
A: This is going to be extremely hard to implement in Excel. Have you considered using a database like ...
|
| Excel lock cells with different passwords | 10/14/2009 |
Q: As I do not know the answer to this one I like to ask it to an expert like yourself. Is it possible ...
A: I'm sorry, but I do not believe this is possible without some advanced VBA programming. A locked ...
|
| Excel 2003 | 10/13/2009 |
Q: We use two 2003 excel spreadsheets daily. One where we keep a list of customer forms we recieve and ...
A: You can use VLOOKUP for this. You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful ...
|
| excell | 10/10/2009 |
Q: The work I'm applying for requires me knowing MS office 2007 Suite. I assume that means excell, ...
A: Well, of course I'm biased. I make Microsoft Office video tutorials, so I personally think that's ...
|
| Excel IF/Then | 10/9/2009 |
Q: I am attemptiong to create a if then statement.. but I am not having much success. Can you please ...
A: In E57 you would say: =IF(H54="N",C70,"") The tough one is J, K, or L because you have to use the ...
|
| Now() function | 10/8/2009 |
Q: I am using excell 2003 working on my spreadsheet I got stuck in one point; in cell A1 putting ...
A: By using the NOW function in all of your cells, you're going to cause Excel to update ALL of those ...
|
| Excel | 10/8/2009 |
Q: Richard, I am creating a spreadsheet and enter invoices with payment dates on them. What i really ...
A: WITHOUT programming, you could use conditional formatting to make the invoices that are due show up ...
|
| dollar sign format | 10/7/2009 |
Q: why is there accounting format and currency formating for the dollar sign?whats the difference ...
A: Format a column of numbers with each and notice the placement of the dollar sign. :) Let me know if ...
|
| calculationg overtime | 10/7/2009 |
Q: I'm trying to set up an excel spreadsheet for calculating regular time, overtime (at time and a ...
A: Well, double-time would be those hours over 12, so: =IF(C4>12,C4-12,0) It's the difference between ...
|
| Using conditional IF | 10/6/2009 |
Q: I have two columns in Excel, column P contains Y or N, column K a quantity. I tried this ...
A: Tell me what you're TRYING to do, and what your data looks like - not what you're doing that's not ...
|
| Transfer of data from one spreadsheet to another in the same work book | 10/6/2009 |
Q: I am constructing a timesheet workbook and I want to list employees on sheet One,(Rates) in column A ...
A: I'm not sure I understand what you mean. You want to send out a spreadsheet with data, but stop ...
|
| Excel | 10/5/2009 |
Q: I have an alphabetical list of names, is there a way to have each letter print on a new page? ...
A: I can't think of a way to do this in Excel. You can sort your list and then manually manipulate the ...
|
| Merging columns | 10/2/2009 |
Q: I use word 2000 and want to merge a few columns of data into one column. One column is address, then ...
A: Well, you could convert the date to TEXT first: =TEXT(A1,"mm/dd/yy") Excel stores DATES internally ...
|
| Excel Formula | 10/2/2009 |
Q: I have 2 spreadsheets. The first sheet has 2 columns, column A has a list of addresses, column B has ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Concatenate | 10/1/2009 |
Q: VBA Expert Richard, Can you please help me with the following formula, I am using Excel 2000 ...
A: This is just a matter of putting together the functions right. A good understanding of OR and IF ...
|
| Assigning Values to a cell | 10/1/2009 |
Q: I am a bus driver in London and I use a lot excel for my work time table which spreads several ...
A: You can refer to a value on another sheet like this: =Sheet2!A1 I would personally use VLOOKUP to ...
|
| Excel | 10/1/2009 |
Q: I have an alphabetical list of names, is there a way to have each letter print on a new page?
A: If by letter you mean that you want to write a letter to each recipient on your list, then YES, you ...
|
| sum multiplication error | 10/1/2009 |
Q: I am doing a basic sum multiplication formula such as C4*8 the result is incorrect when C4 is .24 ...
A: Without seeing your calculations (like what is C4) it's impossible for me to tell you what's wrong. ...
|
| Formula problem | 9/30/2009 |
Q: Here is an example: I have a listing of destinations with one column being for mileage. I am ...
A: This is just going to be a compex, nested IF function. ...
|
| Excel Referencing a Cell | 9/30/2009 |
Q: I would like to know how to reference a cell on another worksheet using data from a 3rd cell eg ...
A: Sure. You could use the OFFSET formula to move over and down a number of rows/columns in your sheet. ...
|
| Student Address Excel Database | 9/30/2009 |
Q: I'm in my High Schools Yearbook class and we are in need of some assistance. I have a list of the ...
A: How are you matching the students with the teachers? I would need to see a sample of how the sheets ...
|
| Excel formula | 9/30/2009 |
Q: Column M is named Current M1=+P1-N1 Column N is named previous N1=(copied from column O on prior ...
A: You're on the right track, but what do you do if it DOES put O > 6900 ?? =IF(O<6900,P-N,X) X = ...
|
| Excel 2000 formulas | 9/30/2009 |
Q: I have constructed a time-sheet and analysis spread sheet which records hrs, for different staff, ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Using the Results of a Formula as Part of a Cell Address in Another Formula | 9/30/2009 |
Q: In Excel 2003 I have two cells. The first contains the formula: =MATCH($B$6,'Zip ...
A: You could use the OFFSET function to move down a number of rows equal to the value of your first ...
|
| excel formula | 9/29/2009 |
Q: how can I create a formula to calculate the total cost of part hours worked i.e if someone works ...
A: Add up the times as normal, and divide by the number of sessions. There are many different ...
|
| ISBLANK formula | 9/29/2009 |
Q: I have a formula:=IF(NOT(ISNUMBER(S4)),S4,R4) which works. I need to state if there is a number in ...
A: How about: =IF(ISNUMBER(S4),S4,IF(ISNUMBER(R4),R4,"closed")) You just need to nest your IF ...
|
| VLOOKUP and IF statments | 9/29/2009 |
Q: This formula gives me an error message saying I have used too many arguments =VLOOKUP(F11,'salary ...
A: You do have too many parameters for your VLOOKUP statement. It won't accept all of those different ...
|
| Find & replace | 9/29/2009 |
Q: I use the find tool on a particular spreadsheet alot, is there a way of inserting the find tool onto ...
A: Not easily, no… but you can create a custom toolbar and put the FIND button on it. Let me know if ...
|
| Prob in Vlookup | 9/29/2009 |
Q: I,m facing prob in vlookup faurmula in ms excel. if same name two times in my data with diffrant ...
A: Make sure your list is sorted ascending for VLOOKUP to work - if not you have to use EXACT MATCH. ...
|
| Working hours calculation | 9/29/2009 |
Q: I need to calculate business hours, from when a job is ordered to when it is completed. Our working ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Cell references - THANKS RICHARD | 9/28/2009 |
Q: Hey Richard, First and foremost, Thanks for helping me out. Here is my question. I am using excel ...
A: Use the OFFSET function. =(B1,35,0) This says go 35 rows down and 0 columns across from B1. Let ...
|
| Trim text | 9/28/2009 |
Q: I am attempting to use a link to a separate page to bring data to a totals table from various ...
A: Use the LEFT() function. =LEFT(A1) I cover the various string manipulation functions in my EXCEL ...
|
| isblank | 9/28/2009 |
Q: I'm trying to do something I think is probably simple, but I'm making it harder than it needs to be. ...
A: The NOW() function is going to continuously update that cell to the current time. You would need a ...
|
| forms / databases | 9/28/2009 |
Q: I would like to build a form in XL (2003 version) that can be filled out by employees on the network ...
A: I honestly would not do this as an Excel form. I would set up a Microsoft Access database or a ...
|
| Email list cleanup | 9/28/2009 |
Q: I have a list of email address in column a (20,000), a second list in column b (1400) and I want all ...
A: Use the MATCH function to determine whether or not that address appears in column A. Let me know if ...
|
| formula | 9/28/2009 |
Q: Please kindly explain me how the following formula works [ ="S " & RIGHT(B7,LEN(B7)-2)]
A: It adds "S" to the right X characters of whatever's in B7 where X is the length of B7 - 2 ...
|
| Pulling Report? | 9/27/2009 |
Q: I'll try to be brief. I have an excel sheet which has a list of over 200 stores I deliver to weekly. ...
A: This really would be better accomplished with an MS Access database, but you could do this with ...
|
| formula | 9/26/2009 |
Q: Would you have an idea for a formula for the following. I want to be able to take a figure on excel ...
A: If your value is in A1 you should be able to say: =(((A1*.45)*.18)*2.4) Or thereabouts. It's just ...
|
| Merging columns | 9/26/2009 |
Q: I use word 2000 and want to merge a few columns of data into one column. One column is address, then ...
A: Your question is in the category of EXCEL, so I'm assuming your DATA is stored in Excel. You can ...
|
| Nested IF Statements | 9/25/2009 |
Q: I am trying to use 20 nested IF statements to lookup a value in a table. for example... ...
A: You can still use VLOOKUP. There is a fourth optional parameter that does an EXACT lookup. Say: ...
|
| PO spreadsteet | 9/25/2009 |
Q: I am looking for a PO spreadsheet. It is a simple one; there are no functions or manipulations ...
A: Have you looked in the Microsoft Office Online templates? Perhaps someone else has made one. If not, ...
|
| excel formula | 9/25/2009 |
Q: I am trying to create a formula that reads =isblank(C15,"0",IF(C15>E15, "3", IF(C15=E15, "1", ...
A: Without recreating your sheet, I couldn't tell you what's wrong with your function, however, your ...
|
| Sum in excel | 9/24/2009 |
Q: I have around 1000 numbers (prices) on column A, and on column B I have the part number. I want make ...
A: You would need VBA code to read the color of the cell. I don't have any code on hand to give you, ...
|
| using a count with an And statement | 9/24/2009 |
Q: Richard I am trying to count the number of times specific "word" appears in one column AND one of ...
A: The easiest solution would be to set column E to: =IF(AND(C2="Confirmed",D2=910),1,0) Then just ...
|
| Linking worksheets | 9/24/2009 |
Q: Richard Is there a way to create a link from a cell in one worksheet to another worksheet in the ...
A: You could create a HYPERLINK from one cell to another. That's about all I can think of without ...
|
| Sales Invoicing on Excel | 9/24/2009 |
Q: I am using Excel 2003 (Small Business Edition) to produce sales invoices. We have a LOT of customers ...
A: You can use VLOOKUP to pull this information from a summary sheet, but I would strongly recommend ...
|
| converting numbers in Excel | 9/23/2009 |
Q: Is there a way to convert negative numbers to positive numbers in a column? I am using Excel 2007
A: Absolute Value =ABS(A1) Let me know if you have any other questions or comments. Also, be sure ...
|
| Embed an "OR" into a "COUNTIF" | 9/23/2009 |
Q: I am trying to count an array if either of two options are true. One way to do this is ...
A: Try: OR(CountIf(a:a,"x"),CountIf(a:a,"y")) Let me know if you have any other questions or ...
|
| Formula Problems | 9/23/2009 |
Q: I'm having some formula problems that I'm hoping you can help me with. I'm looking to exclude ...
A: You can do this with a couple of string functions. Let's say: A1="1234xxxx5678" And you want to ...
|
| Inserting Comma's into values | 9/23/2009 |
Q: I have a column that contains city and state abbreviations without a comma between the two(i.e. ...
A: The big question is this: are all the states EXACTLY 2 characters at the end of the cell? If so, you ...
|
| Populating cells | 9/23/2009 |
Q: Let's say I have a column A and B populated in Sheet 1 and. In Sheet 2 I have column A populated ...
A: Use an IF function in Sheet2!B1 =IF(A1=Sheet1!A1,Sheet1!B1,"") You can use the IF function to have ...
|
| vlookup | 9/23/2009 |
Q: I am trying to match information from one column to another. For example, I have one column of LAST ...
A: Sure, you answered the question yourself. Use the MATCH function to see if the last name from column ...
|
| Change colour of cell when expiry date is greater than | 9/23/2009 |
Q: I am creating a spreadsheet with customer card details on and when I input the card expiry date ...
A: You have a column of credit card expiration dates that are TEXT in the format MM/YY. If they're all ...
|
| Using Sumproduct with 2 criteria | 9/23/2009 |
Q: I'm trying to perform a count in our database which meets either of 2 criteria - ie. count times ...
A: How about creating a second column? In that column, put a 1 if that record meets your criteria, then ...
|
| Excel Referencing a Cell | 9/22/2009 |
Q: I would like to know how to reference a cell on another worksheet using data from a 3rd cell eg ...
A: You can refer to a value on a different sheet like this: =Sheet1!A1 I cover this in Excel 201: ...
|
| Rounding down a sum | 9/22/2009 |
Q: Is there a way to round down this formula "=SUM(V13:V42)" from 3 decimal places to two. I am having ...
A: How about the ROUND function? You can specify the number of decimal places to round to. See: ...
|
| Spread Sheet Formula | 9/22/2009 |
Q: I need to know how to write a formula for the following situation. I have a price sheet: If I enter ...
A: OK, so… Col A is X or not. Col D has all the prices Col E is price IF they mark X And you want to ...
|
| VLOOKUP between files | 9/22/2009 |
Q: For years now I have used VLOOKUP in Excel 2003. I now have Excell 2007 and have a problem I ...
A: Kyle, this feature still works, but Microsoft changed the implementation a little. If you just open ...
|
| REVERSE CONCATENATION IN EXCEL | 9/22/2009 |
Q: my name is Austin Mann. I have a spreadsheet that has a field of data in column A. I need to remove ...
A: Use the RIGHT function to get the right X characters, and use LEN to see how long it is: ...
|
| EXCEL 2007 formula question | 9/22/2009 |
Q: Excel 2007: I want to compare Cell A1 on Sheet 1 to Cell A1 on Sheet 2 and if the Cell titles are ...
A: Depending on how many items you have, you could do this with an IF statement. Anything else would ...
|
| Refrences to OmniPage in Excel 2007 add-ins ribbon | 9/22/2009 |
Q: Help!!! My Excel 2007 Add-Ins ribbon is innundated with 35 references to OmniPage which I can't ...
A: I don't mean to make light of your situation, but that screen shot you sent me is kind of funny. ...
|
| converting letters to numbers | 9/22/2009 |
Q: I found something relevant to what I'm looking for, but I can't quite figure out how to change the ...
A: You wouldn't have to alphabetize it if you use the fourth VLOOKUP parameter. If you set it to FALSE, ...
|
| Range objects in Excel VBA | 9/21/2009 |
Q: Does a range object include the worksheet? Or just a reference the cells themselves (eg. A1)? ...
A: A1 refers to just that cell, relative to the current sheet. If you want to specify a sheet, you need ...
|
| VLOOKUP | 9/21/2009 |
Q: I have the following table and the vlookup formula =VLOOKUP($N9,$P$242:$Q$259,2): 333B0SS 8X10 ...
A: I would have to recreate the sheet to tell you for sure. There is a fourth argument for VLOOKUP ...
|
| Combine Multiple Rows of Data | 9/21/2009 |
Q: I am currently using Office 2007 and in my spreadsheet I have 18162 Rows of Data. (starting at A3 ...
A: And spreadsheet with more than a few hundred rows should be moved to a database. I would recommend ...
|
| trying to creat 2 specfic formulas | 9/20/2009 |
Q: I am trying to create 2 formulas. I have found the IF/OR formula that I want to use, what I am ...
A: Sounds like you need to learn how to use the OR() function: =IF(OR(A1=1,B2=5),"Joe","Sue) Let me ...
|
| Rounding numbers | 9/19/2009 |
Q: Richard, I have a specific need for rounding, and none of the functions seem to hit the nail on the ...
A: Brent, in order to help you, I'd need to know under what circumstances you'll be rounding up vs. ...
|
| using rank function | 9/18/2009 |
Q: I cannot figure out the rank function, I was told I could use it to sort my data, I have data from ...
A: See my tutorial on how to use the RANK function in Excel: ...
|
| Calculating unknown future date from a given date excluding weekends and holidays | 9/18/2009 |
Q: Can you help me with a formula that will calculate future date that is X days from a known date ...
A: There is no function that will automatically ignore holidays. You will need to write your own ...
|
| Excel | 9/18/2009 |
Q: I'm trying to create a spreadsheet for salespeople to calculate product pricing without showing the ...
A: I can't think of an EASY way to do this in Excel. In Microsoft Access (a database program) you ...
|
| Check if a cell is blank - before checking if another cell has an error | 9/17/2009 |
Q: Am using this Excel function to check if D23 has an error =IF(ISERROR($D$23),"INCORRECT COLOR ...
A: Sure. Just use the ISBLANK function and nest the IF functions. ...
|
| "IF" Formula in Excel | 9/17/2009 |
Q: I am currently working with Microsoft Office XP Professional. I am not sure the actual version of ...
A: You've just got your quotes in the wrong places. It should be: =IF(B10="Small",29.89,… You want ...
|
| Code needed | 9/17/2009 |
Q: I have designed a spreadsheet to allow users to book computers. What I need is a code that will ...
A: You can LOCK cells and turn on SHEET PROTECTION, but you would have to manually lock the cells ...
|
| specific formula for sum | 9/16/2009 |
Q: I keep a spreadsheet with dollar amounts in column F. i want to keep a running total of sort that ...
A: The strikethrough is just a matter of cell formatting. For the running total portion of your ...
|
| Copying cells after a filter | 9/16/2009 |
Q: Say I have 20,000 rows in column A. All data entered in column A is either Alpha, Bravo, or Charlie. ...
A: Once you apply a filter, you can't copy and paste it, but you can select the cells and then Autofill ...
|
| remove first character of cell if alphabet | 9/16/2009 |
Q: I have a column of data for which values need to be looked up from another worksheet. but, the ...
A: This is going to be tricky. I would isolate the first character and then check to see if it ISTEXT ...
|
| Alphabetizing | 9/16/2009 |
Q: My data base is in excel and I have added and subtracted from it for years, but I would like to ...
A: That's easy. Just select all of your data (so if you have 3 columns and 10 rows, make sure it's all ...
|
| seperating cell contents | 9/16/2009 |
Q: I have a spread sheet with columns of cells containing figures appearing thus: 12 - 17 - 32 - 41 - ...
A: This is not going to be easy, but it can be done. You're going to have to use the FIND, LEFT, and ...
|
| Copying by dragging down formula | 9/16/2009 |
Q: I'm trying to copy a formula by dragging it down to subsequent cells. The formula is =left(B2,2) ...
A: You need to use ABSOLUTE REFERENCES in your formulas. A1:B1 should be $A$1:$B$1 Let me know if you ...
|
| converting letters to numbers | 9/16/2009 |
Q: I found something relevant to what I'm looking for, but I can't quite figure out how to change the ...
A: You could set up a VLOOKUP table and give each level a value: 2c, 1 2b, 2 2a, 3 3c, 4 etc. Then ...
|
| Complicated Sum | 9/16/2009 |
Q: I have a spreadsheet that looks like a b c d Week 1 totals Week ...
A: Here's what I would do: replace your "tick" with a 1, and leave the cell blank (or 0) for "no tick". ...
|
| Using Sumproduct with 2 criteria | 9/16/2009 |
Q: I'm trying to perform a count in our database which meets either of 2 criteria - ie. count times ...
A: OR is a function. =OR(A1,B1) Is the same as saying IF A1=TRUE or B1=TRUE then MyValue=TRUE Let ...
|
| Connecting a diagram to a table | 9/16/2009 |
Q: How can I connect a diagram to a table in a separated sheet, so that the diagram will automaticlly ...
A: You can refer to values on another sheet as: =SheetName!A1 Where SheetName is the name of the ...
|
| EXCEL Compare | 9/15/2009 |
Q: I have two sheets in one excel file. The data in both sheets needs to compared and mark the matches ...
A: Use the MATCH function. If the value exists, it will return the index (location) of the value, ...
|
| Copying Order of a Formula | 9/15/2009 |
Q: I wrote formulas as indicated below under the numbers. Each row will have 3 sets of formulas for one ...
A: Don't use copy and paste. Use AutoFill and give it enough to see the pattern. For example, if you ...
|
| Consolidation formula | 9/15/2009 |
Q: I am using Excel 2003 and using a consolidation formula to show dat on sheet 2 that is on sheet 1. ...
A: You could say: =IF(A1=0,"",A1) This would display a BLANK if A1 is zero. Let me know if you have ...
|
| Cell Referencing | 9/15/2009 |
Q: Using cell reference I have created a Master sheet, which contains data of sheet, A, Sheet B & Sheet ...
A: If you insert rows or columns INSIDE of the range Excel generally increases the references to that ...
|
| Conditional Formula in a VLookup | 9/14/2009 |
Q: I am using a vlookup to pull data from a master schedule to a sub-schedule. There is always a ...
A: How about: IF(X1=0,"",X1) In this example, X1 is your existing VLOOKUP statement. In other words, ...
|
| Need ISTEXT Formula Assistance | 9/14/2009 |
Q: I need assistance with a formula that would look at cell AG5 and if there is "#N/A" in this cell it ...
A: Use the =ISNA() function to determine whether or not the cell is #N/A or not. It’s not text, it's a ...
|
| Excel | 9/11/2009 |
Q: I'm trying to create a spreadsheet for salespeople to calculate product pricing without showing the ...
A: You can set up a hidden column and then lock the spreadsheet to prevent people from viewing it - but ...
|
| assigning values to letters in excel | 9/11/2009 |
Q: HELP! I am trying to compile a report identifying how many people responded "yes" and "no" to given ...
A: Sure. You could make another column to change Y into 1: =IF(A1="Y",1,0) And then do the same thing ...
|
| Transfer of data from one spreadsheet to another in the same work book | 9/11/2009 |
Q: I am constructing a timesheet workbook and I want to list employees on sheet One,(Rates) in column A ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Numbers w/o .0 | 9/10/2009 |
Q: I have a large data base file and I need the numbers to show up as 1234, not 1234.0 The fields are ...
A: Just select your data and click on the "DECREASE DECIMAL PLACES" button on the toolbar. Let me know ...
|
| Conditional formatting | 9/10/2009 |
Q: I saw your online video tutorial about extending conditional formatting in 2003 using VBA. I would ...
A: Sure, you could use VBA to read the format of one cell and use it to change the format of another. I ...
|
| Multiple IF Formulas | 9/10/2009 |
Q: i can't seem to have a result to the following if combined in one cell: IF value of a certain cell ...
A: =IF(A1<=.25,0,IF(A1<=.75,2,IF(A1<=1,1,-1)))) Try that. A value outside your range will give you -1. ...
|
| excel & chronological organization | 9/9/2009 |
Q: I have created a spreadsheet on Excel X:for mac to track the archives of an art gallery's exhibits. ...
A: Sure, as long as you have the dates in the same column, you can just sort by that column. Let me ...
|
| Sorting Alphatical titles not words | 9/9/2009 |
Q: In Excel 2000, sorry, maybe there is a way in updates. Here goes: How do I select a sort type? ...
A: You'd need a custom function to have Excel ignore "a" and "the" in song titles. It's not a feature ...
|
| Excel COUNT formula | 9/8/2009 |
Q: I am using Excel 2000. I have a column with one number (from 1-16) in each cell. I would like to ...
A: This is in response to your comment: "The answer was not helpful at all. I already knew what Excel ...
|
| Time/conditional formatting | 9/8/2009 |
Q: I have limited experience so please bear with me. I have a report that is copied and pasted into an ...
A: There are MANY ways to do this, but assuming you have your date/time value in A1, then set B1 to: ...
|
| Back up work on Excel | 9/7/2009 |
Q: I am using EXCEL to create a database to catalog my collectibles. When I complete this project I ...
A: You generally can't just save file to the CD drive. Assuming you have a CD-R or CD-RW drive (that ...
|
| Excel 2007 | 9/3/2009 |
Q: When I close excel and reopen it the default open file goes to My Documents. Can it be changed to ...
A: No, but you can change the default file location by clicking on Office > Excel Options > Save. ...
|
| Excell Time Format | 9/2/2009 |
Q: i need help converting this times (11:24:00a) (12:37:00p) into regular excell format & military ...
A: Right-click, Format Cells, TIME, look for the 24-hour time format. Let me know if you have any ...
|
| formatting | 8/30/2009 |
Q: I have a billing ledger, I need the 30 days past due accounts to turn red, my dates read as follows ...
A: The easiest solution would be to create a cell that shows the NUMBER OF DAYS this invoice is late ...
|
| Date change in Excel | 8/29/2009 |
Q: I have this strange thing that happen to my Excel sheet. I used to open the Excel sheet on two ...
A: I haven't used Windows 7 yet, however, it sounds like something that you'd set in the REGIONAL ...
|
| skipping rows of data | 8/29/2009 |
Q: I have 500 years of paleoclimate data (9000 rows by 30 columns). I've got it to where I need to ...
A: Kerry, this would be MUCH easier to do in a database program like Access. Excel is just not designed ...
|
| Exel Counting Not to Exceed Question | 8/28/2009 |
Q: I am an instructor. Part of my student’s grades come from their attendance. Thus I keep an excel ...
A: I would use another cell and say: =IF(A1>50,50,A1) Assuming your calculation is in A1, it will ...
|
| hours to mins | 8/28/2009 |
Q: I asked this of a different Expert and needed to ask a f/u question - but he is now unavailable. I'm ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Excel 97 with Vista os | 8/28/2009 |
Q: I recently bought a Dell Inspiron 546 with Vista. I installed Excel 97 and tried to copy documents ...
A: If you burned those files to a CD they might be marked READ ONLY even if you copy them back to the ...
|
| Calculations in Excel | 8/27/2009 |
Q: I have a spreadsheet where I have a series of calculations. The totals are in Column D. I have ...
A: This sounds like a rounding error. Make sure you're showing enough digits after the decimal points ...
|
| Excell Formulas | 8/27/2009 |
Q: what formula do I use if I have 2,100 people I have scheduled for interviews and I have two columns ...
A: That's just a matter of simple math. I'm assuming you're placing a "1" in each column representing ...
|
| Help With Time Function | 8/27/2009 |
Q: I have time ranges listed in four cells as 7 A.M. - 9 A.M., 11 A.M. - 1 P.M., 4 P.M. - 6 P.M., and 8 ...
A: Instead of putting "7 AM - 9 AM" in one cell, why don't you put "7 AM" in one cell, and "9 AM" in ...
|
| if then statements from a drop down list that comes from range. | 8/26/2009 |
Q: How do I populate a cell from a selection in a drop down list in the previous cell. I would like a ...
A: Link your drop-down box to a specific cell, then use an IF statement to set the value of the second ...
|
| Cell Shading | 8/26/2009 |
Q: I am making a holiday sheet for staff, as I place the dates in the cells, I would like it to shade ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Increasing Date by One Day in Non-Consecutive Cells | 8/26/2009 |
Q: .. I'm using Excel 2002. I have a large spreadsheet that has a date every 10 rows that increases by ...
A: You just have to select enough cells so that Excel can see a pattern. Then you can just Autofill ...
|
| help! | 8/25/2009 |
Q: I have 2 worksheets: both have the same part numbers but each have different costs for the same ...
A: Well, which one is the "correct" price? What criteria should Excel use to determine this? You ...
|
| Conditional Formatting | 8/25/2009 |
Q: I am using Excel 2007. A lot of 2007 conditional formatting seems color dependent. Is it possible ...
A: To the best of my knowledge, you can only use the built-in color and icon sets in Excel with ...
|
| If function | 8/25/2009 |
Q: Hey Rick, I am trying to put a "IF, Then" formula on a spreadsheet I am currently working on. ...
A: That's not too hard: =IF(A3="w",A1-A2,IF(A3="m",A1,"")) That should do it. You can use the IF ...
|
| Time/conditional formatting | 8/25/2009 |
Q: I have limited experience so please bear with me. I have a report that is copied and pasted into an ...
A: You could do this easily if it was JUST the time, but since you've got the time AND date in one ...
|
| copy to new worksheet and keep formulas? | 8/25/2009 |
Q: Is there a way in Excel 07 to copy a spreadsheet to a new worksheet (not new tab on same spreadsheet ...
A: My apologies. I probably should have stated that you have to have that file OPEN in the current ...
|
| Counting comma delimited items in numerous cells in one column against another identifier (zip code) in another. | 8/24/2009 |
Q: I'm using Excel 2003. Okay, I have a worksheet that has one column for the zip codes of numerous ...
A: Well, you could use the FIND function to see if each search term is in each description… but you'd ...
|
| Formula | 8/24/2009 |
Q: I'm working on Vista, excel 2007 and I'm trying to write a formula that basically assigns numbers to ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Excell | 8/24/2009 |
Q: I am a novice when it comes to excel. I am using vista and am wishing to make a simple (well ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Conditional formatting | 8/22/2009 |
Q: I am using excel 2003 and I have have a spreadsheet which is populated by someone else then sent to ...
A: You're going to need some custom VBA to accomplish this. You could EASILY just create a third column ...
|
| how to hide password while typing | 8/22/2009 |
Q: I want to protect the access to some data, so i prompt with inputbox(), but while typing the ...
A: You can't do it with an inputbox, but you can create a password FORM with a single text box and ...
|
| copy to new worksheet and keep formulas? | 8/22/2009 |
Q: Is there a way in Excel 07 to copy a spreadsheet to a new worksheet (not new tab on same spreadsheet ...
A: Open both Workbook files. Right-click on the sheet tab and select MOVE OR COPY. Click on CREATE A ...
|
| Adding Commas | 8/21/2009 |
Q: I need to add a comma after each word in a cell. For example. Cell B2 has the work 14kt White Gold. ...
A: Use the SUBSTITUTE command: =SUBSTITUTE(A1," ",",") This will replace the spaces in A1 with ...
|
| modified workdays function | 8/21/2009 |
Q: I need to be able to determine the number of days between two dates, while excluding dates from a ...
A: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if ...
|
| Copying formulas with an odd increment | 8/17/2009 |
Q: I'm trying to copy a set of 3 formulas down a column while incrementing the cell reference by one ...
A: If you create enough cells so that Excel can see your pattern, you should be able to AUTOFILL them ...
|
| Count of the number of formatted cells | 8/15/2009 |
Q: Would appreciate it if you could help. I have a spreadsheet that contains data which is highlighted ...
A: I don't believe that any built-in function like COUNTIF will tell you the color properties for a ...
|
| timesheet calculations | 8/12/2009 |
Q: I would like to know if there is an excel formula I can use to calculate a timesheet.
A: It's not just ONE formula, it's knowing how to set up the timesheet. I cover timesheets ...
|
| Conditional formatting | 8/12/2009 |
Q: I saw your online video tutorial about extending conditional formatting in 2003 using VBA. I would ...
A: Carey, I used SELECT CASE statement in my example, but you could very easily just use an IF THEN ...
|
| IF Function using range names | 8/12/2009 |
Q: I am wanting to calculate "regular_pay" by using the IF Function with the range names I have ...
A: The problem is probably just that you have your named cells in quotes. Get rid of the quotes and see ...
|
| Adding | 8/7/2009 |
Q: When working with numbers in Excel I used to be able to highlight a column of numbers and the added ...
A: In Excel 2007, right-click on the Status Bar and make ssure SUM is checked ON. Let me know if you ...
|
| Printing nonadjacent cells in Excel 2003 | 8/6/2009 |
Q: I would like to print nonadjacent cells on one sheet in Excel 2003. Whenever I select these cells ...
A: I don't believe that's possible the way you're doing it. You could create a summary sheet with the ...
|
| Conditional Formatting - Training Matrix | 8/4/2009 |
Q: I am designing a training matrix and would like to make use of conditional formatting to highlight ...
A: See this tutorial. Perhaps it will help you: ...
|
| Merging cell contents | 8/1/2009 |
Q: I need to combine the contents for two cells into one cell and delete the original cells without ...
A: Make your concatenation formula in column C, then copy the whole thing and paste it into column D ...
|
| Highlighting Cells (Conditional formatting) | 7/31/2009 |
Q: is it possible to create a rule that allows the cells on one sheet to be highlighted depending on ...
A: Yes, it's possible, but not without some programming. Here's a similar tutorial that might help you ...
|
| Change rounding rule of 4/5 to 5/6. | 7/27/2009 |
Q: I need to round .5 or less down and .6 or greater up to the closest integer, such as 4.5 rounded to ...
A: You could do something like this: =IF(A1-INT(A1)<=0.5,INT(A1),INT(A1)+1) This basically figures ...
|
| Hyperlinks | 7/26/2009 |
Q: I have two quick questions about hyperlinks in Excel. First, if I want to copy a cell from another ...
A: When you link to another cell, you don't get the hyperlink. I don't know how you can get it to link ...
|
| Problem with a SORT | 7/24/2009 |
Q: In a distribution list, one column contains Addresses and Street names. For one given Street, I ...
A: You'll have to separate the street number from the rest of the street name. Not very easy to do. ...
|
| Excel Auto Populate Worksheet | 7/22/2009 |
Q: I have an Excel sheet that I enter information in on particular contracts/sales, I copy a new sheet ...
A: The only easy way to do this without programming is to COPY and PASTE a LINK to the data from the ...
|
| Counting responses from IF function | 7/22/2009 |
Q: I use windows 2000 version of excel. I need help with counting responses to an IF function. I have a ...
A: Why can't you add up the 1 values? What is your function right now? If the cell has a 1 in it, and ...
|
| Excel Formula | 7/22/2009 |
Q: I am trying to write a formula similar to this =if(h2=today,send email) is this possible?
A: You can use the IIF function to perform If/Then analyses on your data. Please see the following ...
|
| hit a wall in excel :( | 7/21/2009 |
Q: I am working on an Excel spreadsheet and am running into some roadblocks with some formulas to ...
A: In your VLOOKUP statement, use the EXACT MATCH option: =VLOOKUP(A1,MyTable,2,FALSE) This will ...
|
| Excel Drop Down List. | 7/21/2009 |
Q: hoping you can help. I know how to set up a drop down list, but I want to be able to use that list ...
A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
|
| Excel | 7/17/2009 |
Q: Greetings! I'm trying to just edit text written within each cell in a column...but as I click on ...
A: I've never heard of this before. Did you make this sheet or did someone give it to you? It sounds ...
|
| Excel Number Sequences | 7/15/2009 |
Q: I want to put a formula in an excel spreadsheet which can be used to display prescription numbers. ...
A: You could use VLOOKUP, MATCH, and/or INDEX to figure out the cell index of the current prescription ...
|
| Looking up and returning data | 7/15/2009 |
Q: I've tried various different functions but nothing seems to work. I have an excel workbook with two ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Problem with Sort/Paste Macro | 7/14/2009 |
Q: The error when I try to sort/paste into the other file is this: A formula or sheet you want to move ...
A: This happens if you already have an object (formula, function, sheet, etc.) with the same name. Open ...
|
| Formula help! | 7/14/2009 |
Q: I want to count the number of 'Y''s in colum B only where colum A=C10. Can you help?
A: You can use the SUMIF and COUNTIF functions to add up or count values based on specific criteria, ...
|
| excell ? | 7/13/2009 |
Q: how can I keep a list in excel automatically alphabetize...without using the sort & filter every ...
A: To my knowledge, you can't do it without some custom VBA programming or a macro. Let me know if you ...
|
| conditional formula | 7/11/2009 |
Q: I have Excel 2000 and want to get a formula for a cell#1(A1) that will use the data in cell#2(A2) ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| averageif does not contain | 7/10/2009 |
Q: wondering if you can answer the following for me. I have an Excel 2007 sheet into which I export ...
A: You could use the FIND or SEARCH functions to determine if a text string (like 'credit') appears ...
|
| copying cells from one sheet to another | 7/10/2009 |
Q: I have data that is in row 28 of three worksheets. Worksheet 4 is a summary sheet. Is there a way to ...
A: Not automatically, no. You could write a macro to do it, but I couldn't instruct you in how to do ...
|
| averageif does not contain | 7/9/2009 |
Q: wondering if you can answer the following for me. I have an Excel 2007 sheet into which I export ...
A: Well, you could make another column that's based on those criteria and will either contain the VALUE ...
|
| copying cells from one sheet to another | 7/9/2009 |
Q: I have data that is in row 28 of three worksheets. Worksheet 4 is a summary sheet. Is there a way to ...
A: Just COPY and PASTE SPECIAL > PASTE LINK the data. Then it will automatically keep itself updated. ...
|
| Conditional Forumla Question | 7/8/2009 |
Q: Good Day Thank you for taking the time to answer my question What I am trying to do is to know 90 ...
A: Just make another field and set that field equal to: =IF((A1-90)<=TODAY(),1,0) Where A1 is your ...
|
| If/Then Statement Help | 7/8/2009 |
Q: I am trying to write an if then statement. I would appreciate any of your help, thanks in advance. ...
A: Depending on how many conditions you're talking about (there is a limit) then this would just be a ...
|
| Listing text in cells by highlighted color | 7/7/2009 |
Q: I have a worksheet (A1:FN75)where I've developed a basic process map, made up of several cells with ...
A: Stuart, while I'm sure this is certainly possible, I don't have the code for it offhand, and without ...
|
| formats in excel | 7/6/2009 |
Q: I have a dilemma I need to take a number and divide it by hours and minutes to result in how much ...
A: What does your data look like now. You have 55 hours represented as: 55:00 If you format this as a ...
|
| Index Match Function Help | 7/6/2009 |
Q: I need to have an INDEX MATCH function return a value AND FORMATTING (color coded numerical ...
A: INDEX, MATCH, VLOOKUP, and all of those related formulae only return VALUES, not formatting ...
|
| Listing text in cells by highlighted color | 7/3/2009 |
Q: I have a worksheet (A1:FN75)where I've developed a basic process map, made up of several cells with ...
A: I'm not quite sure I understand what you're trying to do. Do you want to change the color of a cell ...
|
| grouping data/rows by text in columns | 7/3/2009 |
Q: i want to group cells/rows together that have the word "development" or dev in the second column. ...
A: Just select both columns, press the TAB key to move over to column 2, and then hit the SORT button. ...
|
| computng bonus | 7/1/2009 |
Q: I need to average bonuses of individuals that make over $50,000 to 3% and individuals who make less ...
A: Yes, IF is the function you want. If the bonus is in A1, you would say: =IF(A1<50000,0.015,0.03) ...
|
| IF ELSE | 7/1/2009 |
Q: Below are the two difference "If" conditions ====================================== If ...
A: Use the OR function. =IF(OR(A1="RED",A1="YELLOW"),ValueIfTrue,ValueIfFalse) Let me know if you ...
|
| How NOT to calculate, or how to hide it | 7/1/2009 |
Q: Greetings! I have a spread sheet for a time card. I added some functions, and now it converts 12 ...
A: You can turn automatic calculations off, but then you'll have to manually hit F9 to calculate your ...
|
| EXCEL split 1 row into multiple rows | 6/30/2009 |
Q: How can I (without using VBA/Programming) split one record into multiple records ? EXAMPLE : ID1 - ...
A: WITHOUT programming, I don't think it could be done unless you made a REAL complex string ...
|
| Excel filling a column | 6/29/2009 |
Q: I need to fill a column with ascending numbers say 1 to 9 and then continue with descending numbers ...
A: If you need to do this on a regular basis, just record a MACRO that puts them all in place for you, ...
|
| Giving Different Values to Numbers | 6/28/2009 |
Q: this may be a stupid question, but I can't seem to find the answer anywhere. I'm trying to perform ...
A: You just want to transpose a 1 to a 5, and that's it? You could just do this with a little math. ...
|
| Add 2 negative numbers-have results display as a positive | 6/27/2009 |
Q: This is going to be a little hard to explain but here goes. I have a column of negative numbers in ...
A: I'm not exactly sure what you're trying to do, but if this helps, you can use the ABS function to ...
|
| how to make the title not count in the observision | 6/25/2009 |
Q: i have a excel file which the first row is a title and all the data value start from the second row. ...
A: Don't include row 1 in your COUNT formula. Let me know if you have any other questions or comments. ...
|
| Excel formula | 6/25/2009 |
Q: We are looking for a way to set a formula in an excel sheet we use to track issues. Can you help? ...
A: "Days Old" is simply the difference between two dates. For example, if you have Jan-1 in A1 and ...
|
| Spreadsheet question! | 6/24/2009 |
Q: At work I do a spreadsheet concerning, our part codes, everyone of our part codes has another code ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Excel | 6/23/2009 |
Q: What formula do I use to calculate the number of weekdays between 2 dates? Date Entered Date ...
A: Use NETWORKDAYS. See this tutorial: http://www.599cd.com/tips/excel/workdays?key=AllExperts Let me ...
|
| Drop Box depending on selection from Previous Drop Box | 6/22/2009 |
Q: I have a drop box set up and would like to use another drop box depending on what was selected from ...
A: I do not know how to code this in Excel. I can show you how to do it in Microsoft Access though: I ...
|
| column alignment | 6/21/2009 |
Q: I have a Dell with XP and Excel I make informational statements on Excel and have many columns with ...
A: Right-click on the cells (or column) select FORMAT CELLS and then pick the ACCOUNTING format. Let ...
|
| Forgot the Security Code Of Spread Sheet | 6/20/2009 |
Q: I am working on Excel since last several years, I used security code on a spread sheet to open the ...
A: You would need to look into a 3rd party program to bypass the security. I don't know of any ...
|
| Conditonal Formatting In Excel | 6/19/2009 |
Q: I have two columns of numbers. The numbers imported into column B change on a daily basis. In ...
A: This tutorial might help you: ...
|
| Auto Formulas | 6/18/2009 |
Q: Im working on a work book where every week I have to add a new spread sheet to my workbook. How can ...
A: The easiest way to get data from one sheet to another is to COPY it, then select PASTE SPECIAL and ...
|
| calculating percentages in excel | 6/18/2009 |
Q: I have entered the formula =$G2-$G2*H$2 in Cell I2(Discounted Price Column) to calculate a ...
A: Without programming a macro, the easiest thing would be to just use the AUTOFILL feature to copy the ...
|
| How to count if two cells contains similar text | 6/17/2009 |
Q: Good day. I have two columns, displaying 200 patients' hearing results for the right ear (a) and ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Excel Date conversion issue using Text to column | 6/15/2009 |
Q: I am trying to break up a date format 9/9/2008 into the Day/Month/Year via the text to column ...
A: Why not just use the YEAR, MONTH, and DAY functions? =YEAR(A1) =MONTH(A1) =DAY(A1) There are many ...
|
| Lapsed Time in Excel | 6/15/2009 |
Q: I have a data dump to excel from a SAS program. Two different date time stamps. One is in military ...
A: I don't know what format your times are in. They MIGHT work. I'd have to see a sample of the date to ...
|
| Completion Date | 6/10/2009 |
Q: I am using a study guide and I want to track my progress of completed tasks with a column that shows ...
A: Nope. You'll have to actually type the date in. The NOW function always shows NOW. Let me know if ...
|
| Excel 2000 | 6/8/2009 |
Q: I have column with formatted number cells (1 decimal place). When wanting to locate the next ...
A: You can use the ISBLANK function, and enclose that in a NOT function if you want to find the next ...
|
| VLookup Problems | 6/7/2009 |
Q: I have a spreadsheet that contains thousands of customer numbers (such as 1124527) with information ...
A: All I can suggest is that you're not doing the VLOOKUP right, but without seeing your sheet, I can't ...
|
| Excel IF formula | 6/5/2009 |
Q: I am constructing an IF statement for a validation tool to check % growth in funding year on year. ...
A: To see if a value is within a range like that, you'll need to use the AND function inside your IF ...
|
| Sorting Data | 6/5/2009 |
Q: I have a sheet that I created that calculates data for a group of surveys received by a team. I have ...
A: You have to select ALL of the data. If you sort by only 1 column, only THAT column is sorted. Let ...
|
| excel tables | 6/4/2009 |
Q: the column on the excel table are often called what?
A: Columns. :) I don't know another name for them. Let me know if you have any other questions or ...
|
| countif formulas | 6/3/2009 |
Q: 1)I need a formula to count the number of calls made by technician 3, where the hours logged were ...
A: You're going to have to make a third column with an IF function to see if BOTH of your conditions ...
|
| Excel formula | 6/3/2009 |
Q: I have a formula that is simply adding up several cells. I don't want to sum of these cells in the ...
A: You could use SUMIF function. You can use the SUMIF and COUNTIF functions to add up or count values ...
|
| graphing grades | 6/3/2009 |
Q: I am a teacher from New Zealand and have a small problem. I am collating whole school grades to make ...
A: Use the COUNTIF function. Let's say in column A you have your values, and you want to see how many ...
|
| excel if statement | 6/2/2009 |
Q: Richard, thanks in advance for you assistance. My question is, I'm wanting to count the number of ...
A: I'm sorry, but I don't understand what you mean. If you're trying to count blank cells, why not use ...
|
| Calculate workdates in excel | 6/1/2009 |
Q: The problem is I need to include saturdays, I haven't been able too. Can you help me?
A: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if ...
|
| Excel 2003 | 5/27/2009 |
Q: What is the limit of the IF statements? If so, what is the remedy? Many thanks. Sincerely, Victorino
A: I believe the limit for any function (or cell data, for that matter) is 255 characters long. If you ...
|
| VLookup | 5/27/2009 |
Q: Sheet A has 1,000 employee names with data. On another sheet I have the employee names with their ...
A: VLOOKUP is the way to go, but each of the items must be UNIQUE. You can use the VLOOKUP or HLOOKUP ...
|
| Highlighted contents of cell | 5/25/2009 |
Q: I'd like to know how to highlight the contents in cell. I tried several, but any didn't work. I ...
A: There is no "highlight tool" in Excel. You just click and drag on or in the cells to select items ...
|
| excel | 5/23/2009 |
Q: how to apply if condition in excel
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Excell-Time Sheet | 5/22/2009 |
Q: Hey there, hope you could provide a simple answer for me, if i have a column with 09:00am and ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| If formula ?? | 5/22/2009 |
Q: I am relatively new to excel and want to write a formula to do the following; If cell A4 = USD then ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| setting up A/R info | 5/18/2009 |
Q: I have a column of A/R accounts by past due days. I need to parse out these by categories such as ...
A: Setting up an Aged Accounts Receivable is not terribly hard. Just use the BETWEEN keyword in your ...
|
| conditional formatting? | 5/14/2009 |
Q: First I want to thank you for your help in this matter. I have an Excel (2007) sheet in which I ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| CSV File saving | 5/12/2009 |
Q: I have a spreadsheet with stock codes in with leading zeros on the product code. Is it possible when ...
A: If you format the column as TEXT (right-click, format, TEXT) then export as CSV, it will retain the ...
|
| I am trying to insert the product of A and B in C ONLY if both cells are not blank. | 5/12/2009 |
Q: I am trying to insert the product of values in column A and B in C ONLY if both cells (in colums A ...
A: Use the IF function along with the ISBLANK function. You can use the IIF function to perform ...
|
| Trim last three characters from a string | 5/11/2009 |
Q: I have a string , I need to trim the last 3 characters of the string.. Is there any VBA function ...
A: You can use the LEFT and LEN functions: =LEFT(A1,LEN(A1)-3) Let me know if you have any other ...
|
| Summing various quantities with an identifier | 5/9/2009 |
Q: I'm working on my wedding gift table and trying to calculate how much money is being spent on gifts ...
A: You can set up SUBTOTALS for each room. See: ...
|
| Import pdf files into excel | 5/7/2009 |
Q: I have an excel spread sheet that contains part nos, description and prices. i have also created a ...
A: Excel doesn't handle these kinds of embedded objects WELL. You would be better off using a database ...
|
| Connecting two excel columns | 5/5/2009 |
Q: I have two excel columns in a worksheet ( say a set of pincodes in a column and the corresponding ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| How do i populate cells with txt based on what is in another cell? | 5/5/2009 |
Q: I am trying to create a purchase order template where by selecting a supplier from the drop down ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| 'OR' command | 4/30/2009 |
Q: I'm a young IT guy who is trying to build up a loan form for my bank. Specifically the issue I'm ...
A: Personally, I would use a VLOOKUP statement for this with a list of cities and states. You can use ...
|
| Time intervals | 4/29/2009 |
Q: I have a spreadsheet with multiple columns that have MM/DD/YY HH:MM:SS. I need to take one column ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| find the missing parts | 4/28/2009 |
Q: i have 2 parts in a spreadsheet and 4 parts in a second spresdsheet I need a formula that will look ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Formulas | 4/27/2009 |
Q: i'am trying to create a formula to subtract a quantity based on color & size. i have like 6 diferent ...
A: Without seeing your sheet, or your data, I can't tell what you're trying to do. Perhaps you could ...
|
| finding first name from two worksheets | 4/27/2009 |
Q: I have a worksheet [Data] with all employee information. Columns A & B have surname and first name. ...
A: Combine both names into Column C and then search on this. =A1 & " " & B1 Let me know if you have ...
|
| VLookup value | 4/26/2009 |
Q: If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values ...
A: You can use ISERROR to return a True or False, or even bundle that with an IF and then put whatever ...
|
| Timecard Hours worked | 4/24/2009 |
Q: I'm using excel as a time card. Example: Monday Alex 8:00 17:00 .5 8.5 I use ...
A: I would break this up into two separate columns, or put a zero in that field as a default. Let me ...
|
| Rounding to nearest 10 in excel | 4/23/2009 |
Q: I have tried multiple formulas but can't get the results I need. I am trying to multiple one cell ...
A: The normal rounding in Excel should be to round a number UP if it ends in a decimal 5 or higher. If ...
|
| Excel changes my cell formatting | 4/22/2009 |
Q: I'm recording data for charting in Excel 2007, column C has date and time in it. Date must be ...
A: If you enter a DATE even though your format is h:mm, Excel is going to change the format. That's ...
|
| vlookup issue | 4/21/2009 |
Q: vlookup returns #N/A and this is my formula =VLOOKUP(F2,CODES!$B$1:$D$17,3,TRUE). The value, F2, is ...
A: Ignore my previous answer if you received it. It was wrong. I'm sorry, but I don't know what the ...
|
| Vlookup | 4/20/2009 |
Q: I just cant seem to figure out why database is in this forumla???? Trying to make sense of this ...
A: Well, I can't figure it out either without seeing your sheet. Perhaps it would help you to know how ...
|
| averageif formula | 4/19/2009 |
Q: I can get the formula to work if I put in a number like: =(averageif(A1:A4,">50",B1:B4)), the ...
A: You can't do that directly with AVERAGEIF. What you would have to do is make a second column next to ...
|
| Absenteeism Formula | 4/16/2009 |
Q: Good Morning Richard, I am currently working on an Excel Spreadsheet for Absenteeism at work. I ...
A: I'm not quite sure what you're trying to do, but if I read this correctly, you should be able to use ...
|
| How do I add hours to a particular date? | 4/13/2009 |
Q: I have a format - Date and Time (13.04.09 06:00 PM) and I just wanted to add 53 hours and 30 minutes ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Calculation In Excel | 4/13/2009 |
Q: I am doing daily reports in excel, writing a daily report is not a problem, but when I fill the ...
A: This is just basic Excel arithmetic: =A1+B1 See my Excel 101 Tutorial. Let me know if you have ...
|
| Alphabatizing | 4/12/2009 |
Q: I have a list of words in column A and a list of definitions in column B. I want to put the WORDS ...
A: No, but if you SELECT columns A and B and then sort them, they'll stay together. Or… use a ...
|
| Vlookup with codes and rates | 4/11/2009 |
Q: Im dealing with codes and rates. Currently, when I input a code on worksheet (1), it returns the ...
A: Sure, VLOOKUP can use text. You're just going to have to perform another VLOOKUP based on the city ...
|
| AVERAGEIF | 4/11/2009 |
Q: I am trying to use the AVERAGEIF function with two conditions. Below i have written a quick ...
A: I don't quite understand. You want to average up ALL of the scores where revisit is zero, or you ...
|
| Tracking Membership Durations | 4/11/2009 |
Q: I have a spreadsheet for members of a group i am part of and it contains the date they joined. Is ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| DATE DIFFRENCE | 4/10/2009 |
Q: HOW TO WE CAN KNOW DIFFERENCE BETWEEN TWO DATES IN SINGLE CELL.ie, a1 IS 16/07-90 AND B1 IS ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Excel: Linking cell contents and formula | 4/8/2009 |
Q: I want to link cell contents from sheet1 to sheet2 automatically. I did some commands, but if it is ...
A: You are correct. That's how you do it. Or you can COPY and then Edit > Paste Special > Paste Link. ...
|
| cell values | 4/7/2009 |
Q: How do I keep the cell numbers the same as displayed when I copy and paste? Right now when I paste ...
A: You'll need to INCREASE DECIMAL PLACES using the toolbar button. Let me know if you have any other ...
|
| Excel formula | 4/6/2009 |
Q: Is there a formula I can use in Excel that will calculate current date minus 45 days?
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Creating a quick look Excel tracker | 4/4/2009 |
Q: Richard, I'm trying to create a tracking sheet that will allow me to track the ongoing status of ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| countif(and formula question | 4/3/2009 |
Q: Have a column of dates, and would like to count number that fall within specific range. ex: Dates ...
A: This would be easiest done with a second column. Make an IF statement to see if your date is within ...
|
| Excell field expansion | 4/2/2009 |
Q: Lets say I have a row dedicated to one contact which includes things like name, number, company, ...
A: You would need a second table to store the details, and a single field (preferably an ID) to relate ...
|
| Printing out of Excel | 4/2/2009 |
Q: We have a list of contest classification numbers in a spreadsheet. We need to print them out to ...
A: Use Page Layout view and set an appropriate Zoom ratio. Let me know if you have any other questions ...
|
| Need help with Excel Formula | 4/1/2009 |
Q: First, thank you for taking the time to review my question. I have a spreadsheet that calculates my ...
A: Keith, use the IF function, and simply calculate the price for each tier. Here's a similar answer I ...
|
| Excel Formulas | 4/1/2009 |
Q: I am working with 2 different excel spreadsheet worksheets. Example: Worksheet 1 has column D which ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Screen Split (excel 2007) | 4/1/2009 |
Q: I need to be able to see the first 2 rows which have the heaings. and the bottom row which has the ...
A: I don't believe you can split it twice, sorry. You could use ACCESS instead of Excel and use ...
|
| Conditional Formating | 3/31/2009 |
Q: I have created a spreadsheet that measures several KPI (key performance indicators) for our each ...
A: Sure, you would probably need an IF statement to determine the number of parameters that meet your ...
|
| excel formula | 3/30/2009 |
Q: i have a range of values as in example below.All data are found in column A starting from cell 1. ...
A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
|
| Separating number from text | 3/30/2009 |
Q: which formula do I use to separate the number from the text in a cell. Eg. I want to separate 0.6 ...
A: You'll have to use text string manipulation functions like LEFT and RIGHT. Let me know if you have ...
|
| Excel Dates | 3/27/2009 |
Q: In my spreadsheet I have entered dates in 07 Jan 2008 format. Now I want to change the entire date ...
A: Select the column. Right-click on it. Select Format Cells. Change the date format accordingly. Let ...
|
| Converting Times | 3/27/2009 |
Q: I have a text file that has times in this format "6:11:00a " or "1:04:00p". I need to re-format or ...
A: You'll have to use string manipulation functions like LEFT and RIGHT to break that string up into ...
|
| beginner - using excel to calculate cost | 3/23/2009 |
Q: i'm a new user of Excel and i got a task from my boss to create a complicated (if possible) ...
A: Sure it's possible. You'll probably need to know how to use the IF function though if you have to ...
|
| Excel lost my function! | 3/22/2009 |
Q: I volunteer on All Experts in my own field. Trying to fix something, I reinstalled MSOffice on ...
A: Pat, I've never encountered this problem before, but it sounds like Excel is missing an object ...
|
| CONDITIONAL FORMATTING HELP NEEDED | 3/21/2009 |
Q: I am trying to use conditional formatting to make a worksheet tell me if someone is out of currency ...
A: You just need to figure out the difference in days. Make this its own column, then change your ...
|
| Excel - If than functions | 3/20/2009 |
Q: We are a chamber of commerce with a complicated dues structure. Can you help me figure out how to ...
A: Eric, sorry for taking so long to get back to you. I loved your question so much, however, that I ...
|
| Multiplying selections in combo box and displaying result in text box in a userform | 3/20/2009 |
Q: I've prepared a user form with two combo boxes and one text box. also i've added the list of items ...
A: Sure: TextBox1 = ComboBox1 * ComboBox2 Put this in an AfterUpdate event for either or both of your ...
|
| Filtering questions answered with a yes or no in another spreadsheet (without using Autofilter) | 3/19/2009 |
Q: I have a problem, I have a list of questions which are answered only with a: "Yes", "No", "n.a.". ...
A: I couldn't explain quickly and easily how to have them all directly one after the other, but you ...
|
| Average_Excel | 3/19/2009 |
Q: I have these data : A B Week 1 2-Feb-09 76.67 67.7775 3-Feb-09 94.44 ...
A: Without reproducing the spreadsheet and trying it myself, I'm going to guess that it's just a ...
|
| Excel spread sheet | 3/19/2009 |
Q: I need to be able to type a number in to one cell on a spread sheet and have it turn any other cell ...
A: Not without a macro or some VBA code. Not that I know of, at least. Let me know if you have any ...
|
| Excel value changing | 3/18/2009 |
Q: I need a cell let suppose b1, if b1 contains 0 then replace it with value of c1(put it as value not ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Count | 3/18/2009 |
Q: Hi Tom, I have made an attendance sheet for all employees using excel @2003.I want to sum of ...
A: Sure, just make an IF function to set a cell equal to 1 if your conditions are met. Then add those ...
|
| need a help | 3/18/2009 |
Q: i am prakash, in excel while putting vlookup() it takes first maching value only..but i want to take ...
A: You can specify which column of a range you want to return. See this tutorial: You can use the ...
|
| Excel 2007 Template | 3/17/2009 |
Q: I have created an excel template and have saved numerous workbooks based off the template. I need ...
A: No way that I can think of that doesn't involve some VBA programming. Sorry. Let me know if you ...
|
| Excel to sum roll that match key word in col | 3/17/2009 |
Q: I have a spread sheet where Col A is the description of item (e.g. 'Aircon repair' and another roll ...
A: Use the SUMIF function. Assuming you have 20 rows of data: =SUMIF(A1:A20,"*repair*",B1:B20) Enjoy. ...
|
| Counting times | 3/16/2009 |
Q: I have a list of times (part of which is included below). I want to be able to count them if they ...
A: Use IF to create a new value in a second column that's either 1 or 0 if the time falls in your ...
|
| Excel SUM shortcut on mouse | 3/16/2009 |
Q: When working in Excel, I would like to add the SUM command to my mouse so I can "right click" rather ...
A: You would need a programmable mouse or special software for this. I don't think it's possible with ...
|
| Macros for conditionaling Formating | 3/15/2009 |
Q: Excel 2003 has option of only three conditional formating is there anyway to create macro to increas ...
A: You can do pretty much anything with VBA code. I'm sure you could mimic conditional formatting on a ...
|
| Custom cel format | 3/13/2009 |
Q: I have many part# in my spreadsheet, e.g 10-41, 12-42 These are converted as dates by excel e.g ...
A: Convert the cells ahead of time to TEXT (right-click, format cells) or prefix each item with a ...
|
| Re: percentages in excel | 3/12/2009 |
Q: How do I formulate an equation in excel to give me the original value if I know the current value ...
A: It's just a matter of math. 523.60 = X * 1.08 Solve for X. X = 523.60 / 1.08 There's your ...
|
| Excel 2003 | 3/12/2009 |
Q: I want to know the formula for adding hours to time. eg US time of 9:00 pm plus 14 hours = 11:00 am ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| compare two lists in excel | 3/11/2009 |
Q: I have two columns of emails. Column A is the ENTIRE email list Column B is the EXCLUSION email ...
A: This is VERY difficult to do just in Excel. In Microsoft Access you could run something called a ...
|
| Coding / if statment | 3/10/2009 |
Q: I have two excel sheets. One is an overview of company performance figures on a daily basis. the ...
A: You can pull data from one sheet to another by referring to it like this: =Sheet1!A2 This will ...
|
| Simpler way to do this | 3/10/2009 |
Q: Im currently using this IF statement: ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Multiple Selection Drop Down | 3/9/2009 |
Q: I am working with an Excel sheet and my boss has asked me to create a drop down list which would ...
A: In a nutshell, nope. Drop-down boxes in Excel aren't really designed for multiple selections. You ...
|
| Sorting in Excel without messing up row relations | 3/9/2009 |
Q: I am working in an office with multiple users working on a shared excel document. When one user ...
A: Short answer: don't use shared Excel documents. Excel wasn't built for that. Use a database program ...
|
| Excel Formatting | 3/7/2009 |
Q: How do you get an excel spreadsheet to come up with an ERR message when you type TEXT instead of ...
A: I would recommend using DATA VALIDATION and specifying TEXT as the allowed values. Let me know if ...
|
| Get Previous Date | 3/3/2009 |
Q: Would like to know how to get the Previous/Next Date for a Cell. When I Enter 02/03/2009 - It should ...
A: Just subtract 1 from your date. For example, if your date was in A1, in cell A2 you could say: ...
|
| If function | 2/27/2009 |
Q: I want to use the following if function more than 7 times, how can I and any other way. please help ...
A: How about a VLOOKUP instead? You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful ...
|
| percentages | 2/26/2009 |
Q: How to figure a percent increase of a number in a cell? Ex: 250 plus 10%?
A: If your data is in A1, you'd type: =A1+(A1*0.10) Let me know if you have any other questions or ...
|
| Formulas between worksheets in Excel | 2/24/2009 |
Q: How can I create a formula between 2 worksheets? For example: If cell B3 is $100 on sheet 2 and cell ...
A: You can reference a value on another sheet like this: =Sheet1!B2 If you rename the sheet, and you ...
|
| projects for kids | 2/23/2009 |
Q: i work for the boys and girls clubs of america and i run the computer lab. i have been trying to ...
A: Pick up an Excel book at the local bookstore and pick out examples. Take a look at my Tips & Tricks ...
|
| IF Statement | 2/23/2009 |
Q: I want to know if I can do the following. I create two list. Column A 1 - 100 is a list of items ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| excel date/year | 2/21/2009 |
Q: I have set up my books on excel, A column is the date of the bill. I am working on 2008 bookwork, ...
A: If you don't specify a year, the CURRENT year is the default. The only way around it is to change ...
|
| excel formula | 2/19/2009 |
Q: can you give me the formula to switch the order of a name? currently it's last, first and i need it ...
A: There is no simple formula for this. You're going to have to massage the data using LEFT, RIGHT, and ...
|
| Using nested IF function combined with AND, OR, NOT | 2/18/2009 |
Q: Here's what I am trying to do: I have 4 columns,A,B,C, and D. If A,B,and C are numbers then D=A+B-C ...
A: I can't write the whole thing out for you, no. That's for you to learn. You need nested IF functions ...
|
| Date Function | 2/18/2009 |
Q: I have a worksheet with inputs from several people. The dates have been put using different formats ...
A: Group together as much similar data as you can, and then use string manipulation functions like ...
|
| Countif with multiple sheets | 2/18/2009 |
Q: I have a workbook with a different sheet for every day of the month ie 31 sheets in 1 workbook. I ...
A: You'd need a count on the bottom of each page, and then count up all of those for a summary page. ...
|
| 2 Ifs in one formula | 2/17/2009 |
Q: In column F; is the hire car cost per day, J; no of days traveled, O if they hired a car and column ...
A: You can do this with nested IF statements. I can't easily follow all of your cell references, but ...
|
| combining/sorting alphanumeric data | 2/16/2009 |
Q: I have 2 columns of data-one numneric(Column A), one alpha(Column B). These refer to map numbers- ...
A: If your problem is that you need "1a" to come before "11a" then the issue is one of an alphanumeric ...
|
| Headings | 2/14/2009 |
Q: Sir, how to print a heading for all rows while printing. I want to get the heading printed for all ...
A: In the File > Page Setup options on the Sheet tab you will see a check box to print Row and Column ...
|
| Comparing two text cells | 2/13/2009 |
Q: I have a cell with a drop down of Hi,Med,Lo and another drop down cell of Hi,Med,Lo. I want to ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| the history of a cell | 2/12/2009 |
Q: I'm working as an office clerk presently and I have been asked to create a system to keep track of ...
A: Not easily. If you were using Word, you could track revisions, but that's not available in Excel. ...
|
| Convert Date & time format | 2/12/2009 |
Q: I want to know the way to convert DD-MM-YYYY HH:MM:SS to DD-MM-YYYY HH:MM. This can be done by ...
A: I tried Format Cells and it worked just fine for me. If you need to do it in a macro, you're going ...
|
| calculations | 2/12/2009 |
Q: Given: 3 cells (A1, A2, A3). A1 and A2 are manually entered data. A3 contains a formula performing ...
A: Sure. You could use an IF statement with the ISBLANK and OR functions. Let's say you wanted to put ...
|
| adding multiple time format [h]:mm in one column | 2/11/2009 |
Q: I just want to ask if you can help me with my formula or if there's a better way to solve it, here's ...
A: That's going to happen if you don't convert your times back properly into dates. See this tutorial - ...
|
| If with text | 2/10/2009 |
Q: In column B1:B138 I have a long list of names, In Column c3:c7 & column D3:D10 I have another 2 ...
A: Use the MATCH function to find out if a value appears in another column. Let me know if you have ...
|
| nested if statement | 2/10/2009 |
Q: In excel, I want to make a formula. I have data in Cell A1 in the range from 1 to 10 (including ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Protected Cells To be unviewed or unselected | 2/8/2009 |
Q: In excel I have protected some cells with using Tools>Protection>Allows users to edit Ranges, it is ...
A: You can prevent people from EDITING the data in locked cells, but not from seeing what's in them. ...
|
| Pause And Continue If No Change | 2/6/2009 |
Q: I wanted to Simiulate a day of stock trading where you could stop the progression of a line chart ...
A: How about a KeyPress event or a Button that changes a variable that your macro looks at to see ...
|
| Excell Formula | 2/6/2009 |
Q: Richard, first thank you for this help I have been racking my brain trying to figure out a formula. ...
A: I would use a series of IF statements, but you're probably going to need multiple cells so you can ...
|
| Copy Specific Cells from multiple Tabs | 2/4/2009 |
Q: I need to figure out an easier way to copy the same cells from multiple tabs into a tab in the same ...
A: Try this… Highlight all of the cells in the row that you want to copy. Click on Edit > Copy. Now ...
|
| How to open MS Excel 2007 data in MS Excel 2002? | 2/4/2009 |
Q: I want to know that suppose we have saved data in ms excel 2007 and i want to open the same in excel ...
A: There is a converter you can download from Microsoft's web site. I'm sure you can find it with a ...
|
| Conditional Copy | 2/3/2009 |
Q: I have an Excel 2003 spreadsheet with rows that have OLD part numbers and NEW part numbers. ColumnA ...
A: You can use the IIF function to perform If/Then analyses on your data. Please see the following ...
|
| #VALUE error | 2/2/2009 |
Q: The first problem that I started with was that I had a blank cell that was giving me a zero in ...
A: You can use the NZ or ISBLANK functions. Let me know if you have any other questions or comments. ...
|
| Excel Spreadsheet formula | 1/31/2009 |
Q: On an Excel spreadsheet, I have a Constant in column A, I will be entering data on a monthly basis, ...
A: You can use the IIF function to perform If/Then analyses on your data. Please see the following ...
|
| Excel linking | 1/31/2009 |
Q: I have two excel workbooks i want to be able to link the data from workbood2 into the column of ...
A: Sure… try a copy > paste special > past link. That should create a reference across workbooks. Let ...
|
| Protected Cells To be unviewed or unselected | 1/30/2009 |
Q: In excel I have protected some cells with using Tools>Protection>Allows users to edit Ranges, it is ...
A: Protection can prevent them from EDITING the cells, but not from seeing their content. Let me know ...
|
| EXCEL constant | 1/29/2009 |
Q: How do you set a cell to be a constant factor in an equation? eg. B19-B2, B20 - B2, B21-B2 etc. ...
A: You can use an ABSOLUTE REFERENCE in your formulae, like: =$A$1 That will force Excel to ALWAYS ...
|
| Excel sorting | 1/28/2009 |
Q: I am having a problem with a W98 machine that has Office 2000 on it. There is a shared Excel ...
A: Somewhere in your list you have a set of MERGED CELLS. This happens when you highlight two or more ...
|
| Multiple Worksheets | 1/27/2009 |
Q: How do I get the totals from a column on worksheet A to automatically update in worksheet D in a ...
A: You could Copy > Paste Link the information between two sheets. Let me know if you have any other ...
|
| Mulitple formulas in a cell | 1/22/2009 |
Q: Could you please explain how i can have a choice between two formulas in one cell, say if A15 =X, ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Freezing Panes in Excel 2007 | 1/21/2009 |
Q: I cannot seem to freeze the first column and the first row of my spreadsheet at the same time in ...
A: I don't have that problem. On the VIEW tab click on Freeze Panes. You'll see 3 options: Freeze ...
|
| quoting shipping costs from a table | 1/20/2009 |
Q: I am stumped... I have a list of ports and a cost associated with each port. i am trying to create a ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Basic Excel question | 1/20/2009 |
Q: Sir, How do I look at all the numbers in a column (e.g. C1:C98) and then produce a count of all the ...
A: You could use COUNTIF or even just an IF statement in the adjacent column. You can use the IIF ...
|
| excel calculation - help please | 1/17/2009 |
Q: i have an online book selling website. I need to calculate my profit margin based on the cost of ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| auto-fill from one worksheet to others | 1/16/2009 |
Q: I am working with spreadsheet that projects due dates of treatment plan review meetings that need ...
A: Yes, it is possible, but without knowing the logic behind what you're trying to do, I can't ...
|
| excel column | 1/14/2009 |
Q: How can I separate first and last name created in 1 column into 2 columns example I need "Jane ...
A: Here is an elegant solution: http://blogs.msdn.com/lauraj/archive/2004/07/08/177965.aspx Let me ...
|
| excel links | 1/14/2009 |
Q: i want to know how can i create a sheet that contain clikable links to other sheets?
A: You can create a hyperlink to jump to other locations in your spreadsheet. Click on Insert > ...
|
| Excel formula help | 1/13/2009 |
Q: I'm not a very advanced Excel user and have run into trouble with a formula. I've created a ...
A: Without seeing your data, I can't tell you what to do. You don't just want to ignore the NA errors. ...
|
| Excel Product Comparison | 1/11/2009 |
Q: I have a point of sale system that gives me reports showing how many ive sold of an item in any ...
A: Not easily, no. You could do this in a database like Access if you have the part numbers in a unique ...
|
| Cell format change - Date to Text | 1/5/2009 |
Q: I'm working with an Excel table that has dates entered into it such as 05/23/05 and have a cell ...
A: You should be able to load them just fine into Access as dates WITHOUT converting them to text ...
|
| Calculate minutes between 2 times | 1/4/2009 |
Q: I have Excel 2003, operating on Windows XP. I hope this question is simple for you. I would like to ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| EXCEL FORMULA | 1/4/2009 |
Q: I'm trying to sum values in separate cells and columns that contains negative and positive values, ...
A: You can use an IF function to determine which values are negative or positive. You can use the IF ...
|
| How to Calculate daily sales goals for each person based upon the hours worked. | 1/1/2009 |
Q: I am trying to create a sales chart. I am having a difficult time breaking up our sales goals ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Conditioning Formatting - 6 months expires to hightlight another appointment is due | 12/31/2008 |
Q: I am working on a spreadsheet and want to understand how to flat/highlight a date in a cell e.g. ...
A: Just set the conditional format to something like: Today()-180 Now if your date is more than 180 ...
|
| Excel Conditional formula | 12/31/2008 |
Q: (A) Closed Elapsed Days (Column N) = Column N - Column L (B) Closed Date (Column L) = Column L - ...
A: Use the IF function to set a cell value somewhere to something like "Green" or "Red" and then use ...
|
| Spreadsheet | 12/30/2008 |
Q: Could you please tell me how to Delete the info I have entered in my spreadsheet, Without doing it ...
A: What exactly do you want to delete? Columns? Rows? Blocks of cells? To delete ALL the info just hit ...
|
| Tracking Hours worked | 12/29/2008 |
Q: I work a split shift. I am trying to create a sheet that will track my total end of week hours ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Summing text or numbers | 12/28/2008 |
Q: I am designing an overtime tracker (running totals that will be sorted daily to choose lowest ...
A: Use the ISNUMERIC function. You might need to make a second "temp" column. Let me know if you have ...
|
| Allocating inconsistantly dated bill amounts | 12/27/2008 |
Q: Richard, I'm breaking my teeth trying to figure out an automated method for allocating utility bills ...
A: This is more of a logistics problem than an Excel problem. YOU basically need to figure out the best ...
|
| excel | 12/22/2008 |
Q: How can I sum the sales between 9 pm and 3 am Time Sales From To 1 AM $1.00 9 PM 3 AM 2 ...
A: Use an IF function to determine if the time is between your range, and then SUM that column. You ...
|
| excel formula - complicated | 12/19/2008 |
Q: I am trying to make a formula to: take two variables (one in a row, one in a column) and enter them ...
A: It sounds like you already know what you need to do. Do you just need help with VLOOKUP? You can ...
|
| Conditional sum | 12/18/2008 |
Q: for example, in cell A1 type 30+40 (not "=30+40") so that it appears "30+40" instead of "70". Now, ...
A: You can't do it. By storing your value as "30+40" Excel is treating it as TEXT instead of numbers. ...
|
| Comparing data from two different excel sources. | 12/17/2008 |
Q: .. Here is what I need to do. I am looking to create a chart with data from two separate excel ...
A: As long as you have some kind of KEY field (and ID of some sort) you should be able to use VLOOKUP ...
|
| Excel formula | 12/17/2008 |
Q: i want to calculate a 10% discount for customer who pay for 2 services in advance. here is what i ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Mr & Mrs column in excel | 12/16/2008 |
Q: i purchased data for a mailing and had to add a field for Mr & Mrs. Is there anyway I can do this ...
A: Only if you can think of a way for the computer to know whether or not each name is male or female. ...
|
| Pivot Table | 12/15/2008 |
Q: have a worksheet with a column of amounts and another of codes for those amounts. I need to add ...
A: A PivotTable requires three bits of data, like Salesman, City, and Sales Amount. If you only have ...
|
| date formula | 12/15/2008 |
Q: I am making sheet for expired licence's. in 1 colume i have expirey dates. in other i would like ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Asking Question | 12/12/2008 |
Q: If the condition is true, should have to change the colour of another cell. or If the condition is ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| clock cards | 12/12/2008 |
Q: Time out minus time in = SUM.But i need 1 hour for lunch to be deducted automatic in the ...
A: An hour is 1/24th of a day, so you can say: =SUM(A1-A2)-(1/24) Let me know if you have any other ...
|
| I need to Know the Answer | 12/11/2008 |
Q: If one cell has the contents of two company name (ex. X,Y),and another two diferent cell has list of ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Flag up expiry date | 12/11/2008 |
Q: Surname First Name Licence # Class Expiry Date Bloe Joe 99955533 ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| If Statements | 12/10/2008 |
Q: How do you write an IF statement that has both negative and positive numbers in it? Ex: How would I ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Trouble with linked files (want to add columns and rearrange columns in the source file) | 12/8/2008 |
Q: I am creating an excel file that has all our forecasting information for multiple geographies. I ...
A: Try using a NAMED CELL in your sheet. If that cell moves, your reference to it shouldn't matter. I ...
|
| Email Address | 12/6/2008 |
Q: How i can extracts email address in MS word from MS Excel coloums.
A: That would depend on what format they're currently in. If it's a single, simple list of email ...
|
| Count if | 12/5/2008 |
Q: Im trying to create formula to count "X" in one row but only when 2 other rows meet the criteria. ...
A: Put the number 1 in a different cell in that row if the other cells meet the criteria. Then count up ...
|
| name conversion | 12/4/2008 |
Q: in cell a I have a last name (Wood) and in cell b a first (Stuart) name. is there a formula I can ...
A: You can use the CONCATENATE function, or just say C1=A1&" "&B1 Let me know if you have any other ...
|
| Select from a dropdown list but display from a vlookup... | 12/3/2008 |
Q: I have a cell with a dropdown list of branch code. I also have a lookup table, branch code on the ...
A: I don't believe it's possible, no. You would have to put the result in A2, for example. I'm not 100% ...
|
| Axis scale Color | 12/2/2008 |
Q: how can i change the the color of axis scale numbers color for example : first three scale in red, ...
A: Right-click > Format Axis should do the trick. I haven't done this in a while, but give it a shot. ...
|
| Hiding rows and columns to prevent end user access | 12/2/2008 |
Q: I want to prevent the end users of a "costing template" from being able to the see the nuts and ...
A: You can use Sheet Protection. Once you've hidden certain rows, if you protect your sheet then the ...
|
| function | 11/30/2008 |
Q: I need to write a function in a spread sheet regarding cost/retail. If I have a cost of $.053 and ...
A: That's going to be pretty complicated. I would do it with a loop and a custom VBA function. ...
|
| Body of outlook e-mail from Excel | 11/29/2008 |
Q: Is it possible to copy selected set of cells from a active sheet and paste it body of the e-mail ...
A: Yes. When I copy and paste from Excel to Outlook 2003 everything (fonts, colors, etc.) copy too. I ...
|
| conditional formatting | 11/29/2008 |
Q: In Excel 2007 (or in XP), is there a way to define 2 conditions for a rule to take place? For ...
A: I don't believe so, no. Sorry. Let me know if you have any other questions or comments. Also, be ...
|
| sorting | 11/28/2008 |
Q: I have a column with 1 - 100. When i Delete it row the numbering is off. If i delete row 3, it ...
A: You'll have to manually renumber it (I would recommend Autofill) or write a macro to recreate your ...
|
| Hyperlinks | 11/27/2008 |
Q: How can I copy and paste a hyperlink to another area of the same spreadsheet, relatively. For ...
A: Make your formula in cells C15 and D15 then select them both, then autofill across. Excel should ...
|
| calculating times in excel | 11/26/2008 |
Q: Ok, so at work we use an excel spreadsheet to sign people in, and then sign them out when they see a ...
A: Just use the AUTOFILL feature. Click on the cell and then click on the black dot in the bottom right ...
|
| excel nested if function showing incorrect answer. | 11/26/2008 |
Q: I used the following excel formula to find the result of students. ...
A: How about using something like -1 or 0 to represent absent instead of AA? It's always best to stick ...
|
| Excel Function | 11/26/2008 |
Q: I am an training coordinator, need to prepare reports for We conduct 5 types of training courses in ...
A: You can use the IIF function to perform If/Then analyses on your data. Please see the following ...
|
| Colouring cells | 11/25/2008 |
Q: How do I change the colour of a cell on the bases of an outcome from a formula, or just manual ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Select from a dropdown list but display from a vlookup... | 11/25/2008 |
Q: I have a cell with a dropdown list of branch code. I also have a lookup table, branch code on the ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| excel | 11/24/2008 |
Q:
A: Use the MIN() function Let me know if you have any other questions or comments. Also, be sure to ...
|
| excel>>frontpage | 11/23/2008 |
Q: how can I copy the content only of excel cells into Frontpage without all the formats of excel.
A: Try exporting from Excel to an HTML file first. Then open that in FrontPage or your Web browser and ...
|
| adding %.322% in Excel | 11/21/2008 |
Q: Have a column shoving condo fees for 166 different size suites. Condo fees went up 5.322%, how do I ...
A: You should create a single field somewhere on your sheet and name it FEEPERCENT or something like ...
|
| Daily Compounding | 11/20/2008 |
Q: I'm looking for a single formula which does daily compounding for the below conditions: Loan Amt ...
A: I'm not sure… I've only really used the PMT function with any seriousness. Let me know if you have ...
|
| Import data from mainframe to excel | 11/19/2008 |
Q: I am new to mainframe testing.I have to copy paste the data from mainframe to excel sheet using a ...
A: This is different for each type of mainframe. If you can connect to an ODBC server, then use that. ...
|
| conditional sum on filtered data | 11/18/2008 |
Q: I hope you can help me with the following problem (a few days of trail and error didn't work. I ...
A: Have you tried using the SUMIF function to add up your columns? Let me know if you have any other ...
|
| how do i... | 11/13/2008 |
Q: I have populated many cells in column A with the numbers 1 through 12. The numbers are synonymous ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| Text Manipulation | 11/13/2008 |
Q: I have a spreadsheet full of text from A3 all the way down to row A402 - only in column A. I want ...
A: I'm assuming your CONCAT formula in in B3 and that B4 is blank. Highlight B3 and B4, then autofill ...
|
| Excel 2007 | 11/12/2008 |
Q: I have a workbook with two columns of addresses and need to compare the two columns for "like" ...
A: That's up to you. You could see if one string was inside of another one, or you could compare the ...
|
| excel - sheet reference cell | 11/11/2008 |
Q: i think the best way to explain my problem is to give a brief description of what i am trying to do: ...
A: Sam, you can just use COPY, PASTE SPECIAL, PASTE LINK to paste a linked copy of your cell data in a ...
|
| preventing a cell from changing | 11/10/2008 |
Q: I have one sheet in a workbook that is linked to another, the first sheet contains raw data and the ...
A: Use a named range. Then it wouldn't matter where your cells moved to. Let me know if you have any ...
|
| Excel | 11/10/2008 |
Q: Good day. Would it be possible to link the results of a certain cell? let say, I have column A,B,C ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Microsoft Excel 2007 Nested IF Function | 11/9/2008 |
Q: I am trying to figure out how to calculate overtime for an hourly employee where I only have one ...
A: I would have to reconstruct your sheet to tell you exactly what's going on, but I can see one ...
|
| using Max in Excel | 11/8/2008 |
Q: I have Max returning the maximum value in a column. How do I get the location/instance of that ...
A: Use the INDEX and/or MATCH functions to basically go backwards from a VLOOKUP. Let me know if you ...
|
| Excel 2007. Text in cell representing a value | 11/8/2008 |
Q: I would like to be able to type into a cell and display any of the following three percentage ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Hyperlinks to Lotus Notes | 11/6/2008 |
Q: Is it possible to create a hyperlink in Excel to a Lotus Notes Database?
A: I'm sure it is. I've never done it, but a hyperlink should be able to link to any type of file. I ...
|
| How to count two cells with specific values in a worksheet | 11/6/2008 |
Q: I have a worksheet of 5 column and 100 rows with random cells values, I would like to count the ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Counting spaces as a zero | 11/5/2008 |
Q: Here is my formula however in in some of the input cells there are blank cells. the formula is ...
A: You can use the ISBLANK function to determine if a cell is empty or not. Let me know if you have ...
|
| excel line chart at zero | 11/5/2008 |
Q: I want to make a line chart that starts at zero. for example, my numbers are 5 for year 1, 10 for ...
A: On the chart, you can right-click and format the axis and tell it what you want the max and min ...
|
| prevent saving excel file as different name | 11/4/2008 |
Q: I have an excel file i wish to make readable to anyone. However i do not wish changes to be made to ...
A: You're welcome, Jim. Yes, you figured out a solution that works for you. I'm glad you were able to ...
|
| CONVERT COLUMNS TO TEXT IN SINGLE COLUMN | 11/2/2008 |
Q: I HAVE "FOR EG: I AM AN IT PROFESSIONAL" IN WHICH ALL WORDS ARE SEPARATE IN DIFFERENT COLUMN OF ...
A: Basic string concatenation: =A1&B1&C1&D1 Let me know if you have any other questions or comments. ...
|
| IF formulas | 11/1/2008 |
Q: I am using a spread sheet to merge data to word documents to create contracts and invoices. One ...
A: Oops… if you got my previous answer, I think I sent you to the wrong page. Use this one: You can ...
|
| business study | 11/1/2008 |
Q: 1-all possible investment must be 2-advantages and disadvantages 3-compare the return in table ...
A: Huh? Sorry. Wrong expert. I'm not an investment guy. I'm a software guy. Plus this sounds like a ...
|
| Is this possible? | 10/31/2008 |
Q: I was wondering if there is a way a to fill rows with a certain color when numbers are entered under ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Rank in 2 or more categories | 10/30/2008 |
Q: I want to rank a total sum of the subject the result of the final year. But now my problem is if i ...
A: Just do a multi-column sort under DATA > SORT. Let me know if you have any other questions or ...
|
| Percentage Formula | 10/30/2008 |
Q: Im designing a spreadsheet where when sales commision is paid it can be a percentage of an original ...
A: This should just be basic math. If A1 is 70 and you want B1 to be 80% of that, it's: =A1*.80 Let ...
|
| Excel Random Feature | 10/29/2008 |
Q: I am using the excel random function to choose items to check for quality control. Right now i am ...
A: The only way I can think of to do it is to copy the row and then PASTE SPECIAL… VALUES into a ...
|
| Locking an Excel Workbook | 10/28/2008 |
Q: I work at a college and a colleague of mine needs to lock a spreadsheet that will be located on a ...
A: In a nutshell… nope. You can prevent that copy from being overwritten, but nothing can prevent ...
|
| Auto Filling | 10/24/2008 |
Q: So i have a formula I would like to auto change for me. The formula is... ...
A: If by "dragging" you mean using the AutoFill feature, then the DOLLAR SIGNS you have in your formula ...
|
| excel formula | 10/24/2008 |
Q: I want to find the range between to cells if the value is True the cell to be hilighted is BLUE. if ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Excel | 10/23/2008 |
Q: I'm trying to enter a zero as the first digit in a series. Excel keeps removing it. How do I keep ...
A: Do you mean you're trying to enter a zero as the first character in a longer number? Like a Social ...
|
| Sorting question | 10/22/2008 |
Q: Is there a way that I can force a sort to use the entire worksheet when they sort (so that the ...
A: Not that I know of. You have to select all of the data to keep rows together… or use a Database like ...
|
| checking data in columns | 10/22/2008 |
Q: I have imported a database into excel. I now have many sheets. THe first column of each sheet is ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Combo Boxes (Macros) | 10/21/2008 |
Q: I want to have a drop down combo box with a list that users can choose one of 7 options and select - ...
A: For static data (a list that doesn't change, like shipping types) just use the Command Button ...
|
| prevent saving excel file as different name | 10/21/2008 |
Q: I have an excel file i wish to make readable to anyone. However i do not wish changes to be made to ...
A: Nope. Once you give someone READ access to the file, there's nothing to stop them from saving it to ...
|
| Text discovery and comparison between two tables | 10/20/2008 |
Q: I have two tables of customer information from two different sources. We have a list of customers ...
A: Essentially, you need to first get your data into two tables: your customers and their customers. ...
|
| Cell colour in Excel | 10/18/2008 |
Q: How do I format a cell to colour it's background depending on what specific text (one of five or six ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| saving excel as a gif file | 10/17/2008 |
Q: I created a poster in excel, how can i save it as a gif file
A: You could screen-capture it (PrintScreen) and then paste it into Windows Paint. Save it from there. ...
|
| Count Multiple Entries | 10/16/2008 |
Q: I need a formula to count multiple entries between a date, such as 1/1/08-1/31/08. In this example, ...
A: You would have to use an IF function in the next column. Name two named cells on your sheet called ...
|
| If, then Formula (Complicated) | 10/16/2008 |
Q: Appreciate your help on the following. I have 3 different groups of people| Group A = 1 Group B = 2 ...
A: The easiest thing would be to use SUBTOTALS which would quickly count up the values for you, if you ...
|
| excel spreadsheet | 10/15/2008 |
Q: we have a service spreadsheet that will not accept KP02 HTE the spreadsheet will always change it to ...
A: Turn of AutoCorrect in Tools > Options. Let me know if you have any other questions or comments. ...
|
| time | 10/14/2008 |
Q: I would like to know how to calculate total number of hours and minutes if I put time in and time ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Text discovery and comparison between two tables | 10/13/2008 |
Q: I have two tables of customer information from two different sources. We have a list of customers ...
A: Forget doing this in Excel. It's going to be almost impossible. In a database like Microsoft ...
|
| odd number format | 10/12/2008 |
Q: I am working in Excel 2007 for the first time (eek - I don't like re-learning what used to be so ...
A: How about Copy > Paste Special > Paste Values? Let me know if you have any other questions or ...
|
| linking a cell to show data from another sheet within the same workbook. | 10/10/2008 |
Q: I have compiled a database of financial information for assets within the company with each asset on ...
A: The easiest way to do this is to copy the cell then click where you want it to go on your summary ...
|
| Multiple vlookups? | 10/8/2008 |
Q: I have a workbook with two worksheets; the first worksheet will be a summarization of certain ...
A: Without actually seeing your worksheets I really can't provide you with a specific answer. But what ...
|
| Excel or Visual Basic | 10/8/2008 |
Q: I trying to make an employee schedule with hours of work and I have numbers for example 803=7.5hrs ...
A: What exactly is a 803? How exactly is your data stored? I'm sure you can convert any data from one ...
|
| sum 3 conditions | 10/7/2008 |
Q: The fields are contingency type; amount; commencement date; expiry date. I need to show the amounts ...
A: Without trying to understand all of your terms I'm sure you can solve this problem with several IIF ...
|
| Excel work sheet | 10/7/2008 |
Q: I want a work sheet where I can manage my asset care(maintenance) on a monthly base. The worksheet ...
A: Yes its is possible in excel. You would need to enter the date that the machine needs to be ...
|
| VLOOKUP problem | 10/6/2008 |
Q: I am trying to create a spreadsheet so I can pull and list the data from another spreadsheet. I’ve ...
A: I'm sorry but I really can't help you with the drop down boxes as I don't use them that much. I can ...
|
| slope | 10/4/2008 |
Q: I plotted data onto a graph and added a trend line. I was curious if there is a way to display the ...
A: I don't believe so. Sorry. Let me know if you have any other questions or comments. Also, be sure ...
|
| Excel and word | 10/4/2008 |
Q: I am having problems combining word documents and excel. I have created word documents which I would ...
A: You're better off going the other way… bring your Excel sheets into your Word documents. Just copy ...
|
| TROUBLE TO CALCULATE | 10/3/2008 |
Q: "Dude...good day...I have to make payments of produce boxes. I have 5 suppliers of produce(lettuces, ...
A: Sure… this is just a simple calculation, is it not? Something like =B3*B6 Am I missing something? ...
|
| Excel 2007 Conditional formatting | 10/3/2008 |
Q: I would like to have 2 cells that have F2(date) and F4(date) highlight in red when these cells are ...
A: Excel has a feature called Conditional Formatting that you can use to change the format (color, ...
|
| Commission Structure | 10/2/2008 |
Q: I trying to revise our commission structure calcualtions. I am using Excel 2000 in XP. Gross ...
A: You could use an IF function or a VLOOKUP to solve this problem. Either should work for you. Here ...
|
| EXCEL FOR SCHEDULES/PROGRAMMES | 10/1/2008 |
Q: I am using Excel to maintain a course schedule for a year long programme and I am sure there is a ...
A: Ray, yes it is certainly possible to do what you want with Microsoft Excel. However programs such ...
|
| Range criteria - if function? | 9/30/2008 |
Q: I'm trying to create a formula which will look up a cell with a quantity (A9) and put price in B9. ...
A: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that ...
|
| countif or sumif | 9/28/2008 |
Q: I am trying to count the number of occurrences of calls by the hour. My spreadsheet has a column of ...
A: I would create a new column that has just the HOUR() portion of the time in it. Then you could ...
|
| summing some specific numbers from excel sheet | 9/25/2008 |
Q: i have many +ve, -ve and zero numbers in a row. now i want to sum only positive numbers in one cell, ...
A: You can use the if function to determine whether or not a number is positive or negative. You can ...
|
| Locking cell references | 9/25/2008 |
Q: I am creating a very detailed financial application that will calculate various functions over the ...
A: Once you lock a cell, it's locked. You can't just lock a portion of a cell. You could break your ...
|
| Adding specific columns | 9/23/2008 |
Q: Richard, How do you paste an excel document that is 10 columns wide into an existing document that ...
A: To the best of my knowledge, you can't. You're going to have to add those extra columns to your ...
|
| Conditional Formats and Dates | 9/23/2008 |
Q: Sir, I am trying to set up a spreadsheet that I can use to track when training expires. The issue I ...
A: I cover this exact example (well, almost exactly) in this tutorial... Excel has a feature called ...
|
| DateAdd In Reverse | 9/22/2008 |
Q: Is there a VBA script for doing reverse calculations with dates similar to DateAdd. If I have a ...
A: You can use negative numbers with DateAdd to count backwards. Omitting weekends and holidays is a ...
|
| Excel Help to predict my hours worked | 9/21/2008 |
Q: I am hoping that it is possible to do the following in excel. If so, how? I have 4 preset shifts ...
A: You can use the IF function to have Excel place a value in a cell based on one or more conditions or ...
|
| Excel Date Question | 9/20/2008 |
Q: I am having trouble with a conditional format for excel. What I need to make is a tracker that ...
A: There are many different techniques in Excel you can use to break apart dates or times into their ...
|
| Combining Column Possibilities | 9/19/2008 |
Q: If I have text in column A (let's say 50 rows) and text in column B (let's say 100 rows), and I want ...
A: You'd need a macro to loop through both columns and combine them. Or you can load them into two ...
|
| Days elapsed | 9/15/2008 |
Q: What I need to do is have a function which calculates the days elapsed between current date & time ...
A: You'll need to know how to properly figure the difference between two dates, and the IF function. ...
|
| Excel - converting numbers to hours | 9/12/2008 |
Q: I need to be about to convert numbers to hours and then add and multiply to get a total number of ...
A: You need to keep everything in the same unit. 6.75 hours is the same as 6 hours, 45 minutes. ...
|
| Number Range | 9/12/2008 |
Q: If I have a table: ZIP ZONE 006..009 7 010..029 4 Is it possible to perform a VLOOKUP ...
A: If your table has exactly "006..009" in it, then yes, you'll need to change it and list out every ...
|
| Ask about Reference in Excel | 9/12/2008 |
Q: I have a problem with reference in excel, as if in A1 it content = "Red" then i'll try to make ...
A: You want Excel to stop automatically filling in cells for you??? In Excel 2003... Tools > Options ...
|
| Rounding time/date up in excel to a specific value | 9/7/2008 |
Q: I need help with a formula. Basically, I'm given a date and time - I need to round that date and ...
A: Well, I'm sure there's a BETTER way to do this, but here's a FUN and EASY way to do it. If your ...
|
| Converting Date (01/04/1998) into a Numeric Month | 9/6/2008 |
Q: It's the little stuff that drives you crazy and takes a lot of time. I cant figure out how to ...
A: This is an easy one. B1 should be: =MONTH(A1) Just AutoFill that down and you're good to go. Let ...
|
| countif formula | 9/5/2008 |
Q: i have a spreadsheet that i have made where i record tests scores and the dates from when they took ...
A: You could use the IF function to put a 1 or 0 in the next column to determine whether or not to ...
|
| Excel formulas | 9/4/2008 |
Q: in excel if row a has value of P1 and row B has value of 2 or 3.i would like to know if row A is P1 ...
A: Use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts ...
|
| small to large formulas with text | 9/3/2008 |
Q: I need to put 48 scores in order from min to max with there name attached: EX: I have in column A I ...
A: I don't understand what you're looking for. Can't you just select the rows in question and then use ...
|
| IF and VLOOKUP statements | 9/3/2008 |
Q: Morning, I was wondering if you could help. I have 2 worksheets one containing business address ...
A: Well, you're going to somehow have to relate the two post codes together. If you want to use the ...
|
| Excel COUNTIF | 9/3/2008 |
Q: I have a spreadsheet which uses a COUNTIF formula =COUNTIF(B14:B393,"H") So each time I enter the ...
A: Yes, use the IF function. Something like this: =IF(A1="H",1,IF(A1="HALF",0.5,0)) You'd just create ...
|
| counting values with more than 1 criteria | 9/2/2008 |
Q: Rost i have a question on counting of value, my problem here is i have a set of columns and i want ...
A: You can use the SUBTOTALS feature to count the unique values. As far as putting that on a different ...
|
| Sumif | 8/30/2008 |
Q: I have to count the instances of Status, Agentname combinations. Ex. I have in status column ...
A: You could use the SUBTOTAL function. Let me know if you have any other questions or comments. ...
|
| Comparing grades | 8/30/2008 |
Q: If the letter is one the same then green if once place yellow and two places or more then red. For ...
A: Sure... just say "if the value is A then color red" in your conditional formatting arguments. For ...
|
| custom rounding of numbers | 8/29/2008 |
Q: I have a column of numbers with 1 decimal point ie 25.6,25.7 ,29.8,25.1 Wish to round .6 thru .1 ...
A: You're going to have to subtract the whole number component (use the INT function) then take a look ...
|
| Formula | 8/28/2008 |
Q: I am looking for a formula which can identify and return the top 20 units sold (in descending order) ...
A: I would use an IF function to determine whether or not the field should be included, and then turn ...
|
| counting once items that appear twice and sum them up | 8/28/2008 |
Q: I have a question for you. I've been searching all kind of solution, but i couldn't find a useful ...
A: The only way I can think of to do this in EXCEL is to Sort your list and then use Data > SUBTOTALS ...
|
| barcode scanner info to excel spreadsheet | 8/26/2008 |
Q: Because i can spell Kompewter I am the company's 'IT guy', anyway my boss bought a scanner to scan ...
A: Wow... I've never seen a scanner work like that. Every scanner that I've ever worked with doesn't ...
|
| Concatenate two address functions | 8/25/2008 |
Q: how do i concatenate two address functions? eg: rng1.address = C2:T16 and rng1.address = C30:T33, i ...
A: Well, if you have A1 = Joe B1 = Smith Then to put them together you can say in C1: =A1 & " " & B1 ...
|
| unque count | 8/25/2008 |
Q: Greeting! Thank you for your time. I am sorting my sales record,and I want to know unque count of ...
A: You could use SUBTOTALS with the COUNT function to determine how many unique POs there are - then ...
|
| Excel Help | 8/24/2008 |
Q: I have a concern with my Excel Document that I am currently creating. I need to replace the ABC ...
A: Sorry, but I know of no way to do that. You can add your own titles in the first column/row, but you ...
|
| drop down lists | 8/22/2008 |
Q: I understand ho to create drop down lists from data on the same sheet or document, but how do I ...
A: The EASIEST solution is to COPY and PASTE-SPECIAL > PASTE LINK the data somewhere in your current ...
|
| excel calculation | 8/22/2008 |
Q: I have created a spreadsheet to track patient complaints. I have 3 separate columns that complaints ...
A: Use the COUNT function. Let me know if you have any other questions or comments. Also, be sure to ...
|
| excel question | 8/21/2008 |
Q: I am trying to add the number in column A1, C1, E1, G1, I1, etc.but it won't work. I can't just ...
A: You can hold down the CTRL key while selecting specific columns. Let me know if you have any other ...
|
| Date function | 8/21/2008 |
Q: Im trying to track coupon payments for a bond that pays semi-annual payments on the 14-MAy and ...
A: This tutorial will teach you about how to read the different parts of a date: ...
|
| specific printing please help | 8/19/2008 |
Q: I am trying to print 1000 receipts on a panasonic KX P1150 each page is about 16cm wide and 11cm ...
A: Use the PAGE LAYOUT mode (View > Page Break Preview) and you can dynamically click-and-drag where ...
|
| Mail Merge from Excel File | 8/19/2008 |
Q: how do I create a mail merge for labels and letterheads from a preexisting file, while trying to ...
A: You can use a little IF function to say if their last names are similar put them together. A1 = ...
|
| barcode scanner info to excel spreadsheet | 8/19/2008 |
Q: Because i can spell Kompewter I am the company's 'IT guy', anyway my boss bought a scanner to scan ...
A: The only barcode scanners I've worked with before are HARDWARE wedge scanners... where they plug in ...
|
| percentage question | 8/18/2008 |
Q: Hhat is the formula for percentage in excel. I want to know if you had $9,600 and each it went up ...
A: It's just basic math. For example, if you have $100 in cell A1 and you want to raise it 10% (in cell ...
|
| Excel 2007 - Bullets | 8/15/2008 |
Q: I am trying to insert bullets in a single cell e.g. a) b) c) Please help !!
A: To the best of my knowledge (and a quick look at Excel) that's not possible. You can't put bulleted ...
|
| looping to create colors | 8/15/2008 |
Q: I thought this would be easy! but my experts can't figure it out. here's my code: Sub cellcolor() ...
A: Well, you're not changing your cell value. It's always Cells(1, 1).Select Shouldn't you put the ...
|
| Change the color of entire row based on a cell value of that row. | 8/14/2008 |
Q: Please help me to change the colour of entire row based on the value of one cell of the same row. ...
A: Look at CONDITIONAL FORMATTING. Go to Format > Conditional Formatting. It's pretty straightforward. ...
|
| Percentage | 8/14/2008 |
Q: I am trying to calcuate the percentage that a person would possibly get off on a sale item. For ...
A: That's just a matter of simple math. If A1 = 315 and B1 = 95, then C1 = 1-(B1/A1). Then just ...
|
| Excel 2000 help | 8/13/2008 |
Q: I need to know if it's possible to have one cell modify the numbers in another cell based on the ...
A: Sure... you can use the IF function to have Excel make decisions. See this tutorial: ...
|
| @ sign in Excel | 8/12/2008 |
Q: how can i avoid the @ sign in a word to become a email address? thanx
A: When Excel creates a hyperlink, click on the AutoFormat button that appears and select "stop ...
|
| daily bank interest | 8/12/2008 |
Q: Richard, I run a game where people can send me virtual money. This money needs to accrue daily ...
A: Sure... you could do this with just a little math, but you'll need to make a row for each day if you ...
|
| Text extraction | 8/12/2008 |
Q: I have a large list of data featuring a single column with the information as below: R Garcia HUL 0 ...
A: Yeah, if it was all the same length, you wouldn't have a problem. What you're going to have to do ...
|
| Converting Months | 8/11/2008 |
Q: I am working on a budget and have created the following formula to calculate the number of months in ...
A: It seems like it should be easy, but you're going to have to break it down yourself. I don't know of ...
|
| calculate times | 8/10/2008 |
Q: i should calculate call time durations for some numbers in my office. its like this 00:07:45 ...
A: Diana, that's an easy one. Just add up all of the times in the column, and then convert that total ...
|
| time formula in excel | 8/10/2008 |
Q: this question is pretty straight forward, I am creating a time sheet for my employees (i.e., my ...
A: The answer is straight-forward to: Nope. You can put both times in one cell, but then it will be a ...
|
| Automated list correction | 8/9/2008 |
Q: I have two lists in separate workbook, book1 & book2. I want to create another list in book3 which ...
A: The EASIEST way to do this is to copy and paste LINK the records from Books 1 and 2 into Book 3 so ...
|
| Need to get the count of non empty cells | 8/6/2008 |
Q: I have a set of alphabets, numbers and Special characters in a column. I need to get the count of ...
A: Just use the COUNTBLANK function. Let me know if you have any other questions or comments. Also, ...
|
| formula for V.A.T | 7/24/2008 |
Q: i have the formula for working out V.A.T which is 17.5%, I need a formula so I do not have to keep ...
A: All you should need to do is AUTOFILL your formula down the column, and Excel will copy it down for ...
|
| MAXA problem | 7/24/2008 |
Q: i'm trying to find the highest of a series of data in cells. Its a text/numeric mix for example: ...
A: I would break your string up into two cells using the LEFT and RIGHT functions (isolate that IHC or ...
|
| Excel formula converting with dec2bin | 7/23/2008 |
Q: I am trying to work with the dec2bin function to convert what i place in lets say B2 and have it ...
A: Looks like you've reached the limit of the DEC2BIN function. See: ...
|
| Excel vlookup | 7/23/2008 |
Q: I use the formula wizard to build a vlookup and the correct result shows in the formula wizard box, ...
A: See this tutorial on VLOOKUP: http://599cd.com/tips/excel/vlookup?key=AllExperts Let me know if ...
|
| Export data to publisher | 7/21/2008 |
Q: I am in charge of updating our school's directory which has been kept in excel and published in ...
A: Well, how about a simple copy and paste? Let me know if you have any other questions or comments. ...
|
| Excel value changes with data changes | 7/19/2008 |
Q: I use on the stock page in cell d2 the following formula ...
A: Yes, of course that's going to be a problem. Your cell is dependant upon the value that you're ...
|
| Calculating Vacation time | 7/19/2008 |
Q: I work for a company and I am required to keep a list of available vacation days. after year 1 they ...
A: You could create a table for these values and then use the VLOOKUP function to return the vacation ...
|
| Address Spreadsheet | 7/18/2008 |
Q: I have a spreadsheet of over 25,000 address which I need to sort. Is there a way in excel to tell ...
A: You're right. Excel is not the best choice for 25000 records. You really should import this ...
|
| Excel 2007 - Right Justify on Tab in Column | 7/17/2008 |
Q: I would like to right justify numbers in a column, but not to the furthest right margin of that ...
A: The only thing I can think of would be to have a second column that was the number plus a few ...
|
| Changing colums to rows | 7/17/2008 |
Q: I have been working on a spreadsheet of addresses that is very inefficient to use and make so i am ...
A: Philip, based on what you have there, you should switch to a DATABASE like MICROSOFT ACCESS. For ...
|
| Changing times on excel | 7/16/2008 |
Q: I have times in format in excel i.e. 5/15/08 18:30 and need to change the times by plus 2 hours or ...
A: Yup. See this tutorial: http://599cd.com/tips/excel/adding-dates-times?key=AllExperts Let me know ...
|
| Query on Exported data | 7/16/2008 |
Q: For an example ,if i export a value 720707886 from an application it looks like 7.207E+16 in excel. ...
A: Try using FORMAT CELLS and change it from Scientific to the specific format you want. Let me know ...
|
| doing arithmetic with times | 7/16/2008 |
Q: nice to meet you. using Excel 2003, I want to add minutes to a time that appears as, say, 11:30 AM, ...
A: Chris, see this tutorial on how to add dates and times in Excel: ...
|
| Problem with drop-down filtering | 7/15/2008 |
Q: We use a shared spreadsheet at work where we filter our rows using a drop down menus in the first ...
A: Excel is VERY limited when it comes to large spreadsheets. I think you've hit the limit of the ...
|
| Case of or something like that | 7/14/2008 |
Q: Please can you advice me, or give me the solution how to get the thing working: I'm making a ...
A: Try using the VLOOKUP function. See: http://599cd.com/tips/excel/vlookup?key=AllExperts Let me ...
|
| Calculated Items in Pivot Table | 7/14/2008 |
Q: I have two calculated fields in a pivot table (A,B) and one calculated item C that is the sum of ...
A: Create another field that performs the division with an IF statement to change the answer directly ...
|
| Formulas | 7/13/2008 |
Q: I trying to figure out a formula when you have two dates I need to subtract: Todays date from date ...
A: Subtract them as you would any other values. The result is the number of DAYS between your two ...
|
| excel vlookup | 7/9/2008 |
Q: i have many worksheets and i want to summarize these worksheets in a summary worksheet. For example, ...
A: You can use VLOOKUP to find data from different sheets. I would recommend setting up NAMED RANGES ...
|
| MS Excel formula | 7/9/2008 |
Q: I am trying to make a spreadsheet for work. I need to track the total number of days including the ...
A: If your start date is in A1 and your end date is in B1, then C1 is: =B1-A1 Format that cell as a ...
|
| Print Area | 7/8/2008 |
Q: Richard Rost I want know how I can print maximum area in Excel? Because I’m trying to print one ...
A: By "free area" do you mean MARGINS? Go to File > Page Setup. On the MARGINS tab you can reduce it as ...
|
| merging excel files | 7/8/2008 |
Q: I have excel 2007. I would like to merget two files together. They both have the same columns ...
A: Have you tried just a simple COPY and PASTE? I don't understand what you mean by making the cells ...
|
| False Advertising | 7/7/2008 |
Q: Your bio states: "I am happy to answer any questions about Microsoft Excel, from basic formulas all ...
A: You're right... I did seem to contradict myself there, huh? I should have been more specific... I'm ...
|
| sending filea from excel to txt | 6/21/2008 |
Q: .. i am a regular reader of ur site since few weeks though :) i am a engineer vry comfiortable in ...
A: Yes... just use the SORT ASCENDING button on the toolbar to sort them. You can save them to a text ...
|
| Average Function | 6/18/2008 |
Q: I don't get it. Not a unique thought in my life. Anyway: I am working on a spreadsheet to show the ...
A: COUNTA counts the non-blank cells. What you might want to do is use IF function to check the value ...
|
| Excel date list | 6/18/2008 |
Q: Simple query... I am trying to create a work schedule. I want to automatically fill Col A with ...
A: I show you how to do this in my Excel 101 tutorial... using the AutoFill Handle. Basically, you ...
|
| Array Formulas | 6/17/2008 |
Q: I have a spreadsheet and I need to have formulas that will check column A for a specific customer ...
A: Betsy, you might be better off using the IF and VLOOKUP functions. See these tutorials: ...
|
| Different conditions if positive or negative | 6/17/2008 |
Q: I want to write a formula where if a certain calculation returns a positive number, to keep display ...
A: You can perform both of these operations with the IF function. See this tutorial: ...
|
| Count records that satisfy multiple criteria | 6/12/2008 |
Q: My issue is I have spread sheet that has 2 columns, one of ages and one of gender. I am trying to ...
A: Rob, you're probably going to have to make a series of columns for each of your GROUPS using IF ...
|
| Macro to show more then 1024 characters in Merged Cell | 6/11/2008 |
Q: I have to show more then 1024 characters in the merged cells. Suppose my merge cell area is B28:H35 ...
A: I'm not 100% sure, but I think 1024 is the MAXIMUM length of a cell in Excel. Nothing more you can ...
|
| excell formula | 6/10/2008 |
Q: Ross, I am struggling to find the right formula from the online help topics in excell on this. I ...
A: Well, Chuck, you can use the SEARCH function to see if "home" appears in your cell. In G1, you can ...
|
| Sample Rate | 6/9/2008 |
Q: I need help. I have a excel spreadsheet that I need to have the following done. I have a range ...
A: Well, Nic, if your range is that simple, you could just use an IF function in A2: ...
|
| Average Function | 6/9/2008 |
Q: I don't get it. Not a unique thought in my life. Anyway: I am working on a spreadsheet to show the ...
A: You could use the COUNTA function to determine how many cells are NON-blank (or the COUNTBLANK ...
|
| Excel Formula | 6/8/2008 |
Q: Not sure if you can help, but I am new with Excel and am trying to calc a commission structure with ...
A: You can solve this with a nested IF function. Let's say your Revenue is in column A, in column B ...
|
| Random selection | 6/8/2008 |
Q: I am a basic-level Excel user. Now I have a large spreadsheet consisting of over 6000 rows, but ...
A: You would need to write a macro that loops through all of your rows. For each row, generate a random ...
|
| enhancement in viewing and adding within worksheets | 6/6/2008 |
Q: how could i make dynamic charts which data is entered in other sheet and the chart is displayed on ...
A: By default, all charts are dynamic. If you make a change in your data, your chart should ...
|
| Using Vlookup find multiple result | 6/6/2008 |
Q: This is myThia data is in sheet 1 Acct # Account Name Invoice # Date Amount Type 1 ...
A: You're going to need a seprate VLOOKUP for each item you wish to look up on the other sheet. See: ...
|
| excel autosum, not autopopulating | 6/6/2008 |
Q: Richard, I have a spreadsheet with multiple auto sum formulas, when I enter a new number or change a ...
A: It's possible you turned off Excel's auto-calculation feature by accident. In Excel 2003, go to ...
|
| Consolidating | 6/5/2008 |
Q: I am trying to consolidate/copy data that is split across two different rows/columns into a single ...
A: You can merge cell information together using STRING CONCATENATION. For example, if these are your ...
|
| IF Formula | 6/5/2008 |
Q: when entering a IF formula and i want my False answer to be 0, how do i make it be blank instead of ...
A: Joe, try using an EMPTY STRING: =IF(A1=1234,100,"") Let me know if you have any other questions or ...
|
| finding cross reference formula | 6/4/2008 |
Q: I am trying to reference data in two seperate spreadsheets. I need to match a number from column A ...
A: Honestly, even a sheet with 1000 rows should be moved to a DATABASE program like Microsoft Access. ...
|
| Hide Columns Macro Needs Tweaking | 6/4/2008 |
Q: I modified a macro that I found posted on a site that will hide columns if the cell in the ...
A: Mark, I can't think of any way to make this better. Sorry! The only thing you could do is to ...
|
| Drop down list in excel | 6/2/2008 |
Q: I'm working on a spreadsheet which I use to keep track of all my income and expenses with and have ...
A: I understand exactly what you want to do, however you're not going to like my answer: it is going to ...
|
| excel input | 5/29/2008 |
Q: My name is Jerry. I hope my question won't give you the headache it has given me. When I type the ...
A: Jerry, you must have changed a setting somewhere. I just typed it into Excel and the zero stayed put ...
|
| comparing dates in excel | 5/28/2008 |
Q: Simple comparison: If date in column A>01/01/07, keep, discard. Dates formatted mm/dd/yyyy. I keep ...
A: You can't just put a date inside the IF function like this: WRONG: ...
|
| macro prompt for input | 5/27/2008 |
Q: Richard, im wondering if you can tell me how i change a csript to prompt me for a file name, or make ...
A: If you're programming in VB or VBA, you can use the InputBox command to ask the user for a filename. ...
|
| excel formula/filters | 5/27/2008 |
Q: I have a large list (potentially 60,000+ records). The recs are sorted according to US STATES and I ...
A: Chris, you lost me as soon as you had an Excel spreadsheet with 60,000 records. That's way too many. ...
|
| Filtering 4 values from one column | 5/27/2008 |
Q: Is it possible to filter a range of text using advanced filter or custom filter? I have created a ...
A: Sure... your dropdown list is bound to a specific cell, right? Just apply the filter to that cell ...
|
| formula | 5/26/2008 |
Q: Richard I have a range of numeric values in one column consisting of negative & positive numbers, ...
A: Use another column. In column B, say: =IF(A1<0,A1,0) This says, "IF A1 is negative, then put A1 ...
|
| Mandatory fields in Excell | 5/21/2008 |
Q: I hope this is not to much code I have a form that I would like to have a message box appear when ...
A: That's an easy one. Just say: if Range("B2")="" then ... And you're all set. See, I don't mind a ...
|
| Automatically populate a Coloumn cells | 5/21/2008 |
Q: I have a excel with lots of data. Data are generally Business Name. They look like Ristorante ...
A: It would be EASIER to go the other way... just type in the beginning of the business name in A1, and ...
|
| Excel question re-explained | 5/19/2008 |
Q: You indicated you didn't understand the question. I sent a separate email saying I solved it myself, ...
A: I agree with the "never mind" button, but I doubt people would actually use it. Most of the time, ...
|
| Removing date from time cell | 5/19/2008 |
Q: How do I remove the date from a cell with both the date and time (the date seems to show up ...
A: Lauren, the only easy way I've found to do this is to break up the date/time into its components ...
|
| Missing Record Macro Toolbar | 5/16/2008 |
Q: Toolbar does not appear after clicking OK. Need to have relative cell references. I am using 2003 ...
A: Go to View > Toolbars > Stop Recording and that should turn it back ON. Let me know if you have ...
|
| Excel issue - CountIf help? | 5/13/2008 |
Q: . I have been searching and searching online for an answer to this count issue, and have found ...
A: Make column C say: =IF(A2=B2, 1, 0) Now if you SUM up column C, you'll get the total of rows ...
|
| Macro | 5/11/2008 |
Q: Is there a way to get a macro to run automatically at specific times (10:00, 14:00, 16:00) or at ...
A: In VB or Access you could use a Timer control, but I don't believe there's a similar feature in ...
|
| Assign macro to hyperlink | 5/10/2008 |
Q: Is there a way to create, say, 3 different hyperlinks, where each one runs a different macro on a ...
A: I'm not sure about that feature, but you could just make three different BUTTONS to run your macros. ...
|
| Merging 2 workbook pages | 5/10/2008 |
Q: I have 2 pages, one has 10 cols of data, the other has 5 cols of data, the only thing each ...
A: Marilyn, this is really a task for a DATABASE program like Microsoft Access. Using Excel to do this ...
|
| splitting an address field seperated by space | 5/9/2008 |
Q: I have an exported file that has the address field as plain text seperated by space. I need to ...
A: If the State is ALWAYS 6 places from the right, then it shouldn't be a problem. Here's an example ...
|
| Calculating total hours worked from pm to am the next day | 5/8/2008 |
Q: I have set up a basic staff roster spreadsheet which calculates total hours worked and wages. ...
A: I would recommend making another cell that joins together the date and time into ONE cell, so you ...
|
| Separating text - problem with spaces | 5/7/2008 |
Q: I keep getting an error when I try to find spaces in a text string in order to split off surnames ...
A: Heather, your formulae look OK to me. Personally, I break it down into two separate functions - I ...
|
| Formula question | 5/6/2008 |
Q: Can you tell me how/if it is possible to do the following? I've created a table which shows an ...
A: Just put an IF function in your different columns to display the value if it falls in your date ...
|
| Excel | 5/6/2008 |
Q: My problem is if i have a value in A1 & B1 and i put the formula in C1 as A1*B1, the cell formula ...
A: Well, it wouldn't show "5*5" it should show "25" Sounds like you have Excel set to display ...
|
| Excel | 5/3/2008 |
Q: I used the SumProduct example from Douglas M. Smith ...
A: You can't just check a range inside the AND function. You can individually check ALL of the values ...
|
| Sum If Greater than | 5/2/2008 |
Q: I want to know if you can help me, i want to sum up a range of Col Z 60-5000 but only if a date on ...
A: Yes, Jeff, you can do this, but you'll need to know how to use the IF function, and I'm assuming you ...
|
| Excel Formula | 5/1/2008 |
Q: I want to have a column that I can type "yes" or "no" in a cell, with yes = "1" and "no" = 0 and the ...
A: You'll need two columns for this. Type in YES and NO in, let's say, column A. Now in column B you ...
|
| Linking Excel Worksheets | 4/30/2008 |
Q: I know that I can link a cel from one worksheet to another- and how to do that. What I am wondering ...
A: You can highlight an entire RANGE of cells, COPY it, and then switch to your other sheet, ...
|
| IF Function | 4/29/2008 |
Q: When Nesting with the IF function in Excel 2007, how 'deep' can you go - is there a limit of 8?
A: I believe the maximum limit is 255 characters for the entire cell, not how many "levels" your ...
|
| microsoft excel | 4/28/2008 |
Q: how do you know which cell is the active cell?
A: It's the one that is highlighted and it's name shows up in the NAME BOX. Let me know if you have ...
|
| excel | 4/26/2008 |
Q: My question is , when I write text into a cell,(for this particular instance) I am only allowed 55 ...
A: You can use the LEN function to find the length of a string, but I don't know (off the top of my ...
|
| Excell | 4/26/2008 |
Q: how can we use hlookup formula for two diffirent work books in ms excel
A: Here's a tutorial on VLOOKUP which is a close cousin to HLOOKUP: ...
|
| calculating time in excel | 4/25/2008 |
Q: I am trying to figure billable attendance in excel. I have calculated the actual hours in the ...
A: Just use the IF function, and say: =IF(A1>5,5,A1) Assuming your value is in cell A1. See this ...
|
| IF statements and error messages | 4/25/2008 |
Q: I have a spreadsheet where 1 colum is a validated list with 22 options in column D. For three of ...
A: Honestly, something like this would be MUCH better off in Microsoft Access than Excel... but you ...
|
| frequency | 4/24/2008 |
Q: I need some help. I'm no excel pro! This is what i need : I want to know the average the product ...
A: Good question. If you have two dates, let's say 1/1/2008 and 2/1/2008, those days are 31 days ...
|
| add space between number and text | 4/24/2008 |
Q: how do i add space between number and text in excel I have to add a space between house numbers and ...
A: I'm not sure I understand your question... are you saying you have NUMBERS in one column and STREET ...
|
| Formula bar missing | 4/24/2008 |
Q: When there is a formula in a cell and I go to that cell the the formula does not show any where on ...
A: You may have turned off the Formula Bar. Try clicking on View > Formula Bar (Excel 2003 or earler). ...
|
| Buttons to do addition | 4/23/2008 |
Q: Im needing some help with adding some addition buttons to an 8 sheet worksheet. To give a basic ...
A: I think I understand your question... you just want a series of buttons to add set amounts to values ...
|
| Comboboxes-finding the average | 4/23/2008 |
Q: I have a spreadsheet with comboboxes used for performance evaluations. Each combobox has the ...
A: Make sure your comboboxes are linked to a CELL on the sheet. Then, all you have to do is average up ...
|
