Mirroring a Grandchild row cell in the Child row cell based off current week
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?