Spreadsheets: How to Sort Data Onto Sub Sheets based on values in a given column

One of the most popular posts on this blog, is one I published back in 2011, titled How to automatically pull data between different google spreadsheets  I am often asked by people, is it possible to filter the data based on the value in a column, before pulling the data across?

I have created a video which shows a technique whereby data is filtered internally within a workbook, so data is pulled onto subsequent sheets, based on values in a certain column. In this example I am using a class of students, and all the grade A students are copied onto a sheet called “A”, all the grade B students are copied onto a sheet called “B” etc. This principle could easily be used to organise a list of sales by sales rep, or by region.

The video is about 13 minutes long, but well worth watching, if you are interested in this technique.

For this example I have used Excel, but this would also work with other spreadsheet systems such as OpenOffice or Google Sheets.

The file used in the video example can be downloaded here, if you want to see or copy the formulas used.

Spreadsheet – sort data onto new sheets Shared

The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/

I hope that this helps people to make better use of Spreadsheets, whether it is in education, work, or for personal use.

Automatically pull an entire sheet of data between google spreadsheets

In November 2011, I wrote a blog post titled ‘How to automatically pull data between different Google Spreadsheets‘ – which was based on a feature called ‘ImportRange’. Although nearly 5 years old, this particular blog post is one of my most frequented and certainly the most commented on post that I have ever written.

I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at https://importsheet.com/ – and this does exactly what it says on the tin. It allows you to easily import (or export) a sheet from one file to another. So for example, I use google sheets to log the work that I do for each of my clients, with each client having a separate workbook (this means that I can share that workbook with them, without them seeing other contracts that I work on). I can then have a master dashboard, which imports copies of these sheets into a single location, and using simple functions such as the = function, I can then pull key data out of each of the sheets into my dashboard. Using Importsheet rather than the ImportRange feature is much easier, quicker and less likely to have problems.

The add on is a commercial tool, that does have a pricing plan, however the free version does what most people will want. Obviously I have no idea how the pricing plan may change in the future – they may choose to get rid, or reduce the functionality of the free version, so I wouldn’t suggest that people invest lots of time creating high stakes activity with this add on (unless they are prepared to pay in the future) – but certainly for the moment, this looks rather neat.

Creating equations in online environments

I am currently working on a project, where I am creating web based resources (using Moodle) to teach maths. As part of this process, I need to create properly laid out formulas (or formulae if you prefer the alternate acceptable plural of formula). We are using what is becoming a widely accepted standard of MathJax which in turn supports the use of something called LaTex to create the desired formulas. So for example if I wanted to create something that looked like:

y = 3x^{2}+5x+\frac{2}{3}

I would enter this into the editor using the code:

$$y = 3x^{2}+5x+\frac{2}{3}$$

or

\[y = 3x^{2}+5x+\frac{2}{3}\]

At first I started to learn the exact syntax and would translate what I wanted into either of the codes above. This proved to be both time consuming and prone to mistakes. Then I discovered an excellent website that helps me do this:

https://www.codecogs.com/latex/eqneditor.php

This website gives me a box into which I construct the equation that I want – above the box is a huge suite of grey buttons which each represent a different mathematical function or options. These take a bit of time to learn, but quite quickly one gets the hang of this, and using the buttons and adding the numbers / letters that you require you can quite quickly create the desired formula that you want. Underneath the white box, your formula is displayed as it will appear, so it is possible to see what you are doing, and check that this is correct.

Once you are happy with what you have created, at the bottom of the screen (in a cream coloured box) is the option to choose the export style that you want – so if you need LaTex, you choose that, if embedding into WordPress, you choose WordPress etc. You then copy the code beneath this, and paste into the editor of whatever you are using.

One of the facilities within the editor, is to create correctly aligned equations:

Image of an inline equation

Which in LaTex is created with the code:

\[\begin{align*} x+3 &= 7\\ x &= 7-3\\ x &= 4 \end{align*}\]

This is very hard to manually write out, but quite easy using the codecogs website. The button for this, is the bottom right button on toolbar (letters n and r in brackets) – then under that is a button that has “y=…” as the text, and when you hover over it, it tells you that it is the align tool.

For anyone who is using mathematical formulas regularly this is a really neat tool.

 

Creating a YouTube based discussion activity in Moodle

I run a lot of training on effective uses of a VLE (usually Moodle) and one of the easiest activities that I show, is finding a video on YouTube, and then embedding this into a forum activity within the VLE.

The reasons for doing this are:

  1. By embedding the video (rather than simply linking to it) – we remove all the distractions, adverts, etc. that appear on YouTube around the edges.
  2. By adding this as a discussion activity, we ask the students a question – this will focus their attention whilst watching the video, rather than just passively  ‘absorbing’ it.

It doesn’t matter if students don’t actually post their answers to the forum (although useful if they do), as they will still benefit from watching the video with the question in their mind.

The following video goes through the steps of how to embed the video, and the basic settings within a Moodle forum activity.

And if you want to only show a portion of the video you can always identify the exact start and end points that you want to play, by following these instructions:

https://davefoord.wordpress.com/2012/11/20/cropping-a-youtube-video-before-adding-to-moodle/

Trimming and Embedding a YouTube Video into PowerPoint

I have blogged many times in the past about things to do with PowerPoint, including how to embed a YouTube video, or how to use TubeChop to embed a YouTube video.

In more recent versions of PowerPoint (2013 and 2016), the ability to embed a YouTube video has been made easier, and the following video will take you through the steps:

Although easier to do than in the past, this technique has been unreliable for some people in some organisations, so I always recommend to people to paste the video’s URL onto the slide somewhere as a live link, so if this doesn’t work, you have the fall back of simply accessing the video via the YouTube website.

This technique is showing how to embed the video – this means you still need access to the Internet when viewing the presentation, and it won’t work if the organisation blocks YouTube.

This technique replaces the older method of using the shockwave flash object, or using TubeChop to trim the video.

Bulk uploading items to a Moodle Glossary

The Moodle Glossary is one of the simplest activities to use within Moodle. It’s primary purpose is to set up a glossary that the students populate or at least add to, rather than the tutor populating it – however there are occasions when it is useful for the tutor to populate the glossary. This could be if the tutor wants to provide the students with a ‘correct’ list of technical terms and their definitions, or if the tutor wants to create a crossword using the Moodle Game Activity plugin – which pulls the data out of a glossary activity.

If the tutor is populating the glossary, they could enter the data manually item by item, but this is very time consuming, especially if they already have the data to hand in a spreadsheet or similar. Luckily there is a way to bulk upload these items. It is a little complex, but once you have done a few not too bad, and certainly a lot easier than manually typing in lots of items.

Firstly – we need to create the glossary, the following video from Moodle, goes through this step:

Then the clever part is importing the list of terms from an external source. This is covered in this video:

The XML converter that is used, can be located here:

https://moodle.org/mod/forum/discuss.php?d=91224#p489666 with the direct link to the actual file itself being:

https://moodle.org/pluginfile.php/159/mod_forum/attachment/489666/glossaryXMLconverter_html4.zip

You only need to download and unzip this once – as long as you can remember where you have saved it to. Although it may seem a little convoluted at first, this technique will save serious amounts of time compared to manually entering lots of data.

The only other thing to note, is the default settings of the glossary may allow the students to add their own items to this glossary, if you don’t want this to happen, you can stop this by using either of the following options:

  1. In the glossary settings – make it so the ‘Approved by default’ option is set to ‘no’.
  2. Go into the permissions for that glossary and next to where is says ‘Create new entries’ delete students from the list of roles that can do this.

 

 

Correcting problem with Outlook 2013 not always sending messages

I have recently upgraded my windows computer from Windows 8 to Windows 10, which in general is great, but it did cause a problem with outlook 2013, in that some messages wouldn’t send. The problem wasn’t consistent, which was confusing, I tried searching the internet for help, but the solutions offered all seem very complicated, so I contacted my web host, who talked me through the solution, which worked, so I thought that I would share this here.

Please note – I am not technically orientated, I am just repeating the steps for my benefit in case I need it in the future – if you follow these steps, you do so at your own risk, and if it doesn’t solve your problem, please don’t ask me – I won’t know the answer.

So to solve the problem:

  1. I right clicked on Windows icon.
  2. I selected command prompt (admin)
  3. I typed “sfc/scannow” then enter.
  4. I let this run the scan (took about 15 minutes).
  5. Once scan had finished I closed down Outlook and then restarted it.

As mentioned earlier, I am not technically minded, so I have no idea what is happening with the above apart from it appeared to fix my problem.