Updating Graphs with Dynamic Named Ranges Efficiently (Many Series)

  • Hello OzGrid!

    First post....here we go!

    I am looking for a way to set either the data source for a graph using a Single Dynamic Named Range OR easily create Dynamic Named Ranges for the multiple series (where talking about 119 series) contained within the chart. The technical side of my issue is described below.

    • I have tried to adjust the 'Data Source Range, of the Data Source Tab, for a graph to look at a Single Dynamic Named Range (encompasses my whole data set). I found upon closing and re-opening the graph, the dynamic range data source is lost and replaced with the generic cell referencing. If this works it would be GREAT and save me creating 119 dynamic named ranges and having to updating them all in the graph series tab.

    I can't currently upload an example of the data but can explain its basic structure.

    • There is a series of 119 ground marks stored in one column (running down the page).
    • Each ground mark has, stored against it in consecutive columns, the amount it has moved over time. There is an equal number of moves for every mark and about 30 moves in total for each mark and the data set is always being added to.

    I can always resort to VBA but would love to hear if it can be done without code as my co-workers need to be able to understand process as well.

    Thanks very much OzGrid Community!

  • Re: Updating Graphs with Dynamic Named Ranges Efficiently (Many Series)

    Hi, you can use dynamic named ranges but need to enter both x and y ranges dynamically. You have to use the something like
    After you have entered these and you go back in excel should have changed it to
    Or something similar. You need to enter the sheetname to start or it doesn't work.

  • Re: Updating Graphs with Dynamic Named Ranges Efficiently (Many Series)

    Ahhh problem solved! Thanks a bunch for your help guys.

    Accessed the SERIES formula for the graph on the formula bar and edited the Category range and data series range with my two named ranges.

    Thanks very much again for your help,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!