Creating a Master Sheet Composed of Dependent Dept Sheets // Sheet Linking // Sub-Sheets

Emeka

Hi Community -

 

Looking to crowdsource the best method of creating a Master Sheet (think: Company ALL) composed of smaller Department Sheets (think: Marketing, Development, etc).

 

The ideal set up here is that while only I may have access to the the "Company All" the dept heads have access to their respective Department Sheets and as they make updates to their respective sheet (Perhaps adding a new row/task or changing dates) it autosyncs into the "Master Sheet".

 

The overall function is setting up a Master Sheet thats really just composed of 4 to 5 different "Sub-sheets"... does this make sense? Is there a better way to go about this or think of this type of set up?

 

My understanding of simple "Cell-Linking" doesn't seem appropriate because while it may reflect the change of cell information it wont reflect new rows/data being added; it seems what I'm looking for is more like "Sheet Linking". Additionally, I looked into Zapier links, but that only seems to reflect additions made to the very end of the existing smartsheet/google sheet... not if a row is added to a specific sub-objective in the middle of the "sub-sheet".

 

Thanks for the guidance and brainstorming here Community!

J. Craig Williams

Are you trying to link EVERYTHING from the sub-sheets into the master sheet or only sums and counts and what-not?

 

If the later, then if your sub-sheets performing your consolidation properly on the department level, then there won't be as many rows to cell-link.

As rows are added, the sections will already get those values into their totals and it will be shown on the master sheet.

 

If Reports, you'll need a limited number of people that can see all of the underlying sub-sheets so they can see all of the data. Reports can miss the mark because they don't bring over hierarchies.

 

Did that help?

 

Craig

Emeka

Hi Craig; thanks for the response here.

 

I completely understand your point conceptually except it's not exactly the right solution for this scenario because these project master sheets and subsheets are all task/gant chart oriented instead, not formula/value driven.

 

Does that make sense? Any other thoughts here?

Richard Rymill SBP

Emeka

To try to add value to what my colleague Craig is saying, you can have what you want but you need to be selective in only Cell linking the important KPI (Key performance Information, which can be dates and statuses, not necessarily numbers) to the Master Sheet.

To do this, create a Sheet Dashboard of those KPI's and maybe the high level Gantt Rows, then link them to your Master sheet. 

From the Master sheet point of view looking down on your projects..... think of this as Library View with all your departments Listed in a sensible Hierarchy with their Projects as their children, (open to view and interrogate, close to ignore). 

The links to each project can be double clicked to open ANY sheet which you need to interrogate because you dont like what you see. 

Include Smartsheetlabs Graphs to add color and easy viewing to each sheet and the Master sheet. 

The design of your Projects sheets needs to consider the full process to ensure the whole worklflow achieves what you need at each level or department. 

This process with take time to do it really well, only a few days of concentrated effort but the benefits are huge in a large organisation. 

Hopefully our comments have helped but if you get stuck, that is what my company do and Craig is one of our top specialists at exactly that work. Let me know if you need help we are Samrtsheet Approved Consultants. We normally find that a small investment now ,will pay huge dividends over time and it saves you learning the hard way!

Hope that helps

RichardR  

 

J. Craig Williams

What he said.

 

Also, some more thoughts about "sheet linking'.

 

I'm very concerned about security and privacy.

Having the ability to cells is nice but needs to be controlled. Expanding that to sheets is opening up a potentially large hole in the security of the system, imo.

 

Smartsheet is cloud-based but a browser app. That is why there are limits on thinks like cell linking (5000 incoming). That's pretty small compared to the sheet limits.

What would happen if the sheet went from 50 x 100 to 51 x 100?

Would everything stop linking or only some of the new things?

 

That said, and back to your last question

Yes it makes sense.

I worked with a client that had 10 projects, semi-related by a few key stakeholders, but we ended up splitting them into their own sheets for a variety of reasons.

We cell-linked all milestlones to a single overview sheet and that worked fairly well to keep the dates aligned and the major stakeholders informed.

 

There still may be some use in Reports, especially if the data is one-way - create a daily export to PDF from a user that can seen all department sheets.

 

Hope this helps.

Craig

 

amy16451

Emeka - you're description is exactly what I'm trying to accomplish.  Were you able to successfully make it happen?

Taru

This post has been very helpful as this is what our program is trying to achieve. Can someone please share their screen shots and examples.

 

Many thanks

Taru

Chris A

Bump.

Does that work here?

Richard Rymill SBP

Chris A

Yes it does, you just need to design the workflows carefully for the benefit of your different users. 

Granted you will need to put a little time into the design but the benefits of getting it right are enormous. 

We have helped dozens of companies to create similar but it could take several days to get the final design just right, so you have a choice? 

Learn as you go, which will take time, or get some professional help? If you have budget for that?

RichardR

https://www.smarterbusinessprocesses.com/testimonials/