Original subtitles

1

The next step that gets us closer to our scrolling table is to get the data from the data tab into

2

our calculation tab. And this data is going to be dynamic because it's going to reference the app that

3

it sees here.

4

And this app is basically given based on the selection that the user makes here. Which is actually driven

5

by this one.

6

So when this one changes these are going to change,

7

and as a result the data in the table is going to change. Because it's going to pull different data from

8

the original data tab. Which formula could work here?

9

The Vlookup formula would work. We could do some indexing here.

10

So let's say that this is actual revenue, that this position in this table is fixed for sure.

11

And let's assume if this position was fully fixed we could say well that's going to be the second column

12

that it should reference. When we do the Vlookup for the number of columns it should move, we could

13

say move to. And that's the key here.

14

Now for profit, we could say well that's the third column and that's the fourth column.

15

We could use this type of fixed indexing and have the Vlook up function reference these indices.

16

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

the profit to after cash.

18

Things might move around here, and I want to be on the safe side.

19

I can use another function that can look this up in the rows and can look up the columns as well.

20

To make sure that it has a perfect matrix match.

21

So which function would work here?

22

Index and match, match.

23

For that to work I have different options of using it. Because I have many headers here.

24

If we go to the Data tab you see I have the year, month, and so on.

25

I need to do one of three things.

26

Either I make a helper cell here that combines all these into one so they're all unique to each column.

27

Or I use index match and make them match an array function using Ctrl+Shift+Enter. Or I use another

28

Index function in the Match function and still use the array way of solving this.

29

But because I don't want my formulas to become too complex. And when I design dashboards that are going

30

to be used by other people and I don't know their level of Excel, I always like to keep things simple

31

and to be on the safe side.

32

So I'm going to go by creating a unique key.

33

Using Helper cells. My unique key is going to be a combination of year, and month,

34

and the scenario, and the account. That's going to be my unique key.

35

I'm just going to highlight all of this. Just go all the way to the end because we're going to need

36

the unique key everywhere.

37

I'm going to click F2, Ctrl+Enter.

38

That's going to populate it all without changing my formatting.

39

That looks good.

40

Now what I need is to use that same unique key here, in this case.

41

Because I'm going to be referencing that sheet and doing my index and matches based on the data tab sheet,

42

let me just create a new window.

43

We go to View, new window. It just opened a second session of the exact same workbook.

44

And now in the view tab I'm going to arrange all. I want them vertical and I leave the tick mark here

45

that I want

46

only the windows of the active workbook. That gives me two sessions of the same workbook. In one of the

47

sessions,

48

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

And in the other session I'm going to be in the data tab.

50

Here, I need to create a unique key,

51

that's exactly the same to the unique key that I see in the data tab.

52

So for this one I'm just going to reference the cell. For the next one

53

I'm going to be typing it in because here is actual but I don't have previous year. Previous year is

54

actually the actual dimension but the year is going to be one year before.

55

This is going to be the month, so that's going to be the current month that I have which is referencing

56

the current month selected by the user from the dashboard.

57

And this is going to be the current year.

58

And now I can make the same formula that I have there.

59

It's this, and this, and actual and revenue.

60

I'm just going to take this, I'm going to copy it all the way across.

61

Only until here and then I'm going to adjust the categories that need to be adjusted. The month, everything

62

is fine here.

63

For previous year, I want current year minus one. That applies to these.

64

For budget this is fine.

65

The only thing that's different is, this should be budget and it's written just like this one here.

66

Budget.

67

OK.

68

So, that gives me the same unique key as I see on the data table.

69

Now I'm ready to write my Index and Match formula.

70

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

down. Because after this is the actual profit where I'm showing it on a monthly basis starting from January.

72

That information for June is also repeated in here.

73

All I need is this. If you're planning to add more lines here you might leave some space underneath.

74

If you're really adding lines on a regular basis.

75

You might want to turn this into a data table. The only thing you have to be careful of is that this

76

data table can't have many headers. You might want to add an empty row in between and then just put

77

in dummy headers and turned this into a data table. And then still have your formulas referencing these

78

keys that you need to retrieve from the system.

79

The advantage is that every time you get new lines added that your formulas are going to update automatically.

80

Now for the purpose of this example dashboard, I'm just going to restrict my data set like this. But keep

81

that in mind when you're creating it for your own life dashboard.

82

I'm going to fix this area and press F4.

83

Now, how many rows do I want to go down?

84

Well, I need to match this app right here.

85

Now let's think about the fixing

86

while we're writing this.

87

What do I need to fix here?

88

I need to fix the column and where am I looking this up?

89

I'm looking it up here.

90

I'm going to fix the entire range by F4. They are both from 9 to 51, 9 to 51. Because the

91

index range and the match range have to be in sync. My match type is a perfect match.

92

So I want a zero.

93

This means I'm going to move down so many rows.

94

It doesn't know how many columns it should move and that depends on the unique variable that I have

95

on top. I need to do another match

96

and now I'm going to match for this one.

97

And let's think about fixing. What do I need to fix here?

98

The 8, right?

99

Next argument is the Lookup array. Where I mean looking it up? I'm looking it up here. I'm going to fully

100

fix this,

101

and I want a perfect match. Close brackets, close brackets.

102

I'm going to press enter.

103

Let's take a look at our whole formula.

104

That's an index. We index the whole area.

105

We know how many rows to move down and we know how many columns to move across to get our KPI.

106

Is this the right number?

107

Commuta, actual, June revenue.

108

Let's look for Commuta.

109

Here it is, 6353.

110

That's this one. Now the real test comes when I'm going to just pull this across just to test

111

if it works and pull it down.

112

This one, Accord profit is 800.

113

Accord profit is 800.

114

So it looks good.

115

Now the one thing I want to account for is if this is empty it should also return nothing.

116

I'm going to add an If statement here,

117

and only run this index if this does not equal to nothing. Then it should do that.

118

But again let's not forget the fixing.

119

I have to fix the N

120

in this case.

121

If it doesn't equals to nothing then run, do the index thing and find the number.

122

Otherwise return nothing.

123

Let's pull this all the way across until here.

124

OK it looks good.

125

Now I'm just going to be copying it down to here.

126

Highlight the area, press F2 and Ctrl+Enter.

127

That looks good.

128

Let's do some more testing.

129

Let's look for Mosquit, and let's look for budget profit. It should be 156.

130

So, Mosquit is here.

131

156.

132

That's budget profit.

133

That looks good.

134

The other test is to change the selection.

135

Let's actually go and change it from here.

136

Let's go to game apps and see if we get a different list.

137

We do.

138

That's Perino.

139

That's 15033,

140

for Actual.

141

What is this one? That's actual previous year. That should be 15064, that's 15064. Let's just scroll

142

that's 15033, 15033.

143

OK so that looks good.

144

It's working.

145

Let me close the second session here.

146

And we are going to revert back to our original session.

147

Let's calculate the percentages here as well.

148

This is the actual to previous year for revenues. It's this number minus previous year's number divided

149

by previous year's number.

150

And we want this formatted as a percentage.

151

I just want to make the exception again here.

152

I'm going to do an If this does not equal to nothing,

153

then run this. Otherwise give me nothing and I have to fix the N here.

154

Can I pull this across?

155

Let's check. I can pull it across to this one, that's going to work.

156

It's going to be this minus this, divided by this. Fine, that's going to work.

157

What about this one?

158

It's going to be previous year minus the budget.

159

That's not what I want.

160

I want the actual minus budget divided by budget. That revised formula is what I'm going to copy across

161

to here.

162

Let's double check here.

163

That's this one,

164

minus this, divided by this.

165

So that looks good.

166

And my if is there. I'm just going to highlight this whole area again.

167

I'm going to press F2 and Ctrl+Enter.

168

Let's just update the formatting that they are also a percentage.

169

One last thing I'm going to do is copy the entire line and paste it here as well.

170

It should automatically retrieve game apps from my data table and that's the header.

171

Everything seems like it's working for this one.

172

This minus this, divided by that.

173

Let's just put in the color formatting here as well. Just so that I can tell which one is actual, which

174

one is budget, and so on. I'm just going to click on this and paste it here.

175

OK so that's the basic setup of the calculation table that's going to feed our scrolling table that

176

we're going to create next.