I need to use a cell number in a function that will update itself for each
new row it is in. For example, my function looks like this: VLOOKUP(B3, name,
2, FALSE), where "B3" is the number of the cell I need to use because I'm in
row 3. If I were in row 4 the cell number I would use is "B4". I don't want
to hard code all of these locations in so I'm wondering if someone knows a
way for excel to just update that cell number for each new row I'm in? I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm using
excel 2002. Can anyone help me?

Try this:

=VLOOKUP(INDIRECT("B"&ROW()),name,2,0)

Or a non-volatile version:

=VLOOKUP(INDEX(B:B,ROW()),name,2,0)

--
Biff
Microsoft Excel MVP


"Gluefoot" wrote in message
...
I need to use a cell number in a function that will update itself for each
new row it is in. For example, my function looks like this: VLOOKUP(B3,
name,
2, FALSE), where "B3" is the number of the cell I need to use because I'm
in
row 3. If I were in row 4 the cell number I would use is "B4". I don't
want
to hard code all of these locations in so I'm wondering if someone knows a
way for excel to just update that cell number for each new row I'm in? I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm using
excel 2002. Can anyone help me?

Have a look in the help index for INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gluefoot" wrote in message
...
I need to use a cell number in a function that will update itself for each
new row it is in. For example, my function looks like this: VLOOKUP(B3,
name,
2, FALSE), where "B3" is the number of the cell I need to use because I'm
in
row 3. If I were in row 4 the cell number I would use is "B4". I don't
want
to hard code all of these locations in so I'm wondering if someone knows a
way for excel to just update that cell number for each new row I'm in? I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm using
excel 2002. Can anyone help me?

Have you tried dragging your formula down from row 3 to copy it? Excel will
adjust the formula for you - when you drag the formula to row 4, Excel will
change the B3 to B4

Tyro

"Gluefoot" wrote in message
...
I need to use a cell number in a function that will update itself for each
new row it is in. For example, my function looks like this: VLOOKUP(B3,
name,
2, FALSE), where "B3" is the number of the cell I need to use because I'm
in
row 3. If I were in row 4 the cell number I would use is "B4". I don't
want
to hard code all of these locations in so I'm wondering if someone knows a
way for excel to just update that cell number for each new row I'm in? I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm using
excel 2002. Can anyone help me?

Thank you very much. I got it to work now.

"T. Valko" wrote:

Try this:

=VLOOKUP(INDIRECT("B"&ROW()),name,2,0)

Or a non-volatile version:

=VLOOKUP(INDEX(B:B,ROW()),name,2,0)

--
Biff
Microsoft Excel MVP


"Gluefoot" wrote in message
...
I need to use a cell number in a function that will update itself for each
new row it is in. For example, my function looks like this: VLOOKUP(B3,
name,
2, FALSE), where "B3" is the number of the cell I need to use because I'm
in
row 3. If I were in row 4 the cell number I would use is "B4". I don't
want
to hard code all of these locations in so I'm wondering if someone knows a
way for excel to just update that cell number for each new row I'm in? I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm using
excel 2002. Can anyone help me?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Gluefoot" wrote in message
...
Thank you very much. I got it to work now.

"T. Valko" wrote:

Try this:

=VLOOKUP(INDIRECT("B"&ROW()),name,2,0)

Or a non-volatile version:

=VLOOKUP(INDEX(B:B,ROW()),name,2,0)

--
Biff
Microsoft Excel MVP


"Gluefoot" wrote in message
...
I need to use a cell number in a function that will update itself for
each
new row it is in. For example, my function looks like this: VLOOKUP(B3,
name,
2, FALSE), where "B3" is the number of the cell I need to use because
I'm
in
row 3. If I were in row 4 the cell number I would use is "B4". I don't
want
to hard code all of these locations in so I'm wondering if someone
knows a
way for excel to just update that cell number for each new row I'm in?
I
tried =VLOOKUP("B"&ROW(), name, 2, FALSE) but that didn't work. I'm
using
excel 2002. Can anyone help me?