Unexplained ?NAME? Error in Formula

[Cyberdude]

I suddenly started gettiing a #NAME? error that I can?t explain in a worksheet formula.

The formula I use is:
=Avg20Day

The name Avg20Day is a defined name for the formula:
=INDIRECT(?Avg20Day? & INT((COLUMN() ? 1) / 13) + 1

I?ve been using the problem formula for several months with no problems. Now, when I first open the workbook AND start Excel at the same time, I get a #NAME? error in the cell containing =Avg20Day.

Not knowing a better way to do this, I take the following steps:
1. Click on the cell containing the formula.
2. The formula now appears in the formula bar area. I click on the left of the equal sign, then move my cursor to the left and click on the check mark.
I don?t know what that does, but suddenly the cell on the worksheet displays the correct value of the formula. Thereafter I have no more difficulty while the workbook is open.

If I now close the workbook (but don?t close Excel), then reopen the workbook, there is no error and the cell contains the correct value.
If I now close the workbook AND close Excel, then reopen Excel and the workbook, every cell containing the formula
=Avg20Day displays the #NAME? error.

I tried substituting the formula =INDIRECT(?Avg20Day? & INT((COLUMN() ? 1) / 13) + 1 for the defined name Avg20Day, and it always works OK.

I checked to make sure that Application.Calculation is set to Automatic (just in case).

The final insult is that I use the same defined name and definition formula in at least three other workbooks, and have never had a problem in them. Can someone explain to me what?s going on?

[Bob Phillips]

Shouldn't it be

=INDIRECT(avg20Day&INT((COLUMN()-1)/13)+1)

[Cyberdude]

Shouldn't it be

=INDIRECT(avg20Day&INT((COLUMN()-1)/13)+1)

No. What I have is about 16 defined names like Avg20Day1, Avg20Day2, ... etc.. In each of several nearly identical layouts I have a chart and a lot of formulas that produce data relating to each chart.

For example, for chart 3 I have several references to Avg20Day3. Since the formulas are identical for each chart, to avoid the hassel of making sure the sufixes are correct for each chart, I defined a generic name called Avg20Day (no suffix) that I use instead of Avg20Day3. The generic version figures out which chart number to use as a suffix and ..voila! ... I get the correct suffix no matter which chart the formula is used for.

As I said, I've been using this technique in several workbooks for months now, and it makes my life a little easier (usually). Actually I have about 8 such generics for various variables. None of the others in this workbook have the "Name" error problem.

Thanks for the reply, xld.