Longer If Formulas

[davethearchitect]

I am trying to use the If formula to varify values but I have more then 7 functions. I know there is a way to get more then 7. If someone could help me I would be greatly thankful

Dave

 

[barry houdini]

Hello Dave,

If you reach the limit of 7 nested IFs then normally it's time to investigate using another function or functions, possibly some sort of LOOKUP. Can you describe what you want to achieve?

 

[Joe4]

In an IF formula, you can only next up to 7 levels.

If you have more, than using IF probably isn't the best tool to be using anyway. Something like a VLOOKUP formula would probably be more appropriate.

If you explain your problem in a bit more detail, someone would probably be able to help you construct an efficient solution to your problem.

 

[davethearchitect]

Thanks for getting back to me so fast, what i am trying to do is:

If Cell A2 = (cell named Beds) then put in a price from another spreedsheet tab. buf if Cell A2 = (cell named Plants) then....

=IF(D22=Beds,'Front Price and Qty'!J39,IF(D22=Plants,'Front Price and Qty'!J236,"")) and so on

So D22 is a drop down and i pick which category i want to put on my invoice and if i chose beds for D22, then it will automatically take the price from my pricing page. so i have about 20-30 different categories.

 

[barry houdini]

Your best bet is a VLOOKUP.

If you have a dropdown cell that is based on a list you could list the prices in the next column (or use a formula in the next column to get the price from elsewhere).

E.g. if you have categories listed in B2:B30 and prices in C2:C30

=VLOOKUP(D22,B2:C30,2,0)

 

[shippey121]

you can use the & symbol

go here and look under the Concatenate section

http://www.j-walk.com/ss/excel/usertips/tip080.htm

HTH

 

[davethearchitect]

Thanks a lot

On the link given by shippey121 there is the CONCATENATE(IF... formula thats what i was lookn for.

Dave