Problem with a named function

[Alistair Eberst]

Hi,
No responses to this question yesterday - please let me know if anything's
not clear.

I've managed to pass a reference into a named formula (using the method
described at http://www.j-walk.com/ss/excel/eee/eee009.txt ).

I've named three functions, Myref, Myaddress and location.

Myref is defined
=MID(GET.CELL(6,INDIRECT("rc",FALSE)),FIND("ROW(",GET.CELL(6,INDIRECT("rc",F
ALSE)))+4,FIND(")",GET.CELL(6,INDIRECT("rc",FALSE)))-FIND("ROW(",GET.CELL(6,
INDIRECT("rc",FALSE)))-4)

Myaddress is defined
=ADDRESS(INT((ROW(INDIRECT(Myref))-2)/64)+2,2,,,"germin (table)")

location is defined
=IF(OFFSET(INDIRECT(Myref),0,4)="","",((INDIRECT(Myaddress))))

The only spreadsheet cells with any entries are:
A1 is =IF(ROW(A1),location)
E1 is "3,3"
'germin (table)'!B1 contains "N34"

So far so good. The functions do what they're supposed to and return
the contents of the appropriate cell from the 'germin (table)' sheet.

If I attempt to add extra text into the location function like this
=IF(OFFSET(INDIRECT(Myref),0,4)="","","WQ/"&((INDIRECT(Myaddress))))
then I get a #VALUE error,

but if I do it in A1, as =IF(ROW(A1),"WQ/" & location), there's no
problem.

Can anyone explain what's going on here?

BTW, getting the type of the INDIRECT(Myaddress) i.e.
location as
=IF(OFFSET(INDIRECT(Myref),0,4)="","",TYPE((INDIRECT(Myaddress))))

gives 64 - (an array), if that's any help.

Thanks