Dynamic Named Ranges using Match, Address and Indirect functions

[kdw3]

Hi All,

Looking for a little advice here. I am having an issue when I try a chart a named range. The named range "Refers To" is

=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))

As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.

The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesnt flag up any errors, just nothing.

The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I dont want to have to keep changing the references in the graph so make sure the right data is being displayed. Can anyone shed some light on why this formula wont work. Is it just that charts don't play well with the INDIRECT function?

Thanks
Kevin

 

[p45cal]

3 points:
1.Don't try to add this named range to the Source Data dialogue Data Range tab, instead use the Series tab
2.Use the qualified named range name; Book1.xls!Aname, not just Aname
3.Shorten your refers to: formula to the likes of:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(OFFSET($A$1,MATCH("Kevin",$A:$A,0)-2,,,100)))

 

[kdw3]

Hi,

Thanks soo much for your reply. Points one and 2 I had already done but point 3 is genius!! It appears to work for the time being.

Thanks for your help
Kevin

 

[jim may]

Are these () even needed? just In passing....

=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(OFFSET($A$1,MATCH("Kevin",$A:$A,0)-2,,,100)))

 

[p45cal]

Are these () even needed?

Not at all, just a remnant.