On Dec 24, 12:50 pm, wrote:
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

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"), "")