Results 1 to 3 of 3

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 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!


  2. #2
    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.
    Jan Karel Pieterse

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Thanks Jan Karel!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts