Hi all,
I am trying to create a summary of a sort of calendar I have made.
The calendar is a daily view and runs landscape, with the days/dates (custom formatted to just show the day i.e. d) at the top and then with the task/meeting categories to the left of this (column A).
I want to summarise the calendar in a monthly view so that all the events for that month are listed out on a new sheet.
The way the calendar is filled in is by placing a 1 in the cell that corresponds to the event and the day (using conditional formatting – a non-blank cell will change the font and fill colour to match so it just looks like a filled cell, but really it has a 1 in).
Basically, I know I need a formula that says ‘if the cell is non-blank, return me the value of column A and the value in row 6, but only if the value in row 6 is in a certain month’
I would like it to be a continuous list of events and have the dates next to them as well, so if someone looked at the sheet they could quickly see what was going on that month.
Hopefully, you can all see the screenshots below, if not please help me work out how to do it properly! The first pic is the calendar and the second is my desired outcome!
Thank you all so much
Sam
Excel 2010 32 bit
<tbody>
</tbody>Excel 2010 32 bit
<tbody>
</tbody>
I am trying to create a summary of a sort of calendar I have made.
The calendar is a daily view and runs landscape, with the days/dates (custom formatted to just show the day i.e. d) at the top and then with the task/meeting categories to the left of this (column A).
I want to summarise the calendar in a monthly view so that all the events for that month are listed out on a new sheet.
The way the calendar is filled in is by placing a 1 in the cell that corresponds to the event and the day (using conditional formatting – a non-blank cell will change the font and fill colour to match so it just looks like a filled cell, but really it has a 1 in).
Basically, I know I need a formula that says ‘if the cell is non-blank, return me the value of column A and the value in row 6, but only if the value in row 6 is in a certain month’
I would like it to be a continuous list of events and have the dates next to them as well, so if someone looked at the sheet they could quickly see what was going on that month.
Hopefully, you can all see the screenshots below, if not please help me work out how to do it properly! The first pic is the calendar and the second is my desired outcome!
Thank you all so much
Sam
Excel 2010 32 bit
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | BO | BP | BQ | BR | BS | BT | BU | BV | BW | BX | BY | BZ | CA | CB | CC | CD | CE | CF | CG | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | 1 Oct 2018 | 8 Oct 2018 | 15 Oct 2018 | 22 Oct 2018 | 29 Oct 2018 | 5 Nov 2018 | 12 Nov 2018 | 19 Nov 2018 | 26 Nov 2018 | 3 Dec 2018 | 10 Dec 2018 | 17 Dec 2018 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |
| 7 | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | |
| 8 | Events | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | Meeting 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | Task 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | Sub-task 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 13 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 14 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 15 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 16 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 17 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 18 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 19 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 20 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 21 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 22 |
<tbody>
</tbody>
| J | K | |
|---|---|---|
| 1 | October | |
| 2 | ||
| 3 | Meeting 1 | 02/10/2018 |
| 4 | Event 1 | 06/10/2018 |
| 5 | Meeting 2 | 09/10/2018 |
| 6 | Event 2 | 25/10/2018 |
<tbody>
</tbody>
Sheet1
