Excel graphing

Topic

New Reply

I have a situation where I have many graph sets, each set of graphs consists of three graphs where the first graph in the set spans an X-Y range from A1:Z1 and A2:Z2 say (ie the full range), the second graph range grows each week by one column ie in week 10 the X-Y range will be A1:J1 and A3:J3 in week 11 will be A1:K1 and A3:K3 etc. The third graph range in the set shrinks in a similar way by one column each week.

All the graphs share the same column that the range ends in, ie. I could have 30 graph sets where the second graph end in column J this week and column K next. The graph row data do not change from week to week.

Currently I laboriously have to open each graph and manually change the x and y values of each range for each second graph in each graph set to the new weeks ending column. Is there a way in which I can enter the range ending column once (in a cell say) and have the ranges updated based on the value I input into that cell?

Am at my wit’s end with this one and would appreciate whatever assistance I can get.

Thank you

Theo

Reply | Quote

Replies

Theo,

Attached is a worksheet which I believe solves your problem. It does so via the use of the OFFSET function and NAMED RANGES.
The down side is you have to create two names ranges for each chart one for the X axis and one for the Y Axis. But once done the ranges will automatically adjust as data is added/deleted and the chart changes with it.

Let me know if this is what you were looking for.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi there

Thank you for your prompt response. It is certainly appreciated and I don’t believe the effort one would have to put in is a barrier to using that solution, but unfortunately the graphs I need this for pose a challenge I had not foreseen before seeing your proposed solution. As I am a first time user of the lounge forum (but long time full subscriber to Langalist, Gizmo, Windowssecrets etc) I was not aware that one could upload example files. Thus I am going to try that and annotate accordingly to try and clarify my dilemma.

Regards

Theo

Reply | Quote

Theo,

This entire process, including the import of the data and elimination of zeros that should be blanks by input of an end date, could be automated via the use of VBA {Visual Basic for Applications}. This is however far beyond what can be accomplished on this forum. My suggestion is that you find a consultant that does this kind of work, it will be well worth the money you spend by the time savings and increase in accuracy of the operations.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hey Theo,
I think I have understood your predicament, and hope I have an somewhat easy fix

Rather than write some extravagant code for this, I have simply used a dynamic range name for the actual data, and added some buttons which will change it up and down as you wish….if that makes sense. All you will have to do when you copy and paste the new data, is to hit the button to add data. For your ease, I also added a button to automatically reduce the data by one wk as well, just in case you hit the increase twice, or just wanna play with it

These changes can be found using the INSERT – NAME – DEFINE menu items for the dynamic range names, and within the source data area of the chart itself

The VBA code comoponent (ALT + F11) is really simple (and short) and references cell range A5 (for the purpose of the exercise only, and can be changed to any other cell as needed)

The only real problem will be setting it up for all of the graphs, but once done, clicking the button will change all graphs at the same time

Hope this helps you out, or gives you a better idea

Reply | Quote

Hi there to both RetiredGeek and dabbler68. Thank you for your valuable input, I have tried out some of the additional information I gleaned from dabbler68 and have tested it. What I need to do now works and all that is left now is the lengthy once off exercise of setting up the spreadsheets. Thanks again, learnt a lot, and the VBA script put a nice touch to it, although I would have even been happy to just type in the width every time.

Great stuff and really worth it for me.

Theo

Reply | Quote

Here is a method without VB…

Create the names (insert name – define)
xDate =Sheet1!$B$2:$S$2
yPlanned =Sheet1!$B$3:$S$3
yActual =OFFSET(Sheet1!$B$4,0,0,1,MATCH(0,Sheet1!$C$4:$T$4,0))

Then assign those names to the chart.
[Put a zero in the cell T4 in case the range is filled, this will be the first zero.]

The offset named range starts at B4 and extends to the first 0 in the range C4:T4

Steve

Reply | Quote

Now this is exactly why I read this newsletter and follow lounge postings… I have a similar situation, and knew there had to be a way to improve on all the manual effort. Thanks people. It will take some coding to make the changes but this discussion is going to save me at least half an hour every day once I finish the updates.

Reply | Quote