Thread: Dynamic chart problem

Hi Folks,

I am trying to do a dynamic chart with a method I found on this forum some time back. The method is to define a named range by an OFFSET formula.

For this specific chart that range might move on the worksheet so I made this function:

=OFFSET(INDIRECT("G"&MATCH("Result";C:C;0);TRUE);0;0;1;43)

This does not work in the chart. I get an error saying "A formula in this worksheet contains onr or more invalid references".
If I look at "Name" it looks ok, the range get "highligted" if I select the formula.

It seems to have to do with the INDIRECT function. If i use this function it works:

=OFFSET(G71;TRUE);0;0;1;43)
Any clues?

Thanks
Anders.

Yes INDIRECT does not play nicely with named ranges for chart data source.

Try this variation,

=OFFSET(OFFSET(Sheet1!$G$1,MATCH("Result",Sheet1!$C:$C,0)-1,0,1,1),0,0,1,43)

Works perfect.

Thanks!
Anders