How to Bring value from other cells | How to Reference an array in a cell

Today’s tutorial will cover how to bring a value from other cells. How do you reference an array in a cell? Anika is a member of Better Sheets and she was the one who asked this question.

Are you ready to learn about this topic? Let’s get started!

Here is the scenario that Anika has with her Google Sheet:

The formula in D1 is referencing column A in Sheet 1 (tab). She's referencing that from another cell. She's trying to sort that.

But when she sorts D, what she assumes is going to happen is that Google Sheets would be smart enough to know that this D1 is not necessarily what she wants to sort. Rather, she wants to sort in D1. But Google Sheets is not that smart.

Google Sheets is doing exactly what we are writing here, which is saying sort D1.

Sort by the first column, the only column that exists and sort it true ascending from A to Z.

And it's looking at D1 and saying, “We’re sorting that and there it is: Sheet one, A to A.” But that's not what we want to do.

Now, what saves us and what comes to the rescue is a formula called "INDIRECT."

If we just wrap INDIRECT() around D1, now we have what we wanted done: Sorting this column from A to Z.

Now, this might sound really weird. Why would you do this? I'll give you some examples.

First off, we've answered Anika's question: Just wrap it in INDIRECT().

But then you might be asking, “Why would we ever do this? Why not just do the SORT() formula below?"

=sort(Sheet1!A:A,1,true)

You're getting the exact same result without having to do this D1 stuff. We don't have to reference another cell with it written in.

There are a few reasons you might want to do this.

Maybe this is not vertical. Maybe this is horizontal. Maybe this also has a TRANSPOSE in here.

Now we're sorting it to the right. We can also wrap TRANSPOSE around it and type in “INDIRECT.”

=Transpose(sort(indirect(D1),1,true))

Copy and paste this and we are perfectly fine right now.

What if we want to select it? Maybe we have this in a dropdown menu.

0:00

As we select this dropdown menu, our results are changing. That's pretty cool!

We can also maybe add another dropdown here for true or false.
And instead of this part saying True and that we're doing ascending, maybe we want to choose, right? In this case, we're going to go for D2.

Now we can switch the SORT(). Isn't that cool? Now we have the ability to switch.

We have the ability to switch the following:

What are we sorting?
How is it sorting?

💡

Tip: We must use INDIRECT formula when referencing another cell that has the cell written or an array inside of that cell.

This is really cool. This allows us to do some pretty cool things in our Google sheets.

Watch the video for this tutorial:

Learn from other questions about Google Sheets:

💡

How can I count true and false?

💡

Tagging Titles, Create a Comma Separated List of Tags

💡

How to Automatically Delete Rows If Cell Contains Value

Get more from Better Sheets

I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.

Join other members. Pay once and own it forever. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get lifetime access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.

Don’t make any sheets. Make Better Sheets.