Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:07,880 --> 00:00:14,900 The next step that gets us closer to our scrolling table is to get the data from the data tab into 2 00:00:14,990 --> 00:00:21,410 our calculation tab. And this data is going to be dynamic because it's going to reference the app that 3 00:00:21,440 --> 00:00:22,390 it sees here. 4 00:00:22,430 --> 00:00:30,350 And this app is basically given based on the selection that the user makes here. Which is actually driven 5 00:00:30,470 --> 00:00:32,170 by this one. 6 00:00:32,180 --> 00:00:35,950 So when this one changes these are going to change, 7 00:00:36,110 --> 00:00:41,660 and as a result the data in the table is going to change. Because it's going to pull different data from 8 00:00:41,660 --> 00:00:47,470 the original data tab. Which formula could work here? 9 00:00:48,330 --> 00:00:54,090 The Vlookup formula would work. We could do some indexing here. 10 00:00:54,090 --> 00:01:00,270 So let's say that this is actual revenue, that this position in this table is fixed for sure. 11 00:01:00,270 --> 00:01:07,020 And let's assume if this position was fully fixed we could say well that's going to be the second column 12 00:01:07,440 --> 00:01:13,200 that it should reference. When we do the Vlookup for the number of columns it should move, we could 13 00:01:13,200 --> 00:01:16,540 say move to. And that's the key here. 14 00:01:16,620 --> 00:01:22,230 Now for profit, we could say well that's the third column and that's the fourth column. 15 00:01:22,290 --> 00:01:29,760 We could use this type of fixed indexing and have the Vlook up function reference these indices. 16 00:01:30,090 --> 00:01:38,820 But if we think that the data base format is going to change because I might add in a new KPI. I could shift 17 00:01:38,820 --> 00:01:40,560 the profit to after cash. 18 00:01:40,650 --> 00:01:44,520 Things might move around here, and I want to be on the safe side. 19 00:01:44,550 --> 00:01:51,000 I can use another function that can look this up in the rows and can look up the columns as well. 20 00:01:51,000 --> 00:01:54,430 To make sure that it has a perfect matrix match. 21 00:01:54,600 --> 00:01:56,410 So which function would work here? 22 00:01:58,130 --> 00:02:01,610 Index and match, match. 23 00:02:01,680 --> 00:02:09,420 For that to work I have different options of using it. Because I have many headers here. 24 00:02:09,420 --> 00:02:13,310 If we go to the Data tab you see I have the year, month, and so on. 25 00:02:13,710 --> 00:02:17,040 I need to do one of three things. 26 00:02:17,040 --> 00:02:24,510 Either I make a helper cell here that combines all these into one so they're all unique to each column. 27 00:02:24,990 --> 00:02:32,550 Or I use index match and make them match an array function using Ctrl+Shift+Enter. Or I use another 28 00:02:32,550 --> 00:02:37,730 Index function in the Match function and still use the array way of solving this. 29 00:02:37,910 --> 00:02:42,570 But because I don't want my formulas to become too complex. And when I design dashboards that are going 30 00:02:42,570 --> 00:02:48,270 to be used by other people and I don't know their level of Excel, I always like to keep things simple 31 00:02:48,470 --> 00:02:49,630 and to be on the safe side. 32 00:02:49,630 --> 00:02:53,000 So I'm going to go by creating a unique key. 33 00:02:53,040 --> 00:03:02,120 Using Helper cells. My unique key is going to be a combination of year, and month, 34 00:03:02,320 --> 00:03:09,370 and the scenario, and the account. That's going to be my unique key. 35 00:03:09,630 --> 00:03:18,580 I'm just going to highlight all of this. Just go all the way to the end because we're going to need 36 00:03:18,650 --> 00:03:20,090 the unique key everywhere. 37 00:03:20,340 --> 00:03:26,330 I'm going to click F2, Ctrl+Enter. 38 00:03:26,570 --> 00:03:31,170 That's going to populate it all without changing my formatting. 39 00:03:32,400 --> 00:03:35,000 That looks good. 40 00:03:35,030 --> 00:03:41,530 Now what I need is to use that same unique key here, in this case. 41 00:03:41,780 --> 00:03:48,680 Because I'm going to be referencing that sheet and doing my index and matches based on the data tab sheet, 42 00:03:48,740 --> 00:03:51,480 let me just create a new window. 43 00:03:51,560 --> 00:04:00,040 We go to View, new window. It just opened a second session of the exact same workbook. 44 00:04:00,320 --> 00:04:07,230 And now in the view tab I'm going to arrange all. I want them vertical and I leave the tick mark here 45 00:04:07,290 --> 00:04:07,980 that I want 46 00:04:07,980 --> 00:04:16,040 only the windows of the active workbook. That gives me two sessions of the same workbook. In one of the 47 00:04:16,040 --> 00:04:16,850 sessions, 48 00:04:16,910 --> 00:04:23,390 let's say in this one, I'm going to be in the calculation tab. That's where I'm going to write my formulas. 49 00:04:23,540 --> 00:04:28,340 And in the other session I'm going to be in the data tab. 50 00:04:28,340 --> 00:04:30,390 Here, I need to create a unique key, 51 00:04:30,470 --> 00:04:35,090 that's exactly the same to the unique key that I see in the data tab. 52 00:04:35,090 --> 00:04:39,560 So for this one I'm just going to reference the cell. For the next one 53 00:04:39,560 --> 00:04:46,960 I'm going to be typing it in because here is actual but I don't have previous year. Previous year is 54 00:04:46,960 --> 00:04:51,920 actually the actual dimension but the year is going to be one year before. 55 00:04:52,370 --> 00:05:00,230 This is going to be the month, so that's going to be the current month that I have which is referencing 56 00:05:00,650 --> 00:05:03,760 the current month selected by the user from the dashboard. 57 00:05:03,860 --> 00:05:08,400 And this is going to be the current year. 58 00:05:08,540 --> 00:05:10,990 And now I can make the same formula that I have there. 59 00:05:10,990 --> 00:05:17,220 It's this, and this, and actual and revenue. 60 00:05:20,320 --> 00:05:24,260 I'm just going to take this, I'm going to copy it all the way across. 61 00:05:24,430 --> 00:05:31,900 Only until here and then I'm going to adjust the categories that need to be adjusted. The month, everything 62 00:05:31,900 --> 00:05:32,980 is fine here. 63 00:05:33,130 --> 00:05:39,850 For previous year, I want current year minus one. That applies to these. 64 00:05:39,850 --> 00:05:40,600 For budget this is fine. 65 00:05:40,600 --> 00:05:47,080 The only thing that's different is, this should be budget and it's written just like this one here. 66 00:05:47,090 --> 00:05:47,630 Budget. 67 00:05:51,310 --> 00:05:51,940 OK. 68 00:05:52,130 --> 00:05:57,120 So, that gives me the same unique key as I see on the data table. 69 00:05:57,290 --> 00:06:01,350 Now I'm ready to write my Index and Match formula. 70 00:06:01,790 --> 00:06:10,490 I'm going to Index the entire data range here. For the months we're going to go all the way to here and 71 00:06:10,490 --> 00:06:17,960 down. Because after this is the actual profit where I'm showing it on a monthly basis starting from January. 72 00:06:18,300 --> 00:06:22,700 That information for June is also repeated in here. 73 00:06:22,700 --> 00:06:31,280 All I need is this. If you're planning to add more lines here you might leave some space underneath. 74 00:06:31,760 --> 00:06:34,520 If you're really adding lines on a regular basis. 75 00:06:34,520 --> 00:06:42,170 You might want to turn this into a data table. The only thing you have to be careful of is that this 76 00:06:42,490 --> 00:06:48,290 data table can't have many headers. You might want to add an empty row in between and then just put 77 00:06:48,290 --> 00:06:54,950 in dummy headers and turned this into a data table. And then still have your formulas referencing these 78 00:06:54,950 --> 00:06:58,040 keys that you need to retrieve from the system. 79 00:06:58,040 --> 00:07:05,320 The advantage is that every time you get new lines added that your formulas are going to update automatically. 80 00:07:05,330 --> 00:07:11,090 Now for the purpose of this example dashboard, I'm just going to restrict my data set like this. But keep 81 00:07:11,090 --> 00:07:15,740 that in mind when you're creating it for your own life dashboard. 82 00:07:15,740 --> 00:07:21,690 I'm going to fix this area and press F4. 83 00:07:21,700 --> 00:07:23,530 Now, how many rows do I want to go down? 84 00:07:23,530 --> 00:07:27,370 Well, I need to match this app right here. 85 00:07:27,540 --> 00:07:29,270 Now let's think about the fixing 86 00:07:29,290 --> 00:07:31,280 while we're writing this. 87 00:07:31,420 --> 00:07:33,240 What do I need to fix here? 88 00:07:34,900 --> 00:07:41,380 I need to fix the column and where am I looking this up? 89 00:07:41,540 --> 00:07:43,360 I'm looking it up here. 90 00:07:45,990 --> 00:07:54,540 I'm going to fix the entire range by F4. They are both from 9 to 51, 9 to 51. Because the 91 00:07:54,540 --> 00:08:00,460 index range and the match range have to be in sync. My match type is a perfect match. 92 00:08:00,460 --> 00:08:02,420 So I want a zero. 93 00:08:02,600 --> 00:08:06,060 This means I'm going to move down so many rows. 94 00:08:06,230 --> 00:08:12,410 It doesn't know how many columns it should move and that depends on the unique variable that I have 95 00:08:12,410 --> 00:08:15,920 on top. I need to do another match 96 00:08:16,100 --> 00:08:18,920 and now I'm going to match for this one. 97 00:08:19,050 --> 00:08:24,060 And let's think about fixing. What do I need to fix here? 98 00:08:25,970 --> 00:08:27,460 The 8, right? 99 00:08:30,080 --> 00:08:39,450 Next argument is the Lookup array. Where I mean looking it up? I'm looking it up here. I'm going to fully 100 00:08:39,450 --> 00:08:40,690 fix this, 101 00:08:40,920 --> 00:08:45,980 and I want a perfect match. Close brackets, close brackets. 102 00:08:46,110 --> 00:08:47,320 I'm going to press enter. 103 00:08:48,470 --> 00:08:50,630 Let's take a look at our whole formula. 104 00:08:50,790 --> 00:08:53,790 That's an index. We index the whole area. 105 00:08:53,820 --> 00:09:00,270 We know how many rows to move down and we know how many columns to move across to get our KPI. 106 00:09:00,270 --> 00:09:01,970 Is this the right number? 107 00:09:02,030 --> 00:09:05,750 Commuta, actual, June revenue. 108 00:09:06,060 --> 00:09:08,720 Let's look for Commuta. 109 00:09:08,870 --> 00:09:11,350 Here it is, 6353. 110 00:09:11,420 --> 00:09:18,500 That's this one. Now the real test comes when I'm going to just pull this across just to test 111 00:09:18,500 --> 00:09:21,170 if it works and pull it down. 112 00:09:21,230 --> 00:09:26,380 This one, Accord profit is 800. 113 00:09:26,380 --> 00:09:27,570 Accord profit is 800. 114 00:09:27,760 --> 00:09:29,230 So it looks good. 115 00:09:29,230 --> 00:09:38,150 Now the one thing I want to account for is if this is empty it should also return nothing. 116 00:09:38,200 --> 00:09:41,030 I'm going to add an If statement here, 117 00:09:41,870 --> 00:09:48,840 and only run this index if this does not equal to nothing. Then it should do that. 118 00:09:48,860 --> 00:09:51,450 But again let's not forget the fixing. 119 00:09:51,450 --> 00:09:53,040 I have to fix the N 120 00:09:53,060 --> 00:09:55,230 in this case. 121 00:09:55,420 --> 00:10:01,180 If it doesn't equals to nothing then run, do the index thing and find the number. 122 00:10:01,400 --> 00:10:03,510 Otherwise return nothing. 123 00:10:05,580 --> 00:10:10,030 Let's pull this all the way across until here. 124 00:10:11,350 --> 00:10:12,990 OK it looks good. 125 00:10:14,560 --> 00:10:20,180 Now I'm just going to be copying it down to here. 126 00:10:20,190 --> 00:10:26,850 Highlight the area, press F2 and Ctrl+Enter. 127 00:10:26,870 --> 00:10:27,860 That looks good. 128 00:10:27,860 --> 00:10:30,370 Let's do some more testing. 129 00:10:31,360 --> 00:10:39,510 Let's look for Mosquit, and let's look for budget profit. It should be 156. 130 00:10:39,610 --> 00:10:42,590 So, Mosquit is here. 131 00:10:44,650 --> 00:10:45,970 156. 132 00:10:45,970 --> 00:10:47,460 That's budget profit. 133 00:10:47,470 --> 00:10:48,900 That looks good. 134 00:10:48,910 --> 00:10:54,100 The other test is to change the selection. 135 00:10:54,100 --> 00:10:57,000 Let's actually go and change it from here. 136 00:10:57,310 --> 00:11:04,420 Let's go to game apps and see if we get a different list. 137 00:11:04,420 --> 00:11:05,020 We do. 138 00:11:05,100 --> 00:11:06,470 That's Perino. 139 00:11:06,520 --> 00:11:07,390 That's 15033, 140 00:11:07,390 --> 00:11:09,820 for Actual. 141 00:11:09,820 --> 00:11:18,400 What is this one? That's actual previous year. That should be 15064, that's 15064. Let's just scroll 142 00:11:18,430 --> 00:11:21,480 that's 15033, 15033. 143 00:11:21,490 --> 00:11:23,410 OK so that looks good. 144 00:11:23,590 --> 00:11:24,510 It's working. 145 00:11:24,550 --> 00:11:27,710 Let me close the second session here. 146 00:11:28,540 --> 00:11:34,740 And we are going to revert back to our original session. 147 00:11:34,800 --> 00:11:38,340 Let's calculate the percentages here as well. 148 00:11:38,550 --> 00:11:48,510 This is the actual to previous year for revenues. It's this number minus previous year's number divided 149 00:11:48,510 --> 00:11:50,760 by previous year's number. 150 00:11:52,670 --> 00:11:58,130 And we want this formatted as a percentage. 151 00:11:58,140 --> 00:12:01,340 I just want to make the exception again here. 152 00:12:01,530 --> 00:12:06,920 I'm going to do an If this does not equal to nothing, 153 00:12:07,050 --> 00:12:17,090 then run this. Otherwise give me nothing and I have to fix the N here. 154 00:12:18,740 --> 00:12:20,560 Can I pull this across? 155 00:12:23,000 --> 00:12:27,890 Let's check. I can pull it across to this one, that's going to work. 156 00:12:27,910 --> 00:12:32,470 It's going to be this minus this, divided by this. Fine, that's going to work. 157 00:12:32,470 --> 00:12:33,870 What about this one? 158 00:12:35,430 --> 00:12:37,710 It's going to be previous year minus the budget. 159 00:12:37,710 --> 00:12:39,010 That's not what I want. 160 00:12:39,180 --> 00:12:48,330 I want the actual minus budget divided by budget. That revised formula is what I'm going to copy across 161 00:12:48,470 --> 00:12:49,300 to here. 162 00:12:49,590 --> 00:12:51,090 Let's double check here. 163 00:12:52,510 --> 00:12:53,880 That's this one, 164 00:12:53,880 --> 00:12:56,660 minus this, divided by this. 165 00:12:56,890 --> 00:12:58,330 So that looks good. 166 00:12:58,360 --> 00:13:03,580 And my if is there. I'm just going to highlight this whole area again. 167 00:13:03,850 --> 00:13:07,410 I'm going to press F2 and Ctrl+Enter. 168 00:13:07,960 --> 00:13:13,960 Let's just update the formatting that they are also a percentage. 169 00:13:13,960 --> 00:13:21,240 One last thing I'm going to do is copy the entire line and paste it here as well. 170 00:13:22,480 --> 00:13:27,510 It should automatically retrieve game apps from my data table and that's the header. 171 00:13:27,530 --> 00:13:31,970 Everything seems like it's working for this one. 172 00:13:31,990 --> 00:13:34,170 This minus this, divided by that. 173 00:13:34,300 --> 00:13:42,890 Let's just put in the color formatting here as well. Just so that I can tell which one is actual, which 174 00:13:42,890 --> 00:13:47,950 one is budget, and so on. I'm just going to click on this and paste it here. 175 00:13:49,280 --> 00:13:56,390 OK so that's the basic setup of the calculation table that's going to feed our scrolling table that 176 00:13:56,390 --> 00:13:57,540 we're going to create next. 17306