I am using Smartsheet control Center and have an Intake Sheet and a Summary sheet. I also have a Profile Data Sheet in my project template.
The Intake & Summary sheets have a project # to join data. I'm wondering what is the best way to join data to display information on a report?
1 - Intake sheet populates a Project Profile Data Sheet, then the profile data sheet populates the Summary Sheet
2- Use an INDEX(MATCH() or VLOOKUP()
#1… general rule of thumb… try to never ever use vlookup(). In the hundreds of control center (and general Smartsheet) systems, I've only found 1 reason /use case to use vlookup(). I just don't go there. So index(match())
The larger part of your question: It depends. Where is this report? Where is the data on the report coming from? From the projects (dynamically)? From the summary sheet? etc.
If dynamically from the project consider a hidden helper column that puts the project # on every row via index(match() from the profile data sheet. Then it'll be available on the dynamic report for filter or grouping etc.
If it's on the summary sheet then your summary data process would populate it from SCC and on the summary sheet for using there.
I may not full understand your scope, but happy to assist. If we need to setup a quick sync to walk through it I could offer that for you.
Matt Lynn
Hi,
I hope you're well and safe!
At a glance, collecting the data in a Summary sheet would work to report on. Feel free to share screenshots; I'll try to make a better recommendation.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Hi both,
Thanks for answering my question.
There is data on both sheets that is required on a single report. My question is if there is a best practice between using an index match on the summary sheet using the intake sheet as the source vs connecting the intake sheet to a project profile data sheet, then connect to the summary sheet. The result is the same in either, but am unsure if one will bog the system down more than the other.
Hi,
Good question @Daniel Kim! Our recommended approach is your option 1. That way you have a standard way of populating the data in the Summary sheet, and the performance is good, even at large scale.
Having said that, if you've already provisioned the projects, and this intake field is new, and not in your profile sheets, then it's still valid to use option 2 (but with the Index-Match, not the VLOOKUP).
Hope that helps Daniel.
Cheers,
Lindsay
Smartsheet Lead @ InfoSpark
Asia Pacific Smartsheet Partner of the Year (2023)
Platinum Smartsheet Partner | www.infospark.com.au
Happy to help!
Collecting it using profile data together in the Summary Sheet is best practice.
✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
