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
- 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”.
- If Cell A1 contains the name of a named range, Indirect(A1) would refer to that named range.
- 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