IF STATEMENT HELP CHOOSE AND GET COST

[HERZHIS]

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

 

[JLatham]

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]

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!!!!!!

 

[HERZHIS]

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

 

[driller]

for a snapshot view, may i suggest also something like this...

=IF(OR(D2="TECH",D2="FACTORY"),1000,IF(D2="WEB","limited Free
offer!",VLOOKUP(E2,DATA!$G$2:$H$34,2,0)))
<bg> thanks..
--
regards,
driller

*****
- dive with Jonathan Seagull

 

[JLatham]

#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)),"",VLOOKUP(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.

 

[JLatham]

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]

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