I have the following information that I want to use to populate an excel monthly calendar
[TABLE="width: 538"]
<tbody>[TR]
[TD]stake out[/TD]
[TD="align: right"]Mon, Oct 02, 17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Mon, Oct 02, 17[/TD]
[/TR]
[TR]
[TD]excavate[/TD]
[TD="align: right"]Tue, Oct 03, 17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Thu, Oct 05, 17[/TD]
[/TR]
[TR]
[TD]layout/pour footings[/TD]
[TD="align: right"]Fri, Oct 06, 17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Tue, Oct 10, 17[/TD]
[/TR]
[TR]
[TD]weaping, drain, rock/gravel[/TD]
[TD="align: right"]Wed, Oct 11, 17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Thu, Oct 12, 17[/TD]
[/TR]
[TR]
[TD]build/pour walls[/TD]
[TD="align: right"]Fri, Oct 13, 17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Thu, Oct 19, 17[/TD]
[/TR]
[TR]
[TD]strip walls[/TD]
[TD="align: right"]Fri, Oct 20, 17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Mon, Oct 23, 17[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]
I have used the following formula to figure out actual days the work would be done (not working weekends)
=IF(AND(ISNUMBER($G5),NETWORKDAYS.INTL(N$3,N$3,1,Holiday!$A$2:$A$23),Schedule!N$3>=Schedule!$E5,Schedule!N$3<=Schedule!$G5),$C5,"")
I then use this formula to list all of the work being perform during that day
=IF(ROWS(N$17:N17)<=N$4,INDEX(N$5:N$13,SMALL(IF($N$5:$N$13="stake out",ROW($N$5:$N$13)-ROW($N$5)+1),ROWS(N$17:N17))),"")
Issue that I have is taking the "stake out" out of he formula and inserting a wildcard search that will find any text within the column and return it to my list.
Thank you for your assistance
[TABLE="width: 538"]
<tbody>[TR]
[TD]stake out[/TD]
[TD="align: right"]Mon, Oct 02, 17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Mon, Oct 02, 17[/TD]
[/TR]
[TR]
[TD]excavate[/TD]
[TD="align: right"]Tue, Oct 03, 17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Thu, Oct 05, 17[/TD]
[/TR]
[TR]
[TD]layout/pour footings[/TD]
[TD="align: right"]Fri, Oct 06, 17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Tue, Oct 10, 17[/TD]
[/TR]
[TR]
[TD]weaping, drain, rock/gravel[/TD]
[TD="align: right"]Wed, Oct 11, 17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Thu, Oct 12, 17[/TD]
[/TR]
[TR]
[TD]build/pour walls[/TD]
[TD="align: right"]Fri, Oct 13, 17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Thu, Oct 19, 17[/TD]
[/TR]
[TR]
[TD]strip walls[/TD]
[TD="align: right"]Fri, Oct 20, 17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Mon, Oct 23, 17[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]
I have used the following formula to figure out actual days the work would be done (not working weekends)
=IF(AND(ISNUMBER($G5),NETWORKDAYS.INTL(N$3,N$3,1,Holiday!$A$2:$A$23),Schedule!N$3>=Schedule!$E5,Schedule!N$3<=Schedule!$G5),$C5,"")
I then use this formula to list all of the work being perform during that day
=IF(ROWS(N$17:N17)<=N$4,INDEX(N$5:N$13,SMALL(IF($N$5:$N$13="stake out",ROW($N$5:$N$13)-ROW($N$5)+1),ROWS(N$17:N17))),"")
Issue that I have is taking the "stake out" out of he formula and inserting a wildcard search that will find any text within the column and return it to my list.
Thank you for your assistance