Drop down List w/ Values Pop Automacailly when choose entry from l

[CYNTHIA]

I want to make a drop down list.

I want to make a list w/ a drop down by in a closed different workbook not
open workbook. I do not want to create this drop down menu in the same
worksheet but in a different workbook.

I want to create this list by using validation w a closed workbook.

In this spreadsheet I am making i want when i choose from the drop down list
the price comes up automaically when I choose that entry.

Ex)
Rabbit $45.00
Fish $54.00
Banana $34.00


I choose these entries from a drop down menu. I want these prices to pop up
instead of typing manually.

Someone please help me!

 

[T. Valko]

Can't be done if the source wookbook is closed.

 

[Gord Dibben]

Cynthia

How to use a named list from another workbook using Data Validation.

http://www.contextures.on.ca/xlDataVal05.html

NOTE: the workbook must be open.

To pull the price for the selection use VLOOKUP formulas.

Also to be found at Debra's site

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

 

[CYNTHIA]

Thank you for answering my question.

But I saw in a past spreadsheet that I was able to pull from the drop down
menu and not see where the person got the names.

I chose the name from a drop down menu and the values popped up automaically.

If I want to make this in another workbook without the user seeing it how
can that be done? Running a macro?

I want when the names are choosen from the drop down menu the prices pop up.

How do i do that?

 

[CYNTHIA]

I don't know how that is why I am asking.

Please help. This is in worksheet 2. My table. Wouldn't I use hlookup? Im
not sure how to set it up.

Column A Column B
1 Rabbit $45.00
2 Fish $54.00
3 Banana $34.00

Workbook 1 I choose and I want the price automatically to pop up
Column D Column E
12 Fish $54.00
13 Banana $34.00
14 Banana $34.00

 

[RagDyer]

You can put your list in the same WB, on another sheet, and then just hide
that sheet.

So, in Sheet 2, enter your datalist.
Then, select A1 to B3.
Click in the Name Box (left of the formula bar), and type in a short name,
say
"list" (no quotes).
Then hit <Enter>.

Next, select only A1 to A3, and do the same thing, but type in "name" (no
quotes),
then hit <Enter>.

Then, from the Menu bar:
<Format> <Sheet> <Hide>

This puts you in Sheet1, with Sheet2 now being hidden.

Select A1 to A3, and from the menu Bar:
<Data> <Validation>,
And under the "Settings" tab, expand the "Allow" box and click on "List".

In the "Source" box, type in:
=name
Then <OK>.

You should now have your drop-downs in A1 to A3.

In B1, enter this formula:

=IF(A1="","",LOOKUP(A1,list))

And copy down to B3.

This should do what you asked for.

 

[RagDyer]

Forgot to mention, when using Lookup(), your datalist should be sorted
ascending, so it should look like this:

Banana $34.00
Fish $54.00
Rabbit $45.00



You could retain your original datalist configuration, and then use the
Vlookup() function instead:

=IF(A1="","",VLOOKUP(A1,list,2,0))

 

[CYNTHIA]

hi Rag,

Thanks for your help.

I did it a different way.

I did the same thing for my drop down menu.

I used vlookup in this same and what i did was =VLOOKUP(A4,Price,2,FALSE). I
got the same results. Price is the name in the cell.

 

[RagDyer]

Appreciate the feed-back.