+ Reply to Thread
Results 1 to 8 of 8

Dynamic chart with dynamic input

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    london
    MS-Off Ver
    2007
    Posts
    50

    Dynamic chart with dynamic input

    Hi Excel Forum,

    I am trying to make a dynamic chart for a dashboard like a graph.

    Problem is that the chart needs to exclude #N/A from the chart
    the input for the chart is in one tab (Data IN)
    and fetched to another sheet (Data Out) where the chart is allocated.

    The problem is the inputs are everchanging and need to resize (Gap out #N/A and 0) every time a new Task is fed into the (Data Out) sheet.

    - My goal is to exclude and don't have any gaps every time a #N/A or zero is located in the (Data Out) sheet.

    Thanks

    Lasse
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Dynamic chart with dynamic input

    You've got already nicely created dynamic ranges. Put them for series data instead of 'manual' ranges.

    Instead of:

    1Capture.JPG

    write like:

    C2apture.JPG

    use F3 to get list of named ranges, then select and click OK.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

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

    Re: Dynamic chart with dynamic input

    Not sure why you need 'Data Out' sheet...

    At any rate, you can create dynamic named ranges with following construct (or just use the ones you already have).
    Chart_Category:
    ='Data Out'!$A$2:INDEX('Data Out'!$A$A,AGGREGATE(3,6,'Data Out'!$A$A))

    Then go into Select Data of chart. Edit Axis Label range as 'Data Out'!Chart_Category

    Note: To use named ranges in chart, you need to prefix name with 'Sheet Name'! (if range context is workbook, it will auto adjust to workbook name).

    Do the same for each data series.

    See attached sample.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    12-11-2019
    Location
    london
    MS-Off Ver
    2007
    Posts
    50

    Re: Dynamic chart with dynamic input

    Hi both,

    Thank you very much for your help.
    I think my question could be clearer. Both of you tweaks work, however, I am still generating 0 or #N/A if NA() is used
    The problem is I have multiple lists (named and used as arrays) with data that needs to be used in the chart separately.
    see fx. list one marked with red and list three marked with black.
    under the dropdown, I have marked twenty cells, room enough for even the longest of the lists.
    however, when inserting and using list one in the dropdown it will result in 11 zeroes or not empty cell which then will result in a gap at the end of the chart.

    That's the dynamic input part of the question.
    I don't know if this is possible or if I am stretching the capabilities of XL.

    Thanks,

    Lasse

  5. #5
    Registered User
    Join Date
    12-11-2019
    Location
    london
    MS-Off Ver
    2007
    Posts
    50

    Re: Dynamic chart with dynamic input

    Forgot the image



    Thanks,

    Lasse
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Dynamic chart with dynamic input

    It isn't clear to me what exactly is the issue and how your actual file is structured. Image and the workbook you uploaded does not match.

    I'd recommend uploading file that accurately reflect your workbook set up.

  7. #7
    Registered User
    Join Date
    12-11-2019
    Location
    london
    MS-Off Ver
    2007
    Posts
    50

    Re: Dynamic chart with dynamic input

    Hi again,

    A new workbook i uploaded.
    As I said I want the zeroes after the list to not be a part of the chart.

    br,

    Lasse
    Attached Files Attached Files
    Last edited by Kaizan; 01-31-2020 at 04:43 AM.

  8. #8
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Dynamic chart with dynamic input

    Check attached file.
    I do not know what expected final result but so far it should be what you expect.
    Attached Files Attached Files

+ 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. Replies: 7
    Last Post: 05-08-2019, 05:24 AM
  2. [SOLVED] Chart with dynamic range based on user input
    By vizzkid in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-25-2018, 10:36 AM
  3. [SOLVED] Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA
    By kelseygueldalewis in forum Excel General
    Replies: 11
    Last Post: 09-11-2017, 04:52 PM
  4. [SOLVED] dynamic chart values formula with dynamic starting point
    By Kramxel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2015, 03:30 AM
  5. VBA - Updating Dynamic Line Chart & Inserting Dynamic Formula
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2014, 10:10 AM
  6. Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)
    By BrokenBiker in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-04-2012, 11:40 AM
  7. Can I create a dynamic chart with a dynamic number of series?
    By SG2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-24-2011, 08:44 AM

Tags for this Thread

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