Automatically copy cell based on 2 criteria in another sheet
I am trying to take 1 cell (Term) from my "Active" sheet and I'd like it to autopopulate my Eligibility sheet with a course name, based on the Active Sheet and the Term and name selected.
So Term has F/S and the course names can be 1 of 8 different courses. I'd like it to first recognize the Term (either F(all) or S(pring)) and then match the student name and the dropdown course selected in the Active Sheet and return the Course to the respective Fa acceptance or Sp acceptance column in my eligibility document.
Is there any way to do this? Please and thank you!
Lori
Best Answers
-
Ok. That's expected then. That's the equivalent to #NO MATCH when using an INDEX/MATCH. I usually use an IFERROR to take care of that, but wanted to make sure the base function was working properly first. The IFERROR can make it tough to troubleshoot otherwise.
=IFERROR(INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1), "")
-
Ok. Try this instead then:
=IF(CONTAINS("Engineering", Field@row), 14750, 10400)
-
Happy to help. 👍️
Answers
-
Hi @maineL
I hope you're well and safe!
You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
-
All well here. Hope with you too! I tried the Index/Match, but couldn't figure out how to have more than 1 criteria in the formula.
-
So attempting with the Index/Match formula I still stumble on having more than 1 criteria to meet. Ultimately, I would first like to match the student name, then the term and finally the course to populate which column (fall or spring) populates with the course to be taught. I'm scratching here...
-
Can you share your column structure on both sheets and what your desired outcome would be in the cell with the formula. You can use fake data to screenshot.
-
Sure thing.
Eligibility Sheet - this one I'd like to have the Fa Acceptance / Sp Acceptance columns filled in with the course name as specified on the Active Sheet:
The Active Sheet: The term column, F, below would stipulate which column above would populate with the course name (Fa Acceptance; Sp Acceptance)
Thank you for your help!!!
-
Try an INDEX/COLLECT instead of an INDEX/MATCH.
=INDEX(COLLECT({Column To Pull From}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd Critieria), 1)
-
Hi Paul,
I tried this:
=INDEX(COLLECT({Active TAships Course}, {Name@Row}, Name, {Term}, Term="F"), 1)
But it comes back unparseable...
-
Make sure your {Cross Sheet References} are set up properly referring to the appropriate columns in the reference sheet.
=INDEX(COLLECT({Active TAships Course}, {Name Column}, @cell = Name@row, {Term}, @cell="F"), 1)
-
Ok, so I thought I had the cross sheet names all set, but I'm still unsure:
=INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, Name@cell=Name@row),{Active TAships Term},Term@cell="F"), 1)
Thank you!
L
-
Your syntax is still off. Leave the "@cell" exactly as is from my example, and remove the closing parenthesis from after Name@row.
-
Thank you for your patience. I am getting closer. I now have an Invalid Value.
=INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1)
-
I'm also hoping if there is no data in the respective column based on the criteria, it will return a blank.
-
Are you getting that error for all rows, or are you only getting that error where there is not a match?
-
Yep. Filling in correct information if it's there, but returning Invalid Value if it's not there:
-
Ok. That's expected then. That's the equivalent to #NO MATCH when using an INDEX/MATCH. I usually use an IFERROR to take care of that, but wanted to make sure the base function was working properly first. The IFERROR can make it tough to troubleshoot otherwise.
=IFERROR(INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!