Excel Indirect Sheet Name In Formula

It is a reference to a cell contains either an a1 style reference or an r1c1 style reference or a name defined by the name formula or a reference to a cell in text form.

Excel indirect sheet name in formula. The formula above reduces to indirect sheet1 a1. Written by co founder kasper langmann microsoft office specialist. Using sheet names as variables with indirect now you can change cell d1 to product2 and the revenue numbers will dynamically update and get the numbers from the second worksheet. How to use the excel function.

In this example we have sheet names in column b so we join the sheet name to the cell reference a1 using concatenation. Use the indirect function in excel to convert a text string into a valid reference. This argument can reference to another workbook. So for example you could change a sheet name perhaps with a drop down menu and pull in information from different worksheet.

Again the formula sum indirect a3 c1 c4 will return the value 126 in cell b3. The sheets name is defined by its years. The indirect function is a fascinating built in function in microsoft excel. And now you can take a step further and imbed this indirect formula into other excel functions to calculate the sum and average of the values in a given named range or find the maximum minimum value within the rage.

The indirect function tries to evaluate text as a worksheet reference. So to recap you can use indirect to refer to multiple worksheets variably like this. Sum indirect b6 c6 which returns the sum of the named range data on sheet1. Sheet 2 b1.

An indirect function consists of two arguments. Use the operator to join the sheet name in cell a1 with a1. Indirect a1 g5 that formula returns the value in cell g5 on the sheet named. Let s work on an example where total and average sales of years 2016 2017 and 2018 are given in three different sheets.

In microsoft excel the indirect worksheet function returns the contents of the specified reference and displays its contents. In the example shown the formula in d6 is. It can even be a worksheet name. To create an excel dynamic reference to any of the above named ranges just enter its name in some cell say g1 and refer to that cell from an indirect formula indirect g1.

For a sheet name in a1 a1. To reference a named range on another sheet you can use the indirect function with the required sheet syntax. This reference could be a cell or range of cells. It returns the reference specified by a text string.

You can reference each attribute of the reference workbook name worksheet name and cell reference. If a sheet name has a space in it the reference becomes something like this. How this formula works. One is ref text that is a mandatory argument and the second one is a1.

Using excel indirect function with data validation.