The indirect sheet name is a Microsoft excel function that helps you return value from different sheets using just the text strings. In addition, this function can help you pull data from multiple sheets using just some text strings.
The INDIRECT function in Microsoft Excel is often used when the user needs to return the reference supplied by a text string. You will learn how to utilize this INDIRECT function in this article by referring to the name of an Excel sheet and returning appropriate data from that worksheet. You will discover how to do this by reading this post.
Indirect Sheet Name Use
We can utilize this function to copy a cell or a range of cells from another worksheet since the INDIRECT function locks a cell reference or a range of cells. For instance, in the image that can be seen below, there is a random chart found in Sheet1 and Sheet2 that includes the names of contributors and the contribution amounts for the first day and the second day. With the indirect Sheet, Name will pull both of the data in one single sheet. In addition, it Can pull data from multiple sheets and ranges. The formula we need for this is “=INDIRECT(C3&”!C3:C6″)” Here, C3 is the Sheet name, and C3:C6 is the cell range of that sheet.
Step 1: Open an MS Excel Sheet, Enter data in multiple sheets and open another sheet to Enter Sheet names and other data.
Step 2: After the test data has been created, Enter the formula and modify it to your data placement, and this will pull the numbers from those sheets.
Indirect Sheet Name Sum
We can do numerical calculations for a variety of data that the INDIRECT function has saved by utilizing any other function that comes before the INDIRECT function in the order of operations. Let’s look at our primary dataset once again, shall we? Now that we have these numbers, we will calculate the total amount of money donated on a certain day.
The following output table may be considered, which can be found on a different worksheet inside the same book. To determine the total amount donated for the First Day, we will first use the SUM function in cell C5 before moving on to the INDIRECT function. This will help us make a summary faster as this will pull the total sums from multiple sheets with a simple formula. The formula used for this is “=SUM(INDIRECT(C3&”!C3:C6″))”.
Step 1: Open the previously used workbook, delete the existing formulas from sheet 3 and enter the new formula.
Step 2: Modify the formula according to your preferences, and the formula will pull the data from different sheets. Just remember to enter the sheet’s name and ranges properly.
Indirect Sheet Name And Data Validation
In this step, we will create a drop-down list in cell C4 that contains the names of the sheets. The contribution amounts from the chosen sheet will be shown in the range of cells known as C5:C9, and we will need to give the sheet names to those cells using the drop-down list. Therefore, let’s begin by creating the drop-down list in Cell C4 first. Then, we can create a drop-down menu to pull that data from that sheet. You will just need to change some things around in Sheet3. We have to just place both sheet names in Sheet3 and just use the formula from the first method.
Step 1: Open the previously used workbook and enter the sheet names. Open up the Data Validation tool and enter the range for the Sheet names.
Step 2. Now enter the formula “=INDIRECT(C2&”!c3:c6″)” in cell C3, and then you can use the drop-down menu to select the sheet number to pull data from that sheet.
Conclusion
Now you know how to use indirect sheet names to pull data from multiple sheets in a few ways. Working with multiple sheets will be way easier as you can pull the data you need from other sheets into one sheet for comparison.