Name range problem

[Guest]

I defined a range and named it Pen. Pen referred to A3:A12. I typed the
word Pen in cell B4. When I used the formula =AVERAGE(Pen) I got 4.7 which
is correct. When I tried =AVERAGE(B4) I get #Div/0. What can I do to make
the formula realize that the name I typed in B4 is a defined name and not
just text? I have been struggling with this for some time.

 

[Frank Kabel]

Hi
=AVERAGE(INDIRECT(B4))

 

[Guest]

This works in a brand new spreadsheet but does not in a previously created
worksheet. Is there possibly a setting that I am missing or is there
something else that could be interferring in this spreadsheet?

 

[Frank Kabel]

Hi
this should work in all sheets. What is the exact formula which does
not work and what is the exact error/return value of this non-.working
formula

 

[Guest]

The exact formula is =AVERAGE(INDIRECT(B4)) which returns #Ref. When changed
to =AVERAGE(B4) it returns #Value.

I might not have been clear earlier. When I open a new workbook it works
fine but in the original workbook it does not.
Thanks

 

[Guest]

The actual formula is =AVERAGE(INDIRECT(A6)) where in cell A6 TCO68. The
return is #Ref. If =AVERAGE(A6) is typed I get #value. TCO68 is an offset
range that is evaluated correctly.

I might not have been clear earlier. When I open a new workbook and do a
test this works okay but in the original workbook it does not.

Thanks

 

[Dave Peterson]

If you open that workbook and hit edit|goto and type in TCO68, what happens?

And if it goes to another range successfully, what's in those cells?

 

[Guest]

Using Goto TCO68 it goes to the correct range. When I type in
=Average(TCO68) it does this correctly. The problem arises when I type TCO68
in a cell (A5) and then try to use =Average(A5). The formula does not
recognize that the text typed in cell A5 is a range name.

 

[Dave Peterson]

I would have guessed that:

=AVERAGE(INDIRECT(A5))

would work. It worked ok for me.

When you hit that Edit|Goto and typed TC068, you selected that range.

If you hit Edit|goto once more (still with TC068 selected), you can click the
Special button.

From there, you can look for errors (like #ref!'s) in that range.

You may have to look twice--once for constants and once for formulas.

(That was the only way I could get that error.)