#Value in named range

[Dkline]

In my workbook I have a "Calcs" worksheet and an "Output" worksheet. All of
the calcs are done on the Calcs tab. All of the stuff to be printed is on the
Output tab.

The columns on the Calcs tab that will be used in output are named ranges.
My problem is on this one case is that the values are displayed in one column
on Output are shown through the first 60 rows and then I get #VALUE!. The
source column on the Calcs tab shows values through to the end of the column.

Why is Excel displaying #Value all of a sudden when I know that the value
are on the Calcs sheet? I tried Formula Audit and it returns the #VALUE!
error.

 

[BRob]

You often get a #VALUE error if you are trying to do a calculation where one
of the cells that is being referenced contains text.

Can you see what is different in the cell contents, formatting or formulae
that make up the rows 60 and 61 of both sheets?

If you can't find anything then /copy your spreadsheet/ and then try the
following on the copy. (It won't solve the problem but it will isolate where
it is and you might then be able to put more info onto this thread.)

Copy the content over from row 60 to 61 in Calcs. If the calculation then
works then you know the problem is in that worksheet. If you still have the
problem then try copying row 60 to 61 in your output sheet; if that works
then the problem is on that side.

If neither works then it might be that there is a more complex problem that
causes that error (eg data connections).

 

[Dkline]

Thank you for your reply.

The only way I can get this to work is to not use the
"=colNetSurrenderValue_AT" but use the cell reference instead "=Calcs!RC7".
There are 121 rows in each column.

While using the "=colNetSurrenderValue_AT" I tried to coopy the last cell
before the #value to that first bad cell. It returns the #value.

When I try it in formula audit, it returns #value.

So I do have a solution - use the cell reference rather than the named
range. I just would like to know why the named range stops working in
mid-stream for only one column. All the other columns that point to a named
range are fine.

It's a mystery to me.

 

[JE McGimpsey]

How is

colNetSurrenderValue_AT

defined?

Conversely, are there valid values in the named range?

 

[Dkline]

=Calcs!R3C7:R62C7

SO THAT ANSWERS THE QUESTION! I didn't have the range defined for the entire
column.

Excuse me while I give myself a dope slap upside the head.