comparing values in a sheet and nested if loops 2

[chineerat]

Hi there.

I have the following cells whose values will be in drop-down list. Except for "cost". "cost" will automatically generate values which are dependent on the values of "subject" and "schedule".

Subject Schedule Cost


I will have hidden in the worksheet a reference chart, all the combinations and permutations of "subject", "schedule" and "cost".
for example.

Subject Schedule Cost
maths full time 500
maths part time 300
english full time 525
english part time 255

etc...


So therefore when the user select "maths" in the "subject" field and "part time" in the "schedule" field the "cost" will be "300".

I am wondering if it is pratical to do this in excel, either through macros or formulas via 'if' statements? I am new to macros. If it can be done with formulas just point me in the right direction please...

Thanks a million in advance.

 

[xlbo]

can do this with vlookup formula - no need for VBA (macros)

you will need a new column that concatenates your subject and schedule

=A2&B2

where data is in columns A & B

from your drop down selection, use these elements to look up the data e.g.

=vlookup(F10&G10,LookupRange,2,false)

where your dropdown cells are F10 & G10

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Do something like:

=SUMPRODUCT(($A$2:$A$5=J1)*($B$2:$B$5=K1)*($C$2:$C$5))

Where J1 and K1 are the Subject and Schedule choices, and cols A B and C contain the reference table of Subjects, Schedules, and Rates, from row 2 to row 5.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[chineerat]

Thanks alot GlennUK and xlbo they both worked perfectly.

 

[GlennUK]

Great! :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.