Matching criteria between smartsheets

Not sure this is possible, but here goes! I have multiple smartsheets (let's say 3). Two of the smartsheets collect information from forms that we use to send on to credentialing or contracting so we can load them in our systems. A third smartsheet is a list of line items each with a gap in our network for a provider with a particular specialty and in a specific county. We would love to have some sort of process where the smartsheets could search for open gaps on the 3rd smartsheet that match a line and then check a box or something.


match up open gap "Reporting Category" plus "County" with Specialty in the other two smartsheets and return match or check box if there is a match. (I know we'd have to sync up our column headings)


Best Answer

Answers

  • Austin Smith
    Austin Smith ✭✭✭✭✭

    Not 100% sure this is what you're looking for, but try this in your checkbox field (you may need to rename columns, but more likely will have to reorder them to make vlookup work well, or just switch around the references that I entered so that vlookup works correctly. Only did a very rudimentary test against data.

    =IFERROR(IF(VLOOKUP(County@row, [County 2]@row:[Reporting 2]@row, 2, false) = Specialty@row, IF(VLOOKUP(County@row, [County 2]@row:[Reporting 2]@row, 3, false) = Reporting@row, IF(gap@row = 0, 1, 0), false), false), false)

    This matches the county, then makes sure the specialty matches, then finds the county and makes sure the reporting cat matches, if both are true, it determines whether there is a gap and if so, checks a box.

  • Paula Cosentino
    Paula Cosentino ✭✭✭✭✭
    Answer ✓

    That's amazing Austin. I'll try that. Thank you for taking the time!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!