Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Another means for working with multiple worksheets is three dimensional formulas. In the previous lesson we looked an example of consolidation. All of your employees were using expense sheets that lived on a server resource, you took those expense sheets and consolidated them into one sheet, and as we described in that lesson you're going to end up with a tab for each month and all of your employees consolidated into one workbook. Let's open up a finished example of that consolidation, which you'll find in your Student Folders in section 13 and that's called Travel Expense. Travel Expense has your tabs - January, February, March, etc. Obviously the sheet would be a lot more complex and large. I'm keeping it simple so I can show you this quickly. Here in January, we have the expenses for all four employees and the various categories summarized. In February we have the same thing. In March, we have the same thing, and on our Totals page it's ready to be summarized, and you do that easily using three-dimensional formulas. To create a three-dimensional formula, it begins much like it does any other formula, for example in airfare for Mark A., we're going to type =SUM and when we open our brackets, this is where we can go back and create a three- dimensional formula that will drill down through all of the tabs in our workbook and pull data from each sheet accordingly.
Let's go back to the January tab and get the January airfare. Now in order to anchor this selection and allow us to select right through the end of the year, or in this case the end of March, hold down the Shift key, select the March tab and close brackets. You notice that the formula we've just built summarizes everything from sheet January through March at B2. Hit Enter and that formula has now been entered into our total sheet and because the references are relative, we can copy that formula to the rest of the cells. Let's click on the box and drag it to the right, and then drag them all down, and there you can see very quickly and very easily, we have a summary sheet of our entire annual expenses for all the employees in the various categories. That's a three-dimensional formula and when you combine consolidation with a three-dimensional formula, you can automate an entire department's task.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90678 Viewers
80 Video lessons · 137966 Viewers
59 Video lessons · 56739 Viewers
52 Video lessons · 70363 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.