Dynamic Charts - can't get to OFFSET against COLUMNS

[excel_guy123]

Hi All,

Recently posted on here about creating a macro to do what a dynamic chart is able to do... I have looked around at various sources on the interent, but when people do their tutorials, they always have the newer information being inserted in the rows, and not the columns.

For example - they all seem to have date in the Coulumn A, and then the £ or whatever in Column B, working downwards.

I need my Dynamic chart to format so that it automatically updates the information when new info is inserted into the next column on the same rows (see image below):

When I code the following name into a Name Reference:

=OFFSET(Sheet3!$A$1,,1,COUNT(Sheet3!$B$1:$S$1))

And then preview what it is referencing, it ALWAYS selects cells B1:B4 whereas I need it to be looking at B1:E4 (to represent incremental increases to the columns information.

Can anyone advise on the best way to get this work? My current main sheet is feeding off lots of other sheets (V LookUps and Macros) so I don't want to have to re-code all this to get the dynamic charts to work.

Cheers

 

[Derek Brown]

Try the following:
=OFFSET(Sheet3!$A$1,,1,COUNTA(Sheet3!$1:$1),COUNT(Sheet3!$B$1:$S$1))
This is assuming that A1 is empty - if you should later put something in that cell then you will need to change the formula to:
=OFFSET(Sheet3!$A$1,,1,COUNTA(Sheet3!$1:$1)-1,COUNT(Sheet3!$B$1:$S$1))

 

[excel_guy123]

Try the following:
=OFFSET(Sheet3!$A$1,,1,COUNTA(Sheet3!$1:$1),COUNT(Sheet3!$B$1:$S$1))
This is assuming that A1 is empty - if you should later put something in that cell then you will need to change the formula to:
=OFFSET(Sheet3!$A$1,,1,COUNTA(Sheet3!$1:$1)-1,COUNT(Sheet3!$B$1:$S$1))

Thanks so much for your response Derek - I just tried it now and it works! (Hurrah).

Just a further question - when I'm charting it I'm having to manually name each series. E.g. In the example I have to name Apples

=OFFSET(Sheet3!$B$2,0,1,1,COUNT(Sheet3!$2:$2))

And then name give Pears and Bananas their own names. Which all works.

But is there a faster way I can do it so the chart knows that each series has to use the same formula as 'Apples'.

I can imagine if I have quite a big sheet, with lots of series' - it will be a big pain to have to manually name each series and then properly configure it in the chart wizard...

Thanks again :D

 

[Derek Brown]

Probably the best thing for me to do is point you to what I think is the best site for chart help.
Have a look at "Dynamic Charts" in the index:
http://peltiertech.com/Excel/Charts/ChartIndex.html
In particular, the following page shows you how to set up dynamic names for data and labels:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html