Take a look at Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html
She shows how to use a named range to do what you want.
So say your list of items/prices is in Sheet2!a1:B200.
And your cell with data|validation is in A1 of sheet1.
You could use this formula:
=if(a1="","",vlookup(a1,sheet2!a:b,2,false))
wings wrote:
I am trying to create an invoice template where I am able to populate the
items sold list and prices from another worksheet containing all the items I
sell and their relevant prices.
I thought that I would do this by using a drop down list for each cell in
the list of items purchased. When I select an item from the drop down list
of item descriptions I want the associated price to also be automatically
added to the 'unit price' column.
The first problem I have come across is creating the drop down list. I
tried to use the Data|Validation method as described by the Excel help which
says that you can reference other worksheets or workbooks as the 'source'.
When I try this Excel complains that sources from other worksheets or
workbooks CANNOT be used.
First question is - am I choosing the best way to achieve my aim?
Next - should I be able to reference another worksheet using the
Data|Validation method of creating a drop down list?
Finally - having successfully selected an item from my list of descriptions
how can I get the associated price to populate the 'unit price' column?
Sorry there are a number of things I need help with here, but I would
appreciate any ideas/suggestions that may help me achieve the aim.
--
Dave Peterson