Index - across many worksheets

Using this formula in a summary sheet, it currently works for one 'C-#' sheet (C-2), but I'd like for it to work across all the 'C-#' sheets (anywhere between 2 and 75)

=IFERROR(INDEX('C-2'!$A$2:$A$50,SMALL(IF('C-2'!$A$3:$A$50<>"",ROW('C-2'!$A$3:$A$50)),ROW('C-2'!1:1))-1,COLUMN('C-2'!A1)),"")

-Does anyone have a quick fix for this?

and if so,

-Is there a way to use one formula that will work even as/if more 'C-#' sheets are added?

I do not understand you well.
I'm not sure that I can help you, but I will try

Can you attach the attachment workbook, Sheet before and after sheet with the expected results (for example, manually enter all data on the expected summary worksheet)

expected

Only concerned with the first column of each sheet for now.
Expected outcome attached.

Merge data from Multiple Sheets into Summary Sheet

See if you help VBA in the example.
If not, ask for help continue

workbooks?

navic said:

See if you help VBA in the example.
If not, ask for help continue

Click to expand...

That is perfect. Thank you.
I'm interested to know if you have a macro that can 'copyallsheets' for different .xlsx workbooks in a folder