Referencing Help

[Teedie]

At my job a spreadsheet needs to be filled out every single morning
documenting all of the items we've produced from the previous day. In
addition to the item number, the item's weight and size needs to be filled
in. There are numerous products that we sell on a daily basis, so you can
imagine how this might get repetitive. Is there a way that I can set up a
reference table so that when I type in the item number, certain data
associated with that item number (i.e. weight and size) would automatically
fill in designated columns along the same row? I've tried to use the lookup
function but the reference table shifts down one row each time I progress
down the spread sheet.

Thanks!

 

[Niek Otten]

Use VLOOKUP(). Instead of, for example,

=VLOOKUP(A1,B1:B500,3,false)

use

=VLOOKUP(A1,$B$1:$B$500,3,FALSE)

This will prevent the reference to the table from shifting down.

A good tutorial on VLOOKUP can be found here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| At my job a spreadsheet needs to be filled out every single morning
| documenting all of the items we've produced from the previous day. In
| addition to the item number, the item's weight and size needs to be filled
| in. There are numerous products that we sell on a daily basis, so you can
| imagine how this might get repetitive. Is there a way that I can set up a
| reference table so that when I type in the item number, certain data
| associated with that item number (i.e. weight and size) would automatically
| fill in designated columns along the same row? I've tried to use the lookup
| function but the reference table shifts down one row each time I progress
| down the spread sheet.
|
| Thanks!

 

[Dave Peterson]

Just a typo alert.

Niek wanted to return column 3 of the lookup table. But he only made it one
column wide.

=VLOOKUP(A1,$B$1:$D$500,3,FALSE)

(B is 3 columns wide--so that part should be ok.)

 

[Niek Otten]

Thanks Dave!


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Just a typo alert.
|
| Niek wanted to return column 3 of the lookup table. But he only made it one
| column wide.
|
| =VLOOKUP(A1,$B$1:$D$500,3,FALSE)
|
| (B is 3 columns wide--so that part should be ok.)
|
|
| Niek Otten wrote:
| >
| > Use VLOOKUP(). Instead of, for example,
| >
| > =VLOOKUP(A1,B1:B500,3,false)
| >
| > use
| >
| > =VLOOKUP(A1,$B$1:$B$500,3,FALSE)
| >
| > This will prevent the reference to the table from shifting down.
| >
| > A good tutorial on VLOOKUP can be found here:
| >
| > http://www.contextures.com/xlFunctions02.html
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | At my job a spreadsheet needs to be filled out every single morning
| > | documenting all of the items we've produced from the previous day. In
| > | addition to the item number, the item's weight and size needs to be filled
| > | in. There are numerous products that we sell on a daily basis, so you can
| > | imagine how this might get repetitive. Is there a way that I can set up a
| > | reference table so that when I type in the item number, certain data
| > | associated with that item number (i.e. weight and size) would automatically
| > | fill in designated columns along the same row? I've tried to use the lookup
| > | function but the reference table shifts down one row each time I progress
| > | down the spread sheet.
| > |
| > | Thanks!
|
| --
|
| Dave Peterson