Challenge
The client was a tutor, working 1-on-1 with students to prepare them for standardized exams.
As part of the tutoring, they printed out worksheets that focused on a student’s particular weaknesses. The questions were all sourced from previous years’ exams, as neatly scanned/cropped images on the client’s computer.
With so many students to manage, it was extremely time-consuming to manually copy/paste question images into Microsoft Word. There had to be a better way.
Solution
Part 1: Google Sheets
The first portion involved setting up a system in Google Sheets to easily retrieve a set of questions appropriate for a given student.
Fortunately, the client already maintained a spreadsheet listing all the questions and their topics (e.g., questions 7, 9, and 11-13 were about Geometry; questions 1-6 were about Linear Equations).
Using lookup formulas and filters, I designed a “question number generator” that allowed the user to see a comma-separated list of questions based on either a topic or a student.
By simply selecting the desired topic(s) from a dropdown, the sheet would output something like:
Geometry: 7, 9, 11, 12, 13
Part 2: Microsoft Word (VBA)
In the second leg of the project, I took these questions and fed them into a Microsoft Word macro. At the prompt, you would paste in the output from earlier.
Then, the script would look in the correct local folder(s) to insert all question images into predetermined positions.
Outcome
The client could now prepare for tutoring sessions with just a few minutes of work. It was also futureproof, as long as they kept the same file/folder naming conventions.
We have liftoff! Works great so far.