importing data from multiple worksheets into one table

[Debbie57]

I have 50 worksheets all containing audit results using the exact same questionnaire.  Names, elements, scores, etc are all in the same location in every document.  Is there a way to automatically grab specific fields from these 50 worksheets and import them into a table in a separate worksheet?  Right now, I am copying and pasting, which just seems stupid.  I hope there is another way?  Can someone help?

[#2]

@Debbie57 

When you say "worksheets" are you speaking of 50 separate files, each its own Excel workbook?

It is indeed possible to retrieve data from cells in other workbooks; it's easier to retrieve data from cells in other sheets in the same workbook.

Without knowing more, I'd recommend that you look into the INDIRECT function. If they are separate workbooks, then you will need to have them all open in order to access them, but it will work....... So INDIRECT would be where I'd start.

[#3]

@Debbie57 Alternatively, look into Power Query (a.k.a. Get & Transform Data) to connect to all files in a Folder (in case you have 50 different files) or all sheets in a workbook (if that's applicable in your case).

[#4]

@Debbie57 Yes, you can do this by using Office Scripts and Power Automate. You can create a Flow that gets all the workbooks in a SharePoint folder. Then, using the Run Script action in the Excel Online Business connector, you can run an Office Script on each of those workbooks to get the specific data you need and add that data into a table in another sheet. Here is some documentation about Office Scripts as well as how to get started using Office Scripts and Power Automate. Let me know if you need any additional help!