00:00 Hello. So you have a ton of different information across several sheets, and you want to compile all that together. New one sheet.
00:09 This is the video that I'll show you one way to do that. Uh, but I do want to give a shout out to member, uh, Axel, who brought this to my attention and actually a few, um, better members have asked a similar question or a question where the solution is pretty much the same.
00:27 We have a lot of different information across several sheets, and we want to bring that all together into one tab.
00:33 Now, uh, we want to make sure that we know where that information is coming from. Um, but I'll show you one way.
00:41 Again, there's several ways, and this is pretty much the complete opposite of another video, where I show you how to take a, a data on one cheek and filter it and move it along to other sheets.
00:52 Um, this is taking a bunch of different sheets that all have the same data structure, but bring it all together into one tab.
01:00 And you might want to use this. A couple fun use cases are, um, you want to create a summary or a dashboard and you needed the data all in one place.
01:08 So you can do averages some filters from that. Um, if you want to look at the entire history of say, um, different statuses across the entire project, and you have those in different tabs, maybe you're moving things around.
01:24 You're giving views to different parts of your workflow, to different people, but you really want to see it all in one place.
01:31 So this is how you take data on separate tabs and create a tab of tabs or a sheet of sheets.
01:38 Um, we're going to take information on sheet one and sheet two here, and we're going to view it along all on the tab called all.
01:48 And along with that, we're going to add where that data came from. So you see she one has five give a Rose and it has five pieces of information here.
01:59 We're going to do this with two formulas right now, though, I'm going to finish this up. I'm going to insert it right above, and I'm going to call it sheet source, or as soon as you call it source, and this is priority.
02:14 I remember from the, and this is, I don't know, name, it's not name Oh over. And she wanted to look at it.
02:20 So she, one is task priority and task, and then we have source. And then I'm going to show you how to fix this if we want to add like a third column.
02:31 So first off before we start, I just want to make sure you know, that she one, she two or both structure the same way.
02:40 Sometimes you might duplicate a sheet and then start adding information. You want to combine their sheets. Um, that's a good way.
02:46 Just double check that your sheets all have the same structure when you begin this. So in the hall again, if you want to, uh, just go ahead and grab the, um, formulas are here.
03:00 Go ahead and do that. You have access in the description, uh, to the sheet, um, um, the formulas, but I'm going to share with you first, um, how it works, then I'm going to show you how to sort of take it apart a little and change it.
03:13 Okay. And, and add something to it. So, you know, sort of how it works. So first off in a two, we have, um, this what looks crazy, but let me, um, let me actually just cut this down a little.
03:28 Let me just cut out this half. And if we cut out that half of those, we see, we only have one thing sheet one repeated five times now, how do we know how, how many times it's repeated?
03:41 It's this killer count. We're counting. We're going to sheet one. We're going to call them a doing column, a two to the end.
03:48 And we're saying, how many is there? That's going to bring us a number and we're going to use this R E GT or wrapped or repetition formula to repeat sheet one that many times, however many times you've counted in there.
04:02 And we're going to use this, um, pipe, uh, character, uh, to repeat it, we're going to add that. Then we're going to split it and transpose it.
04:11 So we get an array and that's where we get this, um, curly bracket. So we can also use Ray formula here, think, um, instead of curly brackets and we can get the exact same thing, but for, for less typing, I chose to do this curly bracket.
04:28 And so we're going to change that last one to curly bracket. And, and in order to add, say, maybe we have three or four or five sheets in this case, we only have two.
04:37 So I'm going to show you how to add to it. If you have more than two sheets, we're going to add a semi-colon at the end here, we're going to take them entire, um, formula from the curly bracket to the end parentheses.
04:53 We're going to copy it, command C and paste it. Now, if we just hit enter, we're going to get sheet one 10 times, right?
04:59 Five times plus five times. I really want to go into this, uh, four second formula and change sheet one. Does she too, and also here your sheet one to sheet too.
05:10 And now we have sheet two repeated five times. Why? Because there's five rows. Uh, if we go to sheet one, let's add a sixth task here, six tasks and automatically fingers crossed.
05:27 We now have sheet one and it's in six tasks. So we can name these obviously very differently, right? They don't need me.
05:35 She wants you to, they may be, um, uh, status, different statuses or different people in your org. If you're managing say a sales team or sales reps, um, all right.
05:48 And then the next formula you need to be here in B two. This takes care of everything else. Now this is using indirect and I'm just going go very quickly over this.
05:58 Um, I have a couple other videos that deal with indirect, but generally speaking indirect, what it does is it gets a sheet, um, source.
06:10 And we're going to not really concatenate. We're going to add this and sign, which is really like concatenate. We're going to concatenate on the count of how many, again, the, the core of this is we're counting how much stuff is on sheet one.
06:23 And then we're going to find that all the information from eight two until the end, we have to add this plus one, because this count is going to get us only the number of rows that are between a two to a a, to the end.
06:38 We need to add one because we were missing that first one. Right? Um, and we're really wanting to go to that C X right then five plus one.
06:48 We want to go to the sixth, um, row here. All right, then again, we are using the curly brackets and a semi-colon to get this array of sort of push all these arrays together.
07:04 And you see here, it's the exact same thing, repeated this indirect sheet to, uh, to a, to colon C. And then we add in the count and we make sure that that sheet two and that sheet two are different than the sheet one sheet.
07:19 Right? So again, if I want to add a sheet three here, let's do that. And I'll sh and then maybe this will make more sense.
07:26 We're going to duplicate this. I'm gonna say sheet three. Now all of this is, this is quote unquote, the same information, but it's assigned to a different sheet.
07:35 We want to make that apparent in this all. So we're going back here. We're going to this indirect, all of this From Here to the end, not including the curly, Brian, we're going to copy it at a semi-colon paste it.
07:55 Now, if we just left this alone, right, we get the exact same thing. We don't want to get the exact same thing.
08:01 We want to get it from sheet three. So we go back here. We're going to change this third one to sheet three.
08:08 No, you only have to do this once. And this is really helpful. Someone earlier, um, uh, another member asked, you know, they showed me these very complicated formulas, where they were combining all this data from different sheets, multiple times, like every single formula had all of these sheet things
08:25 . The good thing is you do this once, and now you have all the data in one place, and then you can do averages sums all that stuff.
08:34 You don't have to redo this formula again. And again, you do it once and you're done. So now we're getting it from sheet three.
08:42 And we can tell, because Let's put here, do this third Double check that that is actually sheet three, go back to all.
08:56 And there it is, do this third. Now we need to fix the labels. Okay. So again, all we have to do is copy all of this from the semi-colon to the curly brackets.
09:10 I'm going to do this. Add a semi-colon just before the last curly bracket paste, it, change the sheet two to sheet three and change the count three.
09:27 And now we have sheet three here, five times, right? And she wanted six times. Uh, again, we can go and double check that this is going to change.
09:38 If we want to add a sixth one here, six tasks for sheet three, and we want to make sure that is labeled sheet three, right there, sheet three.
09:51 And it's automatically in this tab of tabs or the sheet of sheets. It's really exciting that really all we have to do is these two formulas.
09:59 Make sure these are correct, and everything else falls into place. Now we can do really cool. Um, Hey, find out all of the priority.
10:06 Number one for everyone. And then everyone is labeled. She wants you to she three or whatever their name is, whatever this is now.
10:14 Let me show you one special thing about this formula that you will need to be aware of, because it might mess you up a little bit.
10:23 So if you change one, let's change that to brand, um, brand development development. Let's say it's a, um, part of our workflow.
10:37 Now we go back to all and we see nothing really has changed here. Okay? Except for the fact that we want our source one, this sheet one to be brand development.
10:49 Well, if we go to this formula here, we see the brand development already got changed. She won in the formula that changed, but the text that we have here did not.
11:00 So we need to go to sheet one. I'm going to come. I'm just going to paste that brand development name and hit enter.
11:09 And now it's all correctly labeled. Okay. Again, if we go back to the second one, she, one is fine on a, sorry, this is text.
11:23 So we actually need to change this one to brand development. And we get the correct answer to again, the anytime that Google sheets knows, Hey, this is a, a sheet it'll change that color and it'll change it to like orange and purple than blue.
11:45 I think we have three here. So you can say, yeah, blue, and you, you don't need to go and change those.
11:50 That's really good. If you use texts though, to label these, then yes, you do need to go and change that.
11:57 So, uh, I highly recommend you make sure that you understand that the text doesn't change, but the, uh, sheet names, when Google sheets knows it's a sheet name that does change.
12:10 Um, the last thing is, before we end this video, we're going to add the status column and say, you want to add a column, pretty simple, walk you through the steps.
12:21 So you want to add a status of done, done, need to be done, need to be started in progress. And let's just finish this off with those that we want to add the status.
12:38 And if we go back to our, all you notice it's here. Okay. Um, let me to add that call, uh, the system.
12:54 Okay. So say we have another column that says, um, count Type And we have first tier. These are all first tier for some reason.
13:19 All right, here, I can add another one. So we know that at all. We want to add that D okay.
13:27 So again, we got to go back to our formula. Um, our first formula where we're labeling based on the sheet name, we don't have to change anything, but in the second formula here on B two, we do have to change.
13:44 See, in text, it says eight two to see we just changed that to a deep, and we're going to get an error.
13:52 It's probably going to say it's missing something we want, maybe we need to change all of this because we really need.
14:06 Yeah. So the error, there was a little confusing. What it really meant, the error we got was that we're combining or rays that are different sizes.
14:15 We, we have this fourth, um, uh, account. Oops. And we were like adding this one, uh, which had four columns to these arrays that had three columns.
14:32 And that's really what that an arrow was giving us. And so all we had to do is go to all of the, um, text here.
14:39 And again, this is only happening because we were using texts here when this indirect, if we didn't use this, if there was some other solution to this way to combine these things, we could, uh, that automatically maybe change, but this is how we're solving it now.
14:56 And this was really, really cool because now as we add the different statuses and account types to the other sheets, they're going to automatically come in here.
15:05 Um, one to also make sure you know, that these are slightly different, right? This is a two D um, but it's in column B.
15:15 If we want to align this all, for some reason we can go here, let's do this in brand development. We're just going to add, do a simple thing.
15:26 Like insert a column, okay, we're going to insert one on the left. Right. So right before this, we want to make sure that this is going to be filled in with the, the sheet name and this all is going to show up on our, um, we need to move this over.
15:49 Okay. So we have priority tasks, status account type in BC, D and E let's go back to our all and see how much this messed up.
15:57 Oh my God. We have things all over the place. Okay. So again, our column doesn't even change, but our B2 formula absolutely does.
16:06 So we changed brand development, exclamation point a to D we changed that to B to C and then automatically this brand developed B2B change.
16:22 What I need to make sure, you know, too, is that the count needs to be one of the columns that has information throughout the whole, um, everything you want.
16:32 So let's change this back to, um, Nope. Let's keep it B. Okay. Let's try to change this. It's gets a little tricky.
16:42 All right. We want them to be, to, to eat. And we actually do want to keep it on B because that's where all the information is, but we do want to change it for all the other ones going forward.
16:53 So we're going to go B to B and we're just going to change the text B to now, we're going to hit enter now.
17:06 Wow. We have some issues here, right? Everything other than brand development, she, two sheet three is left. All right, let's figure this out.
17:16 We need to very simply add column here. And so on the left and insert ones in the left, and you just need to do this.
17:30 This is just structural changes. You're not going to have to deal with this every single time. What's this formula set up and doing well.
17:37 It runs literally without your intervention. So unless you add sheets and you add more sheets, um, go back to our formulas and all of our formulas look great.
17:50 The count is going off of the B column. Uh, the text is correct. Now, as we add this status and account type to the other tabs, other sheets, they're going to automatically show up here.
18:05 So this is all beautiful, beautiful information. And now, you know how to fix this. If you add certain things, change certain things about the structure, but once that structure is set and you're just dealing with the data day to day, um, in individual contributors are creating their own tabs or sheets
18:24 or changing the data on each of the sheets. Once the structure is set, you have nothing else to do. You literally come in here, everything is going to be added as people add, uh, columns, or sorry, add rows.
18:36 If people need to add columns, you know how to do that. And it's a really, really, uh, it seems a complicated right now because you're doing all the work, but you only have to do the work once.
18:46 You never have to come in and change all of the formulas each and every single time you add a piece of data.
18:53 So this is a really, really good way to quickly get. Within 20 minutes, you have now a structure where individual contributors can add information, change information, enrich contextualize, uh, and then you grab it all together into one place.
19:09 It's really, really good to know for a lot of project management and people management and data management. Thanks. Feel free to email me any questions.
19:20 And hopefully if you use this and you enjoy it, uh, let us know, and it'd be really cool to see other examples of this, right?