How do I copy formula but only increment certain ranges?

[Guest]

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

 

[Jerry W. Lewis]

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

 

[J. K. Parks]

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.

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[J. K. Parks]

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...

 

[Gord Dibben]

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.

 

[NRath]

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!

 

[Gord Dibben]

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

 

[NRath]

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]

Welcome to the club<g>


Gord Dibben MS Excel MVP

 

[hm.riyaz]

i am facing exact same problem but i am not really clear about the solution.

as example i want formula like this for a long range of data

=test_test!C2
=test_test!C22
=test_test!C32
=test_test!C42

i want to increase like this. what can i do. please give me a solution.