Using drop-down lists

[RoofIL]

I created a drop down list using data validation in sheet 1 from data in
sheet 2. The data in sheet 2 reads:

A B
1 40 mil - No Tabs $0.83
2 40 mil - 60" Tabs $0.88
3 40 mil - 120" Tabs $0.88
4 40 mil - 28" Tabs $0.95
5 40 mil - Parapets $0.95
6 50 mil - No Tabs $0.93
7 50 mil - 60" Tabs $0.98
8 50 mil - 28" Tabs $1.05
9 50 mil - Parapets $1.05
10 60 mil - No Tabs $1.03
11 60 mil - 60" Tabs $1.08
12 60 mil - 28" Tabs $1.15
13 60 mil - Parapets $1.15

The selection A1:A13 is titled "DuroLastMembrane" Column A is types of
material and column B is their price per square foot.

Sheet 1 reads

A B
C
1 Duro-Last Membrane Square footage Cost
2 DuroLastMembrane =product(A2,B2)

A1,B1 and C1 are column titles. A2 is the drop down list with data from
A1:A13 on sheet two.

I want to be able to enter in a number value in B2 ("Square footage") and
have C2 give me the product of B2 and whatever dollar amount is in column B
on sheet two, depending on which value I pick from the drop-down list in.

Thanks so much.

 

[Luke M]

=IF(OR(A2="",B2=""),"-",VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)*B2)

Copy down as needed.

 

[RoofIL]

Thanks for the help. When I enter this formula and hit enter, a window
title: "Update Values: Sheet 2" pops up. It looks like a "save" or "save as"
window. What do I do there?

Also, I'm not proficient with the formulas in Excel. Can you break down
that formula into what each part means, so I can have a better understanding
going forward? That way I'm not just copying and pasting without actually
learning, so if it doesn't work the way I want, I'll know how to change it.
I'd appreciate it.

Thanks.

 

[RoofIL]

Also, When I copy and paste the function you provided into C2 on sheet 1 and
press enter I get #N/A.

 

[Gord Dibben]

Do you have a Sheet2 or is it named something else?

If so, change the formula to reflect that.


Gord Dibben MS Excel MVP