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