Could you not simply INDIRECT it and use that in the PopTools formula. For
instance, if this formula returns B1:B10, =SUM(INDIRECT(this_formula)) will
sum those cells.
Other than that, you could use an array formula to get a range, like so
=SUM(OFFSET(B1,,,COUNTA(IF(A1:A100="a",A1:A100)),1 ))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Maarten" wrote in message
...
Great, that works, but it seems that I can't do any calculations with the
result. It looks like Excel doesn't recognize the formula result as a
reference. Is there any way to get around with this?
I want to use the results (reference) in a formula ('bootmean' from
Poptools
addin)which may only use cells in column B from a certain class (defined
in
column A).
Is there a way to make Excel recognize the result of the formula below as
a
reference?
Many thanks,
Maarten
"pinmaster" wrote:
Hi, try this:
="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW( INDEX(A:A,MATCH("a",A:A,0)
))+COUNTIF(A:A,"a")-1
or
="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(I NDEX(A:A,MATCH(F1,A:A,0)))
+COUNTIF(A:A,F1)-1
where F1 is your lookup value.
HTH
JG
--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile:
http://www.excelforum.com/member.php...fo&userid=6261
View this thread:
http://www.excelforum.com/showthread...hreadid=494109