summary data from multiple sheets

[Berna11]

Hello, I would like your help to bring data from multiple sheets to one

In the attached example I have several sheets and in column E have various courses,
I would like to report on the Dance sheet the line that contains the Dance entry in the other sheets.

I tried inserting the list of sheets in column I and using:

Please Login or Register  to view this content.

but it is not possible to drag to the right the column A references that are always fixed remain.

You can vary or use another similar formula

Thank you.

[Bo_Ry]

Please try at
H4
=IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW($I$4:$I$6)/(COLUMN($A$1:$Z$1)<=SUBTOTAL(3,INDIRECT("'"&$I$4:$I$6&"'!A4:A100"))),ROWS(H$4:H4))),"")

A4:G4
=IF($H4="","",INDIRECT("'"&$H4&"'!R"&3+COUNTIF($H$4:$H4,$H4)&"C",))

[Pete_UK]

I've set this up for you in the attached workbook.

Your request is very similar to a thread I responded to a few days ago, and there is a detailed explanation of how it works in Posts #7 and #9, so there is not much point in me repeating them here. The cell references in your file will be different, but here is the thread:

https://www.excelforum.com/excel-gen...lank-rows.html

Feel free to post back if you have any questions about it. In your file I have added a data validation drop-down in cell L1, so you can select which course to display quite easily.

Hope this helps.

Pete

[Bo_Ry]

Post #2 for all data,
Here for dance only.

H4
=IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW($I$4:$I$6)/(COLUMN($A$1:$Z$1)<=COUNTIF(INDIRECT("'"&$I$4:$I$6&"'!E4:E100"),$A$1)),ROWS(H$4:H4))),"")

A4:G4
=IF($H4="","",INDEX(INDIRECT("'"&$H4&"'!C",),AGGREGATE(15,6,ROW(A$4:A$100)/(INDIRECT("'"&$H4&"'!E4:E100")=$A$1),1)))

[Berna11]

Thanks in the meantime for your interest.

The solution to Pete_UK even if longer seems ok for now I have to finish
* to adapt it to my complete file to confirm it definitively.

While the second solution Bo_Ky has a small problem, inserting in the sheet 331 other person with Dance does not report the correct name but repeats the one previously.

I attach the file with the new name Tizio.

[Bo_Ry]

Thanks for the feedback.

I miss one thing
A4
=IF($H4="","",INDEX(INDIRECT("'"&$H4&"'!C",),AGGREGATE(15,6,ROW(A$4:A$100)/(INDIRECT("'"&$H4&"'!E4:E100")=$A$1),COUNTIFS($H$4:$H4,$H4))))

[Berna11]

Hello,
now the solution to @Bo_Ry also seems to work

I thank you for your kindness, courtesy