enter named range as opposed to cell ref.

[Gene]

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??

 

[T. Valko]

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!