Original subtitles

1

Now we're going to create our first form control. The first form control is going to control the division.

2

And the division is going to control the main point of view that we're going to have for this dashboard.

3

The table that we want to create after this, that's going to be controlled by the division. Because the

4

apps that we're going to show in that table are going to be the apps that belong to the division that

5

the user selects here.

6

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

is going to work.

8

Let's go to the dashboard view here.

9

That's the dropdown that we want to create and this dropdown is going to decide what is shown in

10

this table.

11

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

Then the variance to previous year, and also the percentage change to budget.

13

I'm also showing the bar that emphasizes this difference. That I can visually tell

14

here I do really well in comparison to previous year, and here I didn't do so well in

15

comparison to previous year. And here

16

I'm also showing the up and down arrows to be able to tell how well have I performed in comparison to

17

budget.

18

So these are percentage based

19

and this is the actual value of the KPI.

20

And I have split the table into three main areas that concentrates on each of these KPI.

21

What I don't have in the original data is the percentages. Because in the original data what I have is

22

the actual value. So I have to calculate those percentages in my data table. But I have to do it in a

23

dynamic way because when I select the game apps it should show the percentages that are related to the

24

apps inside the division game apps.

25

Let's go back to our draft dashboard and let's start to set up the calculation area.

26

Up here I already introduced the selected information. This is the information from the

27

form controls.

28

So I want the results of the division to be here from the form controls.

29

Let's just go in and add our first form control. In the developer tab, insert, and here I'm going to

30

add a combo box and we just have to draw it out.

31

We can organize it and make it nicer later on, right-mouse click, format control. The input range is something

32

I already have in the calculation tab, and this is like my master data. The cell link where I want the

33

results of this form control to be held is in the calculation tab.

34

I keep everything together on the same side.

35

I don't spread and keep anything on the dashboard tab side itself. The dashboard tab is something that I

36

want to lock and protect later on.

37

And I only want to open those cells where the user is actually inputting but I don't want to write anything

38

in those cells. I keep this in the calculation side as well.

39

Ok and here's the dropdown lines. I only have three divisions and I don't want to see any more lines.

40

I'm going to restrict it to three.

41

When I select game apps. What number should I get back in the calculation tab? 2.

42

Because it's the second position.

43

OK if I select utility apps. What number are you going to get? 3.

44

Because it's the third position.

45

What happens if I actually delete this?

46

Is it going to work?

47

Productivity apps.

48

It writes a 1. It doesn't matter if you delete this by mistake it's going to overwrite that cell

49

with the number.

50

I've made that selection

51

but now what I need is to create the calculations that I need for that table and I need to show the

52

apps that are based on this selection.

53

Let's just leave some blank space here because we might need it later.

54

And let's start by putting our calculation from here.

55

I'm just going to copy the formatting of this and paste it here.

56

And put "Calculations for scrolling table".

57

And let's expand this since we're going to

58

need a bunch of calculations here.

59

Now, down here.

60

Let's start with the number.

61

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

to be my KPIs. For the app name,

63

let's make this dynamic now that I see the app that belongs to the division that is selected by

64

the user here.

65

How can I do that?

66

What formula could work here?

67

Well, I'm going to use the Index formula and since this is in the same order as this. This is one

68

two three, one two three. I can index this area.

69

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

I'm going to index this.

71

And what do I want returned for rows?

72

For rows I don't want anything so I can skip that and for columns

73

I want this.

74

You see how I skipped the rows.

75

You don't necessarily need to do that.

76

You can also do just this because Excel is smart enough to know that you have only highlighted one row.

77

So you can't be giving it a row argument.

78

It's going to skip it by default.

79

So if this is a two, it's actually going to move across columns and not try to go down rows.

80

It's smart enough in that sense.

81

I'm going to fix this.

82

I'm going to pull it down.

83

Here I get empty

84

because there's nothing else in here.

85

But what I just want to do is to be on the safe side on two aspects.

86

One is that well the app business is doing really well so I could be getting more apps.

87

I do want to leave some space for new apps.

88

Let's see how many these are, that's 15. Let's add five more until here.

89

I'm just going to put a border around this so I know until where I'm planning to add possible apps.

90

And here I also want to be on the safe side and just put an If

91

that If the results of this index is equal to nothing then show nothing.

92

Otherwise do the index part.

93

OK.

94

Now for the number. For the number I can just check if this does not equal to nothing.

95

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

Just give me row A1.

97

So what is that going to give me?

98

It's going to give me 1, right?

99

Otherwise give me nothing.

100

I can pull this down until here, it's 12.

101

So let's change this to 1. This changes until 15.

102

We change it to 3.

103

It changes until 13, until this one. That seems to work.

104

I'm just going to put a dark border on the bottom here. Just so I know until where I have formulas.

105

With this one.

106

And I also want to capture the division, the main division because I want to show the totals on the top

107

header.

108

I want to show the total revenue, total profit, and total cash for the main division.

109

So I need to capture that as well.

110

I'm just going to leave a space here and put the main division down here.

111

How can I get that?

112

I'm just going to index these and go down by the position of the selected division.

113

I know that utility apps is selected in this case and I don't need an index number for that.

114

I don't need to put any formulas there.

115

Now what I want to do is, let's just make this a little bit more appealing in terms of formatting.

116

Here I want to add the KPIs. The KPIs that I have are these three.

117

I'm going to copy them and paste them as special but transpose them. So I get it one after the other.

118

Now, I need to repeat this because I need one section for actual information, then I need a section for

119

previous year, and a section for budget.

120

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

year here, and let's do budget here.

122

And the other thing that I'm going to do is change the colors of these to match the main colors that

123

I originally had in my wireframe. To these three colors because it is easy for me to distinguish what

124

is what

125

also on the calculation tab. This one is my actual, so that was the darkest one. Then its previous year

126

was the lightest color, and the budget was the one that's kind of in between.

127

These ones I'm just going to make them into the gray color that I have there.

128

After this what I need to calculate is the variances. The variance actual to previous year, and variance

129

actual to budget for each of these KPIs.

130

Let me just make a note here.

131

"Variance actual to previous year" and that's going to be this one, and then I want actual to budget.

132

So I can copy those here,

133

and make them in the same gray as I have there. My variances are going to be percentages.

134

Let me just add the percentage to this.

135

I'm going to Ctrl+C, I'm going to paste special, just the values and I can just delete these.

136

That's the layout of my table.

137

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

Let's think about the type of formulas that I need to get the data out of here and into here into

139

this format that I want, to then further on use in my dashboard.