Can Excel 2003 Do This?

  • Thread starter Thread starter Jessica
  • Start date Start date
J

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
 
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
 
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
 
Debra Dalgleish has some nice notes for getting started with =vlookup()
at:
http://www.contextures.com/xlFunctions02.html
Click to expand...

<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.
 
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
 
Back
Top