Refrencing Multiple Workbooks Into 1

We use Microsoft Forms for quality inspection purposes per department. Each of these forms (25+) creates its own workbook where the data gets uploaded each time an inspection happens in that specific department.

How do I get the data from each different workbook into 1 workbook. I want a central location where i can look at all the data for each department and be able to summarize/graph the data. Each source workbook can have its own worksheet inside this master copy.

The forms and therefore the tables are not uniform through all departments so as far as i know i can't use PowerQuery to combine the data.

I know you can just do =REFERENCE to each workbook and have it pull in the cells but that wont update unless you open every single workbook each time you want the master copy to update.

Is there a way to reference multiple workbooks like this and have them mass update from the source document? Is this a VBA question more than a formula/function?

Wow thank you for this link, this is an absolute game changer!

I've never used Power Query before and had no idea this was possible. I had to make a few tweaks but following along i was able to customize it to handle the data I am using. Incredible, thanks so much for pointing me to this video.