Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to copy cells with absolute reference gettingadjusted?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to copy cells with absolute reference getting adjusted?
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. -- David Biddulph wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to copy cells with absolute reference gettingadjusted?
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"), "") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to copy cells with absolute reference gettingadjusted?
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 Got it. Used the OFFSET function to handle Example: =IF(ISTEXT(OFFSET(Commissions!A3,1,0)), OFFSET(Commissions!A3,1,0), "") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY/PASTE/FORMULA GIVES ABSOLUTE REFERENCE | Excel Discussion (Misc queries) | |||
Changing Cells from Relative to Absolute Reference | Excel Discussion (Misc queries) | |||
Apply Absolute Reference to multiplie cells | Excel Worksheet Functions | |||
HOw do I change group of cells from absolute reference? | New Users to Excel | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) |