Drop Down Lists - assign a value based on selection

Okay, first here is what I have. I will use examples to be as detailed as
possible.

On my worksheet I have a column (Lets say A1). In that column there is a
drop down menu/list of different types of materials. I have created this drop
down from a range of cells using the data/validation/list tool.

Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
8, etc.) next to that cell (lets say A2) I need a formula that will
automatically select a wieght of the type of lumber I choose. For example, a
2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
cell A2 to automatically display "2.25".

Is this possible?

Thanks

Neil, see if this will help,

http://www.contextures.com/xlDataVal02.html

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Neil M" <[email protected]> wrote in message
news:[email protected]...
> Okay, first here is what I have. I will use examples to be as detailed as
> possible.
>
> On my worksheet I have a column (Lets say A1). In that column there is a
> drop down menu/list of different types of materials. I have created this
drop
> down from a range of cells using the data/validation/list tool.
>
> Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2
x
> 8, etc.) next to that cell (lets say A2) I need a formula that will
> automatically select a wieght of the type of lumber I choose. For example,
a
> 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I
want
> cell A2 to automatically display "2.25".
>
> Is this possible?
>
> Thanks

On another sheet in that workbook, enter a 2 column list of Lumber Size and
Weight
Example:
Stock Wgt
2 x 4 2.25
4 x 4 3.50
etc.

Then select that range and name it:
Insert>Name>Define
Name: LU_StockWgt
Refers to: (your range)

Next, on your main sheet:
A2: =VLOOKUP(A1,LU_StockWgt,2,0)

If you don't want to see the #NA when the dropdown cell is empty, try this:
A2: =IF(ISBLANK(A1,"",VLOOKUP(A1,LU_StockWgt,2,0))

Does that help?

••••••••••
Regards,
Ron


"Neil M" wrote:

> Okay, first here is what I have. I will use examples to be as detailed as
> possible.
>
> On my worksheet I have a column (Lets say A1). In that column there is a
> drop down menu/list of different types of materials. I have created this drop
> down from a range of cells using the data/validation/list tool.
>
> Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
> 8, etc.) next to that cell (lets say A2) I need a formula that will
> automatically select a wieght of the type of lumber I choose. For example, a
> 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
> cell A2 to automatically display "2.25".
>
> Is this possible?
>
> Thanks

2nd Formula typo correction:
A2: =IF(ISBLANK(A1),"",VLOOKUP(A1,LU_StockWgt,2,0))


••••••••••
Regards,
Ron


"Neil M" wrote:

> Okay, first here is what I have. I will use examples to be as detailed as
> possible.
>
> On my worksheet I have a column (Lets say A1). In that column there is a
> drop down menu/list of different types of materials. I have created this drop
> down from a range of cells using the data/validation/list tool.
>
> Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
> 8, etc.) next to that cell (lets say A2) I need a formula that will
> automatically select a wieght of the type of lumber I choose. For example, a
> 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
> cell A2 to automatically display "2.25".
>
> Is this possible?
>
> Thanks

Going to try these right now and will advise. Thanks very much

"Neil M" wrote:

> Okay, first here is what I have. I will use examples to be as detailed as
> possible.
>
> On my worksheet I have a column (Lets say A1). In that column there is a
> drop down menu/list of different types of materials. I have created this drop
> down from a range of cells using the data/validation/list tool.
>
> Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
> 8, etc.) next to that cell (lets say A2) I need a formula that will
> automatically select a wieght of the type of lumber I choose. For example, a
> 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
> cell A2 to automatically display "2.25".
>
> Is this possible?
>
> Thanks

YESSSSSSSSSSS!!!! I HAVE EXORCISED THE DEMON!!!

Thank you so very much Ron!!

It worked out great.
Neil

"Ron Coderre" wrote:

> On another sheet in that workbook, enter a 2 column list of Lumber Size and
> Weight
> Example:
> Stock Wgt
> 2 x 4 2.25
> 4 x 4 3.50
> etc.
>
> Then select that range and name it:
> Insert>Name>Define
> Name: LU_StockWgt
> Refers to: (your range)
>
> Next, on your main sheet:
> A2: =VLOOKUP(A1,LU_StockWgt,2,0)
>
> If you don't want to see the #NA when the dropdown cell is empty, try this:
> A2: =IF(ISBLANK(A1,"",VLOOKUP(A1,LU_StockWgt,2,0))
>
> Does that help?
>
> ••••••••••
> Regards,
> Ron
>
>
> "Neil M" wrote:
>
> > Okay, first here is what I have. I will use examples to be as detailed as
> > possible.
> >
> > On my worksheet I have a column (Lets say A1). In that column there is a
> > drop down menu/list of different types of materials. I have created this drop
> > down from a range of cells using the data/validation/list tool.
> >
> > Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
> > 8, etc.) next to that cell (lets say A2) I need a formula that will
> > automatically select a wieght of the type of lumber I choose. For example, a
> > 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
> > cell A2 to automatically display "2.25".
> >
> > Is this possible?
> >
> > Thanks

Thanks for the feedback, Neil....much appreciated. I'm glad I could help.

Regards,
Ron


"Neil M" wrote:

> YESSSSSSSSSSS!!!! I HAVE EXORCISED THE DEMON!!!
>
> Thank you so very much Ron!!
>
> It worked out great.
> Neil
>
> "Ron Coderre" wrote:
>
> > On another sheet in that workbook, enter a 2 column list of Lumber Size and
> > Weight
> > Example:
> > Stock Wgt
> > 2 x 4 2.25
> > 4 x 4 3.50
> > etc.
> >
> > Then select that range and name it:
> > Insert>Name>Define
> > Name: LU_StockWgt
> > Refers to: (your range)
> >
> > Next, on your main sheet:
> > A2: =VLOOKUP(A1,LU_StockWgt,2,0)
> >
> > If you don't want to see the #NA when the dropdown cell is empty, try this:
> > A2: =IF(ISBLANK(A1,"",VLOOKUP(A1,LU_StockWgt,2,0))
> >
> > Does that help?
> >
> > ••••••••••
> > Regards,
> > Ron
> >
> >
> > "Neil M" wrote:
> >
> > > Okay, first here is what I have. I will use examples to be as detailed as
> > > possible.
> > >
> > > On my worksheet I have a column (Lets say A1). In that column there is a
> > > drop down menu/list of different types of materials. I have created this drop
> > > down from a range of cells using the data/validation/list tool.
> > >
> > > Now, in this menu, I have different types of lumber (i.e. 2 x 4, 2 x 6, 2 x
> > > 8, etc.) next to that cell (lets say A2) I need a formula that will
> > > automatically select a wieght of the type of lumber I choose. For example, a
> > > 2 x 4 weighs 2.25 lbs per foot. So, if I scroll to "2 x 4" in cell A1, I want
> > > cell A2 to automatically display "2.25".
> > >
> > > Is this possible?
> > >
> > > Thanks