+ Reply to Thread
Results 1 to 8 of 8

Dynamic named ranges in Chart is dropped when last value is selected from dropdown list

  1. #1
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Dynamic named ranges in Chart is dropped when last value is selected from dropdown list

    On the attached workbook, the TO1Chart worksheet has a chart that calculates weekly data based on values selected from the dropdown lists in cells C24 and F24. It works beautifully as long as I do not select the last date value in the F24 cell, which in this case is 2/5/20. If I try to chart any range using this last 2/5/20 value, then the chart will cause all the named ranges referenced in the Weekly TO Chart to be replaced with static cell addresses. NOT good!

    What can I do to prevent this from happening? Must there be something put in to prevent anyone from being able to use that last value?
    I'm so darn close to having a program that we can use for our agency here. So frustrating!

    NOTE: To the right of the chart are listed the named references and formulas that I have currently placed in the Select Data section of the chart. Hopefully, this will help to illuminate the problem better.

    Also Note: the Daily chart seems to work just fine using the last value in the dropdown list in cell F2. Why?
    Attached Files Attached Files
    Last edited by Dbroek; 08-01-2020 at 11:59 AM. Reason: Actuall, is not solved.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    It's because of 2nd argument in your Series function.

    If you check 1st chart, series is defined as...
    =SERIES("Average",,TOtest.xlsm!ValueDurTO1D,2)

    Where as in your 2nd chart.
    =SERIES("Weekly Duration",TOtest.xlsm!DateTO1W,TOtest.xlsm!ValueDurTO1W,2)

    Remove "TOtest.xlsm!DateTO1W" and it will behave the same. Though that's going to impact your axis label.

    Not sure how to fix that... or why this would cause your named range reference to revert to fixed range.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    I see what you mean.
    I wonder if it is because of the macro I have in there that is designed to always end up with the last table row having N/A errors in the AV and AW columns?

  4. #4
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    I wonder if it is possible to NOT have the last row date (2/5/20) be populated on the chart's end date selection? Whether that would be a work-around to prevent the reversion to fixed ranges? If so, how would you write the formula to do that?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    There is something funny happening with that specific chartobject.
    Delete it and add a new chart, then you should be able to use named ranges for category and values.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    Did a total chart rebuilt from scratch. Yes, that did resolve the problem.
    Good grief... another hard earned lesson.

    Thanks & bless you, Andy!

  7. #7
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    Actually Andy, this will work for awhile but apparently remains unstable. After multiple hits with the last date, it will fall back to fixed references again.

    I'm guessing I think, perhaps, we need to prevent the chart from being able to list that last date in the C24 and F24 cells?
    I have
    DateTO1Weekly
    as a named reference in those cells with the
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    table formula as the named date range. Is there a way to revise this name range to minus or list all but the last column value (date) in the row?

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamic named ranges in Chart is dropped when last value is selected from dropdown lis

    I don't think there is a structured way to get all but last row.

    Instead use formula to build a reference to the valid dates.

    You could also try moving the MATCH formula out of the named range into their own named ranges, in case that is causing a problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Paste named ranges based on dropdown list
    By krish182 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2020, 10:58 AM
  2. [SOLVED] Dynamic Named Ranges used create a Chart
    By sdingman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2017, 10:13 AM
  3. [SOLVED] dynamic chart with named ranges
    By a.hudrea in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2014, 05:18 AM
  4. COUNTIF referencing different named ranges in dropdown list
    By dutton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2013, 10:01 AM
  5. [SOLVED] Dynamic Named Ranges & Chart Series
    By stockgoblin42 in forum Excel Charting & Pivots
    Replies: 17
    Last Post: 05-15-2013, 01:31 AM
  6. Dynamic Chart Using Named Ranges
    By jjcgirl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2013, 11:57 AM
  7. Chart using dynamic named ranges
    By sinspawn56 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-05-2010, 10:39 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1