Request for creating a master excel which refers various sheets, in it, and get output on the basis of formula.

[vaidmohammed]

I need help on a formula creation for generation of a test execution report. My need is :
Background
1. I use to get test status reports, in defined excel format on mails, from various team members who are geographically divided, on daily basis.
2. I use to maintain these reports in 1 folder
3. Everytime before status call, I have to open individual excel status report and have to jot down no. of issues open, close, reopen,etc. observations in my diary, manually.

I wish to have a formula filled master excel, which should get update automatically whenever I open it, with status by referring to issue sheets maintained.

I use to maintain all test execution reports in folder D:\ClientProjectManagement\OpenTestReports. These reports have common format, as attached.

In folder D:\ClientProjectManagement\MasterReport I want that some formula should be built which refers the respective test reports as mentioned in Col. A and give me output under Col. B to Col. N. Like shown below:

Can anyone please help me in building this report.

 

[GraH - Guido]

Hi, my simple advice would be to use a PowerQuery solution and use "get data from folder" to consolidate all reports. Do you have Excel 2010 or higher version available?

 

[vaidmohammed]

Hi, my simple advice would be to use a PowerQuery solution and use "get data from folder" to consolidate all reports. Do you have Excel 2010 or higher version available?

Yes I am using Ms Office 2016

 

[vaidmohammed]

Yes I am using Ms Office 2016

Please advice how to use this option. I will check today itself.

 

[GraH - Guido]

To get you on the way:

1. Ribbon - Data
2. Section Get and Transform
3. New Query - > From File -> From Folder
4. Look for a tutorial how to use it (plenty available on the web)

Attached is the query that does the "combine". See if that would be suitable.

When you have new files, simply press refresh (right click on the green table).

 

[vaidmohammed]

Thanks GraH but it doesn't helps.
I need a formula which will search for the workbook at the defined location and then read worksheet name and get me the countifs of a particular column. In my shared sample file,
I wish to have a formula which will search for the Sample workbook at the defined location, go to Detail worksheet and get me the count of Open, ReOpen, Delivered from Col. H.
Someone can please help me with a formula.

 

[vletm]

vaidmohammed
... formula ... hmm ... with VBA this would be ...
Test this sample ... because NO all needed files!
> Cell A15 has Your Query.xlsx 's path
> Cell A18 has Your filename (which You've given) ... light green cells
> Cells from B18 to N18 has ... formulas. ... what did You want to get to N18?
Those other files have to be open while checking results!
... with VBA ... No!

 

[vletm]

vaidmohammed
As You see there is & "[" & between path and filename.