Most experience users of Excel would know that when you copy a formula from one cell to another on the same worksheet, the default referencing would be one of relative, unless you add the $ sign infront of the column or the row or both in order to have absolute reference.
However, when a formula in a cell in one worksheet is copied to the the next worksheet, Excel's default will be Absolute Reference on the SHEET (Col & Row remains relative). There seems to be no way to have a relative reference of the Sheet Address.
Case:
In Cell Sheet1 A1, I have entered the date value "15-1-2002", format to be become Jan-02
In Cell Sheet2 A1, I have entered the formula "Sheet1!A1+30.5", format to become Feb-02
I copied Cell Sheet2 A1 to Cell Sheet3 A1, Cell Sheet4 A1.. all the way to Sheet12 A1, hoping to obtain in Cell Sheet3 A1 the formula "Sheet2!A1+30.5" and in Cell Sheet4 A1 the formula "Sheet3!A1+30.5" so that I can obtain from Sheet1 to Sheet12 A1 Jan-02 to Dec-02.
However, in Cell Sheet3 A1 to Cell Sheet12 A1, I got "Sheet1!A1+30.5". Absolute reference rules here. So I have to manually ammend on each sheet to get the dates wanted.
This is just one small part of the total frustration. My spreedsheet in Sheet1 and Sheet2 is 30 cols x 1500 rows [forecast for Jan 02 and Feb02]. I need to copy Sheet2!A1..Sheet2!AD1500 to Sheet3, Sheet4.. all the way to Sheet12 and relative reference on Sheet referencing is needed to "roll" the formula to all the 12 sheets, "(accumulation of value)
In Lotus, Sheet reference is automatically relative. Not the case here for Excel when I switch over.
I now have to manually adjust every formula cell that requires relative sheet referencing to calculate the values in all 12 sheets. It is madness!
My simple question. "How to make Sheet reference relative?" so that I can build my financial model effectively.
Thank you.
However, when a formula in a cell in one worksheet is copied to the the next worksheet, Excel's default will be Absolute Reference on the SHEET (Col & Row remains relative). There seems to be no way to have a relative reference of the Sheet Address.
Case:
In Cell Sheet1 A1, I have entered the date value "15-1-2002", format to be become Jan-02
In Cell Sheet2 A1, I have entered the formula "Sheet1!A1+30.5", format to become Feb-02
I copied Cell Sheet2 A1 to Cell Sheet3 A1, Cell Sheet4 A1.. all the way to Sheet12 A1, hoping to obtain in Cell Sheet3 A1 the formula "Sheet2!A1+30.5" and in Cell Sheet4 A1 the formula "Sheet3!A1+30.5" so that I can obtain from Sheet1 to Sheet12 A1 Jan-02 to Dec-02.
However, in Cell Sheet3 A1 to Cell Sheet12 A1, I got "Sheet1!A1+30.5". Absolute reference rules here. So I have to manually ammend on each sheet to get the dates wanted.
This is just one small part of the total frustration. My spreedsheet in Sheet1 and Sheet2 is 30 cols x 1500 rows [forecast for Jan 02 and Feb02]. I need to copy Sheet2!A1..Sheet2!AD1500 to Sheet3, Sheet4.. all the way to Sheet12 and relative reference on Sheet referencing is needed to "roll" the formula to all the 12 sheets, "(accumulation of value)
In Lotus, Sheet reference is automatically relative. Not the case here for Excel when I switch over.
I now have to manually adjust every formula cell that requires relative sheet referencing to calculate the values in all 12 sheets. It is madness!
My simple question. "How to make Sheet reference relative?" so that I can build my financial model effectively.
Thank you.