Once the formula works for Game Div., we can expand on it to lookup values across the different tabs. column_num: How many columns to the right it needs to go to find the answer.Īs before, let’s start simply with the core formula first.row_num: How many rows it has to go down to find the answer.array: This is the area where the answer is.The syntax of the INDEX() function is: = INDEX(array, row_num,) (Here is a link to a guide to using INDEX() and MATCH() functions.)
Go back to the Summary tab and build the formula using the INDEX-MATCH approach. Note that spaces are not allowed in Table Names so you might want to replace the spaces with underscores.ĭo the same for the other two Divisions such that you have these table names: Specify a Table Name under the Design tab.
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. This converts the data to an Excel data table. This will prompt you to specify the area of the data table. Press CTRL + T to display the Create Table window. This approach involves converting all the data in the Division tabs into Excel data tables.Ĭlick on any data cell in the Division tab. Pull the formula down to C8 and it should now show the correct Invoiced amount values. The & symbol is used to combine the single quotation marks and the content of cell B6. The table_array parameter now becomes: INDIRECT(“’”&B6&”’!$A$4:$B$24”) Replace “Game Div.” with a cell reference and make sure to keep the single quotation marks.
#SELECT VALUES FROM DIFFERENT SHEETS FOR EXCEL CHART FULL#
The full formula reads: Cell C6 =VLOOKUP($B$4, INDIRECT(“’Game Div’!$A$4:$B$24’”),2,FALSE)Īs the INDIRECT() function is now included in the formula, all that’s left to do is to make the tab names dynamic. The tab name can’t be replaced with a cell reference without putting it inside the INDIRECT() function since the INDIRECT() function will translate the tab name as the address for VLOOKUP().Īs a first step, wrap the table_array argument of VLOOKUP inside the INDIRECT function. Since the table_array changes depending on the Division, the INDIRECT() function will be used here.īefore applying the INDIRECT() function, it’s worthwhile noting that when the tab names contain spaces, such as “Game Div.”, then any cell reference to that tab will need to include single quotation marks, e.g. The first parameter, lookup_value, is fixed, as well as the col_index_num and range_lookup. Where does the INDIRECT function come in? This was our basic VLOOKUP formula: =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE) The formula uses cell E5 as the address, which points the formula to cell A1. In the same way, if you use another cell (E5) containing “A1” and use that cell as the ref_text in the formula Cell E6 = INDIRECT(E5) To understand how it works, the formula =INDIRECT(“A3”) tells the function to go to cell A3 and returns the value in cell A3, which is “Summary Report”. The syntax of the INDIRECT() function is: = INDIRECT(ref_text,) (Here is a link to a guide to using the INDIRECT() function.) This function gets the right address from a cell or text reference. The INDIRECT() function comes in handy here. In order to make the formula dynamic to account for the different Divisions, the table_array part of the formula should be revised. However, when this formula is pulled down to C8, we get the same value since our tab is fixed to Game Div. In this example, we want an exact match.Ĭell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)
0 kommentar(er)
