Search and sum values in multiple workbooks for accounting

[Petijandro]

Dear Excel Gurus,

I looked through the forums to try to find a solution for this problem but was not lucky.

I'm trying to put together a search box that can loop through all the workbooks in a folder and all the subfolders and find all occurences of that search string and copy them over to a sheet with all the adjacent data (2-3 columns to the right).
This search box would have a Date interval so it would only consider data within that date range.

.
This is how all workbooks look like, we have sheets for every week where our colleagues can track their progress on that specific workday (sheets called "Week 24", "Week 25" etc):

And this would be the end result:

I am just wondering if this is possible in Excel?
I have some basic vba skills and managed to put together the search function that loops through the workbooks but cannot figure out how to work with the dates.

All advice are greatly appreciated.

Thank you ?

 

[Marc L]

Hi,​

​

my first advice with such wild cross posting is you should post a link for each other forums where you have created the same thread​

and do the same in each other forums …​

​

My second advice is to post at least what you have done so far, using the VBA icon …​

 

[Radoslaw Poprawski]

the second option is Power Query.

if every workbook has the same layout, you can append all of them into 1 table using from folder feature in the data - get data tab.
Then you can:
Option 1: create parametrized query using ur search box to filter out the result and present it as a Table.
Option 2: you can use

=filter()

function to filter that appended table to your desired result.

but without some sample files (link to a onedrive folder for example) or minisheet(read my signature) it would be hard to guess the solution

Here is a link to my file merger (or rather appender)

Test (3) (1).xlsx

Microsoft Excel Workbook

1drv.ms

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search and list values and adjacent columns from multiple workbooks
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Petijandro]

Sorry about that, did not know "cross-posting" was a thing. Thank you for letting me know.
Cross-posted link.

I added some sample files to this folder:

Dropbox

www.dropbox.com

 

[Radoslaw Poprawski]

in the week sheets - is it like that always or are those 5 days in separate tab?
also, would it be possible to change that layout since it is not data analyzing friendly?

 

[Petijandro]

Yes, it is like that always.. 5 days in a separate tab with "Week XX" as the sheet names marking the calendar week of those dates.
We can change anything if we can retain the possibility to track progress every 15 mins with the values from the "list" master sheet.

 

[Radoslaw Poprawski]

if that is a Thank you or a smile or hand shake then yes, otherwise its gainst rules on mrexcel forum.
offering it is also against the rules.
I would suggest reading the regulations b4 mods find out

 

[Petijandro]

My bad, sorry.

 

[Radoslaw Poprawski]

but back to the topic.
I will mash up a fast version of what I have in mind, this would be in the convection of clumnar dataset and it will also speed up any analytical work.
your current layout would require some weird data manipulation in power query - its still doable but unnecessary with a correctly prepared table.

rEPORT.xlsx

Microsoft Excel Workbook

1drv.ms

rEPORT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3										Date	Weeknumber	Time start	time end	Value1	Value2	Value3	Value4 - clear text
4										22.06.2021	26	1	1,010416667	test1	test1-abc	0	12321321
5		Search field				value2				22.06.2021	26	1,020833333	1,03125	test1	test1-abc	0	12321321
6		Search value				test1-abc				22.06.2021	26	1,041666667	1,052083333	test2	test1-abc	0	0
7										22.06.2021	26	1,0625	1,072916667	test4	test1-abc	0	0
8		Search interval				21.06.2021	22.06.2021			22.06.2021	26	1,083333333	1,09375	test6	test1-abc	0	0
9										22.06.2021	26	1,104166667	1,114583333	test8	test1-abc	0	0
10										22.06.2021	26	1,125	1,135416667	test10	test1-abc	0	0
11										22.06.2021	26	1,145833333	1,15625	test12	test1-abc	0	0
12										22.06.2021	26	1,166666667	1,177083333	test14	test1-abc	0	0
13										22.06.2021	26	1,1875	1,197916667	test16	test1-abc	0	0
14										22.06.2021	26	1,208333333	1,21875	test18	test1-abc	0	0
15										22.06.2021	26	1,229166667	1,239583333	test20	test1-abc	0	0
16										22.06.2021	26	1,25	1,260416667	test22	test1-abc	0	0
17										22.06.2021	26	1,270833333	1,28125	test24	test1-abc	0	0
18										22.06.2021	26	1,291666667	1,302083333	test26	test1-abc	0	0
19										22.06.2021	26	1,3125	1,322916667	test28	test1-abc	0	0
20										22.06.2021	26	1,333333333	1,34375	test30	test1-abc	0	0
21										22.06.2021	26	1,354166667	1,364583333	test32	test1-abc	0	0
22										22.06.2021	26	1,375	1,385416667	test34	test1-abc	0	0
23										22.06.2021	26	1,395833333	1,40625	test36	test1-abc	0	0
24										22.06.2021	26	1,416666667	1,427083333	test38	test1-abc	0	0
25										22.06.2021	26	1,4375	1,447916667	test40	test1-abc	0	0
26										22.06.2021	26	1,458333333	1,46875	test42	test1-abc	0	0
27										22.06.2021	26	1,479166667	1,489583333	test44	test1-abc	0	0
28										22.06.2021	26	1,5	1,510416667	test46	test1-abc	0	0
29										22.06.2021	26	1,520833333	1,53125	test48	test1-abc	0	0
30										22.06.2021	26	1,541666667	1,552083333	test50	test1-abc	0	0
31										22.06.2021	26	1,5625	1,572916667	test52	test1-abc	0	0
32										22.06.2021	26	1,583333333	1,59375	test54	test1-abc	0	0
33										22.06.2021	26	1,604166667	1,614583333	test56	test1-abc	0	0
34										22.06.2021	26	1,625	1,635416667	test58	test1-abc	0	0
35										22.06.2021	26	1,645833333	1,65625	test60	test1-abc	0	0
36										22.06.2021	26	1,666666667	1,677083333	test62	test1-abc	0	0
37										22.06.2021	26	1,6875	1,697916667	test64	test1-abc	0	0
38										22.06.2021	26	1,708333333	1,71875	test66	test1-abc	0	0
39										22.06.2021	26	1,729166667	1,739583333	test68	test1-abc	0	0
40										22.06.2021	26	1,75	1,760416667	test70	test1-abc	0	0
41										22.06.2021	26	1,770833333	1,78125	test72	test1-abc	0	0
42										22.06.2021	26	1,791666667	1,802083333	test74	test1-abc	0	0
43										22.06.2021	26	1,8125	1,822916667	test76	test1-abc	0	0
44										22.06.2021	26	1,833333333	1,84375	test78	test1-abc	0	0
45										22.06.2021	26	1,854166667	1,864583333	test80	test1-abc	0	0
46										22.06.2021	26	1,875	1,885416667	test82	test1-abc	0	0
47										22.06.2021	26	1,895833333	1,90625	test84	test1-abc	0	0
48										22.06.2021	26	1,916666667	1,927083333	test86	test1-abc	0	0
49										22.06.2021	26	1,9375	1,947916667	test88	test1-abc	0	0
50										22.06.2021	26	1,958333333	1,96875	test90	test1-abc	0	0
51										22.06.2021	26	1,979166667	1,989583333	test92	test1-abc	0	0
52
53
54
55
56
Sheet2

Cell Formulas
Range	Formula
J3:Q3	J3	=IF(J4="","",Table1[#Headers])
J4:Q51	J4	=FILTER(Table1,(INDIRECT("Table1["&F5&"]")=Sheet2!F6)*(Table1[Date]>=Sheet2!F8)*(Table1[Date]<=Sheet2!G8),"")
Dynamic array formulas.

rEPORT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3										Date	Weeknumber	Time start	time end	Value1	Value2	Value3	Value4 - clear text
4										22.06.2021	26	1	1,010416667	test1	test1-abc	0	12321321
5		Search field				value2				22.06.2021	26	1,020833333	1,03125	test1	test1-abc	0	12321321
6		Search value				test1-abc				22.06.2021	26	1,041666667	1,052083333	test2	test1-abc	0	0
7										22.06.2021	26	1,0625	1,072916667	test4	test1-abc	0	0
8		Search interval				21.06.2021	22.06.2021			22.06.2021	26	1,083333333	1,09375	test6	test1-abc	0	0
9										22.06.2021	26	1,104166667	1,114583333	test8	test1-abc	0	0
10										22.06.2021	26	1,125	1,135416667	test10	test1-abc	0	0
11										22.06.2021	26	1,145833333	1,15625	test12	test1-abc	0	0
12										22.06.2021	26	1,166666667	1,177083333	test14	test1-abc	0	0
13										22.06.2021	26	1,1875	1,197916667	test16	test1-abc	0	0
14										22.06.2021	26	1,208333333	1,21875	test18	test1-abc	0	0
15										22.06.2021	26	1,229166667	1,239583333	test20	test1-abc	0	0
16										22.06.2021	26	1,25	1,260416667	test22	test1-abc	0	0
17										22.06.2021	26	1,270833333	1,28125	test24	test1-abc	0	0
18										22.06.2021	26	1,291666667	1,302083333	test26	test1-abc	0	0
19										22.06.2021	26	1,3125	1,322916667	test28	test1-abc	0	0
20										22.06.2021	26	1,333333333	1,34375	test30	test1-abc	0	0
21										22.06.2021	26	1,354166667	1,364583333	test32	test1-abc	0	0
22										22.06.2021	26	1,375	1,385416667	test34	test1-abc	0	0
23										22.06.2021	26	1,395833333	1,40625	test36	test1-abc	0	0
24										22.06.2021	26	1,416666667	1,427083333	test38	test1-abc	0	0
25										22.06.2021	26	1,4375	1,447916667	test40	test1-abc	0	0
26										22.06.2021	26	1,458333333	1,46875	test42	test1-abc	0	0
27										22.06.2021	26	1,479166667	1,489583333	test44	test1-abc	0	0
28										22.06.2021	26	1,5	1,510416667	test46	test1-abc	0	0
29										22.06.2021	26	1,520833333	1,53125	test48	test1-abc	0	0
30										22.06.2021	26	1,541666667	1,552083333	test50	test1-abc	0	0
31										22.06.2021	26	1,5625	1,572916667	test52	test1-abc	0	0
32										22.06.2021	26	1,583333333	1,59375	test54	test1-abc	0	0
33										22.06.2021	26	1,604166667	1,614583333	test56	test1-abc	0	0
34										22.06.2021	26	1,625	1,635416667	test58	test1-abc	0	0
35										22.06.2021	26	1,645833333	1,65625	test60	test1-abc	0	0
36										22.06.2021	26	1,666666667	1,677083333	test62	test1-abc	0	0
37										22.06.2021	26	1,6875	1,697916667	test64	test1-abc	0	0
38										22.06.2021	26	1,708333333	1,71875	test66	test1-abc	0	0
39										22.06.2021	26	1,729166667	1,739583333	test68	test1-abc	0	0
40										22.06.2021	26	1,75	1,760416667	test70	test1-abc	0	0
41										22.06.2021	26	1,770833333	1,78125	test72	test1-abc	0	0
42										22.06.2021	26	1,791666667	1,802083333	test74	test1-abc	0	0
43										22.06.2021	26	1,8125	1,822916667	test76	test1-abc	0	0
44										22.06.2021	26	1,833333333	1,84375	test78	test1-abc	0	0
45										22.06.2021	26	1,854166667	1,864583333	test80	test1-abc	0	0
46										22.06.2021	26	1,875	1,885416667	test82	test1-abc	0	0
47										22.06.2021	26	1,895833333	1,90625	test84	test1-abc	0	0
48										22.06.2021	26	1,916666667	1,927083333	test86	test1-abc	0	0
49										22.06.2021	26	1,9375	1,947916667	test88	test1-abc	0	0
50										22.06.2021	26	1,958333333	1,96875	test90	test1-abc	0	0
51										22.06.2021	26	1,979166667	1,989583333	test92	test1-abc	0	0
52
53
54
55
56
Sheet2

Cell Formulas
Range	Formula
J3:Q3	J3	=IF(J4="","",Table1[#Headers])
J4:Q51	J4	=FILTER(Table1,(INDIRECT("Table1["&F5&"]")=Sheet2!F6)*(Table1[Date]>=Sheet2!F8)*(Table1[Date]<=Sheet2!G8),"")
Dynamic array formulas.

NOW IF YOU WISH YOU CAN ADD ANOTHER WEEK AS ANOTHER TABLE and amend =indirect() function properly (with additional field in the selection menu)
or simply keep it under 1 table which would be easily filterable.

This ofc, can be very easy amended, corrected, changed.
I simply did not included this since I am not sure if this would be to your liking.