Here we want to summarize the revenues from three companies, companies A, B, and C. We've got a tab for each of them and on each tab we've just got a line for revenue in years one, two, three and four. You'll notice that company A has very low revenue, company B has middly revenue and company C as much larger revenue. All we want to do is draw all of that data quickly and easily onto our summary sheets, Multiple Sheets 1. One way to do this is to use the indirect function. I'm gonna note on company A sheet where revenue starts and revenue starts in cell C5. So in my summary sheets, I'm going to note the cell C5 up the top here. And next to that I'll note where the others are, they're in D5, E5, and F5.

I can use the indirect function to go to company A C5.

So I build up my formula by saying go to company A, but that's not gonna go to the company A tab, that's just going to look for text company A. So I need to tell it to look for company A tab by adding on an open quotes exclamation mark. It'll now go looking for the company A tab 'cause it looks for company A exclamation mark. I then need to tell it to go to cell C5.

Now if I press Enter that indeed brings the four through from company A. Fantastic. I want to be able to copy this down and to the right. So in order to do that, I go back into my formula and I want to lock on column B. So I press the F4 button once, twice, three times, and a dollar sign appears before the B. I go to cell C3 and I want to lock on the row three. I want the C to change, so I press the F4 button once, twice. I can now copy that down and to the right, and my revenues automatically flow through. The only downside is that that revenue of four has to be in cell C5. If we went to company A and we then inserted a row unfortunately it now breaks the formula. It's now going to select company A C5 and unfortunately it's finding an empty blue cell.

So what I'd really like is for it to go looking for the revenue line, company A's revenue. So if we delete this out, what we can do is we can name a range. If I go to the company A tab, I'm going to select the four values within the revenue line and I'm going to hit alt+nn. So this gives me my Name Manager. I'm gonna hit New and I'm going to name it Revenue, but I want the scope just to work within this one sheet. I just want it to work within company A. I close out of that, and right now is I repeat the same thing on company B, alt+nn, I want a new named range, again called Revenue, but this time on company B.

And I repeat the same thing for company C.

(keyboard clicks) I can now write my indirect formula slightly differently. (keyboard clicks) In my indirect formula, I can say equals company A. Again, I'm going to lock onto column B. I can say add onto that the exclamation mark. But here's the important bit, I now add on the word Revenue, so it'll go looking for cells named Revenue on the company A tab.

If I close the brackets and press Enter, it finds that four. If I copy it to the right, because I did it in column C it looked in column C and found the four. Because I've done it in column E it goes looking in column E and finds the eight. I can even copy these down. And the beauty is here it goes looking for company B's revenue and here it goes looking for company C revenue. I can now insert lines, but because I've named those cells, Excel automatically changes to the correct cells.