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
-
That's amazing Austin. I'll try that. Thank you for taking the time!
Answers
-
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.
-
That's amazing Austin. I'll try that. Thank you for taking the time!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!