+ Reply to Thread
Results 1 to 4 of 4

Repeating rows of data and charts multiple times (100+)

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    66

    Repeating rows of data and charts multiple times (100+)

    Hi,

    I am trying to create a dashboard to display the same set of data across multiple entities. In the sheet there is a pivot table which I have deleted, to which cells to the right are directly linked, then the charts link to the data cells. I had to sepererate the charts from the pivot to enable multiple charts from one pivot.

    As the pivot table extends the data can easily be copied down and I would like to copy the charts down too, to link with the new data for each entity. Currently the chart just links to the old rows of data. Copying this to a new sheet and moving it back 100+ times is not an option as its incredibly time consuming, but also if I wanted to make changes to any of the charts or data structure I would have to repeat the process all over again.

    Is there anyway to copy all the rows and charts together and repeat below. The idea after is that it would be PDF and each section would equal 1 a4 page. Have illustrated with lines the height of each section.

    Any help much appreciated as it seems crazy that this isnt possible!

    Thanks,

    Alex
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,669

    Re: Repeating rows of data and charts multiple times (100+)

    Hi Adam

    assuming the data ranges are are predictable this is probably possible with a macro, but I'm struggling to see exactly how you want the data to change over time and where new data would be added. To get you started with some ideas maybe copy the first chart to a new sheet and in that sheet run this macro - it should copy the first chart 7 times and replace the source range in the copied charts with each of your "measure" data tables. It relies on the rows and columns being the same distance apart

    Please Login or Register  to view this content.
    Last edited by NickyC; 01-14-2020 at 06:58 AM. Reason: correct typo

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    66

    Re: Repeating rows of data and charts multiple times (100+)

    Hi there, and thanks for your help! So basically I am trying to repeat all the data and charts in blocks. I have updated the file to show a second "dashboard" section. Each section will have numbers relating to one trading entity, and once copied down 100+ times each new row of charts will need to relate to the new data sets to their left.

    The file now has two sections to show better what I am trying to achieve.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,669

    Re: Repeating rows of data and charts multiple times (100+)

    Hi

    this is more complex than i'd though, but this seems to work. It's very unstable and relies on there being exactly 27 rows between the start of each sets of data.

    To begin, have a worksheet in which there are only 8 charts - the ones in your first dashboard - but with successive tables in columns B to M as outlined in your sheet. In order for the charts to display properly the combined height of these sub tables must be no less that the height of your first set of dashboard tables. You may want to use this macro to replicate the row heights in the first table group rows in the rows below

    Please Login or Register  to view this content.
    The next step is to copy your existing 8 charts and paste them at regular intervals adjacent to the table sets below:

    Please Login or Register  to view this content.
    The final step, and the one you're most likely to experience problems with, is to replace the series formulas in each chart below the first 8 with formulas sourcing the data from ranges 27, 54, 81 etc rows below the source ranges in the copied charts.

    Please Login or Register  to view this content.
    good luck!
    Last edited by NickyC; 01-15-2020 at 06:28 AM. Reason: correct macro

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1