Advice needed for creating corrective action submission tool using Dynamic view
We are using Smartsheet to track compliance data for monthly internal inspections across nine different sites. The way our tracking system is set up is when a non-compliance is entered in the form using “No” from the dropdown list, logic then opens a text box to type details and a hidden dropdown list that generates “Corrective action needed?” field defaulted to yes. Data from all sites is being tracked on one sheet. I am hoping to:
1.) pull non-compliance data on three fields (the main trigger question, text details, and corrective action needed?) from one sheet to another so people at different sites can see what the non-compliances were for that month
2.) Create a dynamic view for each location that displays the non-compliant fields and a field for them to enter a corrective action
I am hoping to get insight on the best way to accomplish this. Would it be possible to create formulas that filter for IF it’s location “ABC”, IF inspection month was “May”, then show main trigger question, text details, and corrective action needed in three columns on the new sheet? I'm not sure if VLOOKUP could be used for this? Or would no formula be needed if there’s a way to separate based on each location in dynamic view even though all data is entered on the same sheet? I just want to make sure each site contact only sees non-compliances relative to them. Any advice is appreciated!