Macro to add to Name Manager and Create Charts

So for the second time on this project, I need help on different stuff.

Whats been done so far:
I'm setting up an automated system to filter out information from a report that I receive monthly, resort it and link 12 month rolling graphs (dynamic) into progress reports. In the original workbook I use a whole bunch of VLOOKUP formulas to sort out the data into separate work sheets and then run a macro (thanks to walruseggman) to autofill data into columns on each sheet.

Where I'm stuck:
I started creating the dynamic name ranges for each worksheet, which required 15 or so values per worksheet before I could create the 14 graphs. With a minimum of 15 worksheets, its taking far too long to create. I've found where someone else has created macros that would automatically create dynamic range names, but with the specificity I'd like in the names and the way I've created the 12 month rolling formulas, I can't seem to tweak the code to make it do what I'd like.

The ask:
Is there a way for dynamic range names to be created following a format (I1ChtVal1; I1ChtVal2; I2ChtVal1...) as well as their formulas [=OFFSET(I1ChtCats,0,1); =OFFSET(I1ChtCats,0,2)...] and then for graphs to be created based on those values? The aesthetics of the workbook don't matter, so even if I have to plug formulas or names into cells and just fill into the range names from there, that would still be quicker than entering them all into name manager manually. Also, even though all 14 columns won't necessarily be filled on every worksheet, having the graphs already made in case a new column is added would be helpful.

I've included a mocked-up workbook with what I think are all the necessary components. A full set of values for the first worksheet are also included to illustrate the name format and formulas.

Any help or advice is greatly appreciated.
Example DynNames and Graphs.xlsx

Hi there,

I've had a quick look at your example workbook and am trying to understand exactly what you have in mind.

Is one row of data (starting in Column G) added each month, and are you trying to display a maximum of 12 (ChartLen) values in each graph?

Do you specify the starting row for each set of graph data (e.g. from a dropdown list)?

Do you display data for the 12 most recent months?

I will probably have more questions, but the above are the most basic ones.

Regards,

Greg M

Is one row of data (starting in Column G) added each month, and are you trying to display a maximum of 12 (ChartLen) values in each graph?

Yes,in the original workbook I use a macro to autofill G:U on a monthly basis from Column C. Each graph should typically display 12 months. However, it was easy enough to specify ChtLen to give the option for longer or shorter review periods if that became necessary.

Do you specify the starting row for each set of graph data (e.g. from a dropdown list)?

No, dynamic ranges use an offset formula from the last date in Column G, counting back ChtLen or the number of data points (whichever is less), to specify the length of the graphs as well as which column to pull data from. That way all graphs will represent the current and respective data based on monthly review periods.

Do you display data for the 12 most recent months?

Typically, but at times longer or shorter review periods could be warranted. If that throws a wrench in the works though, I suppose its not absolutely necessary. They should always reflect the most recent months however.

Hi again,

Ok, thanks for that - I've got a better idea of what's likely to be involved now.

A suggestion - as graphs are such visual elements of a workbook, they very often require tweaking once "real" (as opposed to test) data start to arrive - e.g. a different background colour here, a different font weight there, a greater line weight somewhere else. Making such changes to 14 graphs is bad enough, but having to make them to 14*(15+) graphs is something that shouldn't be inflicted on anyone! In view of this, might it be worthwhile to use only a single set of 14 graphs and to use something like dropdown lists or CommandButtons to populate the graphs from the appropriate dataset?

Let me know what you think.

Regards,

Greg M

That's kind of what I was afraid of.

The idea of this workbook is to serve as a quick way to populate tracking data and autograph it into linked word documents. Thus, each graph on each worksheet will be linked into a word document. By using a single set of graphs, it would require the user to switch back and forth between a linked word document and the workbook, updating one at a time. Even though that would be a simple task for most people, if I have an in intern or assistant trying to populate the linked word document, it introduces a more room for error.

I'm actually kind of okay creating the graphs by hand if that's what needs to happen. I can create a copy of the workbook and reuse it for separate caseloads. Though it would be a task, putting in a bunch of work in on the front end would be worth it.

Would it be simple enough to create the dynamic range names via macro?

Hi again,

Thanks for your prompt replies.

Ok, no problem, it was just a suggestion.

Actually, I don't think it'll be too difficult to give you what you need. Right now it's after midnight here, so let me play around with it a bit more tomorrow, and with luck, I may have something to send you then.

Regards,

Greg M

Hi again,

Ok, I think we're heading in the right direction!

The situation becomes a lot more manageable when you use worksheet-level defined names rather than workbook-level defined names.

The attached workbook contains six individual worksheets (each with its own set of graphs) and a Master Template worksheet on which you can perform a normal Worksheet Copy operation as many times as are required - no macros are required.

I used some macros for formatting and customising the graphs - these are no longer required. I've left them in the workbook just in case you'd like to take a look at them, but as I said, you don't need to use them.

Hope this helps - please let me know how you get on.

Regards,

Greg M

I'm sorry for such a lag time in response. I had some unexpected things come up and had to be out of pocket.

That works beautifully. Thank you so much for your help and recommendations!

Hi again,

Many thanks for your feedback and for the Reputation increase - I´m glad I was able to help.

Best regards,

Greg M