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

Ayelet Weiner

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?

Paul 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.

ToddF

Have you tried =SUM(CHILDREN())?

Ayelet Weiner

@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 Newcome

Try something like this...

=INDEX(CHILDREN(), MATCH(1, CHILDREN(Weeknumber@row), 0))

Ayelet Weiner

@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 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.