MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
- good afternoon numbercrunchers !
I wonder if anyone'd like to come for a visit to the dungeon of drudgery:
I have a spreadsheet with about 15 tabs. Each has a different dataset but in exactly the same layout and from each of those tables are drawn a series of charts.
When I'm editing the data I can select multiple tabs and do a sort of batch operation but I can't see a way of doing this with the charts (and they're "too complex" to make them autoextend, or so I'm told by the ghost of Bill Gates)
Anyone fancy a punt on this ?
Ta
I'd be looking at macros I think. Or rethinking the approach.
Any reason that the 15 sets can't be combined, then a draw your graphs from that single set? Pivot Chart from that single table perhaps?
Just get a new job?
a new job where you don't get to use Excel - madness
Cheers all, didn't have my hopes up for an easy fix (not clever enough to write a macro and it's not a process I can record easily)
... so I've decided to take perchy's advice and become an exotic dancer
anyone know of an opening* ? (I have my own tassles)
* đŸ˜‰
are the charts visualising data from more than one of the tables?
Or have you just got 15 copies of the same set of charts, one set for each table?
Assuming you are using a recent version of Excel (2013 +) I'd consider powerpivot to combine data sources and then use a pivot chart to display the data
if its all individual tables then I'd create pivot charts for each set of data, they should auto refresh OK.
If the purpose of the spreadsheet is to share the visuals with a group of people then I'd also seriously look at PowerBI. Its free and the visualisation tools are excellent.
charts reference data
so if data changes, charts change
when you say you "edit the data" do we assume you are changing the data using a "batch" process?
The question is then why doesnt the chart update automatically?
Unless of course the array of data being charted is changing...
in which case you need:
Dynamic chart named ranges.
And with that, Alice slipped back off down the rabbit hole and left a link on the thread...
http://www.criticaltosuccess.com/dynamic-charts-in-excel-resize-with-offset-function-named-ranges/
I'm not sure what you mean by "autoextend" but Stoner's suggestion is probably the answer you're looking for.
err, yeh, probably that. It's monthly updates to a chart that's plotting one complete year against a growing count for this year. Do you reckon that's doable ?Unless of course the array of data being charted is changing...
Oh, and
another baby unicorn dies on the grocer's altar đŸ˜¥Now you can make your [b]chart’s[/b] dynamic by inserting the range names you have created into the formulas charts use to reference data
notwithstanding the unicorn massacre, yes, you need to become a black belt in named ranges and sticking them into the graphing dialogue boxes correctly. It will do what you wish.
Your future looks horrible though, but I see a promotion on the horizon.
It sounds as though Stoner's suggestion is exactly what you want. You do need to read through the whole page at least once before it makes sense but it is very clever
...adds to bag of Excel tricks
I looked into some of dat shit but our work pcs are so woefully underpowered and the dataset so big that I've had to separate the report data into a separate spreadsheet from the working dataset. I don't think I can reference one from the other without opening both, which leads to a hanging machine.Assuming you are using a recent version of Excel (2013 +) I'd consider powerpivot to combine data sources and then use a pivot chart to display the data
There MUST be better ways to run this stuff but I just don't have the time to invest in sorting it out - it's really a peripheral part of my job that ought to be done by someone else. I'm totally self-taught and assume I have multiple bad habits
There MUST be better ways to run this stuff but I just don't have the time to invest in sorting it out - it's really a peripheral part of my job that ought to be done by someone else. I'm totally self-taught and assume I have multiple bad habits
to be fair to Excel, it's actually pretty good at doing this. Its just a notch up from IF(A1="Porn", "Fluff", "Dont fluff")
And to be fair to me, I made a very* lucrative career out of being "totally self taught" and the one that everyone needs when they dont know who "ought" to be doing this stuff.
*no really, VERY.
you mean the dancing ? I'm hurt. Besides, with a bigger waistband there's more room for stuffing 20 quid notesYour future looks horrible though
(if you mean the excel, no chance of promotion for that but at least my future will be brief; planning to be out [i]relatively[/i] soon)
Id offer to help and invite you to send me a copy to do it for you, but what with the all the success, money and the fluffers etc, I dont even use Excel anymore and I cant do it in Google sheets.
It's a bit like George Best giving up the game, and only kicking a foam ball against the wall. Really well.
Don't use excel. Rewrite the logic in Python. You will thank me afterwards.
Id offer to help and invite you to send me a copy to do it for you, but what with the all the success, money and the fluffers etc, I dont even use Excel anymore and I cant do it in Google sheets.
Nah, part of the fun is doing it (largely) myself and, anyway, I'm playing had to get - they've had enough free overtime out of me just because I'm learning something slightly interesting
oldnpastit - one of my colleagues is trying to get me to use sql. There's minimal chance of that and zero of any pythonification, I suspect