Allocate information from Master Worksheet to other worksheets

**MacVic** · 03-20-2014, 07:24 PM

I am using Excel 2013.
What I am hoping to do is to be able to allocated information on a master workbook to other workbooks in the appropriate sheets. For example, I have a list of all the suppliers of advertising in a master workbook, with each separate contract including the supplier, the cost, renewal date, the type of advertising, and the branch location where the advertising is done. I would like to be able to have additional workbooks (with sheets for each of the variables eg for Type of Ad: Bus Ads, Billboards, Yellow Pages, Radio) which would gather the information from the Master Workbook and put it in the correct sheet on this new workbook titled “Type of Ads”. By doing this I could quickly look up a summary of all the Bus ads – and the Bus Ad sheet would list the suppliers, the branch, costs etc. Sort of the opposite of consolidation. Is there any way to do this in Excel? If so how would I go about it?
I really appreciate any help with this. THANKS!

**alansidman** · 03-20-2014, 07:32 PM

Simple answer is yes. If you would like assistance with this project, I suggest you upload a sample workbook(s).

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**MacVic** · 03-20-2014, 08:27 PM

Hope I have done this right! I really appreciate your help..

**alansidman** · 03-20-2014, 08:48 PM

I think that it would just be easier to build a Pivot Table to summarize the data by branch, vendor, type advertising, etc. As the Master table is updated, you can just refresh the PT.

**MacVic** · 03-21-2014, 12:07 PM

Thanks for your help. I am not really sure how to do Pivot Tables....but I will teach myself. Just to understand, I will be making a pivot table for each of the variables, but how does the information get from the master list to the Pivot table automatically, so I can update automatically when making a change to the Master List? Will the pivot tables be linked by formula to the master list? Perhaps you can direct me to a site where i can learn pivot tables, and maybe after learning about them this will all make sense. Thanks again for all your help, being my first post I was so impressed by the speed of answers!

**alansidman** · 03-21-2014, 03:24 PM

If you click on the link in my signature block "Excel Video Tutorials," and scroll down on the site to Pivot Tables. There are several tutorials that will help you to get started. Keep in mind that the UI Mike uses in these videos is Excel 2003, but the concept is exactly the same. In 2013, I believe the starting point will be on the Ribbon to Insert and select Pivot Table.