Best Of
Re: Update one sheet based on two columns from another sheet
I'm glad you figured it out! It's good practice to name a range instead of using the default one. I'd recommend updating Review Sheet Range 1 and Review Sheet Range 2 with a meaningful name.
Have a great week!
Peggy
Peggy Parchert
Re: Update one sheet based on two columns from another sheet
Yes, but you'll need to switch to INDEX/COLLECT for this to work. To demonstrate, I created Review Sheet 2, which is identical to Review Sheet 1.
In the Request Sheet - Review Complete column, I added the following column formula:
=IFERROR(IFERROR(IF(Task@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task}, Task@row, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task}, Task@row, {Review Sheet 2 Task Number}, [Task Number]@row), 1)), "not in sheets")
Breakdown of the Ranges:
- {Review Sheet 1 Review Completed} → "Review Completed" column in Review Sheet 1
- {Review Sheet 1 Task} → "Task" column in Review Sheet 1
- {Review Sheet 1 Task Number} → "Task Number" column in Review Sheet 1
- {Review Sheet 2 Review Completed} → "Review Completed" column in Review Sheet 2
- {Review Sheet 2 Task} → "Task" column in Review Sheet 2
- {Review Sheet 2 Task Number} → "Task Number" column in Review Sheet 2
This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".
Let me know if you need any adjustments!
Peggy
Peggy Parchert
Re: Update one sheet based on two columns from another sheet
Apologies for the confusion—I initially assumed you had a column that named the Task, rather than just the Task Number. Below is the corrected formula for the Review Complete column in the Request Sheet:
=IFERROR(IFERROR(IF([Task Number]@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task number}, [Task Number]@row), 1)), "not in sheets")
Breakdown of ranges:
{Review Sheet 1 Review Completed}→"Review Completed"column inReview Sheet 1{Review Sheet 1 Task Number}→"Task Number"column inReview Sheet 1{Review Sheet 2 Review Completed}→"Review Completed"column inReview Sheet 2{Review Sheet 2 Task Number}→"Task Number"column inReview Sheet 2
This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".
Hope this helps.
Peggy
Peggy Parchert
Re: Customize an auto number
Try this:
=[Auto Number]@row + "-" + [Business Unit]@row + "-" + RIGHT("0" + DAY([Submission Date]@row), 2) + RIGHT("0" + MONTH([Submission Date]@row), 2) + RIGHT(YEAR([Submission Date]@row), 2)
Paul Newcome
Re: Can't create dashboard chart with Report information
You can only chart numerical data. You will need to group the report and then use the Summarize function within the report to get your count / avg / etc. that you want charted.
Paul Newcome
Re: Automations: include attachments
It seems like the ability to add attachments to an automation would be a basic ask. It feels like it's been overcomplicated and should be an easy function. My vote is to add this functionality ASAP.
🚀 Let your creativity come alive at full speed!
After a long search I finally found the right model including Smartsheet Logo :)
https://www.linkedin.com/groups/14245563/
NicoLHC
Re: February Question of the Month - Join the conversation and receive a badge
Here are a few I think could be fun!
- “The Easter Egg Hunter” 🥚🔎 – Awarded to users who discover and share hidden or lesser-known Smartsheet features.
- “Smartsheet Mythbuster” 💡🛠️ – For those who clarify misconceptions, debunk myths, or provide expert-level troubleshooting.
- “The Lifesaver” 🆘🚑 – Given to users who provide a solution that drastically helps someone in a time-sensitive or high-stakes situation.
- “Reverse Engineer” 🔄📊 – For those who take complex challenges and break them down into simple, step-by-step Smartsheet solutions.
- “The MacGyver” 🛠️💡 – Awarded to users who come up with ingenious Smartsheet workarounds using unexpected features.
- “Smartsheet Historian” 📜🕰️ – A badge for long-time Community members who can recall and explain how Smartsheet has evolved over the years.
- “The Speed Solver” ⚡✅ – For those who consistently provide quick and accurate answers to Community questions.
Re: Maxif Next Date to Review
You would use a MAX(COLLECT()) Combo. So something like this:
=MAX(COLLECT([Date]:[Date],[Document Name]:[Document Name],@cell=[Document Name]@row))
Leibel S