Multiple sheets consolidate in a hierarchical single sheet

Meenakshi Sundaram S

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.,).

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