set up prices for a drop down list?

I have a drop down list and what i want to do (which I don't know if it can
be done)
is that once an item is selected in from the list then the price that would
correspond with that item pop up in another column.

Hi

this can easily be achieved using Data / Validation for the drop down menu
and VLOOKUP function to add the price into another column - here's some
notes i have on doing this:

assuming that in Sheet2 you have the following
..............A..............B
1......Item.....Value
2.....Item1.....10.00
3.....Item2.....15.00
4.....Item3..... 20.00

now select from A1 to the end of your list (A4, in the above example) and
choose from the menu,

Insert / Name / Create - ensure Top Row is checked and click okay - you've
created a named range called "Item" (or whatever the title in A1 is)
now select from A1 to the end of the list for all columns (B4 in the above
example) and click inside the name box (left of formula bar) and type
MyTable and press enter - we've created a second named range.

Now click in the cell in Sheet1 where you want your drop down list to appear
and choose Data / Validation from the menu - in the settings tab, choose
List from the Allow drop down box and then click inside the white box under
this and press the F3 key - this will bring up a list of your range names,
choose "Item" (or whatever your first range was called) and click Ok. You
will now have a drop down list in this cell.

Now click in the cell where you want the related information to appear and
type

=VLOOKUP(A1,MyTable,2,false)

where A1 is the cell reference with your drop down list in it - you can use
the F3 key for the MyTable bit too.

now choose an item & see the related information appear ... delete the item
and you'll get a #NA error - this can be supressed by embedding your VLOOKUP
in an IF statement e.g.
=IF(A1="","",VLOOKUP(A1,MyTable,2,false))

where A1 is the cell reference with your drop down list in it

hope this helps, let us know how you go

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"fireycowgrl" <[email protected]> wrote in message
news:[email protected]...
>I have a drop down list and what i want to do (which I don't know if it
>can
> be done)
> is that once an item is selected in from the list then the price that
> would
> correspond with that item pop up in another column.

=if(a1="","",vlookup(A1,tablewith prices,2,False))

--
Regards,
Tom Ogilvy

"fireycowgrl" <[email protected]> wrote in message
news:[email protected]...
> I have a drop down list and what i want to do (which I don't know if it
can
> be done)
> is that once an item is selected in from the list then the price that
would
> correspond with that item pop up in another column.

Thank you for the help.

"Tom Ogilvy" wrote:

> =if(a1="","",vlookup(A1,tablewith prices,2,False))
>
> --
> Regards,
> Tom Ogilvy
>
> "fireycowgrl" <[email protected]> wrote in message
> news:[email protected]...
> > I have a drop down list and what i want to do (which I don't know if it
> can
> > be done)
> > is that once an item is selected in from the list then the price that
> would
> > correspond with that item pop up in another column.
>
>
>

Thank you so much this should work or at least i hope soo....It reads like
what I am looking for.

"JulieD" wrote:

> Hi
>
> this can easily be achieved using Data / Validation for the drop down menu
> and VLOOKUP function to add the price into another column - here's some
> notes i have on doing this:
>
> assuming that in Sheet2 you have the following
> ..............A..............B
> 1......Item.....Value
> 2.....Item1.....10.00
> 3.....Item2.....15.00
> 4.....Item3..... 20.00
>
> now select from A1 to the end of your list (A4, in the above example) and
> choose from the menu,
>
> Insert / Name / Create - ensure Top Row is checked and click okay - you've
> created a named range called "Item" (or whatever the title in A1 is)
> now select from A1 to the end of the list for all columns (B4 in the above
> example) and click inside the name box (left of formula bar) and type
> MyTable and press enter - we've created a second named range.
>
> Now click in the cell in Sheet1 where you want your drop down list to appear
> and choose Data / Validation from the menu - in the settings tab, choose
> List from the Allow drop down box and then click inside the white box under
> this and press the F3 key - this will bring up a list of your range names,
> choose "Item" (or whatever your first range was called) and click Ok. You
> will now have a drop down list in this cell.
>
> Now click in the cell where you want the related information to appear and
> type
>
> =VLOOKUP(A1,MyTable,2,false)
>
> where A1 is the cell reference with your drop down list in it - you can use
> the F3 key for the MyTable bit too.
>
> now choose an item & see the related information appear ... delete the item
> and you'll get a #NA error - this can be supressed by embedding your VLOOKUP
> in an IF statement e.g.
> =IF(A1="","",VLOOKUP(A1,MyTable,2,false))
>
> where A1 is the cell reference with your drop down list in it
>
> hope this helps, let us know how you go
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "fireycowgrl" <[email protected]> wrote in message
> news:[email protected]...
> >I have a drop down list and what i want to do (which I don't know if it
> >can
> > be done)
> > is that once an item is selected in from the list then the price that
> > would
> > correspond with that item pop up in another column.
>
>
>

do feel free to post back if you get stuck.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"fireycowgrl" <[email protected]> wrote in message
news:[email protected]...
> Thank you so much this should work or at least i hope soo....It reads like
> what I am looking for.
>
> "JulieD" wrote:
>
>> Hi
>>
>> this can easily be achieved using Data / Validation for the drop down
>> menu
>> and VLOOKUP function to add the price into another column - here's some
>> notes i have on doing this:
>>
>> assuming that in Sheet2 you have the following
>> ..............A..............B
>> 1......Item.....Value
>> 2.....Item1.....10.00
>> 3.....Item2.....15.00
>> 4.....Item3..... 20.00
>>
>> now select from A1 to the end of your list (A4, in the above example) and
>> choose from the menu,
>>
>> Insert / Name / Create - ensure Top Row is checked and click okay -
>> you've
>> created a named range called "Item" (or whatever the title in A1 is)
>> now select from A1 to the end of the list for all columns (B4 in the
>> above
>> example) and click inside the name box (left of formula bar) and type
>> MyTable and press enter - we've created a second named range.
>>
>> Now click in the cell in Sheet1 where you want your drop down list to
>> appear
>> and choose Data / Validation from the menu - in the settings tab, choose
>> List from the Allow drop down box and then click inside the white box
>> under
>> this and press the F3 key - this will bring up a list of your range
>> names,
>> choose "Item" (or whatever your first range was called) and click Ok. You
>> will now have a drop down list in this cell.
>>
>> Now click in the cell where you want the related information to appear
>> and
>> type
>>
>> =VLOOKUP(A1,MyTable,2,false)
>>
>> where A1 is the cell reference with your drop down list in it - you can
>> use
>> the F3 key for the MyTable bit too.
>>
>> now choose an item & see the related information appear ... delete the
>> item
>> and you'll get a #NA error - this can be supressed by embedding your
>> VLOOKUP
>> in an IF statement e.g.
>> =IF(A1="","",VLOOKUP(A1,MyTable,2,false))
>>
>> where A1 is the cell reference with your drop down list in it
>>
>> hope this helps, let us know how you go
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "fireycowgrl" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a drop down list and what i want to do (which I don't know if it
>> >can
>> > be done)
>> > is that once an item is selected in from the list then the price that
>> > would
>> > correspond with that item pop up in another column.
>>
>>
>>