Share via

Excel chart with named ranges changes randomly

Anonymous
2022-08-02T10:54:52+00:00

Hi,

I have a chart that I can switch from monthly to quarterly view (with a developer button) by defining the data that feeds the chart to be a named range instead of a predefined range.

so in my name manager I have a few named ranges as such:

=CHOOSE('CC Tables'!$P$2,'CC Tables'!$K$7:$K$18,'CC Tables'!$Q$7:$Q$10)

$P$2 is the output value of the developer button.

I go to the chart- go to edit data- choose the data series and then in the series value I refer to the named range.

this all works great!

BUT- sometimes, randomly from what I've noticed, all my reference values turn into _{r9} or _{r15} and the chart looses all contact from the data. I have to go to each data series and refer it again to the named ranges.

sometimes it works fine for months, sometimes this happens- this week alone it happened 3 times.

any suggestions?

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-08-02T13:48:22+00:00

    Dear Anna_med,

    Thanks for posting in Microsoft community.

    We go through your description, since we could not repro the same scenario on our side, may I confirm the following information to help us narrow down this behavior:

    1.If only this specific file has this behavior or all files with similar charts have the same scenario?

    2.What is your subscription type and your Office version?

    3.If you colleagues have the same behavior.

    4.If you save this file on OneDrive, will you have this behavior with Excel Online?

    Meanwhile, you could share this file with OneDrive share link in the thread, we and the community members could help you check if we have the same scenario with this file. If the file is not suitable to share publicly, please let us know and we will get it with PM.

    Thanks for your cooperation, look forward to your reply.

    Have a good day.

    Sincerely,

    Sean Cai | Microsoft Community Moderator

    ***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

    Was this answer helpful?

    0 comments