Passing argument to named formula

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top