Hi,
i think I already know the answer to this but just want to check I'm not missing something obvious. I have a set of data that has had a chart somewhat "manually created" from. I need to recreate the graph but with the ability to cut the same data in multiple ways, and a graph displayed for each (so sectors/ sites/ customers). Ideally in my mind this wold involve the same pivot table produced multiple times then a couple of slicers linked to all the tables so you look at the same date ranges etc across all the graphs.
The graph is a line graph and each year the colour has been set (I'm guessing manually) to make it easier on the eye. In Excel terms, we would define these as series, but to do that in a pivot table I've discovered that where one year ends and the new starts it cannot produce a connecting line between the two series. I have tried all combinations I could think possible in the "hidden cells" window. Am I right in thinking there is no setting that will magically solve this?
If not, is there anything straight forward I might be able to do to force the joining line? I did read a suggestion elsewhere about 1 minus the other but I can't see how that will work in this context.
My current method is that I have just created a table instead and then replicated the table multiple times, pointed the graphs to each and built the filtering option with VBA. But it takes nearly 3 minutes to run which I'm not happy with. This is the core of the code. You will see I tried in the final stage to create the range it picks up the dates first with a view to holding them in the code to use rather than keep going back to the cells each time it needed to do the filtering. I'm not sure if it actually does that - it shaved off another 10 seconds that was all.
At any rate, the question is, if I can't fix the pivot chart issue and continue down the current path, is there anything else I can do to make this code slicker (pref 60 seconds or less). For extra info, it's looping through 35 sheets each with one table on at the moment.
Thanks for advice as always.
i think I already know the answer to this but just want to check I'm not missing something obvious. I have a set of data that has had a chart somewhat "manually created" from. I need to recreate the graph but with the ability to cut the same data in multiple ways, and a graph displayed for each (so sectors/ sites/ customers). Ideally in my mind this wold involve the same pivot table produced multiple times then a couple of slicers linked to all the tables so you look at the same date ranges etc across all the graphs.
The graph is a line graph and each year the colour has been set (I'm guessing manually) to make it easier on the eye. In Excel terms, we would define these as series, but to do that in a pivot table I've discovered that where one year ends and the new starts it cannot produce a connecting line between the two series. I have tried all combinations I could think possible in the "hidden cells" window. Am I right in thinking there is no setting that will magically solve this?
If not, is there anything straight forward I might be able to do to force the joining line? I did read a suggestion elsewhere about 1 minus the other but I can't see how that will work in this context.
My current method is that I have just created a table instead and then replicated the table multiple times, pointed the graphs to each and built the filtering option with VBA. But it takes nearly 3 minutes to run which I'm not happy with. This is the core of the code. You will see I tried in the final stage to create the range it picks up the dates first with a view to holding them in the code to use rather than keep going back to the cells each time it needed to do the filtering. I'm not sure if it actually does that - it shaved off another 10 seconds that was all.
At any rate, the question is, if I can't fix the pivot chart issue and continue down the current path, is there anything else I can do to make this code slicker (pref 60 seconds or less). For extra info, it's looping through 35 sheets each with one table on at the moment.
Thanks for advice as always.
