How do i make a master sheet linked to other sub sheets

**cj22reyn** · 05-18-2009, 11:15 AM

hello,

This is my first post, and I am having trouble with a project i am doing at work. We have a spreadsheet with different companies, but with all the same categories. Ideally i am trying to create a master sheet to monitor all the different companies more easily. I am attaching a sample of the the file, and whenever i add a new job to a sub sheet i want it to appear on the master sheet automatically. On the file i have attached, i just copied and pasted the jobs from the sub sheets. This would be too long and tedious if I had to do it everyday manually, therefore i would like to know the formula for this. Thank you very much for your help.

**JBeaucaire** · 05-18-2009, 12:47 PM

Realistically, how many sheets are you talking about? How many companies, how many possible entries?

The idea of a "Master" is backwards to what you're doing, in my opinion. In a "Master", you enter all your data in the one Master sheet. You insert rows and add data on the ONE sheet. Then the individual Company sheets would automatically pull their own data from the master.

What you're doing is a "Summary"...though not really, a summary usually whittles the company info down.

So, first, since it appears what you really want to do is create an Excel database, do THAT. Put all the data IN "master" sheet. Then ON the master sheet, you can have a filter key that IDs which group the jobs go with, then those sheets can take it from there.

On this sample sheet, I've mocked that up with YOUR data. I added the KEY column to give each JOB a unique incrementing identifier the other sheets can match to.

Then, the other sheets have a "slightly" different layout, but it's consistent. All you have to do is copy one of those sheet (duplicate it) and change the sheet's tab name and it will immediately start pulling in Job's for that company based on the Sheet Name.

Each sheet is preset to show 100 jobs of their own with this layout.

On the Master, you can hide the key column, if desired.