Is there a way to copy cells with absolute reference gettingadjusted?

  • Thread starter Thread starter ysd2cr
  • Start date Start date
Y

ysd2cr

This may be simple, but I am an Excel novice.

I want to be able to copy cells on a worksheet having the absolute
reference adjusted.

Reason. I have a custom macro on sheet 1 that inserts a line and
copies formulas to that line. On Sheet 2, I wish to carry over some
values from sheet 1 directly. Hundreds of rows. But when a row is
inserted on sheet 1, the reference on sheet 2 adjusts. Example
Sheet1 row 100 gets inserted. On Sheet 2 when absolute references are
not being used, the formula on Sheet2!a100 references Sheet1 row 101
now, not row 100.

I know I can change it to an absolute reference to get it to work
properly, but I don't want to have to manually key the absolute
reference for hundreds of lines.

Any ideas?

Thanks,

Marc
 
Changing it to an absolute reference won't stop it being changed if you
insert a row before the cell in question. It merely stops the reference
changing when you copy it up, down, or across.
One way that will stop the reference changing if you insert a row is to use
the INDIRECT function.
 
This may be simple, but I am an Excel novice.

I want to be able to copy cells on a worksheet having the absolute
reference adjusted.

Reason. I have a custom macro on sheet 1 that inserts a line and
copies formulas to that line. On Sheet 2, I wish to carry over some
values from sheet 1 directly. Hundreds of rows. But when a row is
inserted on sheet 1, the reference on sheet 2 adjusts. Example
Sheet1 row 100 gets inserted. On Sheet 2 when absolute references are
not being used, the formula on Sheet2!a100 references Sheet1 row 101
now, not row 100.

I know I can change it to an absolute reference to get it to work
properly, but I don't want to have to manually key the absolute
reference for hundreds of lines.

Any ideas?

Thanks,

Marc
Click to expand...

I altered a cell on the 2nd sheet, see below:

=IF(ISTEXT(INDIRECT("Commissions!A4")), INDIRECT("Commissions!A4"),
"")

to pull from the Commissions sheet, but when copying that cell to the
cell below still has the same formula, it does not adjust to:

=IF(ISTEXT(INDIRECT("Commissions!A5")), INDIRECT("Commissions!A5"), "")
 
This may be simple, but I am an Excel novice.

I want to be able to copy cells on a worksheet having the absolute
reference adjusted.

Reason. I have a custom macro on sheet 1 that inserts a line and
copies formulas to that line. On Sheet 2, I wish to carry over some
values from sheet 1 directly. Hundreds of rows. But when a row is
inserted on sheet 1, the reference on sheet 2 adjusts. Example
Sheet1 row 100 gets inserted. On Sheet 2 when absolute references are
not being used, the formula on Sheet2!a100 references Sheet1 row 101
now, not row 100.

I know I can change it to an absolute reference to get it to work
properly, but I don't want to have to manually key the absolute
reference for hundreds of lines.

Any ideas?

Thanks,

Marc
Click to expand...

Got it. Used the OFFSET function to handle Example:

=IF(ISTEXT(OFFSET(Commissions!A3,1,0)), OFFSET(Commissions!A3,1,0), "")
 
Back
Top