How can i create a master sheet from separate sheets?

Will Poole

i want to create a masters sheet for a general over view of projects in the pipe line

Sheet 1. Sales this will have customer info, size of job and a fee key points of the project

sheet 2. project management, who is contracted what we need to traack

sheet 3 owner billing

sheet 4 sub billing

master sheet would take job (rows) info from each sheet to create a master sheet of perinate information but not all columns as to not cloud the viewer with data.

i would like the master sheet to be a live linking sheet where if sheet 1 adds a job its added to the master or all other sheet

Paul Newcome

It would be MUCH easier if you started from the master sheet and then built reports from that for the limited views.

As is... Do all of the sheets have the job number/name on them? Are all of the job numbers/names unique for each row?

Will Poole

https://community.smartsheet.com/discussion/comment/273042#Comment_273042

I was thinking that would be the way I would have to do it but then on the report can each "department" change the data and the master get updated?

Yes each sheet the job name would be the same

Paul Newcome

Reports can be edited which in turn would update the source sheet(s).

Using your current setup, you could use a Copy Row automation when a new job is added and then use a series of INDEX/MATCH formulas to pull the rest of the information based on the job and hide the columns you do not need. The information pulled via formulas would only work one way though.

Will Poole

https://community.smartsheet.com/discussion/comment/273050#Comment_273050

I like the updating reports will update source sheet i did not know that.

as far as the report idea goes can i add column that wouldn't be on another sheet?

Paul Newcome

If you are starting from a master sheet and then building reports, then all of your columns should be available on the report because it is already in the master sheet. The report will allow you to select which columns are visible so that unneeded data is not shown.

Will Poole

understand that part but on the master i would want it to be brief over view

sheet4 for the project managers would be job name, scope to award, award status, contractor, contract amount, multiple times over for the 4 scopes we work on

master sheet would only have job name, contractor, contract amount x4

Paul Newcome

The master sheet should have everything. You would then break it down using multiple reports.