# Thread: Absolute Referencing Sheets (Excel 2000 >)

1. ## 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???

2. ## 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. ## 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!

4. ## 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. ## 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. ## 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!

7. ## 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. ## 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>

#### Posting Permissions

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