Update one sheet based on two columns from another sheet
Hello
I have a request sheet and a review sheet. The request sheet generates a task number after submission of a new row. That task number will be manually inputted into another sheet. Once a review is done of the request, the user hits review complete. I want that checkbox (once its clicked on the review sheet) to get checked off in the request sheet row based on the task number. Is that doable?
Best Answer
-
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 inReview Sheet 2
. If still no match is found, it returns"Not in sheets"
.Hope this helps.
Peggy
Answers
-
I hope you're doing well!
To set this up, follow these steps:
1. Add a column to both sheets and name it "Review Complete". Make it a checkbox-type column.
2. In the Request sheet, add the following formula in the Review Complete column:
=IFERROR(INDEX({Review Sheet Review Completed}, MATCH([Task Number]@row, {Review Sheet - Task Number}, 0)), "Not in sheet")This will pull the "Review Completed" status from the Review Sheet based on the matching Task Number.
Let me know if you have any questions.
Thanks, Peggy
-
Hi Peggy,
Thank you for your help, but I am running into an issue. I put in the same exact formula with basically the exact sheet you made just to test it out and I keep getting the 'not in sheet' line instead of a check box
Am I missing another reference where {Review Sheet - Task Number} is?
EDIT** I figured out it out, I had to remove the "{Review Sheet - Task Number}, 0))" part of the formula and it worked, I think it was the way you nicknamed the range.
-
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
-
Thanks @Peggy Parchert ,
I did have one final question, can I add on to this formula to reference another sheet with the same "Review Complete" so 2 review sheets and 1 request 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
-
Thanks Peggy,
I'm getting a little bit confused at the beginning of the formula with the (IF(Task@row) part since I don't have a column named "Task", can I replace that column with anything else? The only information that will always stay the same between sheets is the task number
-
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 inReview Sheet 2
. If still no match is found, it returns"Not in sheets"
.Hope this helps.
Peggy
-
This worked! Thank you so much for all of your help, I really appreciate it!!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!