IF Statement with ??? to move cell data

JLen

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.

Genevieve P.

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