Prevent Formulas from Advancing
 

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?


--
dallased25
------------------------------------------------------------------------
dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544
View this thread: http://www.excelforum.com/showthread...hreadid=482033

Prevent Formulas from Advancing
 

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

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

Good Luck.

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?


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=482033

Prevent Formulas from Advancing
 

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
HIS


"dallased25" wrote
in message ...

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?


--
dallased25
------------------------------------------------------------------------
dallased25's Profile:
http://www.excelforum.com/member.php...o&userid=28544
View this thread: http://www.excelforum.com/showthread...hreadid=482033

Prevent Formulas from Advancing
 

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


--
dallased25
------------------------------------------------------------------------
dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544
View this thread: http://www.excelforum.com/showthread...hreadid=482033

Prevent Formulas from Advancing
 

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
HIS


"dallased25" wrote
in message ...

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


--
dallased25
------------------------------------------------------------------------
dallased25's Profile:
http://www.excelforum.com/member.php...o&userid=28544
View this thread: http://www.excelforum.com/showthread...hreadid=482033

Prevent Formulas from Advancing
 

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()))


dallased25 wrote:

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

--
dallased25
------------------------------------------------------------------------
dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544
View this thread: http://www.excelforum.com/showthread...hreadid=482033

--

Dave Peterson