How do I copy formula but only increment certain ranges?
 

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.

How do I do it?? Please help

Mart

A preceding $ indicates a part of the address to hold constant, as in
$A1, A$1, or $A$1 to hold the column, row, or both constant.

Jerry

Martc wrote:

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.

How do I do it?? Please help

Mart

How do I copy formula but only increment certain ranges?
 

What if this doesn't work. It used to, but now when I drag, the copy cell
value is returned to all pasted cells, even though the search value should
return a different value. I can retype the cell from scratch and it works,
but no matter which copy technique I use, the initial cell's return value is
defaulted to all pasted cells.


"Jerry W. Lewis" wrote:

A preceding $ indicates a part of the address to hold constant, as in
$A1, A$1, or $A$1 to hold the column, row, or both constant.

Jerry

Martc wrote:

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.

How do I do it?? Please help

Mart

How do I copy formula but only increment certain ranges?
 

Change your calculation mode to automatic - Tools / Options / Calculation tab, click Automatic.

HTH,
Bernie
MS Excel MVP


"J. K. Parks" <J. K. wrote in message
...
What if this doesn't work. It used to, but now when I drag, the copy cell
value is returned to all pasted cells, even though the search value should
return a different value. I can retype the cell from scratch and it works,
but no matter which copy technique I use, the initial cell's return value is
defaulted to all pasted cells.


"Jerry W. Lewis" wrote:

A preceding $ indicates a part of the address to hold constant, as in
$A1, A$1, or $A$1 to hold the column, row, or both constant.

Jerry

Martc wrote:

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.

How do I do it?? Please help

Mart

How do I copy formula but only increment certain ranges?
 

Excel takes the calculation mode of the first workbook opened during a
session and applies that mode to all subsequent workbooks opened in that
session.

i.e. ignores which calc mode the subsequent workbook had when saved.


Gord Dibben MS Excel MVP


On Wed, 17 Dec 2008 11:46:01 -0800, J. K. Parks <J. K.
wrote:

Thanks much...I took my Options for granted since I didn't make a change.
Are there any usual suspects which would have caused this change. As
indicated in my plea below, it was working fine up til now...

"Bernie Deitrick" wrote:

Change your calculation mode to automatic - Tools / Options / Calculation tab, click Automatic.

HTH,
Bernie
MS Excel MVP


"J. K. Parks" <J. K. wrote in message
...
What if this doesn't work. It used to, but now when I drag, the copy cell
value is returned to all pasted cells, even though the search value should
return a different value. I can retype the cell from scratch and it works,
but no matter which copy technique I use, the initial cell's return value is
defaulted to all pasted cells.


"Jerry W. Lewis" wrote:

A preceding $ indicates a part of the address to hold constant, as in
$A1, A$1, or $A$1 to hold the column, row, or both constant.

Jerry

Martc wrote:

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.

How do I do it?? Please help

Mart

How do I copy formula but only increment certain ranges?
 

I'm having a similar problem to martc's. I'm working in Excel '07, trying to
drag down an Hlookup formula. The formula I'm using is:
HLOOKUP("ABCD",A$1:AB$2881,2,FALSE)

Originally I had no "$", once I put them in, it locks the return row at 2
instead on icreasing 1 with each additional row. Changing manually, it'll
return as expected, but I need to copy down for 2881 rows. I checked the
calculation tab and it's already set to automatic.

Your guidance is appreciated!

How do I copy formula but only increment certain ranges?
 

You want to increment the row index number?

Entered in AC1 and copied down.

=HLOOKUP("ABCD",A$1:AB$2881,ROW(),FALSE)

Or maybe ROW() + 1


Gord Dibben MS Excel MVP


On Fri, 29 Jan 2010 12:47:09 -0800, NRath
wrote:

I'm having a similar problem to martc's. I'm working in Excel '07, trying to
drag down an Hlookup formula. The formula I'm using is:
HLOOKUP("ABCD",A$1:AB$2881,2,FALSE)

Originally I had no "$", once I put them in, it locks the return row at 2
instead on icreasing 1 with each additional row. Changing manually, it'll
return as expected, but I need to copy down for 2881 rows. I checked the
calculation tab and it's already set to automatic.

Your guidance is appreciated!

How do I copy formula but only increment certain ranges?
 

Yes, the row lookup number. The ROW() solution worked perfectly.

Being an Excel novice is killing me. I know most of this is so simple, but
there's no way to guess the syntax or know how to look up my specific
situation. I have more questions than answers...

Thank you for your help with that one!


"Gord Dibben" wrote:

You want to increment the row index number?

Entered in AC1 and copied down.

=HLOOKUP("ABCD",A$1:AB$2881,ROW(),FALSE)

How do I copy formula but only increment certain ranges?
 

Welcome to the club<g


Gord Dibben MS Excel MVP

On Thu, 4 Feb 2010 12:56:07 -0800, NRath
wrote:

I have more questions than answers...