Help! How do I copy/paste formulas without automatic progression?
 

Don't know what to call this, but I'm creating a very large worksheet with
formulas all over the place. I have a VLOOKUP that goes to a data table that
is in a set location (D81:J91), and I'm trying to put a formula using it in a
column going down.

So the first formula, for cell D5 is =C5*VLOOKUP(A5,'Hours
Allocation'!D81:J91,6,FALSE)

The D81:J91 obviously is my data table, but it doesn't move. But when I
copy/paste the formula down column D, Excel automatically changes the
col_index_num it to D82:J92, D83:J93, and so on.

How can I get it to change the first cell (the C5 in the above) and the
lookup_value (the A5 in the above) without automatically changing the
col_index_num (the D81:J91)?

So, for example, I want D25's formula to read =C25*VLOOKUP(A25,'Hours
Allocation'!D81:J91,6,FALSE)

But it ends up being =C25*VLOOKUP(A25,'Hours Allocation'!D99:J109,6,FALSE)

See the problem? How do I get that, with !D81:J91 instead of !D99:J109 when
copy/pasting? If I go in and change it manually, it will take me all day!

How do I do that?

Thanks!

Help! How do I copy/paste formulas without automatic progression?
 

Change your formula to use $ when you want to maintain the column and/or row
references.

=C5*VLOOKUP(A5,'Hours Allocation'!$D$81:$J$91,6,FALSE)

HTH,
Paul


"Ed" wrote in message
...
Don't know what to call this, but I'm creating a very large worksheet with
formulas all over the place. I have a VLOOKUP that goes to a data table
that
is in a set location (D81:J91), and I'm trying to put a formula using it
in a
column going down.

So the first formula, for cell D5 is =C5*VLOOKUP(A5,'Hours
Allocation'!D81:J91,6,FALSE)

The D81:J91 obviously is my data table, but it doesn't move. But when I
copy/paste the formula down column D, Excel automatically changes the
col_index_num it to D82:J92, D83:J93, and so on.

How can I get it to change the first cell (the C5 in the above) and the
lookup_value (the A5 in the above) without automatically changing the
col_index_num (the D81:J91)?

So, for example, I want D25's formula to read =C25*VLOOKUP(A25,'Hours
Allocation'!D81:J91,6,FALSE)

But it ends up being =C25*VLOOKUP(A25,'Hours Allocation'!D99:J109,6,FALSE)

See the problem? How do I get that, with !D81:J91 instead of !D99:J109
when
copy/pasting? If I go in and change it manually, it will take me all day!

How do I do that?

Thanks!

Help! How do I copy/paste formulas without automatic progressi
 

Ah! Thank you!!!

"PCLIVE" wrote:

Change your formula to use $ when you want to maintain the column and/or row
references.

=C5*VLOOKUP(A5,'Hours Allocation'!$D$81:$J$91,6,FALSE)

HTH,
Paul


"Ed" wrote in message
...
Don't know what to call this, but I'm creating a very large worksheet with
formulas all over the place. I have a VLOOKUP that goes to a data table
that
is in a set location (D81:J91), and I'm trying to put a formula using it
in a
column going down.

So the first formula, for cell D5 is =C5*VLOOKUP(A5,'Hours
Allocation'!D81:J91,6,FALSE)

The D81:J91 obviously is my data table, but it doesn't move. But when I
copy/paste the formula down column D, Excel automatically changes the
col_index_num it to D82:J92, D83:J93, and so on.

How can I get it to change the first cell (the C5 in the above) and the
lookup_value (the A5 in the above) without automatically changing the
col_index_num (the D81:J91)?

So, for example, I want D25's formula to read =C25*VLOOKUP(A25,'Hours
Allocation'!D81:J91,6,FALSE)

But it ends up being =C25*VLOOKUP(A25,'Hours Allocation'!D99:J109,6,FALSE)

See the problem? How do I get that, with !D81:J91 instead of !D99:J109
when
copy/pasting? If I go in and change it manually, it will take me all day!

How do I do that?

Thanks!