Offset+counta to autoselect data range for charting purposes

Hi community!

I have learned how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).

So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.

Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... Please note there is no data in there so feel freel to fill it with any numbers you like.

I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?

Thanks a lot for your help! Really appreciate it.

All the best from my side to you all for Xmas time

So according to the attachement "Book2", I would want to set the data source in the offset formula (avobe) for my chart as follows:

AB2:AB9 and AB26 and AB27 as they match with the values in country column: Zimbabwe, Programme and Year.

Hope you can help me with this.

Cheers

You are right, but as I don't know how to generate the chart, I have tweaked it with Paint. So you can see how output would be.

I have also tried the pivot chart, but it does not make the job to automatically update the chart?? I have made a few tests and cannot find the way to make it work.

So that's why the point of the formula. Any idea?


Many thanks!

Hi Andy,

Thanks for your help! However and as you may know the potentials of the pivot charting is quite limited. That's why I was looking for the 'formula' way to achieve 'the same' but with the regular charting features.

Any idea how to achieve this with the formula??

All the best,

Hi Andy,

Thanks a lot for replying!

What do you mean by "build contiguous range of data"?

The main problem with the chart out of the pivot table is that it won't get updated when the pivot does.

There is no way around or solution to do this with the offset+count formula?

THanks!!!