Series Values Using Range Names are Resetting to [0]! When File is Closed

skimmer

New member
Joined
Nov 3, 2011
Messages
1
Reaction score
0
Points
0
Hi all!

I have a strange thing happening....

I have check boxes linked to a series of values (that turn on and off, 0/value, with true/false) and I have range named the values using the offset function (so that the range is dynamic over time). The series values of my chart are then recorded as: 'filename.xlsx'!rangename.

I have done this hundreds of times and have never had a problem. Even here, i have no problem...you check the check box and that series is shown on the chart, uncheck it an it disappears. Perfect. Then I save the file and reopen only to find that 'filename.xlsx'!rangename has become [0]!rangename which obviously does me no good. There are other charts in the file using the same method (with different range names). I have used the sheet name instead of the file name. I have opened a new worksheet in the file and recreated the graph. But, each time after the save, close, reopen I find the [0]!rangename.

Has anyone ever seen this before? Is there something wrong with my rangenames?? Is there some sort of corruption? I can recreate the rangenames (I haven't tried that yet), but thought I'd put it out here first....

Thanks so much for your help!

Kim
 
I seem to recall that calculated range names which use OFFSET only work when the workbook is open, but I may be wrong.
What I would try to do is the following:
- Convert your range with data to a table
- Now re-create the range name(s) pointing to the relevant parts of the table (instead of the OFFSET, use direct table references, Excel will do so automatically)
- These new range names will auto-expand.
- Use those range names from your other workbook.
 
Hi Kim,
I am experiencing the same issue ,I was wondering if you could find a solution for that yet?
Thanks
Alaleh




Hi all!

I have a strange thing happening....

I have check boxes linked to a series of values (that turn on and off, 0/value, with true/false) and I have range named the values using the offset function (so that the range is dynamic over time). The series values of my chart are then recorded as: 'filename.xlsx'!rangename.

I have done this hundreds of times and have never had a problem. Even here, i have no problem...you check the check box and that series is shown on the chart, uncheck it an it disappears. Perfect. Then I save the file and reopen only to find that 'filename.xlsx'!rangename has become [0]!rangename which obviously does me no good. There are other charts in the file using the same method (with different range names). I have used the sheet name instead of the file name. I have opened a new worksheet in the file and recreated the graph. But, each time after the save, close, reopen I find the [0]!rangename.

Has anyone ever seen this before? Is there something wrong with my rangenames?? Is there some sort of corruption? I can recreate the rangenames (I haven't tried that yet), but thought I'd put it out here first....

Thanks so much for your help!

Kim
 
Back
Top