A
Al
Hi,
I'm trying to pass a reference into a named formula (see
http://www.j-walk.com/ss/excel/eee/eee009.txt for the details).
MyRef is defined =MID(GET.CELL(6,INDIRECT("rc",FALSE)),FIND("ROW(",GET.CELL(6,INDIRECT("rc",FALSE)))+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))))
A1 is =IF(ROW(A1),location)
E1 is "3,3"
'germin (table)'!B1 contains "N34"
So far so good. The functions do what thy're supposed to and return
the contents of the appropriate cell from the 'germin (table)' sheet.
If 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
I'm trying to pass a reference into a named formula (see
http://www.j-walk.com/ss/excel/eee/eee009.txt for the details).
MyRef is defined =MID(GET.CELL(6,INDIRECT("rc",FALSE)),FIND("ROW(",GET.CELL(6,INDIRECT("rc",FALSE)))+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))))
A1 is =IF(ROW(A1),location)
E1 is "3,3"
'germin (table)'!B1 contains "N34"
So far so good. The functions do what thy're supposed to and return
the contents of the appropriate cell from the 'germin (table)' sheet.
If 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