Return multiple values across multiple sheets

[Leighleigh33]

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 up to at any given time, when they achieved that result and what the result was (which is a percentage). 
I am creating this sheet to be then imported and used in Power Bi as a dashboard for the school. Is this possible? 

The last sheet titled Summary is what I would like returned from the other sheets in the workbook.

[#2]

all roads to Roman.
I guess if you provide the source workbook and expected result,the discussion on the spesific automation solution can start soon.

[#3]

I have just loaded the sample and the last sheet title summary is what i want returned from the other sheets in the workbook.

[#4]

This is an ETL(Extract-Transform-Load) task.

I would like running sql to automate it.

 https://b23.tv/3n30jrt

 //select  rowid old_rowid,colIndex[1:19] from consolidateSheet;
create temp table aa as 
select rowid old_rowid,colIndex[1:19] from consolidateSheet where f04!='Students';
 cli_one_dim_with_zero~temp.aa~4;
create temp table cc as 
with bb as (
select *,(cast(regexp2('\d+',属性) as int)-1)/2 grp from aaunion order by old_rowid)
select *,regreplace('(\d+)\/(\d+)\/(\d+)','20$3-$1-$2',group_concat(数量,'</td><td>')) Date_Result from bb group by old_rowid,grp;
cli_no_header;
select f03,f04,f02,Date_Result from cc;

 You can find other ways to acheive it too.

[#5]

@Leighleigh33 

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.

[#6]

Whoa thanks I am a real newbie here can you explain how this works?

[#7]

@Leighleigh33 

Power Query variant is in attached file

[#8]

@Leighleigh33 

It all depends on which Excel version/platform you are and which Power BI subscription you and people who uses dashboard have - free or Pro.

[#9]

I am using 365 and have a paid power BI subscription and users have the free dashboard.

[#10]

Thank you so much Sergei, This looks like it will work for my purpose. Is it best to have the data and what is returned in different workbooks? Can you have it on a sheet in the same workbook?

[#11]

Have you watched the video?
You can try this online tool if possible.
http://e.anyoupin.cn/EData/?p=tools.consolidate.consolidateshts/consolidateWithNewF

[#12]

@Leighleigh33 

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. 

[#13]

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?

[#14]

@Leighleigh33 

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.

[#15]

@Leighleigh33 

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 steps

How to use: In Param sheet update the Path to Excel file with Subtest sheets with yours then Refresh the query

[#16]

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.

[#17]

@Leighleigh33 

Updated query attached