Best way to track Sacrament Meeting talks

What are some best practices to track who has given talks?

wrigjef wrote:What are some best practices to track who has given talks?

The attached simple spreadsheet application is all I use. Shows speakers by date and also how long it has been since they spoke (calculated formula used).

The method working its way through Vegas wards is a google spreadsheet that all the bishopric members of that ward can see concurrently.
There is a count query that updates in real time the last time the adults and the youth have spoken.
We track the prayers and the conducting rotation on the same sheet --we haven't needed the query mechanism for prayers.

We would never go back to non-cloud solutions, it's so important for us to all be seeing it in meetings and then during the week for the bishopric member who is making the assignments to have a single list that is always up to date (i.e. that we never have to synchronize).

Here's a version of the spreadsheet for reference if you want to see the query mechanism:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Sorry, that link lets you see but not duplicate now. Here is a version that currently saves a copy:

https://docs.google.com/spreadsheets/d/ ... Lgz5Y/copy

Are there instructions on importing and updating ward members? Thanks...it looks like a great program...

The theory of this would be that it would live in google docs so that everyone in the bishopric could see it in real time. So if you try exporting it to Excel, I don't know if the SQL expressions will work. If you prefer Excel, you might be able to convert it to a vllookup?

In google sheets the ranges are under Data>Named Ranges... From there, just click "+ Add a range" after you have created the 2018 tab. Someone may upload their updated if they have time.

You can do an initial population of the table by exporting a csv file from MLS or LCR and taking the names from there. When I did it, I wanted last name first so I could sort lists and find people more easily. I just used a concatenate function to generate a whole ward's worth of names in last comma first order. The query is supposed to handle last first or first first, though if you sort differently.

Because I took out people that we never were going to ask to speak, I didn't just reimport the list each time I updated because that would restore them to the list. Every once and a while, I or the execsec would just look at the move-in move-out report and the youth birthday reports (i.e. action and interview list) and add members to the appropriate table.

I'm remembering that MLS let me export birthdays easier than LCR but that may have changed and you may not care about the age of the youth on this sheet. (Our bishop at the time wanted the older youth speaking more frequently so that was useful for his tracking.)

Adding new years was a problem so I've received periodic requests to update the spreadsheet so it doesn't break every year. Here is a new version where the tabs aren't labeled 2020, 2021, etc, just current year, last year.

https://docs.google.com/spreadsheets/d/ ... Lgz5Y/copy

Please note Meetinghouse Technology Policy document (which you may or may not be able to access depend on your calling) prohibits using membership data in third-party applications or storing it in a cloud-based service. As it stands now, that policy would likely prohibit pulling any data from lds.org for such an application.

Dean is referring to this document, I believe: https://www.lds.org/help/support/meetin ... y?lang=eng

4.9.4 The use of cloud-based services for storing and/or backing up MLS or any membership related data is prohibited.

A partial list of members and certain birthdays may be "membership related data" subject to the policy even if it is not obtained in a meetinghouse depending on how you see it.

quandmeme wrote:Dean is referring to this document, I believe: https://www.lds.org/help/support/meetin ... y?lang=eng

4.9.4 The use of cloud-based services for storing and/or backing up MLS or any membership related data is prohibited.

A partial list of members and certain birthdays may be "membership related data" subject to the policy even if it is not obtained in a meetinghouse depending on how you see it.

Spirit of the law...

Hi, I'm having problems getting the "youth" tab to work. Even when I add one of the youth names in your lists it showing no date for last talk and the hidden column F is coming back 12/30/1899. Any tips to get this to work? This is a great tool, BTW!

quandmeme wrote: Tue Aug 14, 2018 10:38 am Adding new years was a problem so I've received periodic requests to update the spreadsheet so it doesn't break every year. Here is a new version where the tabs aren't labeled 2020, 2021, etc, just current year, last year.

https://docs.google.com/spreadsheets/d/ ... Lgz5Y/copy

This is a really great sheet, thank you so much for sharing! I'm hoping to implement this in our ward.

One question though (5 years later!), I don't see how it helps with the labels currentYear, 1YearAgo etc. versus 2021, 2022 etc. Doesn't this mean one has to copy paste all sheets every year, or change name of all the sheets? Plus you have to add another column in the queries anyways (unless you want to lose the data from the first recorded year)?

Or is there something I'm missing? I'd be happy if you or anyone would enlighten me:)

I've been tracking who spoke in Sacrament meeting for several years (bishopric counselor) in an Excel spreadsheet. It was a bit complicated and used a macro to perform the tedious tasks. To make it easier for the bishop and other counselor I cleaned it up in Office 365 to make it really user friendly. It uses functions not supported by Google Sheets, but you can use it in a free Office 365 account (I think).

If you want to try it, you can download it here from OneDrive. If you find it useful (or it would be if something was different), please let me know.

Robert
Memphis, TN

I have updated the link to the spreadsheet and added a sample file and simple PowerPoint tutorial. Thanks for the feedback!

- Sacrament Speaker Tracker spreadsheet
- Sample File
- PowerPoint tutorial

If you don't have a place to share your file with your leaders, send me a note and I can set up a shareable file for you.

Robert
sacramentmeetingspeakertracker@gmail.com

ropaswan wrote: Sat Apr 29, 2023 6:23 am I have updated the link to the spreadsheet and added a sample file and simple PowerPoint tutorial. Thanks for the feedback!

- Sacrament Speaker Tracker spreadsheet
- Sample File
- PowerPoint tutorial

If you don't have a place to share your file with your leaders, send me a note and I can set up a shareable file for you.

Robert
sacramentmeetingspeakertracker@gmail.com

Wow, I must say, this sheet is incredible! I hope a lot of wards are using this! So clean and simple to use!

We're probably going to implement this now for the coming year.

I just have a question, how do you keep track of topics alongside of this? In our current google spreadsheet, we write down the topics in the cell right next to the name of the speaker.
Do you use a separate sheet for topics or keep track of it somewhere else?
I tested out using comments in this sheet, but I'm not sure how easy it will be to do it that way.

If you have any suggestions, that would be great. Otherwise, I'm sure we'll figure something out, as I'm really keen on using your sheet!

-Knut

Hi! Sorry for the delay; I didn't get a notice and my email was chunked. Ijust happened to come back today to look.

I have made several changes since the one I posted above, but hadn't included Topics. I am open to suggestions. I see your email now and will reply there.

Thanks,
Robert

Reposting to update file links. To download the file(s):
- Click the link to open the file
- Click File
- Click Save As
- Click Download a Copy
- The file will be in your Downloads folder

- Sacrament Speaker Tracker spreadsheet
- PowerPoint tutorial

Some changes since the previous post:
- Selectable date formats
- Added a second page to the Print by Date/Name lists
- Added support for Topics
- Added tabs for how to use the spreadsheet

If you don't have a place to share your file with your leaders, send me a note and I can set up a shareable file for you. As always, please let me know if you find any bugs or have any questions.

Robert
sacramentmeetingspeakertracker@gmail.com

I have created a Google Sheets version of my Speaker Tracker spreadsheet. Please send me an email if you are interested in testing it before I make it generally available.

Thanks
Robert
sacramentmeetingspeakertracker@gmail.com

I posted a link to the latest version of Robert's sacrament speaker tracker here.

Huge thanks to Robert for his collaboration and blessing for me to add some features to what he created. You rock, Robert!

Steven Van Gemert