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>
[TD="class: xl68, width: 75"]Date[/TD]
[TD="class: xl68, width: 177"]Speaker Full Name In[/TD]
[TD="class: xl68, width: 181"]Congregagation In[/TD]
[TD="class: xl68, width: 118"]Talk Number In[/TD]
[TD="class: xl68, width: 148"]Speaker 1 Out Full Name[/TD]
[TD="class: xl68, width: 168"]Congregagation 1 Out[/TD]
[TD="class: xl68, width: 81"]Talk 1 Out[/TD]
[TD="class: xl68, width: 178"]Speaker 2 Out Full Name[/TD]
[TD="class: xl68, width: 158"]Congregagation 2 Out[/TD]
[TD="class: xl68, width: 64"]Talk 2 Out[/TD]
[TD="class: xl68, width: 115"]Reader[/TD]
[TD="class: xl68, width: 115"]Hospitality[/TD]
[TD="class: xl68, width: 115"]Chairman[/TD]
[TD="class: xl66, align: right"]25/11/2018[/TD]
[TD="class: xl67"]85[/TD]
[TD="class: xl67"]51[/TD]
[TD="class: xl66, align: right"]02/12/2018[/TD]
[TD="class: xl67"]156[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]09/12/2018[/TD]
[TD="class: xl67"]9[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]16/12/2018[/TD]
[TD="class: xl67"]126[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]23/12/2018[/TD]
[TD="class: xl67"]134[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]30/12/2018[/TD]
[TD="class: xl67"]94[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]06/01/2019[/TD]
[TD="class: xl67"]84[/TD]
[TD="class: xl67"]63[/TD]
</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>
[TD="class: xl68, width: 201, colspan: 2"]Philip Newbold[/TD]
[TD="class: xl67, width: 212, colspan: 3"]Assignments[/TD]
[TD="class: xl63, width: 110"]25 Nov, 2018[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]23 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]30 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"]Talk 94[/TD]
[TD="class: xl66"] at Bolsover[/TD]
[TD="class: xl63, width: 110"]27 Jan, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]03 Mar, 2019[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]07 Apr, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
</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:
Donald Williams | Long Eaton | Brian Wright | Long Eaton | David Hill | Brian Wright | Philip Newbold | |||
Trevor Walker | Bolsover | Colin Barnes | Ben Elliott | Ben Elliott | |||||
Craig O'Connor | Bolsover | Ben Elliott | Stephen Hawkins | Colin Barnes | |||||
Colin Barnes | Bolsover | David Hill | Philip Newbold | Brian Wright | |||||
Mark Berry | Bolsover | Philip Newbold | Craig O'Connor | Stephen Hawkins | |||||
Philip Newbold | Bolsover | Ben Elliott | Brian Wright | Darren Saywell | |||||
Andrew Sheppard | Hucknall | Brian Wright | Hucknall | 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>
[TD="class: xl68, width: 75"]Date[/TD]
[TD="class: xl68, width: 177"]Speaker Full Name In[/TD]
[TD="class: xl68, width: 181"]Congregagation In[/TD]
[TD="class: xl68, width: 118"]Talk Number In[/TD]
[TD="class: xl68, width: 148"]Speaker 1 Out Full Name[/TD]
[TD="class: xl68, width: 168"]Congregagation 1 Out[/TD]
[TD="class: xl68, width: 81"]Talk 1 Out[/TD]
[TD="class: xl68, width: 178"]Speaker 2 Out Full Name[/TD]
[TD="class: xl68, width: 158"]Congregagation 2 Out[/TD]
[TD="class: xl68, width: 64"]Talk 2 Out[/TD]
[TD="class: xl68, width: 115"]Reader[/TD]
[TD="class: xl68, width: 115"]Hospitality[/TD]
[TD="class: xl68, width: 115"]Chairman[/TD]
[TD="class: xl66, align: right"]25/11/2018[/TD]
[TD="class: xl67"]85[/TD]
[TD="class: xl67"]51[/TD]
[TD="class: xl66, align: right"]02/12/2018[/TD]
[TD="class: xl67"]156[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]09/12/2018[/TD]
[TD="class: xl67"]9[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]16/12/2018[/TD]
[TD="class: xl67"]126[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]23/12/2018[/TD]
[TD="class: xl67"]134[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]30/12/2018[/TD]
[TD="class: xl67"]94[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]06/01/2019[/TD]
[TD="class: xl67"]84[/TD]
[TD="class: xl67"]63[/TD]
</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>
[TD="class: xl68, width: 201, colspan: 2"]Philip Newbold[/TD]
[TD="class: xl67, width: 212, colspan: 3"]Assignments[/TD]
[TD="class: xl63, width: 110"]25 Nov, 2018[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]23 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]30 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"]Talk 94[/TD]
[TD="class: xl66"] at Bolsover[/TD]
[TD="class: xl63, width: 110"]27 Jan, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]03 Mar, 2019[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
[TD="class: xl63, width: 110"]07 Apr, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]
</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