Creating an invoice with a lookup list

[wings]

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.

 

[wings]

I have now found the answers to my own question by delving into the other
similar topics here. There are a couple of good sites that seem to have what
I need:

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

http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm

So thanks to those who have given these answers in the past - hopefully I am
on the right track now.

Cheers,
Wings

 

[Dave Peterson]

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]

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]

You can create a range name that is local to a worksheet:

Insert|Name|Define
Include the sheet name in the "names in workbook" box
sheet1!myName
refers to:
=sheet1!A1:A10 (say)

This is called a local or sheet level name. When you try to use this range name
for a cell in a different worksheet, excel knows that it isn't part of that
activesheet and yells.

By making it a global or workbook level name, excel doesn't seem to care.

You'd use Insert|Name|define
and not put any sheet name in that "names in workbook" box.


===
Why excel cares where that range is, I have no idea--I guess the developers
thought that it was important--but not important enough to outlaw that global
name (on a different sheet).

 

[wings]

Dave,

Thanks for that - clear as mud now. Seriously though it is really helpful
to have people who know what they are talking about helping us newbies -
thanks a lot!

David

 

[Dave Peterson]

Once you have a workbook with a few names, you may find it easier to see what's
going on with any/all of them if you use Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

NameManager.Zip from http://www.oaltd.co.uk/mvp

Dave,

Thanks for that - clear as mud now. Seriously though it is really helpful
to have people who know what they are talking about helping us newbies -
thanks a lot!

David