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

Intermediate

About this Tutorial

She has a cell, this D one, in which it says, for instance, another array or a row or column in this case, a column, a on sheet one where we're on and she's referencing that from another cell.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

INDIRECT() SORT() TRANSPOSE()

Video Transcript

<div>0:01 Hello. So Anika a member of better sheets asked this question.

She asks, uh, so she has a cell, this D one, in which it says, for instance, another array or a row or column in this case, a column, a on sheet one where we're on and she's referencing that from another cell. 0:23 She's trying to sort that, but when she sorts D one, uh, what she assumes is going to happen is that if indie one is written sheet one, a colon, a, uh, correct array or reference to another set of cells that Google sheets would be smart enough to know that this D one is not necessarily what she wants to sort , but rather she wants to sort in D one, but Google sheets is not that smart. 0:51 Google sheets is doing exactly what we are writing here, which is saying sort D one, uh, sorted by the first column, the only column that exists and sorted true ascending from a to Z . 1:05 And it's looking at D one and saying, or sorting that, and there it is, she wasn't, <inaudible> the text, but that's not what we want to do now. 1:14 What saves us and what comes to the rescue is a formula called indirect.

So if we literally just wrap indirect around the one, now we have what we wanted done, which is it is sorting this a column from a to Z . 1:34 Now this might sound really weird, right? Like, why would you ever, why, why would you do this? Right? And I'll give you some examples. 1:43 Like the question is, why would I ever do that? Well, first off, we've answered a Nika question, right? Just wrap it in indirect . 1:52 But then you might be asking what, like, why would you ever do this?

So we say, what if it was indirect ? 2:01 So, so why would you not just do this sheet one exclamation point a colony. You're getting the exact same issue without what you're getting the exact same result without having to do this <inaudible> stuff, right? 2:18 We don't have to reference another cell with it written in, but there are a few reasons you might want to do this. 2:24 Say, this is not, maybe this is not vertical. Maybe this is horizontal.

Maybe this house, so has a transpose in here right now, we're taking this list, sorting it to the right. 2:39 And we want sheet one eight to, we want B to B here. We want sheet one C to C. And if we copy paste this right, we are not getting, we have to go in here and do exactly the sheet, but we wrote it all here already. 2:57 So we could actually just do this. We can go here, wrap this around in direct. And I think we have to add one more print . 3:08 You know, we don't now copy and paste this and we are perfectly fine right now.

This might be on a different sheet, right? 3:17 We might add another sheet here. And now we can say in the first column, second column, third column. And we, all we have to do is write this. 3:26 So maybe we have a programmatic way to write this, or it's not just an entire, um, right. Or maybe the name of the sheet changes. 3:36 We want to change that for a variety of reasons. We want to have an indirect thing.

We want to make sure that we can edit it as text. 3:46 There's a few other reasons let's go back to here where we have just one and we have this sort of result. 3:54 And we're saying here, well, what if we want to select it? Maybe we have this in a dropdown menu, a few more cells , data validation . 4:04 We go list of items , sheet one, a colon, a sheet, one B to B, move my face, click, save my face back. 4:17 Now, as we select this drop down menu, our results are changing. That's pretty cool.

We can also maybe add another one here for true and false. 4:29 We're going to list of items . True. False save. It's like true. And instead of this part saying true, yes, we're doing ascending. 4:41 Maybe we want it descending or, well, we want to choose. Right. And in this case, we're going to do D two. 4:47 So now we can switch the sort isn't that cool. So now we have the ability to switch . What are we sorting? 4:56 Then we have the ability to switch . What, how is it sorting now that that's cool.

So now we have two things where we must use that indirect when referencing another cell that has the cell written or an array inside of that cell. 5:11 Um, we can, this is really cool. This allows us to do some pretty cool things in our Google sheets. Thanks so much for a Nika ask. 5:20 If you are looking, uh, for other questions, if you have a question and you want to ask just like a Nica, did I do have a video called how to ask or how to ask questions, how you can help just a search through the better sheets.co for how to ask.</div>