Multiple sheets consolidate in a hierarchical single sheet

04/16/18 Edited 12/09/19

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

exec1.PNG

Exec2.PNG

Tags:

Comments

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

    Manager.PNG

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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å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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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?

  • 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å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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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([email protected])) = 0, LEFT(PARENT([email protected]), 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 or Register to comment.