Replied to

I agree Ben, I often use Sheets to quickly add various tags when compiling links for my newsletter. I documented this in the past when I used to compile my own regular Google update.
Replied to Dream Home Office Setup – (benlcollins.com)

Iโ€™m using the new ๏ฃฟ Apple Mac Mini with the M1 chip, powering 2 monitors: an ultrawide Dell U3419W (supported by a Fully Jarvis monitor arm) and an Acer R240HY.

The microphone is a Blue Yeti on a Blue Compass arm, and the light is an Elgato Key light.

Everything sits on Fullyโ€™s Jarvis standing desk, which Iโ€™ve had for years and love.

Thank you Ben for sharing your setup. Another example to add to the list.
Replied to Google Sheets Custom Number Format by Ben Collins (benlcollins.com)

Google Sheets custom number format rules are used to specify special formatting rules for numbers.

These custom rules control how numbers are displayed in your Sheet, without changing the number itself. Theyโ€™re a visual layer added on top of the number. Itโ€™s a powerful technique to use because you can combine visual effects without altering your data.

Ben, this is a fantastic breakdown of the custom number formats in Google Sheets.

In a data set I created, I have used it to add an ‘E’ and the word ‘Group’. This allows me to sort numerically, as well as display the text visually.

Bookmarked Formula Challenge #4: Sort A Column By Last Name (benlcollins.com)

Your challenge is to create a single formula in cell B1 (shown in yellow below) that sorts this list alphabetically by the last name.

Ben Collins breaks down four responses to his formula challenge:

=SORT(A1:A10,INDEX(SPLIT(A1:A10," "),,2),1)

=ArrayFormula(QUERY({A1:A10,SPLIT(A1:A10," ")},"select Col1 order by Col3"))

=INDEX(SORT(REGEXEXTRACT(A1:A10,"((.*)( .*))"),3,1),,1)

=SORT(A1:A10,REGEXEXTRACT(A1:A10,"(?: )(\w*)"),1)

I love how a problem can have so many approaches.

One thing that I picked up is the ability to split a column and create an array on the fly. This is something that I really need to work on as too often I create hidden tabs which such fields and formulas.

Replied to

Thank you Ben for the podcast recommendation about online learning. Not sure exactly what a ‘Type 2’ Ben Collins course would look like. Maybe it is about bringing your own problem or something. Look forward to what you come up with.

You might be interested in Jon Dron and Terry Anderson’s book Teaching Crowds. I discussed it here.

Replied to

Ben, this reminds me of the challenge of having a manager who is also a growth coach. Assume it is not impossible, but also probably not ideal.
Bookmarked How To Connect Google Sheets To A Database, Using Apps Script – Ben Collins (Ben Collins)

Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.

In this post we cover a few ways to get data from your SQL database into Google Sheets.

I usually drag in data from my blog via RSS, however I wonder if this might be a cleaner method.
RSVPed

Ben, SheetsCon looks great. However, being in the middle of the night I am not sure how feasible it is. Just wondering if the video stream will be made available after the event as well?
Replied to Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins (Ben Collins)

Every Monday I send out a Google Sheets tip email and occasionally Iโ€™ll include a formula challenge. I posted Formula Challenge #3 โ€” to alphabetize a string of words separated by commas using a single formula โ€” in January 2020 and had over 150 replies! It would have been too time consuming to reply to โ€ฆ Continue reading Gmail Mail Merge For A Specific Label With Apps Script

Ben, I really need to find some time to dig into these ideas for automation and your work on Apps Script. Along with Martin Hawksey’s post on sending personalised images, they offer some great ideas for improving workflows.
Bookmarked

Ben Collins collates a number of productivity tips in a thread. A great entry point to Sheets and Collins’ work.
Bookmarked Excel To Sheets Helping Organizations Migrate To Google Sheets (exceltosheets.com)

I help organizations make the switch from Microsoft Excel to Google Sheets through workshops, webinars and online courses.
Iโ€™m a Google Developer Expert specializing in Google Sheets. Iโ€™ve taught spreadsheet workshops and courses for the past four years.
There are a lot of myths surrounding Goog…

Ben Collins has created a site/guide to support the move from Excel to Google Sheets.
Replied to 2019 In Review And A Look Forward To 2020 – Ben Collins (Ben Collins)

Best wishes to all of you for 2020! This is my 5th annual review post. Iโ€™m proud and thankful that I get to write this post every year, because it means Iโ€™m still running my own show and my business is still going. At this time of year I like to pause. Stop doing the โ€ฆ Continue reading 2019 In Review And A Look Forward To 2020

Well done on the year Ben. I liked your point about the challenges of multiple roles:

By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. Itโ€™s hard to not feel like youโ€™re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!

This reminds me of a piece from Austin Kleon about the changes moving from one child to two.

Replied to Google Sheets Tip 74: A book, a challenge and the RANK function

I’m thinking about writing a Google Sheets book (perhaps something like thisย Excel one) —ย would you buy it?

You can reply to this highly scientific poll with a quick Yes / No email. If you want to add why, I’d appreciate that insight as well!ย ๐Ÿ˜‰

Personally, I prefer posts and courses, rather than a book.
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.