Dynamic Reporting Calendar in Excel

nikhil0311

Board Regular
Joined
May 3, 2013
Messages
181
Office Version
  1. 2013
Platform
  1. Windows

Book1
ABCDEFG
1Input Data
2DateBDFrequencyActivity
3Friday, November 1, 20191WeeklyMgmt Report
4Monday, November 4, 20194WeeklyRevenue Report
5Monday, November 4, 20194WeeklyTrade 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
19Trade ReportIncome StatementBudget Activity APAC
20Budget Activity EMEA
21Budget Activity CANADA
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,208
Office Version
  1. 365
Platform
  1. Windows
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))),"")
 

nikhil0311

Board Regular
Joined
May 3, 2013
Messages
181
Office Version
  1. 2013
Platform
  1. Windows
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
35Trade ReportIncome StatementBudget Activity APAC
36Budget Activity EMEA
37Budget Activity CANADA
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,208
Office Version
  1. 365
Platform
  1. Windows
Are the value in rows 27,33 etc actual dates, or just numbers?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,208
Office Version
  1. 365
Platform
  1. Windows
How about


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
35Trade ReportIncome StatementBudget Activity APAC
36Budget Activity EMEA
37Budget Activity CANADA
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:
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,008
Messages
5,834,853
Members
430,324
Latest member
bosphoruskid

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