Complicated project management & calendar synergy

somethingz

New Member
Joined
Oct 28, 2021
Messages
5
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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! :)
 

Attachments

  • Skærmbillede 2021-10-31 092753.png
    Skærmbillede 2021-10-31 092753.png
    44 KB · Views: 15
  • Skærmbillede 2021-10-31 092802.png
    Skærmbillede 2021-10-31 092802.png
    23.1 KB · Views: 16

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
EFGHIJK
1
2DeadlineProjectProject AddressName2nd NameFor Calendar
3 
410/1/2021Project 1Address 1Name A 1Name B 1Project 1-Address 1-Name A 1-Name B 1
510/1/2021Project 2Address 2Name A 2Name B 2Project 2-Address 2-Name A 2-Name B 2
610/1/2021Project 3Address 3Name A 3Name B 3Project 3-Address 3-Name A 3-Name B 3
710/1/2021Project 4Address 4Name A 4Name B 4Project 4-Address 4-Name A 4-Name B 4
810/1/2021Project 5Address 5Name A 5Name B 5Project 5-Address 5-Name A 5-Name B 5
910/2/2021Project 6Address 6Name A 6Project 6-Address 6-Name A 6-
1010/3/2021Project 7Address 7Name A 7Project 7-Address 7-Name A 7-
1110/4/2021Project 8Address 8Name A 8Name B 6Project 8-Address 8-Name A 8-Name B 6
1210/4/2021Project 9Address 9Name A 9Name B 7Project 9-Address 9-Name A 9-Name B 7
1310/5/2021Project 10Address 10Name A 10Name B 8Project 10-Address 10-Name A 10-Name B 8
1410/6/2021Project 11Address 11Name A 11Name B 9Project 11-Address 11-Name A 11-Name B 9
1510/7/2021Project 12Address 12Name A 12Project 12-Address 12-Name A 12-
16 
Projects
Cell Formulas
RangeFormula
K3:K16K3=IF(E3="","",F3&"-"&G3&"-"&H3&"-"&I3)



Cell Formulas
RangeFormula
B23:H25,B12:H21B12=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)),"")
 
Solution

somethingz

New Member
Joined
Oct 28, 2021
Messages
5
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
Hi Toadstool, wow! Thank you very much for the very indepth help and explanation!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,573
Members
430,367
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top