Hi Domenic,
Thank you. Your time and assistance is very much appreciated.
Your Formula does provide exactly what I requested. Unfortunately, I over
simplified the example: in a few cases the Numeric Label above the Summed
Target Value could be zero (0). Is there anyway of adapting your existing
Formula to exclude any Numeric Labels of zero (0) and thus return what would
be the next non-zero Numeric Label.
Apologies for my over simplified example.
Further assistance appreciated.
Cheers,
Sam
Domenic wrote:
Try the following...
First, define dynamic ranges for Column A and Column B...
Insert Name Define
Name: NumLabels
Refers to:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.999999999 99999E+307,Sheet1!$A:$A)
)
Click Add
Name: Values
Refers to:
=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.999999999 99999E+307,Sheet1!$A:$A)
)
Click Ok
Then use the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...
=INDEX(NumLabels,ROWS(Values)-MATCH(TRUE,SUBTOTAL(9,OFFSET(Values,ROWS(Va
lues)-1,0,-SMALL(ROW(Values)-MIN(ROW(Values))+1,ROW(Values)-MIN(ROW(Value
s))+1)))=C1,0)+1)
...where C1 contains your 'Sum Target Value'.
Hope this helps!
Hi All,
[quoted text clipped - 58 lines]
Thanks
Sam
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1