I would like to be able to reference filter criteria for reports from a single top level cell. My project and workaround/issue is below:
I am in the manufacturing business. We have machining lines that have a number of operations: ill say on average its ~20 operations. Each operation has an operator that at the end of the shift submits a paper reporting 15 things about the operation. The 15 things are the same for all operations.
These are all conglomerated by a manager and then sent throughout our facility.
I want to build a reporting system to analyze the historical data and allow for easy input. To this end I made a sheet and submission form for each operation. I did a sheet for each operation due to the limit for the number of rows and the amount of historical data I want kept.
I then began my analysis. We have 3 shifts, and 20 operations. I want to be able to analyze by shift or operation, but I REALLY didn't want to build 84 individual sights and reports (include a look at all) and make the user sort through all of that. What I needed is a report that could change its filter criteria based on user input from the sheet level. (as a general rule I don't allow anyone access to my reports other than my fellow devs, to many issues have arisen from this)
My current solution is some dropdowns on a standard sheet with the analysis options. I then use sheet links to parse the data and give what the user selected with 50 locations set aside for each. I reached the max amount of sheet references rather quickly and ended up having to use 5 sheets for this purpose, then conglomerate them with a report.
