Dynamic Charts - Named Range

[eoinymc]

Hi,

I want to make my charts dynamic and can only find explanations of how to do it using data from the chart in column format. i.e. the data goes down from A1:B4. My data goes across the rows. i.e. from A1:D2

How to use defined names to automatically update a chart range in Excel

Microsoft tell me to type this:

[COLOR=#333333][FONT=Segoe UI][B]=OFFSET($A$2,0,0,COUNTA($A:$A)-1)[/B][/FONT][/COLOR]

But, I don't know why?! Can anyone tell what I need to do to this OFFSET code to make it work using my data, the "transposed" way?

I could change the chart data to go the other, but it's being populated by a lot of VBA code and would be a lot of hassle.

Cheers,

Eoin

 

[pgc01]

Hi Eoin

The excel help for the Offset() function seems to me clear and complete, it even includes examples.

Did you read it with attention and still have doubts? Can you be more precise about your problems with the Offset() function, like is there any parameter that is not clear to your?


OFFSET function - Excel - Office.com

 

[eoinymc]

Hi,

Thanks for your reply.

Can you point me to the section where it actually explains what the OFFSET is doing and how I would apply it to a section of data that goes across rather than down?

I followed each step and could recreate it perfectly. But why they are using OFFSET and how I can apply it to my data (which as Ive said is the opposite to theirs), that's where I'm losing it.

Cheers,

Eoin

 

[pgc01]

Hi,

Can you point me to the section where it actually explains what the OFFSET is doing and how I would apply it to a section of data that goes across rather than down?

Hi

The 4th and 5th parameter are the number of rows and number of columns that you want in your range.

Ex.

=SUM(OFFSET(A1,0,0,4,1))

Your range has 4 rows and 1 column, and so this is =SUM(A1:A4)

=SUM(OFFSET(A1,0,0,1,4))

Your range has 1 row and 4 columns, and so this is =SUM(A1:D1)

 

[eoinymc]

Thanks for that..

But I believe you are incorrect.

The 2nd and 3rd parameters are rows and columns.

The 4th and 5th parameters are optional (height and width).

So therefore, in the example above, you can see:

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

So, Rows are 0 and Columns are 0 and height is COUNTA($A:$A)-1

So, my question is why are they doing the COUNTA for height and how do I implement that into my data?

Thanks,

Eoin

 

[pgc01]

... But I believe you are incorrect....

Hi Eoin

I'm sorry but I don't understand what this means.

Is it a question of faith or did you try to write some numbers in A1:A4 and A1:D1 and my formulas did not work? If this is the case, please post which numbers you wrote in the cells so that I can also test. If not please clarify.

Remark:
The only way you'll learn how to work with offset will be to do some testing.

Try some formulas in the worksheet to understand how offset allows you to define where your range starts and its dimensions.

For ex., try

=sum(offset(c3,3,-1,2,6))

and see that it adds the numbers in the respective range: starting in C3, go 3 cells down, 1 to the left and include the next 2 rows and 6 column (which means B6:G7).

If you do some tests like this one you'll be using Offset() without any problem in no time.

 

[eoinymc]

I have done testing and understand how offset works...I am not an Excel novice

What I don't understand is how offset works in this example of using it for dynamic ranges...

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

They are using the height parameter as COUNTA($A:$A)-1. Why are they doing this?

I tried to change it for width instead of height by doing the following:

=OFFSET($A$1,0,0,0,COUNTA($B$1:$M$1)-1)
OR
=OFFSET($A$1,0,0,1,COUNTA($B$1:$M$1)-1)

But neither of these work....so, I guess I'm confused as to what Microsoft are doing and how I change it to work when the data is opposite (columns instead of rows)

Thank you for your help...

 

[eoinymc]

For anyone that was the same as me, here is the answer:

=OFFSET($B$1,0,0,,COUNTA($1:$1))

Thanks for your help.

Eoin

 

[pgc01]

Hi

I'm glad your problem is solved.

Remark:

The formula you use now

=OFFSET($B$1,0,0,,COUNTA($1:$1))

has the same logic as your last one in post #7

=OFFSET($A$1,0,0,1,COUNTA($B$1:$M$1)-1)

Both work the same way
- In the first case you start in B1 and have as many columns as values in row 1
- In the second case you start in A1 and have as many columns as values in B1:M1 minus 1

The logic is the same. You define where it starts and how many columns the range spans.

To determine both where it starts and how many columns it spans, you always have to analyse in each case the actual data you have.