named formula problem

[sphenisc]

In order to try and conserve a bit of memory and prevent things crashin
I'm trying to rewrite some of my heavily used formulas as name
formulas. Here's the gist of the problem, enjoy...

I've managed to pass a reference into a named formula (using th
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 you 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.

Thank

 

[Harlan Grove]

In order to try and conserve a bit of memory and prevent things crashing
I'm trying to rewrite some of my heavily used formulas as named
formulas. Here's the gist of the problem, enjoy...

...

Unclear why you think this would lessen rather than increase the chances of
crashing.

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) ...
The only spreadsheet cells with any entries are:
A1 is =IF(ROW(A1),location)
E1 is "3,3"
'germin (table)'!B1 contains "N34"

...

You do realize that your named ranges calling XLM functions are utterly
pointless, don't you? Why do you believe you need Myref at all rather than just
using the argument to the first ROW() call in the rest of your function? You're
only doing this to shorten your formulas, but you're gaining nothing other than
storage savings. But the equivalent formula without defined names and XLM
function calls would be

=IF(ROW(A1),IF(OFFSET(A1,0,4)="","",
OFFSET('germin (table)'!$B$1,1+INT((ROW(A1)-2)/64,0)))

Since ROW(x) is always greater than 0 for any valid range reference x, this
reduces to

=IF(OFFSET(A1,0,4)="","",OFFSET('germin (table)'!$B$1,1+INT((ROW(A1)-2)/64,0))

Maybe it's just me, but I don't think this is all that long or crash prone, but
I am sure that it'll recalc faster than your convoluted defined names. It may
take a bit more memory to store, but that could be partially addressed by giving
'germin (table)'!$B$1 a defined name like TGT, thus

=IF(OFFSET(A1,0,4)="","",OFFSET(TGT,1+INT((ROW(A1)-2)/64,0))

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 you explain what's going on here?

It's the ROW() call in the ADDRESS() call used to define Myaddress. It gives a
1-by-1 array, and that can screw up some formulas (though I can't replicate the
error in Excel 97). Force it to a scalar with

=IF(OFFSET(INDIRECT(Myref),0,4)="","",
"WQ/"&CELL("Contents",INDIRECT(Myaddress)))

However, I'll stick with my underlying opinion that you should forget these
defined names and just use a simple though longer formula.

=IF(OFFSET(A1,0,4)="","","WQ/"&OFFSET(TGT,1+INT((ROW(A1)-2)/64,0))