Cyberdude
02-05-2007, 12:25 PM
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?
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?