Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using drop-down lists
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using drop-down lists
=IF(OR(A2="",B2=""),"-",VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)*B2)
Copy down as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RoofIL" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using drop-down lists
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. "Luke M" wrote: =IF(OR(A2="",B2=""),"-",VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)*B2) Copy down as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RoofIL" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using drop-down lists
Also, When I copy and paste the function you provided into C2 on sheet 1 and
press enter I get #N/A. "Luke M" wrote: =IF(OR(A2="",B2=""),"-",VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)*B2) Copy down as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RoofIL" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using drop-down lists
Do you have a Sheet2 or is it named something else?
If so, change the formula to reflect that. Gord Dibben MS Excel MVP On Fri, 5 Feb 2010 11:25:01 -0800, RoofIL wrote: 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. "Luke M" wrote: =IF(OR(A2="",B2=""),"-",VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)*B2) Copy down as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RoofIL" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Lists: Allow option of adding own data if not in drop do | Excel Worksheet Functions | |||
How to create 2 dependent drop down lists from 1 original drop dow | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
how do I use one drop-list to modify another drop-lists options? | Excel Discussion (Misc queries) | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions |