It's a quirk of the INDIRECT function. I could try to explain it but it
doesn't make any sense and would probably just confuse you even more. It
even confuses me and I'm supposed to know what I'm doing!

--
Biff
Microsoft Excel MVP


"Gene" wrote in message
...
I built a range reference called REND. I placed it in cell W9 to see how
it
worked. i.e. W9 is =REND. Result is S9:S$50. Tried to use it in another
formula cell.

{=MAX(VALUE(SUBSTITUTE(INDIRECT(REND),"5:","")))} #VALUE error
{=MAX(VALUE(SUBSTITUTE(INDIRECT(W9),"5:","")))} works

I don't want to use the cell for this purpose so I used the name dialog
box
to insert the formula REND. Why??
--
GeneS