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

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?

Find more posts tagged with

Sheets

Formulas

Reports

functionality

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.