Is Excel capable of doing this function?

[Guest]

I have created an invoice to use for my scrapbook business. I don't know if
excel can do the following thing or not. If it can, how do I set it up to
function?

I have a place for an item number. Is there a way to enter the item number
(z1561) and have the discription (stamp pad set) and the price (26.95)
automatically come up. I know if it can, I will have to create the entire
inventory list for all products.

Thanks for any help.

 

[Trevor Shuttleworth]

Use VLOOKUP

Regards

Trevor

 

[Guest]

Vlookup will definitely help you, and also you can combine it with a DROPDOWN
list.
Let me know if you need directions on how to do it and I'll get back to you.

 

[Guest]

Thanks.
A couple of more questions: Where do I creat the master list with the
invetory...do I add a worksheet to the invoice workbook with all of the
information and then link the item number cells to the inventory list?

 

[Guest]

FC,
Thank you. I would love to have the directions.

 

[Don Guillett]

Have you tried looking in the help index for VLOOKUP?

 

[Gord Dibben]

Use Data Validation for in-cell drop-down for selecting an item and VLOOKUP
for filling in the other cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

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

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

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[Guest]

Sorry for the timing but I had to go away. As promise here goes:
1st we'll create the dropdown list, then the VLOOKUP.
For the dropdown you'll need minimun 2 worksheets ( could be more ).
In sheet 2 create a list of items as much as you want ej:
A B C
1 z1561 stamp and set $ 26.95
2 z1562 not stamp, set $ 20.00
3 z1563 could be blank $10.00
Select A1 to A3 and at the upper right side of the sheet assign(write) a
cell name for those 3 items (ej. ITEM ) and press enter.
Go to sheet 1 and select cell A1
On the toolbar on the top menu select DATA, click on VALIDATION and
in the dropdown list select LIST. In the box below write =ITEM (don't forget
the equal sign). Check "ignore blank" and " in drop down list ". Click OK.
You are done with the DROPDOWN list.Try it first.
Now the VLOOKUP formula.
In B1 write this formula =VLOOKUP($A1,SHEET2!A1:C3,COLUMNS(A:B1,0)
You are done!
Of course you'll have to adjust the number of columns/rows as needed. Also
the puntuation in the formula has to be exact as is. HIH, enjoy it

 

[Guest]

Sorry the naming of the cell is UPPER LEFT SIDE ( NOT right side).Got to go.

 

[Guest]

Thank you so much for the help. I think I am understanding it now. As soon as
I get all of our items listed, I will try to finish setting it up. Thanks
again!