Can Excel 2003 Do This?

[Jessica]

Thanks in advance for anybody that can help me with this...

Here's what I want to do:

I have a workbook that I have recreated in excel for my boyfriends work. One
of the sheets is a weekly production report that he sends in to his employer
to get paid. There is a separate sheet that has a list of available units
with three columns; 1)Unit ID, 2) Description, 3)Unit price.

I would like to somehow make it so all he has to do is type in the Unit ID
on the corresponding cell on the production report and have it automatically
fill in the correct description, unit price & when he enters the quantity,
have it total up in the "total unit" column. That way he can just print it
out and it will save him a lot of time doing paperwork.

I know how to do basic functions but I do not know if it is possible for
excel to do this. Maybe in access?

I have the 2003 version of excel if that helps. Thanks again

Jessica

 

[Dave Peterson]

I'd set up a table with all the info in it.

Then you could use =vlookup() to return that other stuff once you type the ID.

And to get the extended price, just multiply qty*unitPrice (like: =c3*d3)

Debra Dalgleish has some nice notes for getting started with =vlookup() at:
http://www.contextures.com/xlFunctions02.html

 

[Jessica]

Do you know where I can find more information on that function? I do have a
table set up already with the information but it is on a separate sheet in
the workbook. Does it need to be on the same sheet? Thanks

Jessica

 

[Dave Peterson]

Debra Dalgleish has some nice notes for getting started with =vlookup()
at:

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

<vbg>

===

I like to put each of my lookup tables in separate sheets. The common key
element goes in the left most column (usually A). It's safe from possible
errors (deleting too many rows/columns--especially).

Take a look at Deb's site. It's enough to get you started. Post back if you
have more questions.

 

[L. Howard Kittle]

Hi Jessica,

Try this little sample to get you started.

A1 = Unit ID
B1 = Description
C1 = Qnty
D1 = Price
H1 to H5 = Unit ID's
I1 to I5 = Descriptions
J1 to J5 = Prices

In B2 enter =VLOOKUP(A2,H1:I5,2,0)
In D2 enter =VLOOKUP(H1:J5,30)*C2

Now enter a Unit ID in A2 and a Quanity in C2, B2 returns the Description
and D2 returns the Price.

HTH
Regards,
Howard