Forum Discussion
Return multiple values across multiple sheets
It all depends on your business process. If you or someone else update results in the same file it could be done in separate sheets. If you copy/paste from another file it's more reasonable to use these other files as the source. It's also better from technology point of view.
- Thanks Sergei,
What you have created works perfectly although I am at a bit of a loss as to how to translate what you have done to my whole spreadsheet. Is there somewhere with the steps so I can see how to apply it to my whole set of data and expand what you have done for a whole school of student names and multiple sheets of data?Power Query variant attached. You should explain the logic to apply for cases like Carly Simon:
+ In the event where a Student has the same Result & Date in the same or in different Subset sheets, which record(s) should be reported
Same comments as Sergei re. Subset sheets consistency, names...
+ Data quality issues: some cells that are supposed to be empty actually contain space(s) ==> Unecessary query stepsHow to use: In Param sheet update the Path to Excel file with Subtest sheets with yours then Refresh the query
- Thank you so much for your assistance, the record that i want reported is the highest subtest level i.e. The highest number subtest for Carly Simon would be Subtest 5, the date achieved and the result. I can see in my dummy data that she achieved a result in 3 after 5- my error with the dummy data.
In theory it shall work if you
1) Change the file path within dashboard Excel sheet on one where is you actual file with tests result
2) Sheet names with test results start from the works "Subtest" (case sensitive)
3) Each sheet has exactly the same structure and some number of columns as in sample file.
Otherwise, i.e. file is located on SharePoint site, sheet names have another logic or we select all sheets in such file; sheets have bit different structure - query shall be modified.
If you are not familiar with Power Query it's hard to say what exactly to do. In general in Power Query editor check step by step each query and correct what doesn't work.
