Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to have relative reference when copying a formula to oth

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    :: Pharma Z - Family drugstore ::

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to have relative reference when copying a formula to oth

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •