Use INDIRECT to Pull Data from Different Worksheets/ Named Ranges

Formulas

INDIRECT is a Powerful function in Excel. In this post I am going to show you how INDIRECT can be used to pull data from Multiple Sheets/ Named Ranges into a Single Sheet or Dashboard without the use of Power Query

Lets recap the basics of this function

INDIRECT function in Excel returns the reference specified by a text string.

That is to say

  1. Indirect(A1) would not return the contents of Cell A1 but rather it would return contents of the cell or range specified in Cell “A1”.
  2. If Cell A1 contains the name of a named range, Indirect(A1) would refer to that named range.
  3. It follows from point 2 above that, if we use a DROP DOWN validation list or FORM CONTROL to change the contents of Cell A1, the reference returned by INDIRECT would change as well.

Picture below will make things clear. This example is taken from Microsoft Office Support.

 

Now back to main subject of this post

In this illustrative example (Click here to Download Excel file), I have the financial data of 11 biggest companies in US stock Market for 3 years in separate sheets. The sheet names represent the Company’s Ticker Symbol

Our objective here is to prepare a DASHBOARD where we can select any two Companies of our choice for comparison using “drop down lists”. The image below is what we are trying to achieve

 

Firstly we will create  named ranges for data of each of these Companies. We can do it manually by selecting the range and inserting name in the NAME BOX

 

But because the Sheets are in Same Format, I write a VBA code to do this naming in order to save time. You can see the code by going to VBA Editor and selecting Module 1

Sub createnamedranges()
Dim SheetName As String, myworksheet As Worksheet
For i = 2 To Sheets.Count
Set myworksheet = Sheets(i)
SheetName = Sheets(i).Name
Set myrange = myworksheet.Range(“B2:D19”)
Names.Add Name:=SheetName, RefersTo:=myrange
Next i
End Sub

Next step is to prepare a list of entities along with their symbols that would be used in the drop downs in DASHBOARD

In the Dashboard Sheet, we now create 2 drop down Lists so that users can select any 2 entities they want to compare

Now the most crucial part. Recall we used named ranges for each Company’s data using that Company’s symbol.

Thus Indirect(B2) above would return reference to named range “F” which represents data of Ford Motors

Thus we can use the following formula to pull data for the company chosen in the DROP DOWN

 

Please do let me know if you have creative uses of this function