Can someone please help with this formula?

ahrenm1

I keep getting an "invalid operation" error message with this formula. Can someone spot where I went wrong? I am not great at formulas. The goal is for multiple criteria (IF statements) to match before entering "Inactive" in the cell.

=IF({Master Cadre Roster Range 1} = [First Name]@row, IF({Master Cadre Roster Range 3} = [Last Name]@row, IF({Master Cadre Roster Range 2} = "Inactive", "Inactive")))

ECarlson_PCG

With your cross sheet references, try this:

=IF(INDEX(COLLECT({Master Cadre Roster Range 2}, {Master Cadre Roster Range 1}, [First Name]@row, {Master Cadre Roster Range 3}, [Last Name]@row), 1) = "Inactive", "Inactive", "")

ECarlson_PCG

Each "IF" needs a condition after it, you only have that in the final IF portion, for example:

=IF({Master Cadre Roster Range 1} = [First Name]@row, "What you want to happen here", IF({Master Cadre…

are you trying to have both First Name and Last Name populate as "Inactive" ? That would be an "AND" condition.

Lisa LS Kennedy

A range can't equal a cell, a cell can meet criteria in a range. Try something like IF(CONTAINS([First Name]@row, {Master Cadre Roster Range 1}) . Are these AND Statements or OR statements?

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

ahrenm1

Yes, thank you. These are all AND statements.

ECarlson_PCG

With your cross sheet references, try this:

=IF(INDEX(COLLECT({Master Cadre Roster Range 2}, {Master Cadre Roster Range 1}, [First Name]@row, {Master Cadre Roster Range 3}, [Last Name]@row), 1) = "Inactive", "Inactive", "")

ahrenm1

This worked, thank you so much!

ECarlson_PCG

Great! I'm glad to hear, have a nice weekend!