Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
I have a Dependent Validation data that I need it to complete this statement
IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
Perhaps this would work for you - the problem seems to be if the class is
LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
You're the BEST-THANK YOU THANKYOU THANK YOU
2 more that you can tackle: 1. When I filled the function down until I fill the TYPE in the cost cell shows #N/A, what do I add to it to show $0 til I choose the type 2. Need a summary sheet of the 2 sheets to analyze, I want it to automatically fill when rows are added 2 different people will be using them so copying pasting is tedious THANKS AGAIN!!!!!! -- Thanks again, Herz and His "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
Now I am going to sound greedy, can I add another column in my table to
populate the cooresponding # of hours per class, so when the class is chosen it enters the cost and the hours -- Thanks again, Herz and His "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
for a snapshot view, may i suggest also something like this...
=IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB","l imited Free offer!",VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) <bg thanks.. -- regards, driller ***** - dive with Jonathan Seagull "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
#1. Use this formula instead:
=IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, IF(ISNA(VLOOKUP(E2,DATA!$G$2:$H$34,2,0)),"",VLOOKU P(E2,DATA!$G$2:$H$34,2,0)))) again, that's all on one line. What that will do is leave the cell 'empty' looking if no match is found in the table. If you still want a zero there, change the ,"", to ,0, between the two VLOOKUP() pieces. #2 - I need to review what you wrote earlier and get a better picture in my mind of how things are set up to do this. Basically it's probably going to take some VBA code to accomplish that. "HERZHIS" wrote: You're the BEST-THANK YOU THANKYOU THANK YOU 2 more that you can tackle: 1. When I filled the function down until I fill the TYPE in the cost cell shows #N/A, what do I add to it to show $0 til I choose the type 2. Need a summary sheet of the 2 sheets to analyze, I want it to automatically fill when rows are added 2 different people will be using them so copying pasting is tedious THANKS AGAIN!!!!!! -- Thanks again, Herz and His "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
Yes, you certainly can. Look up the VLOOKUP() function in Excel Help for
more explanations. But basically what you'll need to do is set up those hours for each class in column I, then you can put this formula where you want to pull the hours =VLOOKUP(E2,DATA!$H$2:$I$34,3,0) or if you use a variation of the long formula we built up earlier, just put the VLOOKUP() I just showed you where the other one is in the two places in the formula. Be carefule with your cut and paste, it's easy to get lost among all those parenthesis. To give a brief recap of VLOOKUP(p1, p2, p3, p4) p1 is the "what to match in the leftmost column of a table" p2 is the address of the table from upper left corner to lower right corner p3 is which column of the table to retrieve data from. p4 is an optional parameter, when 0 or FALSE it says the list in the 1st column does not have to be in order, if it is 1 or TRUE then your first column of information must be sorted in ascending order for it to work reliably. "HERZHIS" wrote: Now I am going to sound greedy, can I add another column in my table to populate the cooresponding # of hours per class, so when the class is chosen it enters the cost and the hours -- Thanks again, Herz and His "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF STATEMENT HELP CHOOSE AND GET COST
BRILLANT IT WORKED, THANKS AGAIN
2. I knew it was going to come down to that but I was just in denial about the VBA code I haven't used it in awhile - I have 12 column sheets A:L, both sheets are identical except Column A has a different group of people both referenced in the DATA sheet, and the rest of the information are dates,the class information, cost and misc notes. I need both sheets combined into one sheet and do some other summarizing after they are compbined for manager review -- Thanks again, Herz and His "JLatham" wrote: #1. Use this formula instead: =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, IF(ISNA(VLOOKUP(E2,DATA!$G$2:$H$34,2,0)),"",VLOOKU P(E2,DATA!$G$2:$H$34,2,0)))) again, that's all on one line. What that will do is leave the cell 'empty' looking if no match is found in the table. If you still want a zero there, change the ,"", to ,0, between the two VLOOKUP() pieces. #2 - I need to review what you wrote earlier and get a better picture in my mind of how things are set up to do this. Basically it's probably going to take some VBA code to accomplish that. "HERZHIS" wrote: You're the BEST-THANK YOU THANKYOU THANK YOU 2 more that you can tackle: 1. When I filled the function down until I fill the TYPE in the cost cell shows #N/A, what do I add to it to show $0 til I choose the type 2. Need a summary sheet of the 2 sheets to analyze, I want it to automatically fill when rows are added 2 different people will be using them so copying pasting is tedious THANKS AGAIN!!!!!! -- Thanks again, Herz and His "JLatham" wrote: Perhaps this would work for you - the problem seems to be if the class is LOCAL, since you say that prices for Tech and Factory are both 1000 and for Web it is $0. Set up a table on the DATA sheet that has all the individual Local classes listed in one column (G2:G34 for this example) and in column H next to each is the cost of that individual local class, in H2:H34. Then this formula in F2 may serve you well (change TECH, FACTORY and WEB to the actual phrases that would appear when chosen) : =IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB",0, VLOOKUP(E2,DATA!$G$2:$H$34,2,0))) if word wrap splits that here, it's all one line. It says if they chose TECH or FACTORY in D2, then show cost of $1000, and if it wasn't one of those, then if they chose WEB, show cost of zero, and if none of those, then do a lookup of the Local Class chosen in E2 in the G2:H34 table on the DATA sheet and return the associated cost. "HERZHIS" wrote: I have a Dependent Validation data that I need it to complete this statement IF D2=LOCAL,TECH,FACTORY,OR WEB (TYPES OF CLASSES) IS CHOOSEN E2=THE INDIVIDUAL TYPE OF CLASS IS AVAILABLE CHOOSE AND WHEN THE CLASS IS CHOOSEN: F2=THE COST AUTOMATICALLY POPULATES ALL THE DATA IS ON A SEPERATE SHEET IN THE WORKBOOK CALLED DATA COLUM A - THE 4 TYPES COLUMN B - LOCAL CLASSES COLUMN C - COST OF EACH LOCAL CLASS COLUMN D - WEB CLASSES COLUMN E - TECH CLASSES COLUMN F - FACTORY CLASSES 4 DIFFERENT COST BUT 3 OF THE TYPES ARE SAME: TECH AND FACTORY=1000 AND WEB=0 FOR THE CLASS CHOOSEN LOCAL TYPE IS THE MIND WRENCHING PART OF THIS IT HAS 11 DIFFERENT CLASSES=3 DIFFERENT COSTS I HAD A IF STATEMENT BUT NOW THAT i HAVE MORE THAN 7 CLASSES FOR LOCAL TYPE, I NEED HELP!!!!!!!!!!!!!!!!!!!! SAMPLE DATA AVAILABLE IN SCHEDULE WORKSHEET TO DROP DOWN AND CHOOSE THE TYPE IN D2 AND E2 MAKES THE DIFFERENT TYPE CLASSES TO CHOOSE FROM AND WHEN THAT IS CHOSEN F2 AUTOMATICALLY POPULATES THE COST OF THE CLASS: D E F 2 LOCAL CLASS 1-6 $205.00 WEB CLASS 7-10 $250.00 TRANEONLY CLASS 11 $300.00 FACTORY CLASS 12-28 $0.00 CLASS 29-32 $1000.00 CLASS 32-48 $1000.00 -- Thanks again, Herz and His |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Cost versus Original Cost | New Users to Excel | |||
our cost to customers cost | Excel Worksheet Functions | |||
our cost to customers cost | Excel Worksheet Functions | |||
Confusion on adding percentage of cost to that cost. | Excel Worksheet Functions | |||
What Bus. templete do I use for daily cost w/ daily & cum cost ? | Excel Discussion (Misc queries) |