Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with functions to nest | Excel Worksheet Functions | |||
How do I nest hyperlinks? | Excel Discussion (Misc queries) | |||
how do I nest functions | Excel Worksheet Functions | |||
How do I nest these 3 IF functions? | Excel Worksheet Functions | |||
7+ Nest If's | Excel Worksheet Functions |