How to have relative reference when copying a formula to oth

[LEONGCT]

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.

 

[Anne Troy]

Paste your formulas. Do a find/replace (ctrl-h) and find, for instance Sheet1! and replace it with nothing.

Edit-Paste special-Formulas. (?)
_________________
TheWordExpert
This message was edited by Dreamboat on 2002-04-25 05:20

 

[Chris Davison]

Hi Leon,

no, we don't want to do anything manually...that sucks

in sheet 2, cell A1, try the following :

=INDIRECT("Sheet"&((SUBSTITUTE(RIGHT(CELL("filename",A1),2),"t",0)*1)-1)&"!A1")+30.5

if it gives the required result of Feb-02, further copy it to cells A1 all the way through to sheet 12... you should have March-02, April-02, May-02 etc etc through to Dec-02

repost if this step works and I'll tackle the second part of your question

 

[Mark O'Brien]

Chris, I'm telling you, you're not allowed to post anymore replies using INDIRECT. It's just showing off.

 

[JLJ34841]

INDRIECT does not work for a closed workbook. What would the formula be for a relative sheet reference and absolute cell reference? I have an external workbook with 200+ worksheets. Each worksheet represents a pipeline and various test results of that particular pipeline. I need the results of each pipeline's test posted into a master spreadsheet. The results are in date format (if that matters). I'm not trying to SUM or AVG anything. Just need the results (dates) posted into my master sheet. Column A in master contains each pipeline's ID number. Column B is where my formula will go. I've been working on this all day to no avail. I'm sure there is a simple solution. Thank you!