This approach involves converting all the data in the Division tabs into Excel data tables.
Click on any data cell in the Division tab.
Press CTRL + T to display the Create Table window.
This will prompt you to specify the area of the data table.
This converts the data to an Excel data table.
To change the formatting of the table, click on any table cell and go to Design > Table Styles to select a scheme you prefer or Clear to revert back to the original.
Specify a Table Name under the Design tab.
Note that spaces are not allowed in Table Names so you might want to replace the spaces with underscores.
Do the same for the other two Divisions such that you have these table names:
Go back to the Summary tab and build the formula using the INDEX-MATCH approach. (Here is a link to a guide to using INDEX() and MATCH() functions.)
The syntax of the INDEX() function is:
= INDEX(array, row_num,[column_num])
- array: This is the area where the answer is.
- row_num: How many rows it has to go down to find the answer.
- column_num: How many columns to the right it needs to go to find the answer.
As before, let’s start simply with the core formula first.
We will assume our only tab is Game Div.
Once the formula works for Game Div., we can expand on it to lookup values across the different tabs.
Start the formula by inputting:
Cell D6 = INDEX(
Go to the Game Div. tab and select the Invoiced Amount column.
Instead of displaying a cell reference, this will show now show as Game_Div.[Invoiced Amount].
The second parameter of the INDEX() function is the row_num.
Basically, that’s how many rows it needs to move down to find the answer.
Instead of hardcoding this, the MATCH() function is used to find what row corresponds to the Date selected in the Summary tab and return the position to the INDEX() function.
The syntax of the MATCH() function is:
The Match formula becomes:
The final formula becomes:
Cell D6 = INDEX(Game_Div.[Invoiced Amount],MATCH(Summary!$B$4, Game_Div.[Date],0))
Pulling this formula down to the Utility Div. row will return the same values since they are hard-coded to look inside the Game Div. tab.
To fix this, we will use the INDIRECT() function to help us get the dynamic tab names.
However, the naming convention in cells B6:B8 is different to that of the table names because the latter uses an underscore instead of the space.
We will need a formula that takes cells B6:B8 and converts them to look exactly like the corresponding table names—that formula needs to replace the space with an underscore.
The SUBSTITUTE () function will help us make this replacement.
As a first step let’s introduce the INDIRECT Function.
- Add the INDIRECT() function by replacing Game_Div. [Invoiced Amount] with INDIRECT(“Game_Div.[Invoiced Amount]”)
2. Replace Game_Div. with the cell reference B6 and combine it with the table header [Invoiced Amount] using the & symbol.
3. To replace the space with an underscore, use the SUBSTITUTE() function.
The syntax is SUBSTITUTE(text,old_text,new_text,[instance_num]).
- text: The cell you want the substitution to take place.
- old_text: What specific character you want to replace. In this case, it is “ “.
- new_text: What to substitute the old_text with. In this case, it is “_”.
- Instance_num: How many times we want the substitution to take place. This is an optional argument. We can leave it out which means we’d like all instances of “ “ to be replace with “_”.
=SUBSTITUTE(B6,“ ”,”_”)&”[Invoiced Amount]”…
4. The same substitution should be applied to the reference of the MATCH() part. The final formula now becomes:
Cell D6 = INDEX(INDIRECT(SUBSTITUTE(B6,“ ”,”_”)&“[Invoiced Amount]”), MATCH(Summary!$B$4, INDIRECT(SUBSTITUTE(B6,“ ”, “_”&“[Date]”),0))
5. Drag the formula down to cell D8.
With these two methods, you can automatically lookup values from different tabs.
One version used cell references and sheet names and the second method used table names.