Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:07,680 --> 00:00:14,520 Now we're going to create our first form control. The first form control is going to control the division. 2 00:00:14,850 --> 00:00:19,740 And the division is going to control the main point of view that we're going to have for this dashboard. 3 00:00:19,740 --> 00:00:25,890 The table that we want to create after this, that's going to be controlled by the division. Because the 4 00:00:25,890 --> 00:00:31,950 apps that we're going to show in that table are going to be the apps that belong to the division that 5 00:00:31,950 --> 00:00:33,910 the user selects here. 6 00:00:33,930 --> 00:00:39,990 I'm just going to show you the end result just so you get a good idea of how this table and the dropdown 7 00:00:40,020 --> 00:00:41,050 is going to work. 8 00:00:41,940 --> 00:00:44,970 Let's go to the dashboard view here. 9 00:00:44,970 --> 00:00:50,520 That's the dropdown that we want to create and this dropdown is going to decide what is shown in 10 00:00:50,520 --> 00:00:52,370 this table. 11 00:00:52,380 --> 00:00:59,880 Here, I'm going to show the name of the app. Then I want to show the values for actual, for the first KPI. 12 00:00:59,880 --> 00:01:04,760 Then the variance to previous year, and also the percentage change to budget. 13 00:01:04,770 --> 00:01:10,140 I'm also showing the bar that emphasizes this difference. That I can visually tell 14 00:01:10,140 --> 00:01:16,470 here I do really well in comparison to previous year, and here I didn't do so well in 15 00:01:16,470 --> 00:01:18,590 comparison to previous year. And here 16 00:01:18,600 --> 00:01:25,020 I'm also showing the up and down arrows to be able to tell how well have I performed in comparison to 17 00:01:25,020 --> 00:01:25,870 budget. 18 00:01:26,190 --> 00:01:28,200 So these are percentage based 19 00:01:28,200 --> 00:01:31,380 and this is the actual value of the KPI. 20 00:01:31,380 --> 00:01:37,960 And I have split the table into three main areas that concentrates on each of these KPI. 21 00:01:38,250 --> 00:01:46,680 What I don't have in the original data is the percentages. Because in the original data what I have is 22 00:01:46,680 --> 00:01:56,650 the actual value. So I have to calculate those percentages in my data table. But I have to do it in a 23 00:01:56,650 --> 00:02:04,780 dynamic way because when I select the game apps it should show the percentages that are related to the 24 00:02:04,780 --> 00:02:07,930 apps inside the division game apps. 25 00:02:08,790 --> 00:02:17,240 Let's go back to our draft dashboard and let's start to set up the calculation area. 26 00:02:17,520 --> 00:02:23,520 Up here I already introduced the selected information. This is the information from the 27 00:02:23,520 --> 00:02:24,540 form controls. 28 00:02:24,600 --> 00:02:29,040 So I want the results of the division to be here from the form controls. 29 00:02:29,040 --> 00:02:37,310 Let's just go in and add our first form control. In the developer tab, insert, and here I'm going to 30 00:02:37,310 --> 00:02:41,840 add a combo box and we just have to draw it out. 31 00:02:42,700 --> 00:02:51,530 We can organize it and make it nicer later on, right-mouse click, format control. The input range is something 32 00:02:51,560 --> 00:02:57,920 I already have in the calculation tab, and this is like my master data. The cell link where I want the 33 00:02:57,920 --> 00:03:02,940 results of this form control to be held is in the calculation tab. 34 00:03:03,060 --> 00:03:06,470 I keep everything together on the same side. 35 00:03:06,470 --> 00:03:11,390 I don't spread and keep anything on the dashboard tab side itself. The dashboard tab is something that I 36 00:03:11,390 --> 00:03:13,400 want to lock and protect later on. 37 00:03:13,520 --> 00:03:19,910 And I only want to open those cells where the user is actually inputting but I don't want to write anything 38 00:03:19,970 --> 00:03:24,710 in those cells. I keep this in the calculation side as well. 39 00:03:26,700 --> 00:03:31,870 Ok and here's the dropdown lines. I only have three divisions and I don't want to see any more lines. 40 00:03:31,870 --> 00:03:35,070 I'm going to restrict it to three. 41 00:03:35,180 --> 00:03:43,170 When I select game apps. What number should I get back in the calculation tab? 2. 42 00:03:43,180 --> 00:03:45,690 Because it's the second position. 43 00:03:45,880 --> 00:03:51,420 OK if I select utility apps. What number are you going to get? 3. 44 00:03:51,460 --> 00:03:53,320 Because it's the third position. 45 00:03:53,320 --> 00:03:56,590 What happens if I actually delete this? 46 00:03:56,590 --> 00:03:57,730 Is it going to work? 47 00:04:00,540 --> 00:04:02,150 Productivity apps. 48 00:04:02,400 --> 00:04:08,010 It writes a 1. It doesn't matter if you delete this by mistake it's going to overwrite that cell 49 00:04:08,220 --> 00:04:11,650 with the number. 50 00:04:11,880 --> 00:04:13,590 I've made that selection 51 00:04:13,740 --> 00:04:21,000 but now what I need is to create the calculations that I need for that table and I need to show the 52 00:04:21,060 --> 00:04:23,760 apps that are based on this selection. 53 00:04:24,150 --> 00:04:28,660 Let's just leave some blank space here because we might need it later. 54 00:04:28,950 --> 00:04:33,400 And let's start by putting our calculation from here. 55 00:04:33,510 --> 00:04:40,250 I'm just going to copy the formatting of this and paste it here. 56 00:04:40,380 --> 00:04:48,520 And put "Calculations for scrolling table". 57 00:04:48,640 --> 00:04:51,170 And let's expand this since we're going to 58 00:04:54,090 --> 00:04:56,500 need a bunch of calculations here. 59 00:04:58,660 --> 00:05:00,580 Now, down here. 60 00:05:00,880 --> 00:05:04,750 Let's start with the number. 61 00:05:04,780 --> 00:05:10,360 That's the index number that I want to have and here's going to be the app name. And then it's going 62 00:05:10,360 --> 00:05:14,660 to be my KPIs. For the app name, 63 00:05:14,660 --> 00:05:22,820 let's make this dynamic now that I see the app that belongs to the division that is selected by 64 00:05:22,820 --> 00:05:24,280 the user here. 65 00:05:25,190 --> 00:05:26,210 How can I do that? 66 00:05:26,210 --> 00:05:28,980 What formula could work here? 67 00:05:31,560 --> 00:05:41,490 Well, I'm going to use the Index formula and since this is in the same order as this. This is one 68 00:05:41,490 --> 00:05:48,000 two three, one two three. I can index this area. 69 00:05:48,100 --> 00:05:52,900 I don't need to fix it because I'm not planning to pull it this way. I'm just planning to pull it down. 70 00:05:52,930 --> 00:05:54,670 I'm going to index this. 71 00:05:54,880 --> 00:05:57,180 And what do I want returned for rows? 72 00:05:57,180 --> 00:06:00,710 For rows I don't want anything so I can skip that and for columns 73 00:06:00,730 --> 00:06:01,970 I want this. 74 00:06:02,350 --> 00:06:05,140 You see how I skipped the rows. 75 00:06:05,200 --> 00:06:07,570 You don't necessarily need to do that. 76 00:06:07,570 --> 00:06:15,850 You can also do just this because Excel is smart enough to know that you have only highlighted one row. 77 00:06:16,030 --> 00:06:18,470 So you can't be giving it a row argument. 78 00:06:18,490 --> 00:06:20,810 It's going to skip it by default. 79 00:06:20,950 --> 00:06:27,550 So if this is a two, it's actually going to move across columns and not try to go down rows. 80 00:06:27,580 --> 00:06:30,190 It's smart enough in that sense. 81 00:06:30,310 --> 00:06:32,770 I'm going to fix this. 82 00:06:34,930 --> 00:06:36,610 I'm going to pull it down. 83 00:06:39,940 --> 00:06:42,130 Here I get empty 84 00:06:42,150 --> 00:06:44,560 because there's nothing else in here. 85 00:06:44,730 --> 00:06:49,830 But what I just want to do is to be on the safe side on two aspects. 86 00:06:49,920 --> 00:06:55,930 One is that well the app business is doing really well so I could be getting more apps. 87 00:06:55,940 --> 00:06:59,020 I do want to leave some space for new apps. 88 00:06:59,310 --> 00:07:06,630 Let's see how many these are, that's 15. Let's add five more until here. 89 00:07:06,630 --> 00:07:14,400 I'm just going to put a border around this so I know until where I'm planning to add possible apps. 90 00:07:17,920 --> 00:07:21,930 And here I also want to be on the safe side and just put an If 91 00:07:21,980 --> 00:07:29,690 that If the results of this index is equal to nothing then show nothing. 92 00:07:29,850 --> 00:07:33,490 Otherwise do the index part. 93 00:07:39,320 --> 00:07:39,920 OK. 94 00:07:40,130 --> 00:07:51,310 Now for the number. For the number I can just check if this does not equal to nothing. 95 00:07:51,650 --> 00:07:58,280 So basically, if there is a value in there then give me row. I'm just going to use the normal index and say. 96 00:07:58,280 --> 00:08:00,450 Just give me row A1. 97 00:08:00,500 --> 00:08:02,950 So what is that going to give me? 98 00:08:04,210 --> 00:08:06,160 It's going to give me 1, right? 99 00:08:06,370 --> 00:08:09,880 Otherwise give me nothing. 100 00:08:12,220 --> 00:08:17,320 I can pull this down until here, it's 12. 101 00:08:17,320 --> 00:08:24,160 So let's change this to 1. This changes until 15. 102 00:08:24,370 --> 00:08:27,050 We change it to 3. 103 00:08:27,090 --> 00:08:31,410 It changes until 13, until this one. That seems to work. 104 00:08:31,410 --> 00:08:39,620 I'm just going to put a dark border on the bottom here. Just so I know until where I have formulas. 105 00:08:42,690 --> 00:08:44,070 With this one. 106 00:08:44,070 --> 00:08:52,560 And I also want to capture the division, the main division because I want to show the totals on the top 107 00:08:52,560 --> 00:08:53,180 header. 108 00:08:53,310 --> 00:08:59,190 I want to show the total revenue, total profit, and total cash for the main division. 109 00:08:59,190 --> 00:09:01,310 So I need to capture that as well. 110 00:09:01,310 --> 00:09:07,680 I'm just going to leave a space here and put the main division down here. 111 00:09:07,800 --> 00:09:09,880 How can I get that? 112 00:09:10,590 --> 00:09:24,670 I'm just going to index these and go down by the position of the selected division. 113 00:09:24,760 --> 00:09:31,180 I know that utility apps is selected in this case and I don't need an index number for that. 114 00:09:31,330 --> 00:09:34,800 I don't need to put any formulas there. 115 00:09:34,810 --> 00:09:44,610 Now what I want to do is, let's just make this a little bit more appealing in terms of formatting. 116 00:09:44,610 --> 00:09:49,940 Here I want to add the KPIs. The KPIs that I have are these three. 117 00:09:49,950 --> 00:10:01,490 I'm going to copy them and paste them as special but transpose them. So I get it one after the other. 118 00:10:01,500 --> 00:10:08,550 Now, I need to repeat this because I need one section for actual information, then I need a section for 119 00:10:08,550 --> 00:10:11,340 previous year, and a section for budget. 120 00:10:11,340 --> 00:10:21,780 I'm going to repeat them three times and here on top I'm going to type in actual, and let's do previous 121 00:10:21,780 --> 00:10:25,330 year here, and let's do budget here. 122 00:10:26,590 --> 00:10:32,680 And the other thing that I'm going to do is change the colors of these to match the main colors that 123 00:10:32,740 --> 00:10:38,800 I originally had in my wireframe. To these three colors because it is easy for me to distinguish what 124 00:10:38,800 --> 00:10:39,370 is what 125 00:10:39,390 --> 00:10:49,000 also on the calculation tab. This one is my actual, so that was the darkest one. Then its previous year 126 00:10:49,000 --> 00:10:59,870 was the lightest color, and the budget was the one that's kind of in between. 127 00:10:59,890 --> 00:11:07,680 These ones I'm just going to make them into the gray color that I have there. 128 00:11:07,990 --> 00:11:14,740 After this what I need to calculate is the variances. The variance actual to previous year, and variance 129 00:11:14,800 --> 00:11:19,210 actual to budget for each of these KPIs. 130 00:11:19,300 --> 00:11:21,300 Let me just make a note here. 131 00:11:21,350 --> 00:11:30,060 "Variance actual to previous year" and that's going to be this one, and then I want actual to budget. 132 00:11:30,310 --> 00:11:34,230 So I can copy those here, 133 00:11:37,820 --> 00:11:44,730 and make them in the same gray as I have there. My variances are going to be percentages. 134 00:11:44,730 --> 00:11:52,020 Let me just add the percentage to this. 135 00:11:52,090 --> 00:12:01,950 I'm going to Ctrl+C, I'm going to paste special, just the values and I can just delete these. 136 00:12:04,630 --> 00:12:07,730 That's the layout of my table. 137 00:12:08,670 --> 00:12:17,880 Now as a next step, what I'm going to do is to bring the data into this table from my original data table. 138 00:12:17,880 --> 00:12:24,450 Let's think about the type of formulas that I need to get the data out of here and into here into 139 00:12:24,630 --> 00:12:29,370 this format that I want, to then further on use in my dashboard. 14399