Complicated project management & calendar synergy

[somethingz]

Hi! I have this complicated idea that I want to figure out but I'm not sure if it's feasable or if I should think of some other way to do it.
Currently I have a sheet in which I want to be able to type in deadline, project ID, project address, task handler etc. For each project I type in this information.

Then I want a calendar on another sheet that copies deadline, project ID, project address, task handler in to the calendar on the date of the deadline.
Currently I've found a free calendar template which shows week days as a header in the columns (i.e. B10 = monday, C10 = tuesday etc) and the date of the month in the rows (ill add an image).
I've figure out how to insert the information to a cell in the calendar using index and match function. However, since my calendar moves horizontally in the week and then vertically week by week I have no clue on how to make it so that I can type in the formula in one cell and copy it to all cells without manually having to type it in. Also, most of my deadlines will be fridays but some deadlines are on other weekdays so the formula has to be able to match the information with any weekday not just on fridays. I hopy my question makes sense!

 

[Toadstool]

Hi Somethingz,

I note your template is copyright so I've just used fixed values to demonstrate but this will work with the results of that template. The template day of the week and day of the month are actually the full date but formatted as ddd and dd respectively.

The formula expects the cells are as your example in respect of the first day being in row 11, there being 10 rows for each day and then the next week number appears in row 22 (which is why there's lots of 11 in the formula).

Retrieving each column from the Project sheet would entail running the search again so to avoid a very long formula I've taken the liberty of adding a column to your Projects sheet (column K) which pre-builds the data to be shown against the matching day.

Let me know if this works for you.

Somethingz.xlsx
	E	F	G	H	I	J	K
1
2	Deadline	Project	Project Address	Name	2nd Name		For Calendar
3
4	10/1/2021	Project 1	Address 1	Name A 1	Name B 1		Project 1-Address 1-Name A 1-Name B 1
5	10/1/2021	Project 2	Address 2	Name A 2	Name B 2		Project 2-Address 2-Name A 2-Name B 2
6	10/1/2021	Project 3	Address 3	Name A 3	Name B 3		Project 3-Address 3-Name A 3-Name B 3
7	10/1/2021	Project 4	Address 4	Name A 4	Name B 4		Project 4-Address 4-Name A 4-Name B 4
8	10/1/2021	Project 5	Address 5	Name A 5	Name B 5		Project 5-Address 5-Name A 5-Name B 5
9	10/2/2021	Project 6	Address 6	Name A 6			Project 6-Address 6-Name A 6-
10	10/3/2021	Project 7	Address 7	Name A 7			Project 7-Address 7-Name A 7-
11	10/4/2021	Project 8	Address 8	Name A 8	Name B 6		Project 8-Address 8-Name A 8-Name B 6
12	10/4/2021	Project 9	Address 9	Name A 9	Name B 7		Project 9-Address 9-Name A 9-Name B 7
13	10/5/2021	Project 10	Address 10	Name A 10	Name B 8		Project 10-Address 10-Name A 10-Name B 8
14	10/6/2021	Project 11	Address 11	Name A 11	Name B 9		Project 11-Address 11-Name A 11-Name B 9
15	10/7/2021	Project 12	Address 12	Name A 12			Project 12-Address 12-Name A 12-
16
Projects

Cell Formulas
Range	Formula
K3:K16	K3	=IF(E3="","",F3&"-"&G3&"-"&H3&"-"&I3)

Somethingz.xlsx
	A	B	C	D	E	F	G	H
1
7		Yearly Planning Calendar
8
9		October 2021
10		Mon	Tue	Wed	Thu	Fri	Sat	Sun
11						1	2	3
12						Project 1-Address 1-Name A 1-Name B 1	Project 6-Address 6-Name A 6-	Project 7-Address 7-Name A 7-
13						Project 2-Address 2-Name A 2-Name B 2
14						Project 3-Address 3-Name A 3-Name B 3
15						Project 4-Address 4-Name A 4-Name B 4
16						Project 5-Address 5-Name A 5-Name B 5
17
18
19
20
21
22		4	5	6	7	8	9	10
23		Project 8-Address 8-Name A 8-Name B 6	Project 10-Address 10-Name A 10-Name B 8	Project 11-Address 11-Name A 11-Name B 9	Project 12-Address 12-Name A 12-
24		Project 9-Address 9-Name A 9-Name B 7
25
Calendar

Cell Formulas
Range	Formula
B23:H25,B12:H21	B12	=IFERROR(INDEX(Projects!$K$3:$K$999,AGGREGATE(15,6,ROW(Projects!$E$3:$E$999)-ROW(Projects!$E$2)/((Projects!$E$3:$E$999<>"")*(Projects!$E$3:$E$999=INDEX($A$1:$ZZ$999,INT(ROW()/11)*11,COLUMN()))),ROW()-INT(ROW()/11)*11)),"")

 

[somethingz]

Hi Toadstool, wow! Thank you very much for the very indepth help and explanation!

 

[Toadstool]

Hi Toadstool, wow! Thank you very much for the very indepth help and explanation!

You're welcome!