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
Spreadsheet with automatic highlighting
Capital Budget Spreadsheet – note the row and column (year) highlights

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

The Helper column holds formulas
The Helper column holds formulas

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.


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.

ADDRESS turns the cell address of the referenced cell (not its’ contents). In our example; ADDRESS(3,14,3,TRUE)=”$N3″

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.


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.

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

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.

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.

Select the shapes you wish to combine.
Both shapes are selected, so the Combine Shapes button is active

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

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.

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!

Can you spot the frog?
Can you spot the frog?


Add a Google Calendar to a Facebook Page


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

To add a Google Calendar to your Facebook Page, you will first need a Facebook Page (not a personal profile page) and a Google (Gmail) account.

Create the Google Calendar

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.

Click the Share this Calendar link.

The checkbox turning on Public Access
Making a Google Calendar Public


Enable the Make this calendar public checkbox.

Click the Save button.

The calendar view will be displayed.

Customize the Calendar HTML Code

Return to the calendar’s details page.

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

finding the HTML to display the Google Calendar
Locate the Embed this Calendar section

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:

Google Embeddable Calendar Helper
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.

Add the iFrames Tab app to your Facebook Page

From your Facebook page go to https://www.facebook.com/StaticHtmlThunderpenny/

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

The Static HTML: iframe tabs page will appear:

view of Thunderpenny app installation page
Static HTML: iframe tabs, Add Static HTML to a Page

Click the Add Static HTML to a Page button.

The Add Page Tab window will open:

Add Page Tab dialog box
Choose your Facebook Page to add the Page Tab too.

Click on the drop-down Facebook Pages Button and select your Facebook Page.

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).

The Static HTML customization window.
The Static HTML window. Paste your calendar code here.

Click the Save & Publish button.

Name the Tab

From your Facebook Page, select Settings.

Finding the App Settings after installation
Return to Settings to 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:

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

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.

Additional Ideas

Now the App code has been connected to your Facebook page, additional tabs can be added. Any piece of embeddable HTML code can be used. For example; on-line catalog search code. To add additional tabs, return to the page Settings and select Apps.

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?”

Response to question by Siri
Siri shows a map of my location

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.

The result of question "where am I"
The saved map

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.

The share sheet
You can share your location in a number of ways.
Additional options are displayed as you pull the panel higher.
Additional options are displayed as you pull the panel higher.

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.

Marked location options
Once you’ve marked the location, you can edit it.

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

Editing the location
Editing a location

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?

Reminder Dialog box with highlighted Dismiss All button
Oops! I hit the Dismiss All button!

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>

Highlighted Search bar
Use the Search bar in the calendar view
Close up of search entry
Close up of search entry

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.

Closeup of the Search Tools view
Closeup of the Search Tools view

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!

Search by the month name if you don't know what date.
Search by the month name if you don’t know what date.

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.

Original Document and Result after Tweet Cruncher is run on the selected text
Original Document and Result after Tweet Cruncher is run on the selected text

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.

Dialog box for Tweet Cruncher
Tweet Cruncher Dialog

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

IntCharCount = 1
Documents.Add DocumentType:=wdNewBlankDocument
Set docNewDoc = ActiveDocument
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
        intActualLength = IntTweetLength
    End If
    blnWord = False
    strPostText = Selection.Text & frmStartCrunchingTweets.txtHashTag & " " & IntPostCount & "/" & IntPostNumb
    'get rid of any hard returns
    strPostText = Replace(strPostText, vbCr, " ")
    Selection.TypeText (strPostText) & vbCr
    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.

Setting or Cancelling the change of Wallpaper
Setting or Cancelling the change of Wallpaper

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

Setting the Picture to display on the Lock or Home Screen
Setting the Picture to display on the Lock or Home Screen

Improve Your View

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.

    Setting the Larger Text size
    Setting the Larger Text size
  • 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.

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

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

Turning on Triple-Click to Zoom
Turning on Triple-Click to Zoom

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

Zoom Options pt 1
Zoom Options pt 1

Don’t worry about turning Zoom on here, that is what your triple-click will do. Instead:

  • 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.

    Zoom Options Part 2
    Zoom Options Part 2
  • 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.

The Zoom Window in action
The Zoom Window in action

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.

Changing the Zoom Window options
Changing the Zoom Window options

Through it you can:

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

    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

turning on the magnifier
turning on the 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.

Choosing the Accessibility Shortcut
Choosing the Accessibility Shortcut


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:

Books Stacked Everywhere - from unsplash.com
Books Everywhere

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

“The old man was peering intently at the shelves. 'I'll have to admit that he's a very competent scholar.' Isn't he just a librarian?' Garion asked, 'somebody who looks after books?' That's where all the rest of scholarship starts, Garion. All the books in the world won't help you if they're just piled up in a heap.” ― David Eddings, King of the Murgos
Organization – the secret power of Librarians!

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.

“It's still National Library Week. You should be especially nice to a librarian today, or tomorrow. Sometime this week, anyway. Probably the librarians would like tea. Or chocolates. Or a reliable source of funding.” ― Neil Gaiman
We love Neil Gaiman

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