Formula

Using Excel 2007...I have used basic formulas in the past, but definitely not real familiar with them.

What I want to do is if cell A1 has a specific value in it then a specified cell will indicate a given value.
In other words, if A1 has an "A" in it, then C3 might read "Holy Cow!"

Thanks for any help...an email with a link would be great, or any specific reply.

Durban

In Excel 2007 you can use more than 7 IF statements, but this will get ugly if you have too many criteria, something like

=IF(A1="A",Holy Cow","")

This says if A1 is "A" then put Holy Cow, or else leave the cell blank "".

If you need multiple criteria then you 'nest IF statements, so if you wanted A to equal Holy Cow and B to equal Darn It, then you would have

=IF(A1="A","Holy Cow",IF(A1="B","Darn It",""))

Probably a better solution would be a VLOOKUP solution and table elsewhere, so imagine a table in E1:F26 with the letters of the alphabet in E1:E26 and the corresponding curses in F1:F26, then in your cell enter

=VLOOKUP(A1,$E$1:$F$26,2,FALSE)

This will lookup the value in A1 in the new table you have set up (Left column) and when it finds a match will take the cell in the second column (your curse) and return that. this is fairly well documented in help if you need much more

Hello Nick,

Thank you for your reply, however I'm afraid I need further explanation.

What I hope to do is plug a given text value in a different cell if, for instance cell A1 contains an unrelated text value. So, I put a part number in cell A1, the formula will automatically put a dollar value in a different cell in the same workbook, but on a different sheet. And can I put yet another value in yet another cell in a 3rd sheet. If you can help me with just one example, I'm sure I can work it out from there. I apologize for being so ignorant on this subject.

Thanks for your help,

Durban

Durban

I'm not too sure I understand, is the cell that you want the result in random???

Let me try and explain my understanding. If you want to produce say an invoice on sheet2 from a list of parts on sheet1 for example then build your table of parts and prices on sheet1 (Part# in column A, Price in column B and we'll say that list extends down to row 20, A1:B20). Do this first, part# in A1:A20 and the corresponding prices in B1:B20

Now go to sheet 2 and we will imagine the area set aside to enter part# is in column A (starting at row 6) and you want the price to appear in column B (again starting at row 6). So in cell B6 enter:

=VLOOKUP(A6,Sheet1!$A$1:$B$20,2,FALSE)

Now, without a part# in A6 this will return #N/A as it cannot find the blank cell in the list on sheet1 but if you now enter a part# from the list in A6 it will show the price in B6. You can copy B6 down as far as you want to cells below to make your list of invoice items.

Not wanting to confuse at this early stage, but the #N/A is annoying when you have no part# in column a so you can add an IF and error check to return nothing until you have a part#

=IF(ISNA(VLOOKUP(A6,Sheet1!$A$1:$B$20,2,FALSE)),"",VLOOKUP(A6,Sheet1!$A$1:$B$20,2,FALSE))

But that's for next time

Many thanks for your patience...this may do the trick.

I am trying to prepare a form that will allow me to automatically fill in prices for me when I type in part #'s, and eventually automatically generate the commission due the sales person as well. Don't know if I'll have the stamina to follow through, but you certainly seem to have pointed me in the right direction.

Many thanks,

Donn