3D formulas in Excel

[Guest]

Does anyone have experience with 3D formulas in Excel. Specifically I would
like to chance a cell value and have the formula automatically pick up the
new end point to the 3D formula.

 

[Bernie Deitrick]

Stef,

Normally, you would use INDIRECT (based on your scant description of what you want to do) but
INDIRECT doesn't work with 3D ranges. Perhaps you could offer a better description, and we can come
up with a solution.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Sure. I have the following formula; =SUM(Sheet2:Sheet4!A1) which calcs cell
A1 on sheets Sheet2, Sheet3 and Sheet4.

I will be adding sheets to this workbook and cannot guarantee that Sheet2
will always be the first sheet and that Sheet 4 will always be the last
sheet.

I was hoping to update a cell with the info on which sheet is first and
which sheet is last and have the formula adjust accordingly.

Any thoughts?

 

[Bernie Deitrick]

Stef,

The normal procedure is to insert a blank first sheet (to the extreme left), name it "FIRST", and
insert a blank last sheet (to the extreme right), named "LAST. Then change your formula to

=SUM(FIRST:LAST!A1)

and make sure that any new sheets are inserted between FIRST and LAST.

Beyond that, you could use a User-Defined-Function in VBA.

HTH,
Bernie
MS Excel MVP