Hi all,
I have a set of data that is extracted monthly in the exact same format (using Nielsen Connect Express, in case anyone has specific experience). The report pulls 4 metrics charted over time across 7 different markets, for a total of 28 individual charts. These will be updated monthly so I want to keep everything linked so I can simply save the new file with the old name and refresh links.
These charts are spread across tabs sequentially named (155_1, 155_2 etc) with the data being in the same spot for each graph type. My plan is to position 4 graphs on one slide per region. So far I've set up my template linking the first national graphs to the slide and saved the chart format for each of the 4 types. Now all I'd really need to do is copy/paste each graph, resize it and load the template. I'd do this 24 more times and then everything would be set up.
But I don't want to do it manually, and I also want to preserve the setup in case I transition this to someone else and all of the links break. Is there an easy way to do this? Few ideas I had below:
1) VBA to move and position everything. Prefer to avoid this if possible but not opposed to it. I don't want the setup to be a ton more than the copy/paste manually.
2) Duplicate the slides 7 times and adjust the linking on each chart.
This is my preferred method, but it seems that a link is simply linking to the excel file and not the actual tab/cell itself. Is there a menu somewhere that I'm not aware of that has the direct link to the chart? If so, all I'd need to do is adjust the tab name for it to work.
If there are any other solutions, I'd really appreciate hearing them! I'll likely copy/paste this post to other discussion boards too, so will share any responses I get that are helpful.
I have a set of data that is extracted monthly in the exact same format (using Nielsen Connect Express, in case anyone has specific experience). The report pulls 4 metrics charted over time across 7 different markets, for a total of 28 individual charts. These will be updated monthly so I want to keep everything linked so I can simply save the new file with the old name and refresh links.
These charts are spread across tabs sequentially named (155_1, 155_2 etc) with the data being in the same spot for each graph type. My plan is to position 4 graphs on one slide per region. So far I've set up my template linking the first national graphs to the slide and saved the chart format for each of the 4 types. Now all I'd really need to do is copy/paste each graph, resize it and load the template. I'd do this 24 more times and then everything would be set up.
But I don't want to do it manually, and I also want to preserve the setup in case I transition this to someone else and all of the links break. Is there an easy way to do this? Few ideas I had below:
1) VBA to move and position everything. Prefer to avoid this if possible but not opposed to it. I don't want the setup to be a ton more than the copy/paste manually.
2) Duplicate the slides 7 times and adjust the linking on each chart.
This is my preferred method, but it seems that a link is simply linking to the excel file and not the actual tab/cell itself. Is there a menu somewhere that I'm not aware of that has the direct link to the chart? If so, all I'd need to do is adjust the tab name for it to work.
If there are any other solutions, I'd really appreciate hearing them! I'll likely copy/paste this post to other discussion boards too, so will share any responses I get that are helpful.