How to use advanced formulas to pull information from two sheets into new (third) sheet

Taylor Mauzy

Hello everyone! I am hoping to get some tips/advice on using Index Match/Collect (I think is the right route). Currently, my company has one of our data sources tracked and managed through smartsheet.

My goal is to make a new sheet, that is populated from pulling the data from our primary data source BUT I want to filter what is pulled by ANOTHER sheet (item directory) which I have populated with our production SKUs and their testing requirements. My intent with this second list, is to basically look at all of our production orders on the primary data sheet, and then I want to check the SKUs list against my item directory, and then pull all of the rows into a fresh sheet so that I can track specific orders which have micro testing required (we test for absence of bacterial pathogens in many of our products, but not all. Hence why I need a list to filter against).

To summarize, I want to filter the data from my primary data source against an item directory, and any SKUs that match the item directory sheet and have 'Yes' marked in the 'Micro Testing' column in the item directory. The end result should be a third sheet, which has all of our production orders that are currently running, and that require micro testing (as determined by their status on the item directory sheet I am trying to filter against).

I am trying to do this using formulas, but I also have full-stack Python experience (self taught) so if someone knows of an easier way to do this using the Smartsheet API, I am all ears. If additional clarity is required, please let me know where the confusion is, and I will try and word it better. Thanks!

P.S. - This would be a lot easier if I could add a column to the primary data source with the testing requirements column, but I am trying to make a system that automatically checks that so it's less work for our scheduling department to add all of that to the existing data source. I am working on getting that done, but this is what I have to work with for now.

Paul Newcome

You should be able to add a column to the original data and then use a formula that looks at the second sheet to automatically flag/check/etc. this new column if that requirement is met. You could then pull a report based on this new column to see only those that meet what you want.