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

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
 
D

David Biddulph

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

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

Got it. Used the OFFSET function to handle Example:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top