Create a Dynamic Password Generator in Google Sheets

I need to create a lot of sample data for tutorials and courses. One of the things that I needed for a recent course I am building was to generate a column containing dummy passwords in Google Sheets. Each password needed to consist of letters, numbers and characters.

Usernames and Passwords in Google SheetsUntil recently, this task would have been relegated to Google Apps Script.

However, with the recent introduction of the LAMBDA function (Well, at the time of writing this anyway), we can do so much more with our Google Sheets.

Before we dive into the formula, it’s important to understand that these ‘passwords’ or random strings of characters are dynamically generated. This means every time you update a cell or reload your Google Sheet the characters in each cell will change.

So once you generate your passwords, copy the range and paste the values back in (Ctrl + c, Ctrl + Shift + v). This way only the values remain.

If you just want to grab the formula and be on your way, you can copy it from the section below. However, if you want to learn how it all works, read on for a breakdown.

The Password Maker Formula

Note in the formulas below there are three parameters that you can change:

  1. [NumChars]: The number of characters in each string in each cell.
  2. [NumRows]: The number of rows to produce the random string of characters in.
  3. [NumCols]: The number of columns to produce the random string of characters in.

Replace the items in the square [] braces with your own values.

For example, if we wanted to generate a matrix of passwords 4 rows deep, 5 columns wide and with each cell containing 12 random characters we would do this:

Matrix of passwords generated in Google Sheets
Matrix of passwords generated in Google Sheets

 

Use just numbers, numbers and letters, etc.

To quickly change the formula to produce only a certain subset of characters, you will need to delete the desired characters from the string contained in the MID function on line 14 of the example above.

You will also need to change the second argument of the RANDBETWEEN function to the length of your new string of characters.

Hint! You can quickly count the string of characters by copying the characters in the formula (including the double quotation marks on each end) and pasting it inside a LEN function. For example:

=LEN("GOAT!1234") = 9

Check out the sample sets below and their letter lengths for convenience.

Name Character Set Length
Numbers “0123456789” 1o
Letters
UPPER
“ABCDEFGHIJKLMNOPQRSTUVWXYZ” 26
Letters
lower
“abcdefghijklmnopqrstuvwxyz” 26
Letters UPPER
and lower
“ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz” 52
Alphanumeric
UPPER
“0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” 36
Alphanumeric
lower
“0123456789abcdefghijklmnopqrstuvwxyz” 36
Alphanumeric
UPPER & lower
“0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz” 62
Characters only “~!@#$%^&*()_-+{[}]|\:;<,>.?/" 29

 

Formula Breakdown

In this section, we will walk through the process of creating the random string generator. Not only does this help to provide an understanding of how the formula works, but it also gives you some insight into a good workflow for building your own complex formulas in Google Sheets.

The Video

The Starter Sheet - To Play Along

If you want to get hands-on to make things more fun, grab the starter sheet from here:

RANDOM STRINGS - Starter Sheet

I'll be referring to locations in the starter sheet as a part of the walkthrough below.

The Character List

Our character list is as follows:

0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()_-+{[}]|\:;<,>.?/

Place the list in a separate cell so you can reference it during testing. In the example (The Starter Sheet), I have added this to cell B2.

If you want to learn how to create a character list with a formula check out the tutorial below:

5 ways to create an ordered alphanumeric list in Google Sheets

Get the length of characters

The first thing we need to do is get the total number of characters in our list. This number will be used in another formula later.

We can do this quickly with the LEN function which gets the length of a string.

In our example I reference the string in cell B2 and apply LEN to it:

= LEN(B2) = 91 

Get the total length of a string in Google Sheets with the LEN function
Click to Expand!

Select a Random Character from the String

RANDBETWEEN

We can select a random number from between 1 and 91 with the RANDBETWEEN function.

Check out this tutorial for an advanced use of RANDBETWEEN Skewed Random Range in Google Sheets (RANDBETWEEN)

This function takes two arguments, the starting value and the end value. Let’s input our range:

=RANDBETWEEN(1,91) = A random number between 1 and 91

It will dynamically return a random value between 1 and 91.

Note! The Google Sheets RANDBETWEEN function updates dynamically. This means that every time you apply a change to your sheet the random number will change. 

MID

We can then use the MID function to find a character in the string at a designated position. For us, this position will be determined by the number that our RANDBETWEEN function returns.

MID takes 3 variables:

  1. The reference string or cell.
  2. The start index in the cell that contains the string.
  3. The length of characters to extract.

So in our example, our formulas would look like this:

=MID(B1, RANDBETWEEN(1,91),1) = A random character from our string.

Where B1 in our example is the string of characters.

Get a random character from a cell containing a string in Google Sheets

You can see in the example above that the formula randomly selected the pipe (|) characters from the string in B1.

Set a Sequence of n Columns

Let’s work on another part of the formula now. We need a way to create a string of random characters at any length we desire – we’ll call this n.

The first part of this process is to generate a row that is cells wide.

This can be achieved with the SEQUENCE function. With this function, we can generate a range or matrix of values at any row or column width.

The SEQUENCE function takes 4 arguments:

  1. Number of rows: For us, this will always be 1.
  2. Number of columns: We will assign our desired n length here.
  3. The Starting value (optional): This will always be 1 in our formula.
  4. The Step between each value (optional): This will also always be one in our formula.

Let’s say we want to create a formula one row deep and five columns across:

=SEQUENCE(1, 5, 1, 1) = Array [1, 2, 3, 4, 5]

Create a SEQUENCE in Google Sheets

Map a Random Character to Each Sequence Item

Now that we have our sequence, we can map a random character to each item in the sequence. To do this we use the Google Sheets MAP function. This is a helper function of the LAMBDA function set.

The MAP function allows you to traverse an array and modify each item in the array. For us, we are going to apply a random character to each item in the sequence we generated.

MAP can take a number of arrays or ranges as its first argument and then a LAMBDA function as its last argument.

The nested LAMBDA array takes an iterator item argument. This represents the current cell the item is modifying as it works through each item in the range. We need to add this argument even if we don’t use it in our formula.

The second LAMBDA argument is the formula expression. That is, what we are doing to change the value in each of the cells.

=MAP(array,LAMBDA(iterator argument, formula expression)

For us, our first argument is our SEQUENCE, and our formula expression is our MID-RANDBETWEEN combo.

=MAP(SEQUENCE(1,5),LAMBDA(cell, MID(B1,RANDBETWEEN(1,91),1)))

Google Sheets MAP a random array of characters

Note that we can always change the second argument of the SEQUENCE to change the number of columns containing random characters.

Join the array of random characters into one  password string

We can combine our random character array into a single string of characters to generate a password with the JOIN function.

JOIN takes two arguments:

  1. The delimiter – This is the value we want to use to separate each cell item when we combine it into a string. In our example, we don’t want to separate the characters so we will leave this as an empty string.
  2. The array – This will be the array we generated in the previous section.

=JOIN(delimiter, array)

=JOIN("",MAP(SEQUENCE(1,5),LAMBDA(cell, MID(B1,RANDBETWEEN(1,91),1))))

Google Sheets JOIN a MAP of a random array of characters

If you just want a single password, then this is a pretty good place to stop, but if you want a bunch of passwords across columns and rows, read on.

Generate an Array of Passwords

We can use another LAMBDA helper function for us to create an array n rows deep by rows wide. To do this we can the MAKEARRAY function.

As the name suggests, MAKEARRAY generates a 2-dimensional array of data generated by whatever is in the contained LAMBDA function.

MAKEARRAY takes three arguments:

  1. Number of Rows: How many passwords deep that we want to run.
  2. Number of  Columns: The total number of passwords we want to run across our Google Sheet.
  3. The LAMBDA function: The MAKEARRAY lambda function also requires three arguments:
    1. The Row Argument: We won’t use this or the Column argument, but they are mandatory for a MAKEARRAY function. We’ll call this argument row_index.
    2. The Column Argument: Again, this is not needed for our password maker, but we will name this argument column_index.
    3. Formula Expression: Here we can insert our password generate from the previous section.

We will also add the string data now into our entire formula. We don’t need to keep it separate.

Google Sheets MAKEARRAY password generator

In the example above we have created an array two columns wide and five columns deep with a random password length of 5 characters (See the second argument of the SEQUENCE)..

Conclusion

This password generator is a really helpful tool for templating spreadsheets to create examples or quickly generate a password or random string of characters for codes.

As we mentioned above, the passwords will change each time you update the sheet. The best solution to provide a static password would be to use a bit of Google Apps Script Magic connected to an onEdit() function similar to this tutorial:

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) Google Sheets (Updated January 2022)

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

 

~ Yagi

How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

  • Automatically change your start and end times.
  • Changing your step increments from a minute to, say, every five minutes or an hour.
  • Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

  1. A whole day list of times in 1-minute intervals
  2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

Continue reading “How to create a time sequence in Google Sheets”

New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃

It’s the big day!

I’m very excited to introduce you to my new course:

Google Sheets: Learn the Essentials with Three Detailed Projects

Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three detailed projects.

And because this is the opening week, I am offering a whopping 60% off the course for the next 7 days only.

Just use the Coupon Code…

NEWCOURSEDISCOUNT

…on the checkout page and you can start the course today.

CHECK OUT THE COURSE!

Google Sheets Project Main Page
Click to Expand!

First 5 Chapters Free

I have also opened up the first 5 Chapters of the course for you to try completely FREE.

Go on and give it a try now!

CLICK HERE!

Share the love

If you are already a Google Sheets legend, you know how important it is to learn how to master spreadsheets. If you know someone transitioning industries, just starting out or simply needing to boost their skillset please share this post.

This is a whopper of a discount and not one to miss.

The offer expires on Sunday the 9th May 2021.

DON’T MISS OUT!

Why I built the course

This has been a project I wanted to build for a long time. The best way to learn something is by getting hands-on and building a project.  That’s how I know I learn best.

I wanted to build a Google Sheets beginners to pro course that I wanted when I first started in the world of Google Sheets. Something with real-life examples and a set of projects that I can build and maybe even modify and reuse in the future. Something that not only gives me the how-toos but the why I should be building and designing my spreadsheets in a certain way.

Go on, check out the link to find out more about the course and try out the first 5 chapters on me. I know you are going to get a lot out of it.

FIND OUT MORE!

This offer expires on Sunday 9 May 2021.

~Yagi 🐐

The Monster Guide to Data Validation in Google Sheets: Free Course (Updated May 2022)

In this tutorial, we will cover everything there is to know about Data Validation in Google Sheet.

Why am I writing this tutorial? Well, there is a lot to cover for one, but I also find that there are a lot of snags and nuances to Data Validation in Google Sheets that make it easy for even the experienced user to get stuck.

I didn’t start out to write a course on data validation. To be honest, I was just preparing some notes for a short introduction as a part of a beginners video series, but the more I looked at it the more I found that data validation really deserves a deep dive on its own.

There are even some things in this course that I learnt that I wish I had known years ago that would have saved me and my team a lot of grief.

If you are coming at data validation as a complete beginner or a seasoned spreadsheets veteran, there should be something in here for you to learn. Below you will see a contents page of major topics and you will also find an embedded video tutorial for each of the topics if you need to just jump to something important.

Using the contents page to jump to a link will also give you a URL to that item if you want to bookmark it for quick reference later.

Let’s dive in!

Data Validation Basics

What is data validation?

Data validation helps you to control what your users enter into your cells. Among many other things it also allows you to:

  • Create drop-down menus for your users.
  • Provides date pickers.
  • Ensure users of your Google Sheet to enter text into a cell only.
  • Validate emails and URLs.
  • Even create designer rules for your users to abide by to ender data in a cell.

Why is data validation important?

Let’s say you need users to put in a set of numbers in a cell. These numbers are then calculated in another cell by one of your awesome formulas. Perhaps your data entry users were not properly instructed or were confused about their task. Without data validation, they could put in words instead, breaking your carefully crafted formula.

Likewise, you may want to reduce responses to a question you have asked in a sheet to specific items that your users select from a dropdown list for you to better analyse the frequency of the response. Without data validation rules, your users could enter any response that may well be similar, but is now incredibly difficult for you to analyse.

Finally, you may just want to help guide your uses and provide convenient suggestions for them to enter their data in cells. You can also do this with data validation.

How do I get me some data validation goodness?

To get to data validation in Google Sheets, you have two approaches. First, select the cell or range for your data validation and then:

  • In the menu bar go to  Data > Data validation. OR
  • Right-click and scroll to the very bottom and select view more cell actions << data validation.

Data Validation basics video

Check out the Data Validation basics video for a quick tour of what this Google Sheets tool can do for you:

In the video tutorial, I’ll cover,

  1. 00:00 Intro.
  2. 00:17 Accessing the data validation menu.
  3. 00:41 What does each part do?
  4. 01:12 What can I choose to use data validation for?
  5. 02:10 Criteria – List from a range in your Google Sheet.
  6. 04:49 Criteria – List of items not in your Google Sheet.
  7. 05:18 Criteria – Having to choose a Date.
  8. 06:29 Criteria – Choosing numbers between a range.
  9. 08:14 Removing Data Validation

List from a range. Dropdowns!

In this part of the course, we will dive into creating dropdown menus from reference data in your Google Sheet. I’ll guide you through how to set up and organise your reference data to work best for your data validation list from a range dropdown.

We will even dip our toes into creating a basic dynamic dropdown list that changes based on your selection of a previous dropdown list.

Check out the video below!

In the video tutorial, I’ll cover,

  1. 00:00 Preview of what our dropdown will look like.
  2. 00:22 Creating an ordered dropdown list from a range of cells.
  3. 01:59 Creating a dropdown list from horizontal data.
  4. 02:45 Create a dropdown list from a matrix of data.
  5. 03:50 Using relative and absolute values for dynamic lists.
  6. 05:57 Wrapping up data validation list from ranges.

An  extra note on relative and absolute ranges in data validation in Google Sheets

As we mention in the video above, you can make your dropdown somewhat dynamic by using relative ranges.

With a normal Google Sheets formula or function in a cell, you are able to drag down the cell to duplicate the range. In this case, the formula will update automatically and reference the data in the next row or column of the cell depending on if you dragged the data down or across.

For example, in cell C1 we have the formula = A1 + B1. If we drag C1 down to cell C2. Then the formula in that cell would be = A2 + B2. Drag it down again and the formula is =A3 + B3 and so on.

If you don’t want your formula reference to change you are ostensibly locking them and making them absolute. So if we want to just add A1 to all the cells in column B we could change our column C equation to look like this = A$1 + B1 for our first row. The dollar sign indicates that row one will be locked or made absolute.

Dragging the formula down now to cell C2 will result in this  = A$1 + B2, C3 to = A$1 + B3 and so on.

We have locked the row in place.

If we drag our C1 cell to the right to cell D1, however, we would get this = B$1 + C1 . So to lock cell A1 from moving to the right we need to lock the column too with a dollar sign like this = $A$1 + B1.

You can find out more about relative and absolute cell references in my tutorial here:

How do I lock certain cells in a formula in Google Sheets?

In data validation, it is a little different, however. Your data validation’s row and column cell references will come locked or absolute out of the box. You will need to update them manually. You can do this by adding an equals sign at the front of your range and removing the relevant dollar sign ($) reference, just like in the video.

List of Items

You can also create a data validation dropdown list from a list of items in your Google Sheets cells. One of the benefits of this is that you don’t need to reference any data in your Google Sheet.

Nevertheless, there are some things to consider and I will be covering them in the video below.

Check it out!

  1. 00:00 Introduction to creating a list from a range with data validation.
  2. 00:08 Why use List of Items rather than List from a Range.
  3. 00:18 What it all looks like.
  4. 00:30 Creating a list from items.
  5. 01:43 Duplicating the dropdown.
  6. 02:01 A better approach to duplicating data validation rules.

Validating Numbers

You can also restrict cells to only display numbers or even ranges of numbers. Google Sheets data validation has a large list of rules to help you set your cells up just how you want them.  Having said that, there are some pitfalls that you may fall for that I hope to steer you away from in this video below. I’ll also show you a few alternate solutions.

Have a gander!

  1. 00:00 Intro to using number data validation.
  2. 00:25 Number is between two digits.
  3. 01:14 Can I use decimal numbers?
  4. 01:32 Numbers is not between to digits.
  5. 02:18 Enter a number less than a value.
  6. 02:52 Number is less than or equal to a value.
  7. 03:15 Number is greater than a value.
  8. 03:43 Number is greater than or equal to a value.
  9. 04:07 Number is equal to a value.
  10. 05:12 Number is not equal to a value.
  11. 05:31 Number is between multiple sets of ranges.

Validating Text, Emails and URLs

The text data validation in Google Sheets is more than just validating some simple text, it allows you to check if certain text exists in your cells or not. The text criteria allow you to check for valid emails and URLs. However, like most technology, there are some limitations that we will highlight in the tutorial below:

  1. 00:00 Intro to using text validation in Google Sheets.
  2. 00:11 Enter text that contains certain a certain string of text characters, word or phrase.
  3. 3:05 Enter text that does not contain a certain string of text characters word or phrase.
  4. 03:54 Enter text that equals a certain string of characters, word or phrase.
  5. 04:19 Enter a valid email.
  6. 05:36 Enter a valid URL or website.

While text validation has a few weaknesses we can use custom formulas to resolve these. I will provide a few examples in part 1 of using custom formulas with data validation.

Validating Dates and a Date Picker in Google Sheets

I’ve really leaned on date data validation quite a lot in my Google Sheets career. It is extremely helpful to help guide users into putting in the correct date into the spreadsheet that you need to conduct good analysis.

We will also talk about educating your team on using the hand dandy date picker!

Check out the video!

  1. 00:00 Intro to date validation.
  2. 00:16 Enter a valid date (My favourite!).
  3. 00:34 Users entering different date formats. Is it a problem?
  4. 01:04 Formatting data validation cells. The solution to many of your date validation issues.
  5. 01:47 Displaying a date picker and quickly showing your users how to find it.
  6. 02:49 Enter date equal to a specific date.
  7. 03:41 Enter date before a specific date.
  8. 04:58 Enter date on or before a specific date.
  9. 06:52 Enter a date after a specific date.
  10. 07:20 Enter a date on or after a specific date.
  11. 08:10 Enter a date between two dates.
  12. 09:20 Enter a date that is not between two dates.
  13. 09:56 Wrap up.

While there might seem like a bit of repetition in this tutorial, I have sprinkled in a few troubleshooting tips and tricks along the way to help you better prepare your date validation.

Custom Data Validation with Using Simple Regular Expressions

I won’t lie, exploring the depths of custom data validation is a vast and endless rabbit hole (Of fun! 🐐).

I’ve gone ahead and split up this part of the course into two chapters. This first one explores using custom data validation with regular expressions.

Wait! Please done run away.  Here! Here’s a cookie… 🍪. Okay, gang! Grab em and hold them down! Someone get the eye vices.

Yeah, yeah, regular expressions can be scary. They are so esoterically irregular (Pun intended) that they can be quite difficult to memorise or master. But I will let you in on a little secret: only the most bespeckled, sun fearing of goats memorise data validation rules. The rest of us just look them up.

In this chapter, we will cover just a few very common rules that you can simply copy and change for your own use. Hopefully, they should be useful for your day to day Google Sheets work.

Check out the video!

I this tutorial we will cover:

  1. 00:00 Intro to custom formulas.
  2. 00:09 A basic example.
  3. 01:54 Removing data validation.
  4. 02:02 Cell must contain the word.
    1. Formula example: =REGEXMATCH(B2,"\b(be)\b")
  5. 05:03 Must contain at least one word from a list.
    1. Formula example: =REGEXMATCH(B3,"\b(be|at|in)\b")
  6. 07:01 Must start with a word.
    1. Formula example: =REGEXMATCH(B4,"^(Be)\b")
  7. 08:09 Must end with a character.
    1. Formula example: =REGEXMATCH(B5,"\?$")
  8. 09:34 Wrap up.

Got a bit of Stockholm Syndrome for regular expressions now? Check out a couple more examples of using regular expressions in Google Sheets:

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets: Conditional Formatting with Custom Formula

More Data Validation Using Custom Formulas in Google Sheets

In this second part of using custom formulas in our data validation, we will branch out and use some other formulas to create some interesting validation rules. We will also start using some more compound formulas that I hope will inspire you to create your own custom formula rules. If you come up with a good one I would love to hear about it in the comments below.

I’ll be sure to add the formula examples for each part in the show notes below the video if you want to copy and modify them for your own project.

  1. 00:00 Intro to custom formulas in data validation part 2.
  2. 00:27 Greater than the current day’s date.
    1. Formula example: =B6>TODAY()
  3. 02:28 Date must be a weekday.
    1. Formula example: =AND(WEEKDAY(B7) <> 7, WEEKDAY(B7) <> 1)
  4. 04:47 No more than 15 characters in the cell.
    1. Formula example: =LEN(B8) <= 15
  5. 06:08 Must meet specific phone number parameters.
    1. Formula example: =AND(LEN(B9) = 10, NOT(REGEXMATCH(TO_TEXT(B9),"\D|\s")))
  6. 10:14 Whole numbers between 1-20.
    1. Formula example: =AND(ISNUMBER(B10),
      NOT(REGEXMATCH(TO_TEXT(B10),""[.]"")),
      B10>0,
      B10<21)
  7. 12:37 Wrap Up

Oh…yeah. I squeezed in some more regular expressions too. Umm…sorry. Not sorry. 🐐

Using Data Validation on Checkboxes

In this chapter, we will look at how we can control check or tickboxes in Google Sheets. One of the cool things I discovered is that  I  changed the true and false condition to whatever I want.

Such power! Yes, I abused it.

Have a look a the video, if you dare!

  1. 00:00 Intro checkbox validation.
  2. 00:10 Enforce entry of true and false in a cell.
  3. 01:18 Change what is truthy and what is falsy.
  4. 02:38 Changing a checkbox from another cell.
  5. 04:21 Wrap up.

Update dropdown list in Google Sheets dynamically based on previous dropdown choice

That’s a mouthful. However, this is one of the most searched for things people want to do with data validation. In our previous chapter on List from a Range we covered one approach, but it is kinda messy.

Here, I will give you a more appropriate example on how to create a dropdown menu that changes based on another menu choice.

One great example of this is a list of cities after you have chosen a state. Or a list of parts once you have selected a specific tool.

Here is the link to the starter file if you want to follow along. Go to File > Make a copy to generate and edit your very own version.

Starter File

Here’s the video!

  1. 00:00 Intro.
  2. 00:16 Demo example.
  3. 01:23 Why using relative cells is not a great option.
  4. 01:44 Add a ‘Notes’ Google Sheets tab.
  5. 02:24 Designing the input data.
  6. 02:33 Getting a unique list of items.
  7. 03:13 The item selector dropdown setup.
  8. 03:39 Create a dynamic option list in ‘Notes’.
  9. 04:39 Create a dynamic dropdown list.
  10. 05:00 Wrap up.

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice.

I’ve added a bonus tutorial on dynamic dropdown lists here after some comment queries. Check it out. It contains a detailed written tutorial and a YouTube video!

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)

Conclusion

Hey, you made it! Congratulations on complete the course. I hope you found it useful. It was a big one to build for you, but I enjoyed the process.

So what did you find useful? Can you think of anything  I missed? I would love to hear you thought in the comments below.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

 

Changelog

31 May 2022 – Updated List from a range. Dropdowns! video with an updated ending that better describes the dynamic and static movement of selected ranges when they are duplicated down or across a sheet. 

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)

Okay, wait! Stop!…

…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.

I promise to give you some clear examples with an explanation of each to you can apply it to your project.

Take a deep breath, mate, and let’s get cracking.

We are going to look at two related scenarios:

Scenario 1:

Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.

Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.

Scenario 2:

You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.

Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.

 

We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.

Continue reading “Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.”

%d bloggers like this: