Populating Schedule

Copper Contributor

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 ))))))

2 Replies


2023-02-18 no access.jpg

If it's too difficult or inappropriate to share the file via SharePoint, considering putting it in DropBox or OneDrive, or (if the file is not too large) edit the post and attach the file (look for a hyperlink to browse for files).

I have deleted the link, and attached the file instead. Thank you.