Combine Sheets Into One Sheet As It Happens

Intermediate

About this Tutorial

Jodi asked: ab out Building a sales CRM spreadsheet that has 4-5 tabs at the bottom. Each sales person gets their own sheet and then I want a master spreadsheet that pulls each salespersons Information they enter (customer, job, status etc) into one place.

I’m not sure how to get the master sheet to pull in from the other sheets as they enter the data so that the master sheet shows real-time what’s happening without making one row = a row from another sheet. I just want it to come in one row after the next as it happens.

Other Videos Mentioned in this video:
https://bettersheets.co/tutorials/add-a-timestamp-to-task-lists-without-now-formula
https://bettersheets.co/tutorials/spreadsheet-automation-101-lesson-2-onedit-trigger

Spreadsheet Automation 101: https://bettersheets.co/courses/spreadsheet-automation-101

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IMPORTRANGE() SORT()

Video Transcript

<div>00:00 So Jodi's asking to combine multiple sheets into one sheet. Hi Jodi, if you're watching this video.

And there's one parameter that is very interesting that I think elicits a possible video here, why I'm making this video, and also ends up being we might not have a real solution here. 00:20 So what she- She wants is a main sheet, she has multiple sales pages, and she could have, you know, one salesperson, two salespeople, three- multiple salespeople. 00:29 They have all of their information here of what they're doing, the different sales and the CRM, sort of, we're putting all that information here. 00:36 She wants a main sheet, where they all come together.

Now this is relatively simple, but there is a big butt here. 00:46 She does not want the rows to come in as rows where they are, so she wants them, Jodi, as I get from your email, you want them to come in as they come in. 01:01 Like, based on the time at which they, or the order they come in. So if one salesperson comes in, and then another salesperson comes in with another order, you want those to be in that order, not as a sheet order. 01:15 So, the first thing I thought of, right, is to do something like import rain.

You can do import range , or this is in the same sheet. 01:27 So we can do array formula . Actually, not even array formula , sorry. We can do curly brackets, that's all we need. 01:36 Sales one, we need them in quotes, say, Sales one, because there's a space there, in single quotes, exclamation point, a colon b. 01:49 And I'm just gonna get, yeah, I'm gonna get all of a colon b, hmmm, yeah. And then a semicolon, and I'm gonna take exactly that, but do it for sales two. 01:59 And this will, end up getting some solution, but not the solution that you already want.

So how do we go from, where do we go from this? 02:08 We have now from sales one, two, three, but we need to do a little bit more work. I realize we probably don't need the header again, so we can change the a's to a two. 02:21 We probably don't want all of the everything, so we don't need a thousand rows here. So let's just delete a bunch of these rows so we can see sort of what's going on easier. 02:35 I'm gonna try to delete all the rows. Let's do the delete, the deleting of the rows. Let's see. Here.

Sort of the loom is getting in the way. 02:44 Alright so we go back to our main, and there it is. You know, we can absolutely sort this if we had some way to sort it, right? 03:01 Maybe date. There's some date here. Let's make a date. Let's put all those dates as well here. Doesn't matter if they're all the same date. 03:17 You'll see what happens in a hot second, or what we can do.

And the main will grab not to colon B, but to colon C. 03:28 And you might see one other error , or issue that we have to overcome is that we don't know who the salesperson is for any of these. 03:35 So perhaps, you know, we enter another column, and we put salesperson here. And you're looking at this and you're like, but this doesn't solve the actual problem we're talking about, but it is fine. 03:48 We'll get to that in a moment. Wait, this was three, three, three. Okay, so now we got all of our data in the same place. 03:57 Let's do D colon D, not to colon C. There we go.

So now we have the salesperson. And as you can see, this is exactly what Jody was trying to avoid, right? 04:10 We have all of these rows and we don't have the sales in the order of the date. So one simple thing we can do here, which could sort of. 04:22 Sort of solve this problem is we just do an equal sort . We can sort these columns. One actually, A2, colon D, the sort column will be C. 04:36 No, not C. Sorry, C. So that means three. We have to put a three here. Is it ascending? Do we want the date to go from. 04:44 The lowest date to the highest date. In this case, I think true.

But you'll see maybe we want it reverse chronological order. 04:54 And let's do that as well. We just change it's true to false and that's reverse chronological order. So the ones at the top are the newest ones. 05:03 . And as you can see, the salespeople are different. One, two, three. And we have here the name of the person, the status and the date. 05:12 And this date could be whatever date is it that you want to sort by . So this is a solution. We can take this sorting and actually move it. 05:23 So we can actually do this like a data , call this data and then call this one main.

And there we go. 05:32 We got our sorting. Let's make sure this is actually the right color, background color we like here. Excuse me. So now instead of just taking all that information, putting it on one sheet and done, we do have to do a little bit of work here with this data page. 05:54 We don't need all of these columns. And we end up getting, you know, an order if this is if your. 06:05 Data has some way to sort it. If you have literally within the data away to sort it, then you can do it this way. 06:12 But let's talk about other issues. You might not have a date.

You might enter this information and be done and think, okay, I enter the information. 06:21 I know when I enter this information. Google sheets knows when I. I enter this information. Why can't I just sort it by the date which I enter that information? 06:28 Well, Google sheets does sort of know when you enter it, but it doesn't care. It, it, it cares showing the information as is about the information and the value that's inside the cell. 06:41 Sure, like you have here. Where is it? Where is it? Oh, they moved it around.

You have a version history here. 06:53 Oh, it's just, but last edit, Google sheets knows when you edit this stuff. And so like, why can't we use that information? 07:02 And, and we sort of can. We can. Use that information, but we have to use it at the moment at which it happens. 07:07 We can create a little timestamp . I'm gonna close this. We can. Do some kind of on edit, but here's a thing. 07:15 I can show you this process, but it's going to be very determinant on when you think something is edited.

And when you think something has been entered if salespeople are entering information and then updating that information for each and every edit that they do, the timestamp will change. 07:35 If you say you know, go into Apps Script , create a little timestamp . App script and say on edit, we have let's see. 07:44 This shows up. Can you function ? Function on edit. We'll just have to type it with an E here. And in this Apps Script , we can say whenever there's an event E, we can call this event as well. 08:01 Whenever there's an event E, take down the time, take down.

Where it's edited, and then we can add in, like, if this event, you know, dot time or whatever, we end up coding this as. 08:15 I'm just not going to show you in this video because I have it is about unedited and adding timestamps and stuff here on better sheet. 08:20 So I'll try to link those as well to you in the description. But we can add this. The issue is going to be selecting when that on edit and when the time is actually edited. 08:32 You just need to know, is it when the name is entered? Is it any time the status is entered?

Is it when it's entered and then if it had a blank before and then it has a new thing? 08:45 You edit it or you update the information, it has some value before. That means you might just, you might not want that timestamp to be updated. 08:54 You maybe only want that timestamp to be added once and done, never added to, never edit it again. You just need to say that date and this date could be completed date. 09:07 It could also be last updated date.

So you could update the state no matter what, anytime any column is updated in this row. 09:19 So all of these questions is why I said at the beginning of this video that like we might not have a solution. 09:25 It, I could sort of show you these options, but I also show you in other videos like a timestamp video, how to create a timestamp in a cell on an edit. 09:34 We have these options available. It is up to you to select when these things actually matter. If you don't want this data . 09:42 So I showed you a little simple way.

Gather all the data with this curly brackets and then sort it literally using the sort tab. 09:52 This also has some problems where you can't edit this information. You must edit it on a sales page. That might be a good limitation though. 10:00 It might be the limit you need the the rails you need the constraints you need. It might be. If.

However you need to edit it in two places. 10:08 I do have another video about that which is a little app strip that says take these two tabs and just update it no matter which one is edited. 10:16 That can be problematic if especially if you have multiple sales people and you have different information onto pages that video is very much about the information on both. 10:26 Both of these tabs are the same information and just need to be updated and cross-referenced.

So I hope this is okay of an answer and it is a hundred percent okay if someone, a better seats member is watching this and you're like you didn't solve my particular problem. 10:43 One, you can email me and two, you can go watch. Other videos because I have videos on time stamping, I have videos on edit, there's an entire course Spreadsheet Automation 101. 10:52 If you're a member and watching this, Spreadsheet Automation 101 is completely free for you.

Just go and start the course, three hour course, talk to all about App Script . 11:00 So all of the things I didn't talk about today it covers. And, If you are wondering how to do this where the data is on other sheets, check out Import Range . 11:15 Import Range is gonna gather information from other Spreadsheet files. So that will get you to this point here. I'm just using curly brackets because it's a very simple way to gather a range. 11:26 And get them listed in a particular order and exactly the order that Jody didn't want.

And so hopefully then the sort formula will sort you out, but you do have plenty more options beyond this. 11:41 Depending on, you know, what is an edit, what is a date? You want timestamps? Do you want? Do you want the information to be the same across all cells ? 11:50 Do you want it to be editable on both cells ? All of these add each one adds just one layer of complexity each time. 11:56 So hopefully this was an okay video for you and you got some interesting tidbits about what cheats can do.</div>