Forum Discussion
Return multiple values across multiple sheets
I have multiple sheets in a workbook for teachers to record marks in tests students take. Students will progress through these tests at different rates and I want a summary of what test students are ...
In general that is job for Power Query, especially if you are going to publish summary on Power BI. With that it's better to keep file with subtests separately, perhaps even more better if each subtest is in separate file and all of them are kept in some folder, local one or on SharePoint.
Summary could be combined in another file by Power Query if you need such file, same query could be used to publish dashboard directly on Power BI site without intermediate Excel. You may schedule data refresh for that site with the dashboard. Thus, when done, the only job will be to update files with the subtests results.
Will try to make some mock-up bit later.