Adding new data series for line graph while grouping worksheets

[refresh]

Hi There,

I am trying to find an easier way to add a new series to my charts, while it is easier to do that for one worksheet I have around 150 worksheets in one excel file with the same data and a graph on each worksheet but for different business units so every time I have to change anything it becomes few hours task as I have to go in individual worksheet and make that change even if I use Macros. I tried by grouping all the worksheet together and inserting a new line in between the current data, excel will automatically update the data selection but apparently not.

Is there any way I can group all and add new data for my line chart .

Thank you in advance..

[Ken Puls]

Hi there,I think we're going to need a bit more info in order to be able to help. Can you upload a workbook that has 4-5 worksheets in it showing how your data is laid out?What I'm trying to figure out is whether your source data is in the same location on every worksheet, or if it's in different locations. Are the new values always identical, or do they vary between worksheets, and any other characteristics that are the same/different between worksheets. Are there other sheets that shouldn't be updated, etc...We can write a macro to loop through every worksheet in the workbook inserting the new rows of data into your table, but we want to be careful that it's going into the right place on the right sheets only, and in the right place so it doesn't cause any damage to other data.

[refresh]

Thank you Ken for the reply..

As requested please find the file with 4 worksheets for different business units.. All worksheets are identical in terms of format but data may vary as this is a labour usage in each unit. Recently I added row 6 ( DEC 2014/15) line and I did it using a macro but still had to go in each worksheet to update. I have only recently used macro first time so my skills in that area probably not that great. The data in my original file is linked to TM1 and at times I have negative numbers in data.

I am open to your suggestions on how to better set up the file in future if you see any particular issues with the way I have set up the file.

Again thank you for you time and help.

Regards

[Ken Puls]

Hi,

So, just to make sure I'm clear... you have a macro that inserts the new data, the issue is that it's not being picked up in the charts, is that correct?

My personal preference would be to format your charts source table as an Excel Table. Then when a new data series is added to table it will be automatically pulled in to the chart.

[refresh]

Hi Ken,

I have recorded a Macro which updates the data and also the chart but as I have 100 worksheet in the file I have to individually go in each worksheet and update it using macro. I am after a quick way so I can group all the worksheet and update them together

I will keep in mind to use excel table in future to avoid all these hassles.

Thank you.