Mar 17 2020 06:52 AM
Hello!
I have modified a calendar template to accomodate my team's projects. The current template we're using only auto-populates the calendar with individual dates, and we're looking to have it show the entire date range.
(Example: Instead of having to write Project A start date, Project A end date as two separate instances, we want to convert the table to include project start/end date, and have the project name listed for all dates in that range on the subsequent calendar tabs)
The current formula showing on on the calendar dates is: =IF(ISNA(MATCH(E4&"_"&$A6,Projects!$A:$A,0)),"",INDEX(Projects!$C:$C,MATCH(E4&"_"&$A6,Projects!$A:$A,0)))
The formula for the reference number associated to each project record is probably what needs to be adjusted (I think). It currently reads: =IF(E6="","-",E6&"_"&COUNTIF(E$6:E6,E6)) *E6 is the date associated to the record
In a nutshell, how do I turn that reference formula (from Projects Tab, column A) to count a date range? So If I were to add a second column for each project to show start date and end date, how can the reference formula capture both dates and show the project listed for all included days on the calendar?
Bonus Points:
I'm also trying to create a seperate view of the data given in this project list that will show overlapping channels and their corresponding dates in a chart style (see Annual Calendar tab), where the columns are dates and rows are project channels (that were written on the project list). If anyone has pointers for how to pull this data to auto-populate these bar charts, that would also be very helpful.
Thank you in advance for saving me from future headaches!
Mar 17 2020 02:55 PM
IMHO, such logic doesn't work. If, for example, you have two projects P1 and P2 both having the reference ended by _1, they both are to be placed into the first row of the day if start/end day overlapping.
If your Excel support dynamic arrays you may use FILTER function like
=IFERROR(FILTER(Projects!$C:$C,(D4<=Projects!$F:$F)*(D4>=Projects!$E:$E)),"")
assuming each project has start and end dates.