The normal procedure is to insert a blank first sheet (to the extreme left), name it "FIRST",
insert a blank last sheet (to the extreme right), named "LAST. Then change your formula to
and make sure that any new sheets are inserted between FIRST and LAST.
that, you could use a User-Defined-Function in VBA.
MS Excel MVP
"Stef" <Stef@discussions.microsoft.com> 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
> 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:
>> 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
>> up with a solution.
>> MS Excel MVP
>> "Stef" <Stef@discussions.microsoft.com> 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.