• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why does Excel reset my dynamic name range?

E

Excel_Kid_663

New Member
I have made a chart and it is fed from a dynamic name range. The named range basically is an offset function that returns a range based on cell references with dates in. But anyway, When I apply it to my chart the series value looks like this:


=Gas_Dashboard!Gas_Spend_Dynamic


Where Gas_Dashboard! is the name of my sheet and Gas_Spend_Dynamic is my named range. I know it works, I have another for the labels and it all works fine. However, whenever I save my document then close it when I come back my named range looks like this:


=[0]!Gas_Spend_Dynamic


Why is it resetting my worksheet to [0]!? Do I need to select an option or something? I am using Excel 2007.


Many Thanks
 
Excel_Kid_663


It looks like Excel has replaced the sheet name with the name of a Data Table range that the data is being sourced from
 
Ok here it is:


-Removed Link


The workbook has a few Worksheets but I've cut them out to make it easier. ill explain what to do. On the worksheet called 'Front sheet' there are two charts at the bottom. Edit either one going to 'Select Data' You will notice there are 2 or 3 series depending which one you choose. If you go in and edit the series values you will see it looks something like this:


=[0]!Name_Range


Change it to:


=Data!Name_Range


Same with the label range too.


Do this for all the data values and label value and you will notice the charts range changes from November 2013 to February 2014. Now if you go into the 'Data' sheet and add or remove a value from the table there you will notice the chart is dynamic, it updates with the table. But now save your workbook and re open it, it should be completely reset back to =[0]!name_Range


This is really irritating. Any help is much appreciated.


Thanks in advance
 
When I edit the chart I see ranges like : =test.xlsm!RANGEGAS


I don't see a [0] anywhere?


Can you walk through step by step how/where you get them


ps, Nice looking dashboard
 
Why thank you sir.


Firstly I am opening the document, this version is stripped down and only contains the front page and data. I am going to the bottom left hand chart, right clicking it and going to 'Select Data' Once there I chose the first series which is electricity spend and go to edit and it looks like this:


Untitled-1_zpsc7ccc951.png



I have tried this in Excel 2010 at work and these charts and ranges work fine, I use 2007 at home I am wondering if its maybe a setting or something which needs changing. When you open the document, and look at the bottom left hand chart, how many months do you see? it should go up to Feb 2014 but as you can see from mine it doesn't, however if i alter the =[0]! to =Data! then it displays right, it just resets every time I go out and come back in.


EDIT: The img code doesn't work but you can paste this link into your web browser to see my screen shot


http://i839.photobucket.com/albums/zz319/lionheart663/Untitled-1_zpsc7ccc951.png
 
For anyone who comes across this post I have found the answer out. It simply requires the most up to date version of the service pack for office. I installed Microsoft Office service pack 3 and this problem was solved. I would like to thank hui for looking into this for me and anyone else who tried to help me on this. (I saw someone reposted this in mr excels forum too)
 
Excel_Kid_663


That's really good news


I keep my versions of Excel up to date all the time and just assume everyone else does


Thanx for letting us know.
 
You must log in or register to reply here.
Back
Top