Mirroring a Grandchild row cell in the Child row cell based off current week

Mirroring a Grandchild row cell in the Child row cell based off current week

A. MarcusA. Marcus
edited 05/21/20 in Formulas and Functions

Hey all,

I'm relatively new to Smartsheets and am hoping someone can help me out. I promise I've looked everywhere for an answer and I think I'm making this more complicated than it is. Thanks in advance for the guidance!


I have a sheet where I'm tracking historical quote metrics by Sales rep and Week. At the suggestion of the Pro Desk, we setup the sheet with hierarchies.


Primary Column has: Sales Leader (Parent)>Sales Rep (Child)>Week (Grandchild). The other Columns house the metrics I'm tracking/reporting on.


I was hoping I could easily create a row report that shows Sales Leader / Sales Rep and the current Week quote metrics. Unfortunately, due to the way hierarchies work with reporting, I didn't like the way the report looked. I wanted the Columns that had the current Week metrics to show up next to the Sales rep(Child) name as opposed to the Week(Grandchild) row showing underneath the Sales rep row.


I have no need to sum the annual metrics on the Child row, I know I can create a Summary Sheet formula for that. So I was wondering, is it possible to have the Child row mirror a specified Grandchild row based on current Week?


I currently have a checkbox formula that checks the box for the current Week. I tried using an IF statement to mirror the current Week row cell on the Child row, but I keep getting a circular reference.


=IF([Weeknumber]:[Weeknumber]=1, =[# Quote]@row)

If this is possible, I'm hoping that there is a way to make the formula dynamic so that the Child row will always show the current Week metrics.


TL:DR - is it possible to have the Child row mirror a specified Grandchild row based on current Week?


Answers

  • Have you tried =SUM(CHILDREN())?

  • @ToddF - I just tried it with the SUMIF formula and it didn't work. I don't know how to just get the SUM of the specific row/cell that has the Weeknumber box is checked. Would a VLOOKUP be better for this?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...

    =INDEX(CHILDREN(), MATCH(1, CHILDREN([email protected]), 0))

  • @Paul Newcome Thanks for the response! For some reason, I'm getting a #NO MATCH. However, I decided to just pull in the current week numbers into a different sheet and it works just fine.

    I really appreciate your assistance though!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    That's odd that it didn't work. I use something very similar.


    Either way... At least you have something working. That's what counts.

Sign In or Register to comment.