Create overview list that pulls in data from downloaded sheet

[Jyggalag]

Hi all,

I am currently downloading a bunch of sheets that look like this:

I would like to make an overview in my sheet called "List", where the data from "Sheet1" is automatically pulled in.

However, I would like to do this without making any changes to the downloaded sheet, including not changing its name as well, in order to automate the process as much as possible.

So far I have gotten some very nice help from @Fluff with an incredibly smart INDIRECT INDEXMATCH formula that can pull in data based on the sheet name and a value in the column. However, this means that I would have to rename the sheet every time to make its name match with a value contained in the list overview.

Does anybody know of some smart automation solution to this, maybe PowerQuery?

Apologies for the confusion and thank you so much everybody!

 

[NdNoviceHlp]

If you are "downloading" the sheet you must be using VBA. Why not just change the sheet name while you're at it? HTH, Dave