How do I compare data on 4 sheets?

Hello,

I have a [PCN Checklist] that holds active and non-active PCN#'s. I need to check each PCN # on the [PCN Checklist] smartsheet to see if it's in any of the 3 other smartsheets:

[PCN Processing Log]

[PCN Archive Log 1]

[PCN Archive Log 2]

If it's not in any of these 3 smartsheets, it did not go through the PCN Process.

The columns in all 4 smartsheets is a text column with the title {PCN #}. However, 2 of the above smartsheets, the PCN # has a hyperlink attached to it. I've tried different formula's and also created a "Clean PCN #" column without the hyperlink and can't get it to work.

=IF(OR(ISNUMBER(MATCH([PCN #]@row, {PCN Processing Log Range}, 0)), ISNUMBER(MATCH([PCN #]@row, {Archived 1 Range}, 0)), ISNUMBER(MATCH([PCN #]@row, {Archived 2 Range}, 0))), "Match", "No Matches")

This returns: #No Match

It would be ideal if it could return the following if Matched or Not Matched per sheet:

Processing Log:

If matched return, "Matched Processing Log"

If not matched, return "Check Archive Log 1"

Archive Log 1:

If matched return, "Matched Archive Log 1"

If not matched return, "Check Archive Log 2"

Archive Log 2:

If matched return, ""Matched Archive Log 2"

If not matched return, ""Not Processed"

This might be a long shot.

Any help would be greatly appreciated!

Thanks in advance for your help!

Answers

  • Katy H
    Katy H Community Champion

    Jamie, I would recommend creating helper columns for each archive sheet to simplify the complexity of the overall formula. I tested an example and have included a video for your reference. I was able to get a functioning PCN with a hyperlink using a helper column that used a VALUE function to convert it to a number, but would only work if the PCN is only a number.

    Video Breakdown

    Formulas Used:

    For each of the helper columns that checks for the PCN number in each of the archive sheets. These are split into separate columns, but could potentially be merged into one if preferred. There is just a higher likelihood of the formula "breaking" the more complex it gets.

    =IF(COUNTIF({PCN Processing Log - PCN Number}, [PCN Number]@row) > 0, 1, 0)
    =IF(COUNTIF({PCN Archive Log 1 - PCN Number}, [PCN Number]@row) > 0, 1, 0)
    =IF(COUNTIF({PCN Archive Log 2 - PCN Number}, [PCN Number]@row) > 0, 1, 0)
    

    For the formula that checks if the PCN has gone through the PCN Process, it just checks all the helper columns and writes yes or no based on the results.

    =IF(COUNTIF([PCN Processing Log Match]@row:[PCN Archive Log 2 Match]@row, 1) > 0, "Yes", "No")
    

    To convert a hyperlinked PCN into an actual number (this only works with numbers, no text)

    =VALUE([PCN Number]@row)
    

    Let me know if this answers your questions!

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!