In the trading comps summary tab, we want to pull in the key numbers from the other sheets. Now, most of these companies we've put in as hard numbers for simplicity, but what we now want to do is we want to pull in our two companies that we've done, which is Coca-Cola and Keurig, Dr. Pepper. And you can see for Keurig Dr. Pepper, we've used a function called the indirect function. And the indirect function assembles a function through disparate parts and it's a very clever way of being able to do the reference once and be able to copy it right. Let me explain this. If I take for the case of company one, which is Coca-Cola, I just pull in their name manually and I'll take it from the very top of their sheet in cell A1. And that actually is referencing a range name, which is called company name. So you can see from this formula we have equals, and then we have the sheet name. And because the sheet name is a number 1, Excel has to put that into quotes, otherwise it will not treat it as a label, it will treat it as a number. Then we've got the exclamation point, and then we've got this range name, company name. Now we actually have all this information on this sheet. We've got the number 1 in the cell to our left, and if I go down further, you can see in row 81 we've got the range name, company name. So we can assemble those two pieces together to recreate all the information to build the formula. And we do that using the indirect function.

So the indirect function, the first thing we want to reference is the sheet name and I can pull that from the cell to my left. Then what I want to do is I want to put a dab of glue and glue in Excel is the ampersand. Then we're going to put the exclamation point in quotes because whenever we're dealing with text in functions, you must put it in quotes. Then another dub of glue, and I'm gonna go down and pick up my name of the range and close parentheses in hit enter. And you can see it pulls in the Coca-Cola company. Next is I want to be able to copy this right. But if I copy this right, I'm going to lose the reference to that number 1. So I want to be able to fix the number 1. I don't want to be able to fix it if I copy down because for Keurig Dr. Pepper, it needs to be number 2. But for Coca-Cola I'm going to absolute reference, not the whole thing, not the row. Because I want to be able to copy relatively, but I want to fix the column and that means as I copy it, right, it will always reference the number in column B. Now, down below, if you remember that range name, which had company name, if I hit enter and go down again, you'll see that there are many other range names that we're going to be pulling in to populate the table above. And these range names all that come from the individual sheets one and two. And these range names are sheet dependent range names. In other words, they're range names that you can have on sheet one and on sheet two. But because they're defined only as working on that sheet, it means that as long as we tell Excel what sheet we want to get from, we can use the same range name. So if I go to Coca-Cola now and I press F2, and I'm going to come to this reference, I'm gonna press F4 once, F4 twice. And I just want to fix the row because when I copy it down, I want that range name to be the same for Coca-Cola, and Dr. Pepper. But when I copy it right, I do want that range name below to change. So let me test this. I'm gonna copy this Control C, I'll go down to Keurig, Dr Pepper, control V. And you see it does work because this time it's still using the company name range name, which is down in row 81. But now instead of picking it from the sheet called number one, it picks it from the sheet called number two. Next, if I copy this control C and I paste it to the next two cells, I'm gonna paste it as a formula so I don't miss all the formatting. It pulls in the share prices for Keurig Dr. Pepper and Coca-Cola and the 52 week high. And this is because the range name that's picking up on the other sheet is the comp share price and the share price high. If I go to the sheet tab two and I go to the 52 week high, you can see in the name box just to the left here, that's pulling in share price high. And that range name works on sheet number two, but it also works on sheet number one as well. And this is because when we created the range name, we made it sheet dependent. Let me show you how to do that. If I created a name here, I did control F3 and then I choose new and I create a range name called test. The scope of range name can either be for the whole workbook or I can assign it just to this sheet, which is number one. And that means that that range name will reference cell C25 on this sheet only. And I could also create another test range name on sheet number two in the same cell, but make it dependent only on sheet two. So you need to use those sheet dependent range names for this to work. So we've done the share price in 52 week high, and then we just do a percentage there. And then we can pull in the market capitalization. We select these, the enterprise value, the price earnings ratios, the revenue numbers, the EBITDA numbers, the LTM EBIT and EBITDA numbers. And I'm gonna paste those in as formulas so I don't lose the formatting. I'm just going to take out those Xs there and continue, right? We haven't done the EBITDA numbers and we have got all our multiples coming in. So this is a very, very effective way of building a comps summary tab. And the beautiful thing about this, it's very easy to rearrange the companies. Because if I wanted to flip Coca-Cola and Dr. Pepper, I'll just need to type number two there and number one, and I've changed the order of the table. So this is a very, very useful, an effective way of building a comps summary table.