INDIRECT() w. Range Naming - Applications

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
5 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
€96.21
List Price:  €134.70
You save:  €38.49
£79.77
List Price:  £111.68
You save:  £31.91
CA$143.95
List Price:  CA$201.54
You save:  CA$57.58
A$160.33
List Price:  A$224.47
You save:  A$64.14
S$135.93
List Price:  S$190.31
You save:  S$54.37
HK$776.63
List Price:  HK$1,087.32
You save:  HK$310.68
CHF 90.06
List Price:  CHF 126.09
You save:  CHF 36.02
NOK kr1,139.30
List Price:  NOK kr1,595.07
You save:  NOK kr455.76
DKK kr717.87
List Price:  DKK kr1,005.05
You save:  DKK kr287.17
NZ$177.29
List Price:  NZ$248.21
You save:  NZ$70.92
د.إ367.26
List Price:  د.إ514.17
You save:  د.إ146.91
৳11,945.58
List Price:  ৳16,724.30
You save:  ৳4,778.71
₹8,518.63
List Price:  ₹11,926.43
You save:  ₹3,407.79
RM448.65
List Price:  RM628.13
You save:  RM179.48
₦154,771.52
List Price:  ₦216,686.32
You save:  ₦61,914.80
₨27,852.21
List Price:  ₨38,994.21
You save:  ₨11,141.99
฿3,415.60
List Price:  ฿4,781.97
You save:  ฿1,366.37
₺3,525.07
List Price:  ₺4,935.24
You save:  ₺1,410.17
B$620.23
List Price:  B$868.35
You save:  B$248.12
R1,852.48
List Price:  R2,593.54
You save:  R741.06
Лв188.12
List Price:  Лв263.38
You save:  Лв75.25
₩146,004.73
List Price:  ₩204,412.46
You save:  ₩58,407.73
₪366.53
List Price:  ₪513.16
You save:  ₪146.62
₱5,844.26
List Price:  ₱8,182.20
You save:  ₱2,337.93
¥15,707.80
List Price:  ¥21,991.55
You save:  ¥6,283.75
MX$2,012.91
List Price:  MX$2,818.16
You save:  MX$805.24
QR364.43
List Price:  QR510.22
You save:  QR145.78
P1,382.66
List Price:  P1,935.78
You save:  P553.12
KSh12,923.70
List Price:  KSh18,093.70
You save:  KSh5,170
E£5,089.49
List Price:  E£7,125.49
You save:  E£2,036
ብር12,725.10
List Price:  ብር17,815.66
You save:  ብር5,090.55
Kz91,190.88
List Price:  Kz127,670.88
You save:  Kz36,480
CLP$99,038.09
List Price:  CLP$138,657.29
You save:  CLP$39,619.20
CN¥729.75
List Price:  CN¥1,021.68
You save:  CN¥291.93
RD$6,065.06
List Price:  RD$8,491.33
You save:  RD$2,426.27
DA13,522.10
List Price:  DA18,931.49
You save:  DA5,409.38
FJ$232
List Price:  FJ$324.81
You save:  FJ$92.81
Q770.19
List Price:  Q1,078.30
You save:  Q308.10
GY$20,913.42
List Price:  GY$29,279.63
You save:  GY$8,366.20
ISK kr13,961.60
List Price:  ISK kr19,546.80
You save:  ISK kr5,585.20
DH1,005.73
List Price:  DH1,408.06
You save:  DH402.33
L1,844.84
List Price:  L2,582.85
You save:  L738.01
ден5,920.21
List Price:  ден8,288.54
You save:  ден2,368.32
MOP$800
List Price:  MOP$1,120.03
You save:  MOP$320.03
N$1,842.15
List Price:  N$2,579.09
You save:  N$736.93
C$3,678.44
List Price:  C$5,149.97
You save:  C$1,471.52
रु13,621.60
List Price:  रु19,070.79
You save:  रु5,449.18
S/372.86
List Price:  S/522.02
You save:  S/149.15
K405.52
List Price:  K567.74
You save:  K162.22
SAR375.60
List Price:  SAR525.86
You save:  SAR150.25
ZK2,766.47
List Price:  ZK3,873.18
You save:  ZK1,106.70
L478.77
List Price:  L670.30
You save:  L191.52
Kč2,420.15
List Price:  Kč3,388.31
You save:  Kč968.16
Ft39,675.18
List Price:  Ft55,546.85
You save:  Ft15,871.66
SEK kr1,106.30
List Price:  SEK kr1,548.87
You save:  SEK kr442.56
ARS$102,565.23
List Price:  ARS$143,595.42
You save:  ARS$41,030.19
Bs692.24
List Price:  Bs969.16
You save:  Bs276.92
COP$438,826.54
List Price:  COP$614,374.71
You save:  COP$175,548.17
₡50,760.09
List Price:  ₡71,066.16
You save:  ₡20,306.06
L2,539.15
List Price:  L3,554.92
You save:  L1,015.76
₲780,390.60
List Price:  ₲1,092,578.06
You save:  ₲312,187.46
$U4,451.49
List Price:  $U6,232.27
You save:  $U1,780.77
zł411.19
List Price:  zł575.68
You save:  zł164.49
Already have an account? Log In

Transcript

Hello, as a continuation of the previous discussion, where we were talking about the basics of index formula, this time, take care of experiment number two. Now by now you would be wondering where the index formula could actually be applied. And this will be the starting of the answer seeking exercise. We have three columns, so Jan FEHB March and each of them has two numbers space below it. So that Jan 12534 months Five, six, now, we want a mechanism by which if I change the yellow cells to let's say, m er I should be getting answer 11. If I write FEHB, then I should get an answer us seven basically some of the numbers underneath it.

Now one option could be that you start using if people do that, they say, if the cell is equal to fab then in that case it should fetch me a summation of these two values. Perfect is please go ahead ask another question. If the same sell a 15 is equal to j n.in double quotation, then in that case sum should be based on these two values, he else some should be based on the AMA are closing the brackets once, once again, once again, and that's the formula. Now, notice just for three months, the formula length is so large. Imagine if you had 12 months data, the length of the formula would have been four times this. So we want to fast and easy mechanism which gives us the answer in a very small and easy formula.

And the main point is it should not increase in length the moment more months come into the picture. Now let's see how to achieve this. First, I'm going to choose these one and two, and named this as g n You can use uppercase lowercase in writing the name gn, but make sure that after writing the name, you press enter. If you have these two cells APB into these two cells, I'm writing m ER and pressing Enter. So, Henceforth, I have three more names said pointing to these two cells, Jan pointing to these two and march Mar, two these cells. So now, what if I write some formula and this sum needs a location from where the numbers must be picked for totaling up now the location is depend on the jello cell.

Basically, this is the desk where address chip is mentioned. If address changes, the sum formula will go to a different address, but who will lead me to this address the concept of redirection that is going to be given by indirect formula, so indirect, go to this particular cell that contains the address it, and this will tell you where to go. So as of now five is pointing to where three and four. And because of which I'm getting seven. By the way, if you wanted to know what is going on in excels mind, you can simply choose the indirect formula, right starting, starting right from I ending till the bracket of indirect and pressing function key f nine. So the moment I change this to Jan, I get three, the moment I change this to Mr.

I'm getting five plus six. So internally what is happening in red is a formula which is going to this desk it is taking the address and going to the right location. And these two numbers once it is fair to the sum formula, it quickly adds up the numbers. Now, you might tell me, Look, if there are nine more months of data, do I need to name these two cells respectively for all the remaining nine months? No. Let's see fast how this can be done.

Let's see I have data till December. And what I'm doing is I'm simply going to add plus one To the previous cells so that we populate a random set of numbers for our exercise, now, instead of naming them individually as APR me yg NGU l, I can choose the entire block, including the name. As discussed in one of the previous videos with respect to names, mass name or bulk naming, we have a technique with us that is, you go to the Formulas tab and there is a button called create from selection. Primarily, it points to the fact that you can create the names from the selected area. So I click on Create from selection. And it says Create names from selection and take the names from where from the top row five press OK.

It seems that nothing has happened on my screen. But you know more. Let's look at the drop down of the name box. Whoa. I'm getting all the names of the month in the T letter format, which has begin in the heading just for testing. If I put let's say j u l mm despite this He says and to make the formula work, I will simply modify the drop down list such that it incorporates other nine months.

So, I go to the yellow cell data within which look for data validation, click on Data Validation the items suggest that allow a list but the source is restricted to only these T cells. So, let me change those three selections right from Jan till December. And as I press OK, this drop down has all the 12 months value, if I now choose AG, let me check 10 plus 1121. So, there you go with the same length of that formula, we are able to derive an answer right from Jan till December. This is using the technique of indirect and naming and which will use it in a much more powerful manner in the next few videos. Till then practice this exercise where we also saw how to bulk name a particular range

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.