Month: March 2019

Do you know … Excel VLOOKUP?

I frequently need to correlate two sets of data – generally information about accounts, where the logon ID will be found in both data sets. I’ve imported my information into Access, defined a relationship between the two tables, and used a query to correlate my data. I’ve written quick scripts to pull the data into an associative array for correlation. These are not quick approaches.

Using the VLOOKUP function in Excel, you can search through data in rows and retrieve values from the record’s other columns. HLOOKUP provides the same function, but searches data in columns and retrieves values from the record’s other rows (Vertical Lookup and Horizontal Lookup).

Today, I have a list of individuals with their reporting structure and need to identify which accounts have Skype for Business provisioned.

The Skype user list is, unfortunately, comes from a different program.

To lookup user IDs from the first table against the Skype info in the second table, I use =VLOOKUP(B2,S4BInfo!A:B,2,FALSE)

The first parameter in the function is the information you want to find, the second parameter is the area where you’ll be looking for the data, the third parameter is the column in that range that you want to return when a match is found. The fourth parameter indicates if you want to find the closest match (‘TRUE’) or an exact match (‘FALSE’). So my formula says “find the value in B2 within columns A and B of the SBInfo tab. Return the value from column 2 of that range, and I want an identical match”.

Note that the third parameter column number may not match the column number in the sheet – if I used the range C:D from the table below, I would still want to return the data in column 2 because my target data is still the second column in the search range.

Fill down and I have a single table that contains both the reporting information that I needed and a column indicating if the individual has a Skype for Business account

 

Did you know … Teams can notify you when someone comes online?

If you send a chat message to someone who is offline, the message will be waiting for them when they sign in. But sometimes you want to know when someone becomes available. And Teams can do that for you!

In the “Chat” app, click on the “Contacts” tab. Click on the ellipses on the individual that you want to know when comes online and select “Notify when available”.

When the person is online, Teams will notify you.

You can turn the notification off the same way you turned it on.

You can view all of the accounts for which you receive availability notifications. Click on your avatar and select “Settings”.

Select “Notifications”.

Scroll down to the bottom of the notification settings and click on “Manage notifications”.

There’s the list – you can add new notifications here and remove no longer needed notifications.

 

GA133 HB 62 – Electric Vehicle Registration Fees

As the new transportation bill moves into the Senate, I wanted to calculate exactly how badly electric vehicle drivers are getting screwed. It depends, of course, on the MPG rating for the comparison vehicle. The best case for their assessment would be a vehicle with very bad mileage — say a big truck that gets 8 mpg. That truck has to drive 7,619 miles a year to pay the same amount of gas tax as the 100$ hybrid vehicle registration fee. It’s got to drive 15,238 miles in a year to achieve parity with the 200$ electric vehicle registration fee.

But how many vehicles get eight miles per gallon? The CAFE 2016 fleet average is 34.0 mpg. Comparing the electric vehicle registration fee to the fleet average, you’d have to drive 32,380 miles a year to hit parity on the 100$ registration fee. 64,761 miles to hit parity on the 200$ registration fee. There are plenty of cars that get better than 34 mpg — for a driver whose car gets 40 mpg to pay 200$ in gasoline tax, they’d be driving 76,190 miles a year!

According to the 2000 information from the Office of Highway Policy Information, Americans drive an average of 13,476 miles per year. An average driver needs to own a vehicle that gets worse than 8 mpg — 7.0749 mpg — to pay the same amount of gas tax as a driver who registers a Chevy Volt.

 

0.105 tax / gallon 0.105 tax / gallon 0.105 tax / gallon 0.105 tax / gallon 0.105 tax / gallon
8 mpg 15 mpg 25 mpg 34 mpg 40 mpg
0.013125 tax per mile 0.007 tax per mile 0.0042 tax per mile 0.003088 tax per mile 0.002625 tax per mile
fee Miles to pay same tax fee Miles to pay same tax fee Miles to pay same tax fee Miles to pay same tax fee Miles to pay same tax
200                          15,238.10 200                          28,571.43 200                          47,619.05 200                          64,761.90 200                          76,190.48
100                             7,619.05 100                          14,285.71 100                          23,809.52 100                          32,380.95 100                          38,095.24

Did you know … you can create templates in Microsoft Forms?

Microsoft Forms is a secure (and free!) alternative to SurveyMonkey, but it can be time consuming to repeatedly create essentially the same form. Fortunately, there are two ways you can use an existing form as a template for a new form.

Log into Microsoft Forms – if you’ve never used Forms before, you’ll need to create a new form. Click the “New Form” button.

Add a title and description to your form, then use the “Add question” button to build your form.

Click on “Forms” in the upper left-hand corner to return to the Forms home page. The form you created will appear under “My forms”. Click the ellipses in the upper right-hand corner of the form listing.

Select “Copy”

Volia – now there is a copy of the form.

You can also create a public form template. When editing your form, click the “Share” button.

Click “Share as a template”

Copy the URL that is displayed.

Anyone (even people outside of Windstream) can use a form template URL to create a new form. Simply access the URL and click “Duplicate it”.

There is not currently a public library of Forms templates (although I’ve suggested it on Microsoft’s UserVoice site), but if you create a template that would be useful for our organization, feel free to share it in the comments.

 

HTML Opacity v/s Alpha

I am building a page that allows employees to search for public MS Teams groups — for some reason, Teams uses a ‘starts with’ search, and our staff rarely manages to find the public Teams that are out there. I wanted the list of teams and descriptions to have a visible line separation, and a table border looked bad with the enterprise color scheme. I decided to use even/odd table rows to display a slightly lighter background color. I set an opacity on the background so the actual background image is still visible.

My font colors changed! The opacity applied to the text as well.

tr:nth-child(even) {background-color: rgb(52,52,52); opacity: 0.5;}

Instead of setting an opacity on the row, I added an alpha channel to the row background color without impacting the text within table cells.

tr:nth-child(even) {background-color: rgba(52,52,52,0.5);}

 

 

Do you know … how Teams and Skype interact?

How Teams and Skype interact can be confusing – where will messages be delivered and what should you expect to work between the two systems?

Channel conversations don’t exist in Skype, so you’ll need to use the Teams desktop, mobile, or web client to participate in channel discussions and to access files and tabs within channels.

Meetings are straight-forward too. You’ll need to use the Skype desktop, mobile, or web client to join Skype for Business meetings; you’ll need to use the Teams desktop, mobile, or web client to join Teams meetings.

Calls and Chat – this is where interoperability gets nuanced and potentially confusing! And you may need to know whether the person with whom you want to communicate is using Teams or Skype.

Island Mode

We are currently operating in what Microsoft calls “Island” mode – both Teams and Skype are an island, and communication stays on that island. Your presence in Teams comes from your Teams client, and your presence in Skype comes from your Skype client. If you send a message from Teams, it is delivered to the recipient in Teams. If you send a message from Skype, it is delivered to the recipient in Skype.

Teams and Skype Interoperability Within Windstream

Employees and contractors with @windstream.com accounts
This doesn’t work – calls and chats sent from Teams will be delivered to Teams.
Messages and calls originated in Teams are delivered to Teams.
Messages and calls originated in Skype are delivered to Skype.
This doesn’t work – calls and chats sent from Skype will be delivered to Skype.

Working In Island Mode

Because messages are only delivered within their island, you may get messages in either your Teams or Skype client – so should leave both running. If you forget to open your Teams client, you will get e-mail notices for missed chat messages (and any channel messages where you are at-mentioned) whenever you are inactive in Teams. You can customize your notification options if you want to get messages more or less frequently, or want to be notified of activity in any channels you follow.

** You will begin receiving e-mail notifications for missed Teams activity after you log into Teams for the first time. If you haven’t checked Teams out yet, visit https://teams.microsoft.com to try it out.

Teams Only

If you only use Skype to receive messages from colleagues who haven’t yet transitioned to Teams, you may elect to set your account to Teams Only mode. When you’ve switched to Teams Only, you’ll see some differences in your chat and calling communication – messages sent from Skype will arrive in your Teams client, and your presence in both Teams and Skype will reflect your Teams presence. As a Teams Only user, you’ll be able to seamlessly transition between the mobile and desktop client, find messages from Skype users when searching Teams, and you can often shut down your Skype client. Don’t uninstall Skype just yet! You may still use your Skype client to join meetings – you can join Skype meetings through a web client, but you won’t be an authenticated user and may need to wait in the “lobby” to be admitted to your meeting. But you can disable the auto-launch and open Skype only when it is needed.

Working In Teams Only Mode

Teams Only: Teams and Skype Interoperability Within Windstream

Employees and contractors with @windstream.com accounts
If the person has previously contacted you from Skype, the conversation will be marked with a Skype logo. You can reply to chat threads marked with a Skype logo initiate communication with Skype recipients. I pin chats from frequent Skype contacts to ensure I have a previous conversation available. You will not be able to:

Use rich text (typographical emphasis, font colors, etc)

Use emojis or gifs

Share your desktop

Include more than one person in the chat

If there isn’t a previous conversation to continue, or if you need to use one of the unavailable features, you can use Outlook to initiate communication to Skype users (** this requires a fairly old Skype client — old clients will set themselves as the default chat app on launch).

If you use Outlook to start a chat and the recipient replies hours later, the reply will be delivered to you in Teams. You’ll be able to use this conversation to continue delivering messages to the individual in Skype. If you need to use one of the unavailable features, you’ll need to use Outlook to initiate a new chat through your Skype client.
Messages and calls originated in Teams are delivered to Teams.
This doesn’t work – all calls and chats sent to you from Skype will arrive in Teams
All calls and chats sent to you from Skype will arrive in Teams. There are a few restrictions when chatting between Teams and Skype. You will not be able to:

Use rich text (typographical emphasis, font colors, etc)

Use emojis or gifs

Share your desktop

Include more than one person in the chat

Workaround – you can schedule a meeting in Teams or use Outlook to initiate communication through your Skype client.

Identifying Teams and Skype Users

How can you tell if someone is using Teams or Skype? Check their presence. In Teams, use the ‘new chat’ button and start a chat with the individual.

Look at their avatar – in the lower right-hand corner, you’ll see a small circle. This is a status indicator.

If their status is green, red, or yellow, their Teams client is open.

If their status indicator is a gray X, hover your mouse over their avatar or their name – an activity card will be displayed. When you send a Teams message to someone who has used Teams in the past, they’ll get an e-mail notification alerting them to missed activity. If you send a Teams message to someone who has never used Teams … well, they’ll see it when they log into Teams for the first time 😊

Never Used Teams Used Teams But Is Currently Offline
The individual’s status is offline and their last activity date is 31 December 2001. The individual’s status is offline, but their last activity date is recent.

In Skype, someone who is actively using Skype and someone who is a Teams Only user will have an active presence (or be listed as recently offline).

Someone who is using Teams but hasn’t changed to Teams Only mode will be listed as offline for a long period of time. They’ll still get an e-mail notification if you send them a Skype message, but you might get a faster response sending them a message through Teams.

Federation

If you communicate with federated contacts — those who do not have @windstream.com addresses — you may wonder where your messages end up on their side. As of 07 March 2019, there is no direct federation between Teams tenants. All federated communication transits Skype. This means your federated contacts will continue to receive messages sent from your Teams or Skype clients to their Skype client unless their account is configured as Teams Only.

 

The same holds true for you – whether your federated contact is using Skype or Teams, messages and calls they initiate will be delivered to you in Skype if you are using our default “Island” configuration and will be delivered to you in Teams if you have elected to be a “Teams Only” user.

 

Did you know … Microsoft Teams’ like button doesn’t need to imply sentiment?

Teams isn’t like Facebook where you’ve got an array of sentiments from which to select – but the “Like” button can be used for something other than saying “I like the information in this post”. When information is posted that doesn’t really warrant a response beyond “yup, I read this” – use the like button instead of replying “got it” or “yup, I did this”.

Why? When everyone replies to an informational post, you can end up with a LOT of replies. Each new reply moves the post down to the bottom of the channel (and alerts anyone following the channel of new activity). Do twenty people really need to know you’ve read the post? Do you want to see this every time a new person acknowledges the post?

Instead of replying to a post, just click the “Like” button – the little thumbs up in the upper right-hand corner of the post.

Anyone who wants to track who has read the post can hover their mouse over the like button and see who has responded. Anyone who doesn’t … well, clicking ‘like’ doesn’t count as new activity so the thread isn’t considered unread. Activity notification is only generated for the person who wrote the post. They don’t even know anything happened.

And we can all use context clues to differentiate between “Lisa really enjoyed reading my reminder to complete the Security Awareness CBT by Friday” and “Lisa finished that CBT” 😊

 

Did you know … Teams is great for in-person meetings too? 

In the mid 1990’s, I taught university classes – everything from introductory physics to string theory and computer forensics. There are some parallels I see between classroom instruction and business meetings. Many classes, and meetings, include some sort of digital presentation – content projected on the wall or a displayed on a TV. Even if the projector can auto-focus and you are using a projector screen instead of the textured, beige wall … it can be a struggle for everyone to see the presentation.  

To combat this problem, we wrote a program, ClassCast, that mirrored the instructor’s computer to all of the student computers – no more squinting to see what was being projected! Students could grab screen-prints of important information or download the presentation from the class’s web page. I frequently added links, textbook references, and additional information into the presentation comments too. 

Teams provides the functionality we built into ClassCast and adds additional features that benefit in-person meetings. Try it – start a meeting once everyone assembles in the conference room. Make sure people’s speakers and microphones are muted! Share your screen in the Teams meeting. Attendees can clearly see what is being projected (you may not even need that projector), notes can be added in OneNote or the meeting chat (a great place to stash questions too). Links and resources can be shared through the meeting chat. If you share a PowerPoint presentation, it is automatically shared out to meeting participants. Even if your presentation isn’t PowerPoint, you can record your entire presentation – complete with a transcription and share the recording in your Teams space. 

ClassCast inadvertently helped with the second problem I experienced as an instructor – attention. In the University setting, inattention often meant students suffering through a core requirement who sat playing solitaire or surfing the Internet; but it also meant the student in the back row, students who could barely see the presentation … well, their attention wandered. When everyone could see the presentation, they were less distracted. Having ClassCast running made it a deliberate act to open a card game or web browser – instead of staring at a desktop with those inviting (fun) icons, students had to close ClassCast. Yes, there were still a few students playing and ignoring my lesson – but it became a rare exception rather than the norm.  

I don’t see deliberate inattention when hosting business meetings, but attention still wanders. An IM pops in, and that flashing box constantly draws your attention. The new message indicator appears in your system tray and it could be important. My director at Alltel had a rule prohibiting computers in meetings, but a text comes across your phone … and his rule became pointless with the advent of smart phones. Having meeting content on your desktop immerses you in the meeting. If you are speaking to a presentation, that presentation content is right there. If the group is meeting to collaborate on a document, instead of having one person performing edits as they are suggested, the document can be edited collaboratively.  

Did you know … you can share PowerPoint presentations in a Teams meeting?

Since you can share your desktop or an application, of course you can share a presentation. And if you start your presentation before sharing the application, participants don’t even watch you click around opening the presentation and starting the slide show. But did you know Teams has special functions just for sharing PowerPoint presentations?

Once you have joined a meeting, select the “Sharing Tray” from the meeting control bar.

Click “Browse” next to PowerPoint and select the source of your presentation – this can be a PowerPoint File you’ve already shared to the Teams space, one stored on OneDrive, or a file uploaded from your local computer.

If you chose to upload a presentation, it will be placed in a location where participants can access it – the channel’s Files section in the case of channel meetings, and your OneDrive (with appropriate permissions added) in the case of private meetings. While this eliminates having to send the presentation to participants after the call, it also means that anything in the file is available to meeting participants. If you routinely hide slides to customize a presentation … just remember meeting participants can access those slides too.

The slideshow will start, and you will have a control bar to navigate through the slide deck. When you start a presentation, participants have the same control bar – they can look through your deck independently.

A participant can even request control of the presentation – you don’t have to “drive” while someone else speaks to a set of slides.

Control is automatically granted – you’ll see a banner, and you will be able to take control back. But you cannot currently restrict who is able to take control of your presentation. A RFE has been submitted to Microsoft to either restrict who is allowed to take control or use requests instead.

If you don’t want people to ruin the surprise (or if you want to ensure participants are focused on the slide being discussed), you can prevent participants from navigating the slideshow on their own. Click the little eyeglasses next to the slide control.

Participants will see a banner indicating that they cannot move through the presentation independently.

Clicking the little eyeglasses again will restore participant’s ability to navigate through the slides.

When you are finished with the slide deck, click “Stop presenting” – you will remain in the meeting, but the slideshow is no longer displayed.

 

Did you know … you can create a Team using a template?

We’ve created Teams for several similar projects – and it’s time consuming to configure the new team with all the configuration that we’ve found works well for the project. And then I’ve got to add the usual suspects to the new Teams space. None of these are particularly laborious, but not creating the Teams space on a blank slate would certainly save time. Is there a better way?

Yes! I can create new Teams using existing Teams as a template. Click “Join or create a team”.

Click “Create team”

Click the link to “Create a team using an existing Team as a template” – there’s no need to add a Team name or description here.

Click the radio button in front of the Team you wish to copy and click “Choose team”.

Provide a name and description for the new Team. You can select which elements will be copied from the original Team – for our migrations, I can put core team members in the template source & add only the specific individuals being migrated.

I’ve had problems with some 3rd party apps partially coming though … not enough that they work, but enough that I cannot just install the app either (or uninstall it). My current recommendation is to remove 3rd party apps from the template team.

When you have finished making your selections, click “Create”.

Wait for it – this takes longer than just creating a team. You can watch the purple line spin, or you can close the status window.

If you watch the purple line spin, you’ll be given the option of adding additional members. Either add members or skip this step.

Voila – a new Team with all of the channels I had set up in my original Teams space.

It is important to realize that content is not copied – while tabs are created, you will need to create a new Wiki, OneNote, Planner, etc. Click on the tab, and you will be prompted to create the item for your new Teams space.

You’ll need to input URLs for Website tabs.

Alternately, you can use the Microsoft Graph API to create Teams. You can create a Teams space with channels, pre-configured tabs, and even pre-installed apps.