INDIRECT to extract a value from another worksheet


The ExcelHelpDesk Support team received the following request for help

Problem – Extract to a value from another worksheet

I am trying to create a summary of data from an excel document that has 86 worksheets. I need the contents of cell Sheet 2 cell AF5 to appear in sheet 1 E2, Sheet 2 AF 6 in Sheet 1 F2, and Sheet 2 AF3 in Sheet 1 G2 then same cells from sheet 3 in E3, F3, and G3 and so on down the page. Any suggestions other than manually enter =Sum(Sheet2,AF3) etc etc?

For this problem we received a sample workbook, that explained where help was needed. See the screen shot below of the spreadsheet and an explaination of what was required. You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

Step 1 – Include the INDIRECT function

Now we change the formula to use the INDIRECT function. It needs to include a string that has the worksheet and cell reference. That string is then used by the INDIRECT function to retrieve the value from the worksheet.

The formula is
=INDIRECT(“‘” & A2 & “‘” & “!$AF$5”)

The first part of the formula includes the worksheet name. Here we use the value that is included to Column A for the row required. Note that we must include the single quote value to allow for worksheet names that have spaces included to them.

Next we need to include an exclaimation mark to indicate that we have a worksheet reference and lastly the cell reference in the worksheet is included.

Step 2 – Drag down the formula

Now to automatically populate each of the values into the worksheet simply drag the formula down the Column D and the values will be referenced and extracted into the worksheet. See below the resulting spreadsheet and formula that was used.

Step 3 – Other possible improvements

For this formula we have essentially hard coded the cell value that is to be extracted for this INDIRECT reference. This could also be a referred value so that it could be changed when needed. It could also refer to a range of cells that were totalled or averaged.

Once you have this formula mastered it can save alot of time when working with multiple worksheets

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment