Excel Indirect Sheet Name Reference

How this formula works.

Excel indirect sheet name reference. How to use the excel function. It returns the reference specified by a text string. As you can see here we have the required arrey. Now we need to get these values to the main sheet.

As you know the usual way to reference another sheet in excel is writing the sheet s name followed by the exclamation mark and a cell range reference like sheetname range. Sum indirect b6 c6 which returns the sum of the named range data on sheet1. Here reference is sheet name and a1 is cell reference. 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.

It can even be a worksheet name. Using indirect function to refer a worksheet. Excel accepts the syntax when extracting values from different sheet which is reference a1. Supposing you have data with the same format across multiple worksheets or workbooks and need to get data from these worksheets or workbooks dynamically into another sheet.

So to recap you can use indirect to refer to multiple worksheets variably like this. At first download the working excel file that i have used to write this article. You can use the indirect worksheet function to create linked references to other workbooks. The indirect function can help you quickly get it done.

Create 3 worksheets titled as s1 s2 and s3 under the workbook name b1. Syntax of indirect function. The indirect function tries to evaluate text as a worksheet reference. The following screenshot demonstrates how an excel indirect formula can handle this task.

We will refer the worksheets s1 and s3 in worksheet s2. The indirect function is a fascinating built in function in microsoft excel. So for example you could change a sheet name perhaps with a drop down menu and pull in information from different worksheet. This reference could be a cell or range of cells.

Excel dynamic worksheet or workbook reference. In each s1 and s3 worksheets take some random numbers in the range of a1. You can reference each attribute of the reference workbook name worksheet name and cell reference individually by using the indirect function to create a user defined dynamic reference with worksheet cell references as inputs. Copy the formula to the remaining cells using the ctrl d or drag down from the right bottom of.

Written by co founder kasper langmann microsoft office specialist. Excel indirect function returns the reference specified by a text string. Let s break apart the formula you see in the screenshot and understand. Here we are going to see how to refer another worksheet in excel using the indirect function.