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

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

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

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

"Maggie" wrote:

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

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

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()


"Maggie" wrote:

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

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

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)),"",INDIREC T(ADDRESS(MATCH(G6,DBCustomerName,0),COLUMN(DBCust omerSalesTax))))

Ronald R. Dodge, Jr.
Master MOUS 2000

"Maggie" wrote in message
...
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

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

Hi Maggie,

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Maggie" wrote in message ...
|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

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

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.

"Duke Carey" wrote:

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()


"Maggie" wrote:

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

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

Just the thing for VLOOKUP.
Read Help; buy a book; experiment; come back when you have tried
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Maggie" wrote in message
...
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.

"Duke Carey" wrote:

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()


"Maggie" wrote:

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

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

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



On May 7, 3:15 pm, "Niek Otten" wrote:
Hi Maggie,

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Maggie" wrote in ...

|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

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

I figured vlookup out. Thank you!!!

"Duke Carey" wrote:

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()


"Maggie" wrote:

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