I have a problem with a formula. Here is a very simplified version:

I have a worksheet set up with monthly sales of product A and B.
I use this worksheet to enter in the sales for A and B as well as returns
and a total is calculated.
At that point a copy is saved of this worksheet in the same workbook named
January.
Then the process start over for February and so on each month.
I have a Summary worksheet set up so sum these totals. Initially all the
formulas have #Ref! since the January, February through December worksheets
do not yet exist.
My problem is that once the January worksheet is created, the formula still
shows #Ref!. It doesn't recognize the January worksheet and pick up the
number for each of the products.

I tried to manually calculate, and update links. That didn't help

Any suggestions?

Select one of the error cells and F2 then Enter.

Should be OK now. Assuming the January sheet has been created.

To simulate F2 and Enter on each cell.............

Select cells and EditReplace

What: =

With: =

Replace all.


Gord Dibben MS Excel MVP

On Fri, 28 Jul 2006 14:00:01 -0700, TimN wrote:

I have a problem with a formula. Here is a very simplified version:

I have a worksheet set up with monthly sales of product A and B.
I use this worksheet to enter in the sales for A and B as well as returns
and a total is calculated.
At that point a copy is saved of this worksheet in the same workbook named
January.
Then the process start over for February and so on each month.
I have a Summary worksheet set up so sum these totals. Initially all the
formulas have #Ref! since the January, February through December worksheets
do not yet exist.
My problem is that once the January worksheet is created, the formula still
shows #Ref!. It doesn't recognize the January worksheet and pick up the
number for each of the products.

I tried to manually calculate, and update links. That didn't help

Any suggestions?

Thanks Gord. Now I just have to come up with a way to write VBA code to make
that happen automatically.

"Gord Dibben" wrote:

Select one of the error cells and F2 then Enter.

Should be OK now. Assuming the January sheet has been created.

To simulate F2 and Enter on each cell.............

Select cells and EditReplace

What: =

With: =

Replace all.


Gord Dibben MS Excel MVP

On Fri, 28 Jul 2006 14:00:01 -0700, TimN wrote:

I have a problem with a formula. Here is a very simplified version:

I have a worksheet set up with monthly sales of product A and B.
I use this worksheet to enter in the sales for A and B as well as returns
and a total is calculated.
At that point a copy is saved of this worksheet in the same workbook named
January.
Then the process start over for February and so on each month.
I have a Summary worksheet set up so sum these totals. Initially all the
formulas have #Ref! since the January, February through December worksheets
do not yet exist.
My problem is that once the January worksheet is created, the formula still
shows #Ref!. It doesn't recognize the January worksheet and pick up the
number for each of the products.

I tried to manually calculate, and update links. That didn't help

Any suggestions?