Export SMMS opportunities to Excel Online: transcript
This transcript was generated automatically from the recording and may contain errors. It has not been manually verified. Time links return to that point in the video.
00:00:01 In this video, we're going to demonstrate how to take a list of opportunities from SMMS for a particular operation, and using the online version of Excel, convert that list into an Excel table that can be filtered and adjusted so that it's a useful information. There is a separate video for those that want to use the desktop version of Excel. But go into SMMS, log in, click on Opportunities,
00:00:31 at the top, then click on opportunities. Then in the operation column type in the
00:00:38 name of the operation for which we're going to make a list. I'm going to pick
00:00:44 one that has a lot of opportunities. In this particular case we have 121
00:00:49 opportunities. It would be nice if we could make a filter, filtered list by
00:00:59 just saying, give me all the available ones. But unfortunately, that list doesn't exclude
00:01:06 those that are already taken and that really aren't available. The other filters, including
00:01:11 the reserve filter, that one works. The status filter, that one works. So you can say, I want
00:01:21 to see the active hidden, and this operation doesn't have any, or I can see the ones that
00:01:29 active if they needed a new one and so these filters works and even the
00:01:35 service type works I just want senior service I don't want young service
00:01:39 missionaries in this list but to make a spreadsheet what you have to do is go
00:01:45 over here to this download icon and click on it and then it will save a comma
00:01:52 separated values file. Because we're going to edit this online we want to save
00:01:59 this in OneDrive and so I go to my OneDrive and I've created a
00:02:07 temporary folder where I want to save this. You can use the name it gives you. I'm
00:02:13 just going to say Las Vegas Family Search here and then I'll get rid of this
00:02:20 little timestamp and save it. Now this is saved in OneDrive that I can come back to when I can get
00:02:26 into OneDrive. The easiest way to get into Excel or OneDrive is to go into your email
00:02:34 online and then click on this tic-tac-toe menu and then go into Excel or OneDrive. I find OneDrive
00:02:43 I've the easiest way to get to my file because when I click on my files it puts
00:02:49 that folder I just edited it most recently right on top and I can open that
00:02:54 up and then I can open up the spreadsheet. Now by default when you open
00:03:02 up anything on the cloud it's going to be in in view only mode so you have to
00:03:10 click on editing so that and then you click on convert the desktop app version
00:03:18 of this video is a separate video so we're gonna click convert the conversion
00:03:22 that this process is actually does converted into an Excel spreadsheet so
00:03:30 it's now an Excel spreadsheet now there are some columns that are just in the
00:03:35 way and so like the purpose I hit the letter and down then holding the control
00:03:42 key I do the same thing for these columns then I right click here and and I'm
00:03:50 gonna do it until it works but I right click here and I just say okay I want to
00:03:56 delete that column and I should be able to select more than one column and and
00:04:02 delete those columns. Now you could choose hide the columns and it would leave the column there.
00:04:07 I also don't see I need the country. I don't need the GPS locations so I can delete those columns.
00:04:15 And then the last three columns are not used at all and so we're going to delete those columns.
00:04:23 Now we have a spreadsheet but some of the columns aren't wide enough so we come up here we highlight
00:04:28 like the whole table by clicking up in the upper left-hand corner there and then
00:04:33 we go into format. Now sometimes when you come into the online version it's not
00:04:39 going to show it's going to show the single line ribbon and you're not going
00:04:42 to have as much detail. I prefer the classic ribbon because then I can see
00:04:48 the labels on these options. But I go into format, I go into auto-fit com
00:04:53 with. And now this is a little more readable. Now here's the easy part. To convert this into
00:05:01 a table, you go to the top left cell A1, column A row one, and then you go to the bottom right
00:05:08 hand cell. In this case, it's going to be row 94 and column W. I've now highlighted the data
00:05:20 data that's in the table and then that's in the spreadsheet and then I say format as
00:05:25 a table. You can pick any style that you want but this is what I'm going to use. Yes, the
00:05:33 top line is the header line and so my table has header should be checked. Click OK. Now
00:05:40 you've got a table. Well, what is that accomplished? Well, one thing that it's accomplished is
00:05:46 is that now you can filter, and you can filter efficiently.
00:05:50 For example, if you say I just go over here to column U,
00:05:55 which is the assignment status, and say I really just
00:05:57 want to see those that are unassigned.
00:06:00 I don't want to see anything else.
00:06:04 I'm looking for an opportunity number
00:06:06 that I can give to somebody to use, so I click Apply.
00:06:10 And then I come over here to the availability common,
00:06:13 and I want to say I don't want to see the reserved ones.
00:06:15 I just want to see those that are available and you will notice that this is a pretty
00:06:22 Slick table because we've taken
00:06:24 120
00:06:26 122 opportunities and put them into just a small list of those that are available and then you could go to the
00:06:34 opportunity ID
00:06:35 Column and give away those numbers to people who want to apply for that opportunity
00:06:40 Now, if you are in the situation where you want to do some coordination, you probably
00:06:48 want to see more than just the unassigned.
00:06:52 You want to see the inactive as well as the unassigned so that you can look at what might
00:07:02 be converted from active to inactive or vice versa.
00:07:07 You also have the data that can be used to show the address for each opportunity.
00:07:14 If you have an operation that has more than one location, each opportunity should have
00:07:20 the right physical address.
00:07:22 You also may want to look at the recommended service length.
00:07:26 And maybe some of these 24-month missions should be turned to 12 months.
00:07:31 and maybe there might be here some that don't have years, maybe those could be six-month missions.
00:07:36 Same thing with hours per week.
00:07:39 The operation manager may say, we're not having too many people work 40 hours a week.
00:07:43 We may want to turn those into something different.
00:07:48 But this gives you the opportunity to look at all of the opportunities and filter them
00:07:53 any way that you like. And that way you can review these and make adjustments to
00:08:01 the number of opportunities that are available. Some of the opportunities
00:08:05 might need to be made inactive. Some of the opportunities might need to have
00:08:10 their hours adjusted because there should be at least eight hours in the hours
00:08:13 per week. But this allows you to filter that and look at things that could be
00:08:20 modified so I hope that's helpful for you and call me or contact me and email if
00:08:27 you have questions.