The terror of advancing cell numbers!

S

Sivodsi

Hi everybody,
I apologize for this really obvious problem, I haven't encountered i
before so its really ummm "bugging" me.

I am using the vlookup function, and in the formulae it refers to
range of figures A1-Z100 in another worksheet. If you copy and past
the formulae to find the next piece of information xcel automaticall
advances the the references, so that now the range of figures it refer
to is A2-Z101, which means that the vlookup function is no longe
looking in the full range of figures. How do I stop it from doin
this?

In the past I just did one row, manually changing the table it refer
to, then copying and pasting that entire row of formulae to the next
and xcel would get the idea that I didn't want the cell numbers t
advance. But I was using a different version of xcel which i
remarkably insistent in its cell advancing afforts. To avoid this
manually did two rows of formulae, setting the table array manually an
then copying and pasting both rows. Instead of sticking to the tabl
array as it should have done, the bloody thing advanced the cel
numbers by a factor of two!!!! Grrrrrrr.

Please help me with this basic problem
 
R

RagDyer

Look up "Relative" and "Absolute" cell references in Help.

"Absolute" references, depicted by the dollar sign ($), do *NOT* change as
the formula is copied to other cells.

For example:

=VLOOKUP(A1,SHEET2!$A$1:$Z$100,5,0)

Will retain the data list references A1:Z100 throughout any copying of the
formula, even though the lookup cell, A1 *will* change as the formula is
copied down the column.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi everybody,
I apologize for this really obvious problem, I haven't encountered it
before so its really ummm "bugging" me.

I am using the vlookup function, and in the formulae it refers to a
range of figures A1-Z100 in another worksheet. If you copy and paste
the formulae to find the next piece of information xcel automatically
advances the the references, so that now the range of figures it refers
to is A2-Z101, which means that the vlookup function is no longer
looking in the full range of figures. How do I stop it from doing
this?

In the past I just did one row, manually changing the table it refers
to, then copying and pasting that entire row of formulae to the next,
and xcel would get the idea that I didn't want the cell numbers to
advance. But I was using a different version of xcel which is
remarkably insistent in its cell advancing afforts. To avoid this I
manually did two rows of formulae, setting the table array manually and
then copying and pasting both rows. Instead of sticking to the table
array as it should have done, the bloody thing advanced the cell
numbers by a factor of two!!!! Grrrrrrr.

Please help me with this basic problem!
 
D

Dave Peterson

Try $a$1:$z$100 (and then drag down)

or you could define a range name that points at A1:Z100.

Select your range and type:
MyRng
(or whatever you want to call it)
and hit enter

Then your =vlookup() becomes:

=vlookup(a1,myrng,2,false)
 
Top