Create links to the first item of each group in Google Sheets

Imagine you have a Google Sheet tab containing a course worth of hundreds of students sorted into classes or groups. After each exam, quiz or assignment, your teacher for each class needs to enter in the grades.

To make life easier for your teachers, we want to create a summary page where we can click on their class and be navigated directly to their group.

We don’t want to have to manually create a link for each location so we are going to write a formula to do the work for us.

Now, the formula is a little complex. So first off, we will generate our list of classes and add a separate section start link for each one. Then we will combine the two together to generate a single formula that gets all the unique sections and provides a link for each one.

If you are playing along, you can grab a copy of the starter sheet here:

STARTER SHEET

Getting a unique list of classes from the Grade Google Sheet tab

The Unique List

Our first task is to get a list of all the classes in our grade sheet without duplication. We can do this with the Google sheets UNIQUE function. UNIQUE takes a range of data as its first argument. For us this is the Class column in our Grades sheet tab.

In cell, Summary!A2 we will add:

=UNIQUE(Grades!A3:A)

Sorting out that Empty Cell

If you look at the image above you will see that we have an empty cell that shouldn’t be there.

What’s going on?

Well, to make things a little easier to read for our teachers we have separated each section with a space indicated in black.

Note! We didn’t manually space each cell. I actually used my Free Google Workspace Add-on Spacer to do this quickly.

We can use the SORT function on our UNIQUE range to fix this.

=SORT(UNIQUE(Grades!A3:A))

Create a link formula for each class

Now that we have our column list of all our classes in Column A of our Summary Sheet tab we can add our link to the start of each section.

We will be applying our formula here starting in cell Summary!D2 and duplicating the formula for each section.

Finding the Row number of the first item in the group

Our first task is to find the row number for the first item for each class in the Grade Google Sheet tab.

We can do this with the MATCH function. MATCH return the position of a cell based on a search value.

The position of the cell is based on the range provided in the second argument of the function. For us our range will start on row 3 so we will need to add the first two rows to our final count to get the correct row.

MATCH takes 3 arguments:

1. Search Key: The item to search for in the range. For us this is Cell A2 for our first item.
2. Range: The range of data to search in. We will start our range in the Grades sheet from cell A3 and keep our range. Note that we have locked in our range with dollar signs or made the range absolute. This means that when we drag the formula down, the start row will not update to A3, A4 etc.
3. Search Type: We use zero (0) here to state that we want an exact match.

=MATCH(Search Key, Range, Search Type) + Range offset

=MATCH(A2,Grades!$A$3:$A,0) + 2

Drag the formula down column D to calculate all the start rows to see the results.

Getting the URL

Next, we need to get the URL of our Google Sheet including the Grades tab and the column.

An easy way to do this is:

1. Navigate to Grades!A3.
2. Right-click.
3. Select View more cell actions down the bottom of the dialogue.
4. Select Get link to this cell.

This will copy the URL to this cell in your clipboard.

This will give you a link that will look a little like this:

https://docs.google.com/spreadsheets/d/10HyVOStfgtf-w7CZvLtrxGJd_7eqMmqiFAQjn6VLSi4/edit#gid=1225353740&range=A3

https://yagisanatode.com/2020/11/27/google-sheets-linking/

Creating the Hyperlink in the cell

Our final job is to combine our matched row with our link to create our direct link to the first item  in the group.

We do this by using the HYPERLINK Google Sheets function. This function takes two arguments:

1. URL: For us this will be our copied URL minus the row number. We replace the row number with our match formula.
2. Link label: The label we are going to give to the link.

Let’s update our formula in Summary!D2:

=HYPERLINK("spreadsheetURL/edit#gid=sheeID&range=A" & MATCH(Search key, range ,search type) + range offset, "link label")

=HYPERLINK("https://docs.google.com/spreadsheets/d/1UoTPauxWT1hhLxcrkXBGN-K9WitThwiPFykvzf0JeFc/edit#gid=1225353740&range=A" & MATCH(A2,Grades!$A$3:$A,0) + 2, "Section Start")

 

Note how we remove the row number from the range at the end of the URL and used the ampersand (&) to join it to the result of our match formula.

...range=A" & MATCH(...

Go ahead and drag this new formula down to get the link for the location for each group in the Grades sheet. Then test it out!

You might be satisfied with leaving it there, but you can take it to the next level by combining all the formulas we have used so far into just one.

Pretty cool I reckon. Check it out below.

One formula to collect all unique classes and apply a link to them

Okay. Time to show off.

Let’s use a single formula to create a unique list of all the classes in our Grade sheet tab and add it’s corresponding location link to it Doctor Manhattan-style.

Let’s start with the formula and then break it down:

As you can see there is a lot of familiar territory here.

Go ahead and paste the formula in Summary!F2 of the Starter sheet and watch the magic unfold.

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

ARRAYFORMULA

The Google Sheets ARRAYFORMULA function allows us to apply single cell formulas to a range of cells.

In  our formula, ARRAYFOMULA allows us to apply a range to the first argument of our MATCH function ( The search key) and also the link label argument of our HYPERLINK function.

Now our formula iterates through each unique class searching for each one and finding its starting row in the Grades sheet and applies the class name to the label of the link.

IFNA – The tidy up

Our SORT-UNIQUE formula will include an empty row that we have shuffled to the bottom of our list. When we apply our MATCH on this empty row we will get an N/A result.

To hide this N/A result we use the IFNA function. This function encapsulates the main formula inside the ARRAYFOMULA as it’s first argument. It’s second argument asks what you want displayed if there is an N/A result. For us, we want to leave it empty so we make an empty string. Line 9

=IFNA(main formula, what to display on N/A)

Updating HYPERLINK

In the first argument of you HYPERLINK function our URL string stays the same. Again we join MATCH to the end of the URL to get the desired row. Lines 4-6

As our second argument we add the SORT-UNIQUE formula from Cell A2 of the Summary Sheet tab. This will allow us to display each class on each row as the label for the link. Line 7

Updating MATCH

In the MATCH function’s first argument we need to add a search key. Because we are using ARRAYFORMULA we can now provide a list of keys to iterate through.

This means that we can add our SORT-UNIQUE formula to this first argument and MATCH will look at each unique class name and find its respective row.

The Video

Conclusion

Linking to the start of grouping of data in a range can be a huge timesaver for you and the teams you share your Google Sheet with.

What would you implement this in? Sales sheet, experimental test group data, project management list? I’d love to hear 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.