Multiple sheets consolidate in a hierarchical single sheet

We have sales team members having their own sheets to capture their client info and industry-specific potential billings. Please see attachments exec1 and exec2

Is there a way for the Sales Manager to view all of this in a single sheet that is grouped by industries? The fixed objects are Industries. The variables are the client list (ABC, DEF etc.,).

Find more posts tagged with

Sheets

Comments

Meenakshi Sundaram S

Apologies, missed attaching the screenshot of how the manager sheet should look like.

Andrée Starå

Hi Meenakshi,

Yes, you can. With a well thought thru setup, you can do this with reports.

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant / Get Done

Meenakshi Sundaram S

Hi Andrée,

But I thought parent-child (hierarchical) setup is not possible with reports, right? Please, can you let me know if I'm missing something here?

Andrée Starå

You are right that it's not possible to have it set up in the same way with collapse and expand, but you can get the information in the report separated in the same way but without the ± sign.

Would that work or do you need/want the collapse/expand possibility?

Best,

Andrée

Meenakshi Sundaram S

that would be great. The reason I want the collapse function is to sum all the parent row numbers into an ultimate sales number. Is that possible too?

tere.pile42541

I would be interested in this. I have some ideas, but would be nice to validate before I start making a bunch of changes

Andrée Starå

In short.

You can create a new sheet (master sheet) with the same relevant columns and either sum the relevant information on this sheet or on all the other sheets and then cell link to the master sheet. You then need to add a column for the sorting of the information. Both on the new sheet and all other sheets that you want in the report. This is so that the information in the report will have the same structure as children and parents.

Would that work for you?

Best,

Andrée

J. Craig Williams

Here's how I might handle it (to expand on what Andrée stated)

1. On each EXEC sheet, add a column to capture the sort (and provide means to pull out the child rows only), maybe something like this

=IF(COUNT(CHILDREN(Industry@row)) = 0, LEFT(PARENT(Industry@row), 2) + 1, "")

that would return "Me1" for all Media children, "Te1" for all Technology children, and so forth. (I'm assuming that there may be multiple industries that start with a particular letter, so I defaulted to 2 letters)

2. Create a KPI sheet to sum all of the industries. In the column used to capture the sort, use "Me0", "Te0", etc (hard-coded or by formula -- I think hard-coded would work)

3. Create a Report to bring in any row from sheets with the sorting column non-blank. Sort the Report on that (hidden) column and then by Industry.

I hope this helps.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…