Power Query - Get multiple strings from multiple sheets (non-table data) and refresh a main table.

[RockandGrohl]

Hello!

I have an excel book that is structured with a blank template, one sheet called IRO, and then the other tabs hold various lookups and validations.

The user should fill out one or more of the templates (duplicating the template sheet if they need more than one), then head to the IRO page and hit refresh, where it then fills in a table which holds one line per template file with all the data in each template tab laid out horizontally. For example, if a user filled out 3 template tabs, the IRO tab would be a table with 3 lines of data.

I'm getting quite stuck at the numerous hurdles:

1. How do I point power query to look at my data in each valid sheet?
   1. Each valid sheet will have an "X" in cell A1
2. Once the sheet is being looked at, I need to grab data from multipe non-continuous sources.
   1. For example, columns A, B, C & D in the IRO pertain to what should be in cells D4, D5, D6 and D7 in each template tab
   2. There are about 20 cells that need to be brought into the IRO table for each template tab.

Are there any Power Query gurus available to help a noob like me? Thanks!

 

[johnny51981]

Unfortunately, Power Query is only able to read Tables and Named Ranges from the workbook that is conducting the querying. Seems silly...but without the controls in that, you would run the risk of, inadvertently, creating a circular reference.

There might be a solution...but I feel that it would take some VBA to do that because the tables would need to be uniquely named, even across worksheets.

There could also be some further roadblocks...but that is what I see, at least to give you a first go at a response.