How to use Power Query to get a specific value from a external spreadsheet to specific cell location in target spreadsheet

[kdjonesmtb]

Want to get summary data from a spreadsheet using Power Query and pull it into specific column cells in a target spreadsheet

Do you need to write a script to do that or can you use named ranges to place the data in a target sheet

I have up to 10 separate spreadsheets that I need to pull testing data summary columns for

I am able to get the power query to pull summary data but it puts it into a separate worksheet for every data element

Want to be able to pull these data elements below into a single worksheet from multiple Spreadsheets


Example


Test Plan 1: Test Passed Test Failed Test Blocked Test Not Executed Comments
Test Plan 2: Test Passed Test Failed Test Blocked Test Not Executed Comments

Repeated for up to 10 test plans

 

[alansidman]

Sample data and an explanation of exactly what you want done with the data and then where you want it to go specifically would help significantly.

 

[kdjonesmtb]

Hope this shows what I am trying to do

Using the Power query against three spreadsheets

Summary will pull the summary sheet cells from Rider Test Plan 1 and two into the rows of the summary sheet

Each row represent Test Plan summary values
Could have up to ten rows of individual Test Plans

thinking that I need to do this with a custom query that can put the summary data into the rows on the Summary sheet by Rider Test plan

 

[alansidman]

Since you did not show what the summary should look like, I am guessing that you want to append all the sheets into one sheet. Is this correct or are you attempting to do something else. Really need to see what the expected results should look like.

 

[kdjonesmtb]

 

[alansidman]

Combine Data From Multiple Worksheets into a Single Worksheet in Excel

In this tutorial, you'll learn how to quickly combine data from multiple sheets into one single worksheet (using Power Query).

trumpexcel.com