Absolute Referencing Sheets (Excel 2000 >)

Topic

New Reply

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???

Reply | Quote

Replies

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

Even absoluted cells would not handle dragging around in the weay that you describe

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

Now why did I not think of that …
Tx Hans…so simple a solution!

Reply | Quote