Dave,

Thanks for that - I think I am getting the hang of what I need to do now.

One thing perplexes my though. When I first tried to use Data|Validation to
call up a range of cells from another worksheet for my drop down list, I
defined a name for the range of cells on the sheet and in 'source' for the
drop down list entered '=sheet!rangename'. Excel did not like this and
stated that is was not possible to enter a source for another worksheet or
workbook (contrary to what the help pages say). In the end I got it to work
by simple entering '=rangename' as the source entry despite the fact that the
range was on another sheet. I still cannot see why prefixing this with
'sheet!' does not work.

Can you enlighten me?

David

"Dave Peterson" wrote:

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