| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional reference
Thanks a lot!
"Bob Phillips" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional reference on average interest calculation | Excel Worksheet Functions | |||
| Conditional Formatting Reference / Inserting Rows | Excel Worksheet Functions | |||
| Conditional formatting a full row triggered by a single cell reference... | Excel Worksheet Functions | |||
| Expanding conditional formating with reference cells changing | Excel Discussion (Misc queries) | |||
| Can a conditional cell formula reference data selection in a comb. | Excel Worksheet Functions | |||
Threaded Mode
