Cross-Reference Formula Help with IF Statement

KKathol

Hoping someone can help me fix this formula:

=IF({Scope & Proposal Master List status column referenced} = "Won", INDEX({Scope & Proposal Master List project name column referenced}, MATCH([Project Name]@row, {Scope & Proposal Master List Status}, 0)))

I have two sheets. In the "destination" sheet (Project Wins) I have a column labeled Project Name. In that column, I want to populate the project names from the Project Name column in the source sheet (Scope & Proposal Master List) only if the Status column in the source sheet says "Won".

ericncarr

Hi @KKathol do you want to populate the Project Names on multiple rows or collect them all in one row?

If you want to collect them on multiple rows, I recommend creating a "#" helper row and numbering 1-x, x being however many Projects you want to capture.

Then do an index(collect()) from your destination sheet to your source sheet.

=INDEX(COLLECT({Project Name Range on Source Sheet}, {Range where "Won" might be present on source sheet}, "Won"), [#]@row)

You could add criteria as you go forward, such as date ranges, project types, etc.

KKathol

Thank you! The [#]@row part … Do I need to create a reference for that to my source sheet or does the formula just "know" where to pull from as long as the other information is accurate?

I'm trying it and it is saying Unparseable:

=INDEX(COLLECT({Scope & Proposal Master List Project Name}, {Scope & Proposal Master List Status}, "Won"), [#]@row)

KKathol

@ericncarr

Forgot to tag you in the comment above!

ericncarr

@KKathol the [#]@row you would add to your destination sheet where you want to pull in the Project Names. Putting numbers 1 - x in that column will basically just tell your index(collect()) which number in the index you've created you want to show on that line. If you add that in does it work?

KKathol

@ericncarr I actually got this formula to return a project name in the appropriate column and sheet:

=INDEX({Scope & Proposal Master List Project Name}, MATCH("Won", {Scope & Proposal Master List Status}, 1))

However, now it is only pulling the project name from the last row of the column that I'm using for my range, and that status is not "Won".

When I change the 1 at the end to a 0, it gives me a #NO MATCH error.

Jason Tarpinian

I think I'm following this chain correctly, and I'm a visual person, so here's what I think you're looking for? (for my own simplicity they're on the same sheet, but you can replace the ranges with your cross sheet ranges)

So "Project Name" and "Status?" would be from your source sheet.

"Row ID" is the Smartsheet system column.

"Row #" =MATCH([Row ID]@row, [Row ID]:[Row ID], 0) … this is a super helpful formula that will always return you the row #, which can then be used as a helper in lots of formulas.

"Winning Project Names" =IFERROR(INDEX(COLLECT([Project Name]:[Project Name], [Status?]:[Status?], "Won"), [Row #]@row), "") … this formula, as Eric mentioned, will collect all of the project names that include a status of "Won", and then return you the n'th value based on the "Row #" formula from above. The IFERROR is used to clean up when there isn't an n'th value, like the row 6 and below.

"List" is just a bonus formula I threw in to show another way to do this. JOIN() function is helpful in creating lists, so it uses the same criteria as the formula above, but joins then together in a list separated by a line break, CHAR(10), for a clean easy view - helpful if you want to throw it on a metric widget on a dashboard.