Forum Discussion
Cretstoppa24
Dec 18, 2024Copper Contributor
Setting up a dashboard based on multiple workbooks
I'm wanting to setup a dashboard showinh test results from different sites. My vision is that each site would have their own workbook that they input data on, but every site would be inputting in a table that's of the same format. The Dashboard would pull info from each of these sheets and would show a chart/graph for each site displaying their pass/fail rate with a slicer/timeline to select the month that would control all of the charts/graphs. All the workbooks will be held in a central sharepoint.
What would be the best way to set this up?
- JonCosmosGenerateCopper Contributor
See attached Excel file, provided that you can consolidate the various workbook data tables into a source sheet within the existing workbook. From there, you can use an OFFSET with nested MATCH functions. The 'dashboard' sheet in the enclosed file will make it clearer (and this is without using the potential benefits of tables).
If you want users to maintain separate workbooks, you'd be able to use the data tables, their columns, and rows as unique identifiers to do the same with MATCH functions.
I hope this helps, but if you'd like to go into more detail, happy to.