Change formula based on results of a drop down

[Megtruck]

Hi All:
I am hoping what I want to be able to do is a function of excel. I am sure it is, I just dont know how to do it, and have a hard time explaining it, so bare with me!!

I have a spreadsheet for work with a bunch of different drop down columns and formulas based on the different vehicles we work on.

I work for a heavy duty collision repair shop and I have created a spreadsheet to track all of our jobs.

So I have a column for "job type" and there are many types listed in the drop down. I also have another column labeled "job total". That column has a formula in it to add up the totals of all other categories and give me one total. Part of these categories include different labor rates.

I am wondering if there is a way to have excel recognize when i pull up a certain "job type" that the formula will change the labor rate automatically?

I hope this makes sense. I am willing to share my spreadsheet if that would help.

Thanks
Meaghan

[hrlngrv]

If the job type would be in cell F7, you could use a table somewhere else with job types in column 1 and corresponding labor rates in column 2. For example,

Job Type	Labor Rate
A	60.00
B	62.50
C	63.75
D	65.00

If this table were in X!A1:B5 (in another worksheet named X), you could pull in the labor rate using =VLOOKUP(F7,X!$A$2:$B$5,2,0).

Table lookups are generally the best way to handle this sort of thing.

[Megtruck]

Where would I put that formula on the spreadsheet?

[jeffreybrown]

Where would I put that formula on the spreadsheet?

You're going to put this formula wherever you want the result! The formula you were given...

=VLOOKUP(F7,X!$A$2:$B$5,2,0) >> =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

F7 >> lookup_value >> job type

X!$A$2:$B$5 >> table_array >> table used to lookup the result

2 >> col_index_num >> returns the second column for the table above

0 >> range_lookup >> returns an exact match

This is all speculative as we don't know the actual layout of your spreadsheet. You will have to adjust all references to meet your needs.

Hope this helps,