Results 1 to 5 of 5

Thread: Adding new data series for line graph while grouping worksheets

  1. #1

    Adding new data series for line graph while grouping worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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..
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,414
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,414
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •