Create dynamic name ranges on several columns for dynamic charts

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Create dynamic name ranges on several columns for dynamic charts

Post by BittenApple »

Hello team,

Can we create a dynamic name ranges such as A46 to J53 and use that one for data source of a column chart? Or should I created named ranges for each column individually?

Please advise me.

Regards,
Bita

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create dynamic name ranges on several columns for dynamic charts

Post by HansV »

Create a dynamic range for each column individually.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Create dynamic name ranges on several columns for dynamic charts

Post by BittenApple »

Hello Hans,
I have created a name range for each series:
=offset($A$42,,, CountIf($A$42:$A$53,<>""),1)

If I don't have a value, then 0 is displayed in the cell, which is shown in chart and this is for x-axis and values for x-axis are just condensed in one place.

Can anyone throw a light?

Regards,
Bitten

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create dynamic name ranges on several columns for dynamic charts

Post by HansV »

Do the source cells contain formulas? If so, what do they look like?
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Create dynamic name ranges on several columns for dynamic charts

Post by BittenApple »

Hello Hans,

Thanks for the response.
The source cells are getpivotdata formula. The sheet name is chart!, this converted to the tab where the source data is, which is ok. If I have x-axis static, this wouldn't be a problem. Once I saw that values are jammed to the left of the chart. I need to test it more though.
But what I need is to have x-axis to be dynamic. Is this possible?
Thanks,
Bittenapple

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create dynamic name ranges on several columns for dynamic charts

Post by HansV »

I'd have to see a sample workbook to understand what you're doing.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Create dynamic name ranges on several columns for dynamic charts

Post by BittenApple »

Hello Hans,
I will put something together soon and post it soon.
Regards,
Auslee