Dynamic Chart Range

[LFabbro]

Hi guys,

So.. I have to do a dynamic column chart for my work and I'm not managing to get it done. I looked up how to do the dynamic range - most cases the main solution is by using the OFFSET function - but in my case both the series and the values are dynamic, in a way that it doesn't seem to fully work for me. Just to clarify, here's an example:

Baseline

X Y Z
A 1 2 3
B 4 5 6
C 7 8 9

The bold values below are the ones that are probably going to be added, for instance:

Updated

X Y Z T
A 1 2 3 4
B 4 5 6 7
C 7 8 9 10
D 1 2 3 4

Is there a way to make set up a formula that will automatically add this new range on the chart?

Best,


LF

 

[Chrisdontm]

Reference your range with OFFSET like this;

=SUM(OFFSET($B$2,,,COUNT($B:$B),COUNT($2:$2)))

This is summing a range with OFFSET.

The range is defined with the OFFSET formula.

Notice the height of the range is found by counting the numbers in Column " B ",
And the Width of the range is found by counting the numbers in Row " 2 "

You may have to adjust it a little bit depending on how you have it laid out.
You might need it to go like this;

=SUM(OFFSET($B$2,,,COUNT($B:$B)-1,COUNT($2:$2)-1))

Or something like that...

 

[Sunny Kow]

Convert your data range into an Excel Table.
Any addition will auto-update your chart.