Master/Details sheets

Ray Hope

I have come across a few instances where we have a project sheet, which includes detail items that need different columns of information than the rest of the rows in the sheet. For example, there might be a section of checklist items where we collect information. I have come up with a couple options, and am wondering if there are better options.

FIRST OPTION

Keep all the details rows in the same sheet, and create more columns for the data that is needed.

Pros: All information is in one sheet. When we create a new project, we can have a template with all the necessary rows and columns.

Cons: This makes a very wide sheet. As well, there are sometimes more than one set of detailed data rows that need their own data, so the sheet would get even wider. It might be possible to label the columns more generically, but this is not always the case.

SECOND OPTION

Create a separate sheet for any set of detailed data, and create a link on the main sheet to that detailed sheet.

Pros: The main sheet is clean. The detail sheet is specific to the specific data.

Cons: The data for the project is spread across multiple sheets. When we start a project, we need to create multiple sheets and link them.

Any suggestions or experience would be much appreciated.

Genevieve P.

Hi Ray,

My personal preference is your first option, keeping everything in one sheet, especially if you will be duplicating the project multiple times.

Then if you want to create cleaner views, you can create a Report from the sheet that only shows specific columns. It won't have quite the same look since Reports don't show hierarchies, but it would allow you to filter out rows/columns to make sure it's just displaying relevant information. Since Reports are based on the source sheet, if you make edits to the Report then it will update the underlying sheet.

Ray Hope

Hi Genevieve,

I gave both ways a try. You're right that you don't get the hierarchy in the report. Unfortunately, this would be a deal-breaker. I thought maybe I could have multiple views against the same sheet. This appears to be possible in the Premier version, but we don't have that. I haven't seen the actual functionality, but from what I read, it looks like it would do the job.

Using the second approach, I found a couple tricks that will makes it work better than I thought. I can create the sheets I need with links between each other, and put them in a folder as a template. When I create a new project, I can save the folder to a new location, and it will copy all the sheets in that folder and maintain the links between those new sheets - very nice! This also allowed me to create a simple roll-up report for all items in all detail sheets for all project.

Thank for you feedback - much appreciated!

Ray

Genevieve P.

Hi Ray,

Ah, understood! Yes, the Dynamic View Premium Application does have the capability of creating different views of the same sheet, however even that does not display hierarchies as the sheet would.

It sounds like your second approach, with duplicating folders, will work well for you - I'm glad to hear it. 🙂