Prevent Formulas from Advancing

[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?

[goober]

=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?

[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]HIS


"dallased25" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[dallased25]

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?

[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]HIS


"dallased25" <[email protected]> wrote
in message news:[email protected]...
>
> 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]

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