Ctrl shift to the right, ctrl shift down to select it. Then use the Excel formula separator and let's just move on to the next sheet and follow the motions: ctrl shift right, ctrl shift down and then finally the last sheet. Let's repeat that. Close the bracket press enter and we have all of our data combined together in a dynamic way onto a single summary sheet. Let's quickly autofit this and adjust the number formatting and that's it. We're done..
But wait that's not the latest file. You have an old version. The latest file has like over 15 tabs. Hold my coffee. Okay so since we have a lot of tabs here it doesn't make sense to reference each of these separately we still want to keep things super dynamic but we're going to use a slightly different approach.
We're going to start off with VSTACK. Go to the first tab here hold down the shift key go all the way and select the last tab that we want included. This now is a 3d reference. It's automatically going to include all the sheets that are in between the first one and the last one. The range that i want is this but some sheets have more data some have less data so in my case to be on the safe side I'm going to go with 50 but of course you can go with a bigger range. Depends on how much data you have. And now we have all of these sheets appended with.
A simple formula. But now you have all of these zeros in the middle this looks horrible what's he gonna think? Don't worry we're just gonna use the FILTER function and filter these out. So we're going to start off with FILTER - our array is our VSTACK. I'm just going to copy this because we're going to use it again we want to include values that don't have a 0. so I'm going to use VSTACK.
Want to sort our filtered result. The column that we want to sort this by is one two three fourth column so we're going to go to the side here put the column id which is four, the sort order: whether we want ascending or descending. I'm just going to go with the default one ascending or you can skip that argument altogether because default is ascending. Press enter and we have our stacked list automatically sorted by product in ascending order..
What if he wants to add data from another file and drop it in the middle of these. Is it gonna work? I hope it does okay so let's test it out. This is another file that i have and i have extra data in this R10-3 tab and I'm to drag and drop it here and we're going to see if our index sheet automatically updates. So notice in this tab i have Absorbent Cotton so that should show up first because we're sorting by product name. Does it really work? Does everything automatically update like we want it to?.
Let's click drag drop it here and then what happens? It's right here we can see data from Miller which was the data that we just added automatically populated in here and automatically sorted as well so that's the beauty of using 3d functions because it includes anything that's in between your first and the last sheet that you have referenced in your formula. VSTACK allows us to automatically stack the data, FILTER allows us to get rid of the empty spaces or the zeros and SORT.
Allows us to dynamically sort our results. Now even if the regional director wants multiple levels of sorting so we want to sort by product and then by sales but by sales in descending order we can also do that. So let's just be fully prepared all we have to do is update our sort index and include the different columns that we want to have because we're going to be referencing multiple columns we need to find the curly braces or curly brackets and type in the column ids so one column i want.
To sort is still product name which is the fourth column then i'm going to add in the total sales column which is the seventh column so I'm going to put a seven and close the curly bracket. Now I can define the sort order because I'm using the curly brackets I have to define those with curly brackets as well the source order for product is ascending that's a one the sort order for total sales is descending that's minus one let's close that bracket press enter and now we.
Posts Related:
Have our multiple level of sorting. Products are sorted in ascending order whereas the total sales
Within the products is sorted in descending order. That's it let's get to our meeting! okay so i hope you enjoyed this slightly different video we just had to switch things up for a change let me know what you thought about it and if you're new here subscribe if you haven't subscribed yet and i'm gonna see you in the next video.