0306 Creating Qtrs based on Financial or Calendar Selection

Advanced Excel Dashboard Crash Course Section 3: 2nd Dashboard using Functions
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€88.46
List Price:  €123.85
You save:  €35.39
£75.37
List Price:  £105.52
You save:  £30.15
CA$138.19
List Price:  CA$193.47
You save:  CA$55.28
A$155.11
List Price:  A$217.17
You save:  A$62.05
S$129.96
List Price:  S$181.95
You save:  S$51.99
HK$774.92
List Price:  HK$1,084.92
You save:  HK$310
CHF 82.69
List Price:  CHF 115.77
You save:  CHF 33.08
NOK kr1,041.08
List Price:  NOK kr1,457.56
You save:  NOK kr416.47
DKK kr660.36
List Price:  DKK kr924.54
You save:  DKK kr264.17
NZ$167.99
List Price:  NZ$235.19
You save:  NZ$67.20
د.إ367.26
List Price:  د.إ514.19
You save:  د.إ146.92
৳12,188.92
List Price:  ৳17,064.98
You save:  ৳4,876.05
₹8,462.20
List Price:  ₹11,847.42
You save:  ₹3,385.22
RM426.95
List Price:  RM597.75
You save:  RM170.80
₦160,354.96
List Price:  ₦224,503.36
You save:  ₦64,148.40
₨28,187.10
List Price:  ₨39,463.07
You save:  ₨11,275.97
฿3,309.66
List Price:  ฿4,633.66
You save:  ฿1,324
₺3,857.27
List Price:  ₺5,400.33
You save:  ₺1,543.06
B$565.91
List Price:  B$792.30
You save:  B$226.38
R1,839.21
List Price:  R2,574.97
You save:  R735.76
Лв172.99
List Price:  Лв242.19
You save:  Лв69.20
₩139,971.25
List Price:  ₩195,965.36
You save:  ₩55,994.10
₪358.69
List Price:  ₪502.19
You save:  ₪143.49
₱5,565.74
List Price:  ₱7,792.26
You save:  ₱2,226.52
¥14,488.55
List Price:  ¥20,284.55
You save:  ¥5,796
MX$1,957.85
List Price:  MX$2,741.07
You save:  MX$783.22
QR364.06
List Price:  QR509.70
You save:  QR145.64
P1,361.73
List Price:  P1,906.48
You save:  P544.74
KSh12,933.70
List Price:  KSh18,107.70
You save:  KSh5,174
E£5,075.98
List Price:  E£7,106.58
You save:  E£2,030.59
ብር13,459.34
List Price:  ብር18,843.61
You save:  ብር5,384.27
Kz91,190.88
List Price:  Kz127,670.88
You save:  Kz36,480
CLP$94,605.53
List Price:  CLP$132,451.53
You save:  CLP$37,846
CN¥729.42
List Price:  CN¥1,021.22
You save:  CN¥291.80
RD$5,902.61
List Price:  RD$8,263.89
You save:  RD$2,361.28
DA13,288
List Price:  DA18,603.74
You save:  DA5,315.73
FJ$225.56
List Price:  FJ$315.80
You save:  FJ$90.23
Q770.30
List Price:  Q1,078.46
You save:  Q308.15
GY$20,924.33
List Price:  GY$29,294.90
You save:  GY$8,370.57
ISK kr12,929.70
List Price:  ISK kr18,102.10
You save:  ISK kr5,172.40
DH926.13
List Price:  DH1,296.62
You save:  DH370.49
L1,709.82
List Price:  L2,393.82
You save:  L683.99
ден5,441.97
List Price:  ден7,618.98
You save:  ден2,177
MOP$801.19
List Price:  MOP$1,121.71
You save:  MOP$320.51
N$1,867.19
List Price:  N$2,614.14
You save:  N$746.95
C$3,690.56
List Price:  C$5,166.94
You save:  C$1,476.37
रु13,561.38
List Price:  रु18,986.48
You save:  रु5,425.09
S/367.72
List Price:  S/514.83
You save:  S/147.10
K405.78
List Price:  K568.11
You save:  K162.33
SAR374.98
List Price:  SAR524.98
You save:  SAR150
ZK2,781.79
List Price:  ZK3,894.63
You save:  ZK1,112.83
L440.50
List Price:  L616.72
You save:  L176.22
Kč2,204.44
List Price:  Kč3,086.31
You save:  Kč881.86
Ft35,802.41
List Price:  Ft50,124.81
You save:  Ft14,322.40
SEK kr965.37
List Price:  SEK kr1,351.56
You save:  SEK kr386.18
ARS$117,263.27
List Price:  ARS$164,173.27
You save:  ARS$46,910
Bs693.19
List Price:  Bs970.49
You save:  Bs277.30
COP$426,824.69
List Price:  COP$597,571.64
You save:  COP$170,746.95
₡50,658.12
List Price:  ₡70,923.39
You save:  ₡20,265.27
L2,602.65
List Price:  L3,643.81
You save:  L1,041.16
₲803,267.16
List Price:  ₲1,124,606.16
You save:  ₲321,338.99
$U4,195.69
List Price:  $U5,874.13
You save:  $U1,678.44
zł378.49
List Price:  zł529.90
You save:  zł151.41
Already have an account? Log In

Transcript

Hello everybody welcome back to the second dashboard. In this video we will learn how to get the quarter based on the selection that the user will make. To do so I have created a new column quarter and here we will be retrieving the quarter for that. In the map sheet. I've created this small table where I put in the month and there are two quarters some of the countries go with April to March for quarter say for them. I've used financial quarter and for the people who select calendar that is January from Jan to December then for them quarter is different.

So I've created a quarter which has both the parameters. Now as we all know that V lookup has two parameters, that is appropriate answer and exact answer. The exact is where it actually looks for the exact number and appropriate is where it looks for the slabs. Say for example 1234 to six seven and nine and 10 onwards. So we will be using And appropriate match to get the quarters. To make the work easy, I've used the name range, and that is selected the data and named it quarter data and the heading as quarter heading.

So let's get started. And I'll try to the example on 2012. And we'll be getting it through the other sheets as well. Now here I'll be selecting equals to the first parameter we require as month in numbers because in our source the month is specified in numbers 147 and 10, which we've just seen in the map sheet. Now, we will start with month there it is, then we want to convert a date into a month. So we will be selecting the timestream date, bracket close enter.

That gives just the month and numbers and now we'll be using this month for V lookup. So we'll keep cursor before him and start v lookup. Now the lookup value will be the answer derived from the month, we'll take the quarter data as the base. Currently, we are looking for a specific one. So we'll go with second column comma zero for our exact answer. Now I do know that we won't get an answer.

But still, for the sake of doing it, I'm just trying it out. As you can see, we get exact because one was the number but if I tried to drag it down, and I select a specific range, I do get errors. Now, I do not want the error, so I go with appropriate answer. So I'll be selecting one and I'll drag it down. So you can see we have all the quarters ready for us. Now what I want is I want the user to have the control over which quarter he wishes to select He or she wish to select.

So we'll be using match under column index number. But what is the base for the match? Now we'll be selecting the heading, which will be constant throughout and I'll press f4. Now that particular heading has to be compared with the quarter heading, which we have named already. And looking out for the exact answer, bracket, close, enter, and we'll drag it down. Now we may see an error and that's totally okay.

And if we select financial quarter automatically data gives the financial quarter and if you go for calendar quarter, we get the calendar quarter. Now this is something that we can put into our dashboard user control and based on the user control we can get the relevant answers. So if the user wants financial Quarter they can select the financial quarter from the dashboard and get the financial quarter as a base result. In the next video, we'll see how to create a dynamic database before then I'll add this particular formula into the other sheets. I have as well. Keep practicing.

See you in the next video. Thank you

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.