Prevent Formulas from Advancing

D

dallased25

Does anyone know how to prevent a formula from advancing when you copy
it down. My situation is this, I'm trying to use the VLookup function
to pull data from a table. When I copy the formula down, it doesn't
just advance the lookup value number, it also advances the table array.
I do want the lookup value to advance of course, but not the table
array. I want the table array to be the same all the way down. For
example, the formula looks like this:

=VLOOKUP(B2,'September 2005'!A2:G296,1,FALSE)

When I copy it down it changes to:

=VLOOKUP(B3,'September 2005'!A3:G297,1,FALSE)

and the numbers A3:G297 keep advancing as I copy the formula down.

Like I said, I do want the B2 to advance to B3, but I do not want the
A2:G296 to change when I copy the formula down. Is there a way to do
this?
 
G

goober

=VLOOKUP(B2,'September 2005'!$A$2:$G$296,1,FALSE)

The $ in front of the row and column stops the cell reference fro
changing as you copy it.

Good Luck.

Does anyone know how to prevent a formula from advancing when you cop
it down. My situation is this, I'm trying to use the VLookup functio
to pull data from a table. When I copy the formula down, it doesn'
just advance the lookup value number, it also advances the table array
I do want the lookup value to advance of course, but not the tabl
array. I want the table array to be the same all the way down. Fo
example, the formula looks like this:

=VLOOKUP(B2,'September 2005'!A2:G296,1,FALSE)

When I copy it down it changes to:

=VLOOKUP(B3,'September 2005'!A3:G297,1,FALSE)

and the numbers A3:G297 keep advancing as I copy the formula down.

Like I said, I do want the B2 to advance to B3, but I do not want th
A2:G296 to change when I copy the formula down. Is there a way to d
this
 
N

Nick Hodge

Look up absolute and relative references in help

Basically you need to prefix the row and columns with $ signs this fixes it.

=VLOOKUP(B3,'September 2005'!$A$3:$G$297,1,FALSE)

You can shorten this by pressing F4 when entering the formula

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
D

dallased25

Awesome, thanks for the info! One more question though. Is there a wa
to make it so that it puts the $ in there automatically the first tim
around, rather than me having to go in there everytime and edit th
formula
 
N

Nick Hodge

As I said, as soon as you are entering any part of any formula press the F4
key before moving on or re-select the reference and press F4

Repetitive presses toggles through the four options (In no particular order)

Absolute Row, Absolute Column
Relative Row, Absolute Column
Absolute Row, Relative Column
Relative Row, Relative Column

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
D

Dave Peterson

You could also give that range a nice name.

Select A2:G296 on that sheet.
Insert|Name|define
give it a nice name (myTable)

then you can use:
=vlookup(b2,myTable,1,false)

If there's nothing else in that 'september 2005' sheet, maybe you could use the
whol column:

=vlookup(b2,'september 2005'!a:g,1,false)

======
As an aside, it looks like you're returning the value you're looking for (column
1).

If you're just looking to see if that value exists in that first column, you
could use the =match() worksheet function:

=if(isnumber(match(b2,'september 2005'!a:a,0)),"It matched","not there")


Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
 
Top