help on sumproduct of named ranges

[Guest]

hello,

please help me to accomplish a simple formula...

i have many different named range, to simplify the data here goes below...

my range name for search criteria:
MRO <A2:A200>
MRO1 <B2:B200>

my range name for summing result something like a calculator
MRO2 <C2:C200>
MRO3 <D3200>
MRO4 <E3:E200>

In a formula like this, its okey
=sumproduct(--(MRO>0),--(MRO1>2),MRO2)

then, if i use a cell say A1, then i place the range name like "MRO2" or
"MRO3", or "MRO4" ---- one at a time...

then,how to write it down in a formula something like this:
=sumproduct(--(MRO>0),--(MRO1>2),"&A1")

i need to save space so i may prefer to use A1 as the holder for my result
range.

i hope it may make sense....trying to gain more simplicity.

thanks and regards,
driller

 

[Dave Peterson]

=SUMPRODUCT(--(MRO>0),--(MRO1>2),INDIRECT(A1))

Be aware that these aren't nice names anymore, well, if you ever plan on
upgrading to xl2007. xl2007 has 16384 columns and mro1, mro2, mro3, ... are
gonna be cell addresses. You may want to use a different name: _mro1, _mro2,
.... to avoid future headaches.