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

  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.
    Regards,
    Jan Karel Pieterse
    www.jkp-ads.com

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
  •