updating formula when a worksheet is added

[Guest]

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.

 

[Dave Peterson]

What does "shift and quit working" mean?

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

 

[Guest]

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

 

[Dave Peterson]

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.

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