Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Absolute Referencing Sheets (Excel 2000 >)

    Is there a way to absolute reference sheets in a 3D function?
    For example, if I have a function like: =SUM(Sheet1:Sheet30!A1), it sums up all the values in A1 across all the sheets. BUT, if a sheet tab is dragged to a new location, (i.e. Drag sheet30 infront of sheet 1 (beginning of WB), the function is adjusted, and it ends up calculating incorrectly!!!). I have tried typing: =SUM($Sheet1:$Sheet30!A1), but it throws out a syntax error. Any ideas concerning absoluting sheets, or is this not possible???
    Regards,
    Rudi

  2. #2
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute Referencing Sheets (Excel 2000 >)

    Sheet1:Sheet30 indicates a range of sheets. Sheet1!A1 + Sheet2!A1 + Sheet3A1.................would give you absolute sheet references.
    Carla

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Absolute Referencing Sheets (Excel 2000 >)

    Hi Carla. Tx for the reply.
    However...I am trying to avoid the formula method as this will produce an extremely long expression! I am aware that this method will work, but I am after a shorter method, (if it exists???)
    =SUM(Sheet1:Sheet30!A1) is great if it would keep the sheet references irrispective of their position!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Absolute Referencing Sheets (Excel 2000 >)

    I think this is unavoidable. A reference such as Sheet1:Sheet30 is only valid if Sheet30 is to the right of Sheet1. If you drag the sheets around, Excel wouldn't know what to do with the formula if it wasn't adjusted.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute Referencing Sheets (Excel 2000 >)

    Even absoluted cells would not handle dragging around in the weay that you describe <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I think your issue relates to preventing sheet dragging - rather than absoluting the formulae.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Absolute Referencing Sheets (Excel 2000 >)

    Tx Hans and Andrew!

    Andrew, that is a valid and logical suggestion. The question now is: How can that be done??? There is no event called Workbook_SheetMove???! Is there a way to code a workbook to prevent sheets from being moved?

    Sorry if I put you on the spot!
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Absolute Referencing Sheets (Excel 2000 >)

    You can protect the structure of the workbook (Tools | Protect Workbook..., Structure check box). This means that worksheets in the workbook can't be moved, deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Absolute Referencing Sheets (Excel 2000 >)

    Now why did I not think of that <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>...
    Tx Hans...so simple a solution!
    <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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