BenElliott
Board Regular
- Joined
- Jul 19, 2012
- Messages
- 144
I have been working on an Excel workbook for booking talks in various locations and a variety of talks. This is to replace a small database programme that only works on Windows systems (PC or laptop) and only allows me to look at 15 weeks worth of data at a time and doesn't allow me to easily access some of the data. The creator of this programme cannot help me to customise reports.
The Excel database I have created is as follows:
<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:6473;width:133pt" width="177"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:6144;width:126pt" width="168"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:6509;width:134pt" width="178"> <col style="mso-width-source:userset;mso-width-alt:5778;width:119pt" width="158"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4205; width:86pt" width="115" span="3"> </colgroup><tbody>
</tbody>
I would like to interogate this database to provide a report as this:
<colgroup><col style="mso-width-source:userset;mso-width-alt:4693;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3882;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:2944;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:3200;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2901;width:51pt" width="68"> </colgroup><tbody>
</tbody>
The trouble is, I can't see how. I can use advance filter but if I filter for name in Chairman it will not allow me to see the names in other columns. I can use array formulae to extract chairman, reader or speaker and give the date of each but I can't see how to give the above result.
I would be very grateful if anyone can see a way around the problem. I am using Office365 but haven't yet received the update to allow Sequence, Filter, etc.
Thanks for your help in anticipation.
Ben
The Excel database I have created is as follows:
Date | Speaker Full Name In | Congregagation In | Talk Number In | Speaker 1 Out Full Name | Congregagation 1 Out | Talk 1 Out | Speaker 2 Out Full Name | Congregagation 2 Out | Talk 2 Out | Reader | Hospitality | Chairman |
25/11/2018 | Donald Williams | Long Eaton | 85 | Brian Wright | Long Eaton | 51 | David Hill | Brian Wright | Philip Newbold | |||
02/12/2018 | Trevor Walker | Bolsover | 156 | Colin Barnes | Ben Elliott | Ben Elliott | ||||||
09/12/2018 | Craig O'Connor | Bolsover | 9 | Ben Elliott | Stephen Hawkins | Colin Barnes | ||||||
16/12/2018 | Colin Barnes | Bolsover | 126 | David Hill | Philip Newbold | Brian Wright | ||||||
23/12/2018 | Mark Berry | Bolsover | 134 | Philip Newbold | Craig O'Connor | Stephen Hawkins | ||||||
30/12/2018 | Philip Newbold | Bolsover | 94 | Ben Elliott | Brian Wright | Darren Saywell | ||||||
06/01/2019 | Andrew Sheppard | Hucknall | 84 | Brian Wright | Hucknall | 63 | Darren Saywell | Ben Elliott | Trevor Walker |
<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:6473;width:133pt" width="177"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:6144;width:126pt" width="168"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:6509;width:134pt" width="178"> <col style="mso-width-source:userset;mso-width-alt:5778;width:119pt" width="158"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4205; width:86pt" width="115" span="3"> </colgroup><tbody>
</tbody>
I would like to interogate this database to provide a report as this:
Philip Newbold | Assignments | |||
25 Nov, 2018 | Chairman | |||
23 Dec, 2018 | Reader | |||
30 Dec, 2018 | Talk 94 | at Bolsover | ||
27 Jan, 2019 | Chairman | |||
03 Mar, 2019 | Reader | |||
07 Apr, 2019 | Chairman |
<colgroup><col style="mso-width-source:userset;mso-width-alt:4693;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3882;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:2944;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:3200;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2901;width:51pt" width="68"> </colgroup><tbody>
</tbody>
The trouble is, I can't see how. I can use advance filter but if I filter for name in Chairman it will not allow me to see the names in other columns. I can use array formulae to extract chairman, reader or speaker and give the date of each but I can't see how to give the above result.
I would be very grateful if anyone can see a way around the problem. I am using Office365 but haven't yet received the update to allow Sequence, Filter, etc.
Thanks for your help in anticipation.
Ben