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

Ayelet Weiner
Ayelet Weiner ✭✭✭✭✭
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?


Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!