# Highlighting the Current Year

Here is a version of a spreadsheet that I’ve been using for a couple of years to track and plan capital purchases. A number of people review this spreadsheet and I want to make it as easy as possible for them to read the spreadsheet. You’ll notice that the Budget year is highlighted in green and items being purchased in that year are highlighted as well. This is accomplished with our friend conditional formatting and the following spreadsheet functions:

• ROW
• COLUMN
• MATCH
• INDIRECT
• ISBLANK

This spreadsheet makes use of a helper column of formulas. Rows where the value equals TRUE are highlighted.

Cells give a value of TRUE when there is a value for that row in the Budget Year selected in cell A1. You can see that 2017 has been selected as the Budget Year and that rows 9 and 20 have a value for that year and are highlighted as a result.

This is the formula that returns the value

Working from the interior of the formula outward.

MATCH(\$A\$1,\$F\$2:\$AU\$2,0)

This looks for a match between the value in A1 and the Budget year headings which start in cell F2 and go to AU2 (the year 2033, which is incredibly optimistic – but that is another story). MATCH returns the number of the first item in that array of cells that matches the value in A1. This is why even though there are two columns for every year (a Budget column and an Actual column) MATCH will only return the Budget column, as it is the first value to match.

So the result of MATCH(\$A\$1,\$F\$2:\$AU\$2,0) is 9

However, if I actually want to capture the column I need to to add 5 to compensate for the fact I have 5 columns (A-E) before column F and the year headings begin.  This is why I’m adding 5 in the formula.

MATCH(\$A\$1,\$F\$2:\$AU\$2,0)+5 =14

In the next step I use ADDRESS and ROW to capture the address of the cell I’m testing.

ROW() captures the value of the row of the cell where the formula is written. If the formula is in A3, then row() returns 3.

The ISBLANK function in the next step has a bit of a hiccup with that “\$N3” string, so we use INDIRECT to convert that string to something ISBLANK can understand.

Finally, ISBLANK is used to test if there is a value in the referenced cell or not. If there is nothing in the cell ISBLANK = TRUE.

If ISBLANK = TRUE, then the last portion of the formula looks like this: TRUE does not equal FALSE, so the result of the formula in cell A3 is FALSE.

I could have put that formula into the conditional formatting dialogue – but for clarity and ease of working I choose to make the helper column instead.

In the conditional formatting dialogue I’ve used the following formula =\$A3=TRUE

I’m using a simpler version of the formula in the conditional formatting dialogue to highlight the year.

=MATCH(\$A1,\$F\$2:\$AU\$2,0)+5=COLUMN()

In this case I find the column number of the year and test to see if it matches the column number of the current cell. If it does then the cell receives a green highlight fill.

Cell A1 uses Data Validation to offer the user a nice drop-down list of years.

# 2017 Redcliff Youth Soccer Schedule

WebGenii Consulting has sponsored a soccer team again this year. And for every family looking to find out where and when their children are playing – here is the schedule.

# PowerPoint – Making a Mask

Creating a mask effect in PowerPoint is easy, once you’ve located the Shape Combine command. You can add this command to the Ribbon or the Quick Access Toolbar.

Below, you see it being added to my toolbar.

Adding the Shape Combine Command to the Toolbar, alternately look for the Combine Shapes command as more options are available.The command will not be active until there are two shapes selected. Below, I’ve created a blue rectangle and a red oval. The oval shape will be cut out from the rectangle.

Blue rectangle with red oval positioned for the cut out.You may prefer to add the Combine Shapes command instead. More options are available as you can see below.

The result of the Shape Combine command, a rectangle with an oval “hole: in the center.

The result of the Combine Shapes CommandOnce the mask is created, you can dress it up. Below, I’ve changed the fill to an image of a leafy forest floor.

The forest floor has a hole in it.Now I can layer whatever image I wish (in this case a frog) under the mask. You can animate the layer underneath the mask. Can you image a wheel of creatures rotating into the viewpoint in the center of the mask? That would be great for a talk about ecology!

Adding a public Google Calendar is a great way to present activities in an easily shared fashion.

Make a public calendar. This can be an existing calendar or one created for this purpose.

### Make the Calendar Public

After the calendar has been created, click on the drop-down arrow beside the calendar name and choose Calendar Settings. The Calendar Details page will open.

Enable the Make this calendar public checkbox.

Click the Save button.

The calendar view will be displayed.

### Customize the Calendar HTML Code

Click on the drop-down arrow beside the calendar name and choose Calendar Settings. The Calendar Details page will open:

Scroll down the page and find the Embed This Calendar section.

Click on the Customize the color, size, and other options link.
A new window or tab will open, the Google Embeddable Calendar Helper:

A preview of your calendar is visible. You can change the title, default view and other elements of the calendar. The view will update to show you what your calendar will look like when the changes are applied. If you do make changes, be sure to press the UPDATE HTML button to ensure those changes are reflected in the HTML.

Be prepared to copy and paste the HTML code. However, do not do so yet.

Click the Use App link on the left hand side of the page.

The Static HTML: iframe tabs page will appear:

Click the Add Static HTML to a Page button.

The Add Page Tab window will open:

Click the Add Page Tab button.

You will be routed to the Set Up Tab view. Click the Set Up Tab button.

Follow the instructions in the index.html area

Copy the HTML code that you customized previously for your Google calendar into the index.html area.
Replace the instructions with your code (or the instructions will appear along with your calendar).

Click the Save & Publish button.

## Name the Tab

Click on the Apps category on the left.

Apps that have been added will be listed on the right.

From the Static HTML: iframe tabs app section, click on the Edit Settings link.

The Edit Static HTML: iframe tabs Settings dialog box will appear:

Enter the name of your calendar in the Custom Tab Name: text box.

Click the Save button.

Click the OK button.

You can also add an image that will appear beside the link on the right hand side of your page.

Click the Go to App link for the Static HTML: iframe tabs app.

The app will walk you through creating an additional tab.

Woobox has an app called Tweet Feed for Pages, which will embed your Twitter feed into a tab on your page. The concept is similar to embedding a Google Calendar. Woobox’s app will walk you through the process of adding the tab.

# Where Am I?

A fairly regular occurrence when you do a lot of driving; you find something during your drive that should be reported (roadkill, accidents, debris, etc.). If you are on a rural road, the GPS coordinates would be ideal. But, since you are a sensible person you don’t want to take your hands off the steering wheel to fiddle with your phone.

I have a solution for iOS  phones (mostly – it still will require one touch of the screen).

The solution is to use Siri and ask the question “Where am I?”

Now unfortunately, Siri doesn’t store this information, and once your screen turns off it will disappear. However, if you touch the map, the information will be transferred to the maps app. The maps app will hold the information until you are ready to deal with it.

Now when you can pull over you can use either the option to Share My Location or Mark My Location.

Selecting Share My Location brings up your share sheet.

You can also  pull the panel a little higher up over the map, for more information.  Like the GPS coordinates or contact options.

You can use the Mark My Location to do exactly that on the map. A red pin will display on the map, and the option to Edit the location appears.

If you choose to edit the location, a satellite view of the location opens, and you can drag and drop the pin.

Marked locations remain in the map app for later reference.

# Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

You can then reopen the item and reset the reminder.

# Tweet Cruncher

I have a Social Media project on the go (waves at https://twitter.com/librarytrustees) that is going to involve tweeting sections of existing documents.

I really hate counting characters. So I decided it was time to make myself a tool for the job.

You can see above what I have; the selected area of the original document is highlighted in varying colours, corresponding to the resulting text broken up into tweets. Additionally, I have inserted my chosen hashtag and a count of the sequence of tweets.

The length of the tweets and the Hashtag are entered in a dialog box when the Tweet Cruncher runs. This information is saved with the document, for consistency with subsequent tweets. The Tweets are not exactly the tweet length; I’ve added a bit of code to “round off” each tweet to whole words. The hashtag and sequence count are additional to the length.

And realistically, there will still be editing for content and meaning. Nevertheless, this tool should save me a ton of counting and get the project going faster.

```Sub BreakIntoTweets()
Dim IntSelection As Integer
Dim IntPostNumb As Integer
Dim IntPostCount As Integer
Dim IntCharCount As Integer
Dim IntTweetLength As Integer
Dim rngSelectedRange As Word.Range
Dim strPostText As String
Dim intColourPick As Integer
Dim docNewDoc As Word.Document
Dim docWorkingDoc As Word.Document
Dim strPropertyName As String
Dim strHashTag As String
Dim blnWord As Boolean
Dim intActualLength As Integer

Dim arrColourOptions As Variant
arrColourOptions = Array(wdBrightGreen, wdPink, wdTurquoise, wdYellow)

Set docWorkingDoc = ActiveDocument
strPropertyName = "HashTag"
strHashTag = frmStartCrunchingTweets.txtHashTag
docWorkingDoc.CustomDocumentProperties(strPropertyName) = strHashTag
IntTweetLength = frmStartCrunchingTweets.txtTweetLength
Set rngSelectedRange = Selection.Range
MsgBox rngSelectedRange.Characters.Count & " characters are selected. Including Paragraph Marks"
IntSelection = rngSelectedRange.Characters.Count
IntPostNumb = IntSelection / IntTweetLength
MsgBox IntPostNumb

rngSelectedRange.Characters(1).Select
IntCharCount = 1
Set docNewDoc = ActiveDocument
docWorkingDoc.Activate
For IntPostCount = 1 To IntPostNumb
Selection.MoveRight unit:=wdCharacter, Count:=IntTweetLength - 1, Extend:=wdExtend
If (Right(Selection.Text, 1) <> " ") Then blnWord = True ' extend to word
If (Right(Selection.Text, 1) <> ".") Then blnWord = True
If (Right(Selection.Text, 1) <> "?") Then blnWord = True
If (Right(Selection.Text, 1) <> vbCr) Then blnWord = True
If (Right(Selection.Text, 1) <> "!") Then blnWord = True
If blnWord = True Then
Selection.MoveRight unit:=wdWord, Count:=1, Extend:=wdExtend
intActualLength = Selection.Characters.Count
Else
intActualLength = IntTweetLength
End If
blnWord = False
strPostText = Selection.Text & frmStartCrunchingTweets.txtHashTag & " " & IntPostCount & "/" & IntPostNumb
'get rid of any hard returns
strPostText = Replace(strPostText, vbCr, " ")
docNewDoc.Activate
Selection.TypeText (strPostText) & vbCr
docWorkingDoc.Activate
intColourPick = IntPostCount - (4 * Int(IntPostCount \ 4)) 'note this is why no base 1 option for array here, also \ means different than / (truncation function)
Selection.Range.HighlightColorIndex = arrColourOptions(intColourPick)
IntCharCount = IntCharCount + intActualLength
On Error Resume Next
rngSelectedRange.Characters(IntCharCount).Select '(errors on final character of selection)
Next IntPostCount

End Sub```

# iOS 10, Accessibility and Vision Options

If you are using an iPhone or iPad and your vision is compromised (or, ahem if you are over 50), you might find these options useful. With every version of iOS the accessibility options have improved and expanded.

The following is the sequence of steps I follow when setting up an iOS device for someone with vision issues; as always your mileage may vary, but even if you only catch yourself squinting at your phone from time to time – give these options a try.

# Change your Wallpaper to Black

It is cool to have nifty images on your phone screen, but if you are having trouble reading the labels for your folders, then it may be time to use a black background. You may also want to do this for your lock screen so that notifications and buttons stand out more.

The easiest method is to take a black photograph and use it as your background. Set your device on a table, block the light and take a photo (you may need to turn off your flash first). Use this photo for your background by going to Settings>Wallpaper > Choose a New Wallpaper and selecting your new photo from the Camera Roll.

Choose the Set option and then select the Set Home Screen option

Next go to Settings > General > Accessibility and select the following options:

• Larger Text, you can start by using the slider to set your preferred text size or you make things even BIGGER by turning on Larger Accessibility Sizes and using the slider again. This comes with a couple of caveats. 1) Text is not affected everywhere on the device – tho Apple is clearly working to change this, and third party apps are (slowly) coming around. 2) Sometimes, you can have too much of a good thing and have text that throws off the formatting or wraps weirdly or overlaps illegibly. Again, these problems are gradually being worked on as apps are updated.

• Bold Text – turning this option on will require a reboot of your device, because EVERYTHING has to be redrawn.  But this will make the typeface used far more readable.
• Button Shapes – turning on button shapes will make them easier to find. Now, if they were only used more consistently throughout the OS, that would be awesome.
• Increase Contrast and turn on Reduce Transparency and Darken Colors.  This should also help text stand out more.

• On/Off Labels add the | 0 symbols to your toggles, and I say why not!

# Use your Home button to Zoom IN

While you are still in Settings > General > Accessibility , move down to Accessibility Shortcut. Here you can customize the action of the Home button when your triple-click it.  Choose Zoom

You’ll need to fine-tune Zoom’s behaviour, by going to Settings > General > Accessibility  > Zoom

• Turn on Follow Focus and Smart Typing.
• You may choose to Show Controller, but I find there is a balance between clutter and helpfulness, for me this clutters the screen too much.

• Select Zoom Region and choose Window Zoom.
• Play with the Maximum Zoom Level slider, you’ll probably adjust this more later.

To turn the zoom window on triple-click your home button and the zoom window will appear, outlined in black with a small tab handle at the bottom.

This is a toggle, so a second triple click turns the zoom window off.  And what is really great, is that you can interact with the screen through the window.  Click on links and type in dialog boxes whatever you need to do. This window is not just for viewing!

## Customize the Zoom Window

By touching the tab handle shape at the bottom of the zoom window, a customization menu opens up.

Through it you can:

• Zoom Out (turn off window)
• Change to Full Screen Zoom
• Resize Lens,

which will allow you to resize the Zoom Window with the handles provided.

• Choose Filter, which will set a colour filter on the Zoom Window
• Show Controller
• Change Magnification using the slider, which uses the setting from the Maximum Zoom Level, you set earlier.

# Add a Magnifier to the Home Button

The new magnifier option isn’t for your screen, but rather for all the small print articles you need to read. From menus in dark restaurants to the label on a pill bottle, if you have your phone you can read it. Go to Settings > General > Accessibility  > Magnifier

Turn on the Magnifier and Auto-Brightness.

Now when you triple-click your Home button, an Accessibility Shortcuts menu will pop up. Tap the action you want to perform.

# Update: A New Free to use Photo Site

I just came across a new free photo site called https://unsplash.com/  if you are creating visuals for your library’s social media feed, they have great pictures of books and people reading. Perfect for combining pictures and quotes. I was inspired by the picture below:

So I headed off to the internet to find a quote that I thought was perfect for these random stacks of books.

Using the tip I mentioned in this post, I combined the text and image into a picture sized for Twitter.

And then because I’d fallen down into the internet quote-hole I used another picture from unsplash and a terrific quote from the always library friendly Neil Gaiman.

If you want to look back – I have a previous post on other free graphics sites.