My Projects 1 and 2 tab contains random projects.
The Schedule tab is where I need for it to search through the Projects 1 and 2 tab, and find matching projects to the dates, and display certain data.
I have manually copied over the data I want displayed automatically by inputting data into the Projects tab. I.E. On 2/14, I want to search the Projects 1 and 2 tab for any jobs starting on 2/14 and display the NAME, CREW, and ESTIMATOR in the Schedule tab (as shown). The project carries over until the "completed" date. I want to do this for each date listed on the schedule. How can this be done? I also need to SUM the project totals going this current week. I have this function working in google sheets, but cannot get to work in excel.
=MAP(D1:U1, LAMBDA(dateval, IF(ISDATE(dateval), IFERROR(QUERY({'Projects 1'!$A$2:$H;'Projects 2'!$A$2:$H}, "select Col1 where Col1 is not null and Col4 <= date '" & TEXT(dateval,"yyyy-mm-dd") & "' and Col5 >= date '" & TEXT(dateval,"yyyy-mm-dd") & "' ",0 )), IF(ISDATE(OFFSET(dateval,0,-1)), IFERROR(QUERY({'Projects 1'!$A$2:$H;'Projects 2'!$A$2:$H}, "select Col7 where Col1 is not null and Col4 <= date '" & TEXT(OFFSET(dateval,0,-1),"yyyy-mm-dd") & "' and Col5 >= date '" & TEXT(OFFSET(dateval,0,-1),"yyyy-mm-dd") & "' ",0 )), IFERROR(QUERY({'Projects 1'!$A$2:$H;'Projects 2'!$A$2:$H}, "select Col8 where Col1 is not null and Col4 <= date '" & TEXT(OFFSET(dateval,0,-2),"yyyy-mm-dd") & "' and Col5 >= date '" & TEXT(OFFSET(dateval,0,-2),"yyyy-mm-dd") & "' ",0 ))))))
