Dynamic Reporting Calendar in Excel

nikhil0311

Board Regular

Book1
ABCDEFG
1Input Data
2DateBDFrequencyActivity
3Friday, November 1, 20191WeeklyMgmt Report
4Monday, November 4, 20194WeeklyRevenue Report
6Tuesday, November 5, 20195Bi WeeklyForecasting
7Tuesday, November 5, 20195Bi WeeklyIncome Statement
8Wednesday, November 6, 20196WeeklyClient Meeting
9Thursday, November 7, 20197WeeklyMgmt Report APAC
10Friday, November 8, 20198MonthlyBudget Activity
11Friday, November 8, 20198MonthlyBudget Activity APAC
12Friday, November 8, 20198MonthlyBudget Activity EMEA
13Friday, November 8, 20198MonthlyBudget Activity CANADA
14
15
16Output Final
17Business dayFriday, November 1, 2019Monday, November 4, 2019Tuesday, November 5, 2019Wednesday, November 6, 2019Thursday, November 7, 2019Friday, November 8, 2019
18Mgmt ReportRevenue ReportForecastingClient MeetingMgmt Report APACBudget Activity
20Budget Activity EMEA
Sheet1

Range A2 to D13 is the input data where we have different dates and activities
What I am looking for is the activites to be listed out in different columns as per the date
for example if we look at the input data i.e. A4:A5, the data is november 4th Monday, based on this we are looking to get the output below cell C17
Thanks in advance. Please let me know if you havd any questions

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
In B18 copied down & across
=IFERROR(INDEX(\$D\$3:\$D\$13,AGGREGATE(15,6,(ROW(\$D\$3:\$D\$13)-ROW(\$D\$3)+1)/(\$A\$3:\$A\$13=B\$17),ROWS(B\$18:B18))),"")

Hi Fluff. Thanks a lot for your help. Appreciate the quick response. If i want the same output in the below calendar, how can i do that, could you please help?

Book1
BCDEFGH
25November
26SundayMondayTuesdayWednesdayThursdayFridaySaturday
2712
28Mgmt Report
29
30
31
32
333456789
34Revenue ReportForecastingClient MeetingMgmt Report APACBudget Activity
36Budget Activity EMEA
38
3910111213141516
40
41
42
43
44
4517181920212223
46
47
48
49
50
5124252627282930
52
53
54
55
56
57
58
59
60
61
62
Sheet1

Are the value in rows 27,33 etc actual dates, or just numbers?

actual dates

Book1
BCDEFGH
26SundayMondayTuesdayWednesdayThursdayFridaySaturday
2701/11/201902/11/2019
28 Mgmt Report
29
30
31
32
3303/11/201904/11/201905/11/201906/11/201907/11/201908/11/201909/11/2019
34 Revenue ReportForecastingClient MeetingMgmt Report APACBudget Activity
36Budget Activity EMEA
38
Database
Cell Formulas
RangeFormula
B28=IFERROR(INDEX(\$D\$3:\$D\$13,AGGREGATE(15,6,(ROW(\$D\$3:\$D\$13)-ROW(\$D\$3)+1)/(\$A\$3:\$A\$13=B\$27),ROWS(B\$28:B28))),"")
B34=IFERROR(INDEX(\$D\$3:\$D\$13,AGGREGATE(15,6,(ROW(\$D\$3:\$D\$13)-ROW(\$D\$3)+1)/(\$A\$3:\$A\$13=B\$33),ROWS(B\$34:B34))),"")

Last edited:

Replies
4
Views
400
Replies
2
Views
269
Replies
1
Views
354
Replies
1
Views
264
Replies
1
Views
242

1,219,792
Messages
6,150,292
Members
450,949
Latest member
faizanmalik10

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.

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

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