Tag: Ben Collins

Replied to How to create dynamic named ranges in Google Sheets (Ben Collins)

See how you can use the INDIRECT function to create dynamic named ranges in Google Sheets, which expand automatically if new values are added to the range.

Another great post Ben.

I recently revisited a timetable solution that I developed to support schools with creating a basic timetable based on core data of:

  • Days
  • Sessions
  • Forms
  • Classes.

One of the problems that was uncovered was that the system we were trying to feed the information into would not accept staff codes for staff who were not yet active in the system. My initial answer was to find each of these staff members in the final output and delete their codes. The question that was raised was whether this could occur at the beginning of the process by leaving the codes associated with inactive staff blank in the list.

This created a problem where the blank cells were not being recognised and were subsequently not being generated in the output. I found if I added a blank space then my original formulas would work. I therefore added an IF formula to replace any blank cells with a blank character CHAR(32). This meant I replaced:

JOIN(",",'Basic Classes'!$C3:C103)

With:

ARRAYFORMULA(JOIN(",",(IF(ISBLANK('Basic Classes'!$C3:C103),CHAR(32),('Basic Classes'!$C3:C103))))

The issue with this though is that there would never be 100 classes. However, I wanted to create a flexible solution, therefore put in a range that would suffice all contexts. By replacing blanks with CHAR(32), this meant every timetable created had 100 teachers. This would not work.

To work around this, I created a series of dynamic ranges using your example of named ranges. I entered this formula in a cell and referenced it to create a named range. To get the right range I used the form list (Column B) as a reference as this is a required cell in my data collection:

"'Basic Classes'!$C3:C"&COUNTUNIQUE('Basic Classes'!$B$3:B)+2

The final formula was:

ARRAYFORMULA(JOIN(",",(IF(ISBLANK(INDIRECT(TEACHERS)),CHAR(32),INDIRECT(TEACHERS)))))

This meant that the blank cells were only replaced for classes without teachers.

Here is a copy of the spreadsheet if you are interested.

Replied to Use The Onion Method To Approach Complex Formulas (Ben Collins)

If you’re building complex formulas, then I advocate following a one-action-per-step approach.


What I mean by this is that you build your formulas in a series of steps, and only make one change with each step.


The Onion Method is a framework by which to approach hard formulas, and consists of these three elements:

  1. Put each new step of the formula in a new cell
  2. Label each step with a simple “Step 1”, “Step 2”, etc. in adjacent cells
  3. Change the background color of each formula cell, so they can be easily found
I love the way you breakdown complex examples using the onion method through all your courses.
Replied to How to Vlookup Multiple Criteria and Columns in Google Sheets (Ben Collins)

Learn how to setup Vlookup to work with multiple criteria, by combining input criteria, creating helper columns or even searching with dynamic arrays.

I am going to assume that I am probably pushing it, but is it possible to do a vlookup with multiple criteria that also incorporates wildcards? Basically, I have a list of school names, but the problem is that they are not always unique (i.e. St Mary Primary School). To differentiate I have recorded their location. I was hoping (dreaming?) of making a vLookup that would allow me to write a part of the school name (i.e. Mary) and a part of the location (i.e. Melb). Just thought I would ask.

Also on: Read Write Collect

Replied to Google Sheets Query function: The Most Powerful Function in Google Sheets by an author

The ORDER BY clause sorts our data. We can specify column(s) and direction (ascending or descending). It comes after the SELECT and WHERE clauses.

14. Let’s sort our data by population from smallest to largest. Modify your formula to add the following ORDER BY clause, specifying an ascending direction with ASC:

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)
Ben, is it far to say that ‘Order by’ only ever relates to numerical values? Is there any way of ordering text using a QUERY formula? Does it require the addition of a GROUP BY or a WHERE clause?
Replied to 2018 in review and a look forward to 2019 – Ben Collins (Ben Collins)

Create two other courses. The shortlist at the moment includes: 1) “Beyond Sheets” on what to do when your data outgrows Google Sheets, 2) Regular Expressions in Google Sheets, 3) a Data Studio course, and 4) Google Sheets for Educators

Congratulations on your year Ben.

Thought I would place my vote for the ‘education’ course. Although all of your courses provide insight and opportunities within education, I feel that there is real scope for an explicit education offering. Although there are some great offerings in regards to introductions (including your own), I think that a course which collated a range of educational scenarios would be a real benefit.

RSVPed Interested in Attending https://courses.benlcollins.com/p/apps-script-blastoff

A free, introductory course teaching Google Apps Script from scratch

I have long had an interest in Google Apps Scripts. However, I have really gotten past simply appropriating other people’s code, especially Martin Hawksey’s work.

I have read a number of posts:

I look forward to this new offering. I always enjoy the way in which Collins’ breaks things down with his courses.

RSVPed Interested in Attending https://www.crowdcast.io/e/pivot-tables-101

Come and learn how to use Pivot Tables in Google Sheets. This webinar will last for approximately 1 hour. All the example datasets will be made available to registered users.

I have watched Ben Collins’ webinars in the past, they are always well thought out and a great introduction to his longer courses. He has also written an introductory post.
Bookmarked 18 best practices for working with data in Google Sheets – Ben Collins (Ben Collins)

This article describes 18 best practices for working with data in Google Sheets, including examples and screenshots to illustrate each concept in action.

Ben Collins provides a guide for working with data in Google Sheets. Some of the useful steps that stood out were documenting the steps you take, adding an index column for sorting and referencing, creating named ranges for your datasets and telling the story of one row to check the data. Another tip I picked up from Jay Atwood has been to import data, if moving from Excel to Sheets, rather than simply copying and pasting.
Liked How to use Google Sheets: A Beginner’s Guide by Ben (Ben Collins)

This tutorial will help take you from an absolute beginner, or basic user, of Google Sheets through to a confident, competent, intermediate-level user.
Google Sheets is a hugely powerful tool, for everything from digital mark…