what can I do if I need to nest more than 7 formulas?

[Maggie]

I have a spreadsheet to keep track of sales tax information. I have a drop
down list in the first column to choose the customer, and I want the next
several columns to fill in automatically with the customer's address and some
other information. I did fine with a nested IF formula until I reached the
limit of 7. We have more than 7 customers who we charge sales tax, and I need
to hit them all somehow

 

[Barb Reinhardt]

I'm not sure exactly what you're doing, but you could create a user defined
function to do this as well.

http://www.ozgrid.com/VBA/Functions.htm#UDFIntro

 

[Duke Carey]

You need to use the VLOOKUP() function, or perhaps the HLOOKUP() function.

You're not specific about how you are using the IF()s, but one of the lookup
variants should give you what you want.

You might also explore MATCH() and INDEX()

 

[Ronald Dodge]

Rather than useing IF, THEN, ELSE type function for this sort of thing, you
would actually be better served using one of the lookup functions such as
MATCH or VLOOKUP, or in more complex situations, even using the
DB<Functions> to get what you are looking for.

One such case would be the following:

Table contained is customer name in column "A", and their sales tax rate in
column "B"

You give column A the range name of "DBCustomerName"
You give column B the range name of "DBCustomerSalesTax"

This is assuming there is nothing else in those 2 columns of that particular
worksheet.


Now you are having G6 with the customer name and you want H6 to fill in
automatically with the sales tax percentage based on the customer name in
G6. Here's the formula to be in H6:

=IF(ISERROR(MATCH(G6,DBCustomerName,0)),"",INDIRECT(ADDRESS(MATCH(G6,DBCustomerName,0),COLUMN(DBCustomerSalesTax))))

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Niek Otten]

Hi Maggie,

Look here:

http://www.cpearson.com/excel/nested.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a spreadsheet to keep track of sales tax information. I have a drop
| down list in the first column to choose the customer, and I want the next
| several columns to fill in automatically with the customer's address and some
| other information. I did fine with a nested IF formula until I reached the
| limit of 7. We have more than 7 customers who we charge sales tax, and I need
| to hit them all somehow

 

[Maggie]

Basically using it to fill in info from another sheet. Ex:
IF(b9=customer1,customer1 address,IF(b9=customer2,customer2 address.....etc.

I don't really get how VLOOKUP and HLOOKUP work.

 

[Bernard Liengme]

Just the thing for VLOOKUP.
Read Help; buy a book; experiment; come back when you have tried
best wishes

 

[krcowen]

Maggie
Anybody who can handle 6 nested If statements can learn any of the
methods noted above.
Ken

 

[Maggie]

I figured vlookup out. Thank you!!!

You need to use the VLOOKUP() function, or perhaps the HLOOKUP() function.

You're not specific about how you are using the IF()s, but one of the lookup
variants should give you what you want.

You might also explore MATCH() and INDEX()