Returning a value based on parsing info in another Smartsheet

John E

I am trying to return a value from another smartsheet based on information from my current smartsheet...

I have a WBS number in my current sheet (Sheet A) that I am using as a reference point (ex. 1LO-150001234-P-15-01-09), and I am looking for the first 13 characters of that WBS number in a specific column of a different sheet (Sheet B) (ex. 1LO-150001234). If it finds a match to that number from Sheet A in Sheet B, I want it to return a value from a different column in that same row of Sheet B to a cell in Sheet A (ex. return the value of either a red, yellow, or green dot).

Add'l Info... It is likely that there will be multiple instances of finding a match on Sheet B, and that the corresponding values can be different (ex. three rows that match "1LO-150001234" and one is a green dot, one is a yellow dot and one is a red dot. If that happens I only want it to return the value of the worst case (ex. red if it exists anywhere, yellow if it exists anywhere, green if it exists anywhere, or blank if its blank).

Here is where I am right now with the formula, but I can't think of how to complete it... any help would be appreciated.

=IF(LEFT([Project Number]2, 13) = {ORBLE Range 1}, LARGE({ORBLE Range 2}, 1), "???")

Paul Newcome

You are going to need to use a COUNTIFS to count how many are red, how many are yellow and how many are green. The LARGE function only works on numbers because strings and symbols could have different meanings for different people.

=COUNTIFS({Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {Range 2}, @cell = "Red")

Then you would nest that COUNTIFS into a nested IF to say that if the count of red is greater than zero, output red, if the count for yellow is greater than zero, output zero, so on and so forth.

=IF(COUNTIFS(..............., "Red")>0, "Red", IF(COUNTIFS(..............., "Yellow")>0, "Yellow", IF(COUNTIFS(..............., "Green")>0, "Green")))

Paul Newcome

Not quite. There should only be 1 COUNTIFS inside of each IF.

I have

IF(COUNTIFS(......., "Red")>0

but you have

IF(COUNTIFS(=COUNTIFS(.........., "Red"), "Red")>0

Paul Newcome

You are going to need to use a COUNTIFS to count how many are red, how many are yellow and how many are green. The LARGE function only works on numbers because strings and symbols could have different meanings for different people.

=COUNTIFS({Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {Range 2}, @cell = "Red")

Then you would nest that COUNTIFS into a nested IF to say that if the count of red is greater than zero, output red, if the count for yellow is greater than zero, output zero, so on and so forth.

=IF(COUNTIFS(..............., "Red")>0, "Red", IF(COUNTIFS(..............., "Yellow")>0, "Yellow", IF(COUNTIFS(..............., "Green")>0, "Green")))

John E

@Paul Newcome That is very helpful... here is where I am at now:

=IF(AND(LEFT([Project Number]@row, 13) = {ORBLE Range 1}, COUNTIFS({ORBLE Range 2}, "Red") > 0, "Red", IF(COUNTIFS({ORBLE Range 2}, "Yellow") > 0, "Yellow", IF(COUNTIFS({ORBLE Range 2}, "Green") > 0, "Green"))))

I am getting "Incorrect Argument Set".

Essentially, I think I am asking it to look at the first 13 characters of a cell on Sheet A ("Project Number"@row) and compare them the all the cells in a specific column on Sheet B ("ORBLE Range 1"). If those first 13 characters match any cell in that column of Sheet B, then add up the colored ball symbols from a different column of Sheet B (ORBLE Range 2). If there is more than 0 red ball symbols, then return a red ball, if not, then look for any yellow ball symbols and if there are more than 0, return a yellow ball... so on and so forth for the greens.

Is my nested formula in the right ballpark for this?

Paul Newcome

No. Each of your COUNTIFS would be the same syntax I had in my first formula. Then you would create the nested IF using the CLUNTIFS the same way I have in my second formula.

John E

@Paul Newcome

One more try... with my brain turned on this time...

=IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Red"), "Red") > 0, "Red", IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Yellow"), "Yellow") > 0, "Yellow", IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Green"), "Green") > 0, "Green")))

Still getting an INVALID OPERATION error.

John E

I think I just needed to get rid of the extra = sign... tell me if I'm wrong.

John E

Got rid of the extra = sign, but now all the cells are showing blank... not red, yellow, or green dots.

Paul Newcome

Not quite. There should only be 1 COUNTIFS inside of each IF.

I have

IF(COUNTIFS(......., "Red")>0

but you have

IF(COUNTIFS(=COUNTIFS(.........., "Red"), "Red")>0

John E

Finally got it right!

=IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Red") > 0, "Red", IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Yellow") > 0, "Yellow", IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Green") > 0, "Green")))

Huge thanks to @Paul Newcome !!!

Paul Newcome

Happy to help. 👍️