3D formulas in Excel
 

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.

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


"Stef" wrote in message
...
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.

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" wrote:

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


"Stef" wrote in message
...
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.

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


"Stef" wrote in message
...
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" wrote:

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


"Stef" wrote in message
...
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.