IF Statement with ??? to move cell data
I have the below as my main sheet (plus additional columns that aren't relevant). Then I have a separate sheet for each Name. On the separate sheet I want to record the "Unique Identifier" if "Approval" = Approved.
Originally I thought of combining an If statement with index match but don't have anything on the "Name" sheet to match. I don't want to copy the entire row because there are many other columns I don't need on the "Name" sheet.
The goal is that on the "Sally" sheet for it to show on separate rows: 12345 and 54285.
Any brilliant ideas?
Thank you in advance.
Answers
-
Hi @JLen
You can use a JOIN(COLLECT to bring together all of the unique identifier names, based on your criteria of the Name and "Approved". See: Formula combinations for cross sheet references
Try something like this:
=JOIN(COLLECT({Unique Identifier Column}, {Name Column}, Name@row, {Approval Column}, "Approved"), ", ")
This will join the values together into the same cell with a comma between them, like so:
12345, 54285
You could also have the formula in a multi-select column and use CHAR(10) to make them individual selections:
=JOIN(COLLECT({Unique Identifier Column}, {Name Column}, Name@row, {Approval Column}, "Approved"), CHAR(10))
However there currently isn't a way for this to parse down into multiple rows. If you want to do that, you would either need to create a Report to show the columns you want and filter based on your criteria (perhaps GROUPING by the Name column?) or you could set up your current metric sheet to have pre-filled rows for each person, numbering them, and use INDEX(COLLECT instead of JOIN. Here's another post with a similar solution.
I hope this helps!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!