updating formula when a worksheet is added
 

What does "shift and quit working" mean?

Can you share a "before" formula and an "after" formula--even if it fails?

JakeShipley2008 wrote:

I have a workbook that I will add new sheets to. I have created a amacro that
will re-name all my worksheets to consectutive name e.g. CMM1, CMM2, CMM3,
and so on as I add the worksheets. The data I need to pull is always from the
same cells in each sheet. Normally I would pull it into the summary sheet
with =sheet1!A1 but when I add the new sheets the formulas shift and quit
working.

Note: I am not summarizing the data I need to pull it over by individual cells

Hopefully this makes sense I would appreciate any help you can offer.
--
Jake

--

Dave Peterson

updating formula when a worksheet is added
 

Before would be:

=CMM1!A1

After:

=#ref!A2

I know this happens becuase I am making sheet changes. I was wondering if
the is a way to make the sheet absolute similiar to A$1$ on a cell assignment
--
Jake


"Dave Peterson" wrote:

What does "shift and quit working" mean?

Can you share a "before" formula and an "after" formula--even if it fails?

JakeShipley2008 wrote:

I have a workbook that I will add new sheets to. I have created a amacro that
will re-name all my worksheets to consectutive name e.g. CMM1, CMM2, CMM3,
and so on as I add the worksheets. The data I need to pull is always from the
same cells in each sheet. Normally I would pull it into the summary sheet
with =sheet1!A1 but when I add the new sheets the formulas shift and quit
working.

Note: I am not summarizing the data I need to pull it over by individual cells

Hopefully this makes sense I would appreciate any help you can offer.
--
Jake

--

Dave Peterson

updating formula when a worksheet is added
 

If CMM1 doesn't exist then you'll see that error--but the formula will stay the
same.

I thought you said you started with other sheet names and renamed them to cmm1,
cmm2, ...

If you're trying to create a formula that doesn't return an error if the sheet
doesn't exist (kind of pre-building a bunch of formulas???), then how about
something like:

=IF(ISERROR(CELL("address",INDIRECT("'cmm0'!a1"))) ,"",INDIRECT("'cmm0'!x99"))

This will return "" if CMM0 isn't there--otherwise, it'll return the value in
x99 of cmm0.






JakeShipley2008 wrote:

Before would be:

=CMM1!A1

After:

=#ref!A2

I know this happens becuase I am making sheet changes. I was wondering if
the is a way to make the sheet absolute similiar to A$1$ on a cell assignment
--
Jake

"Dave Peterson" wrote:

What does "shift and quit working" mean?

Can you share a "before" formula and an "after" formula--even if it fails?

JakeShipley2008 wrote:

I have a workbook that I will add new sheets to. I have created a amacro that
will re-name all my worksheets to consectutive name e.g. CMM1, CMM2, CMM3,
and so on as I add the worksheets. The data I need to pull is always from the
same cells in each sheet. Normally I would pull it into the summary sheet
with =sheet1!A1 but when I add the new sheets the formulas shift and quit
working.

Note: I am not summarizing the data I need to pull it over by individual cells

Hopefully this makes sense I would appreciate any help you can offer.
--
Jake

--

Dave Peterson


--

Dave Peterson