Home / Formulas / INDIRECT to extract a value from another worksheet

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

About Excel Help Desk

Check Also

vLookup and Match Names and Numbers

For this problem we received some sample workbooks. We then used some formulas to compare the information and match the number to the name

3 comments

  1. I would like to seek your help for the following calculation in EXcel:
    Electricity Consumption Slab Rate
    667 0 – 75 3.33
    76 – 200 4.73
    201 – 300 4.83
    301 – 400 4.93
    401 – 600 7.98
    601 and above 9.38
    =IF(H133>600,H133*9.38,IF(AND(H133>400,600>=H133),H133*7.98,IF(AND(H133>300,400>=H133),H133*4.93,IF(AND(H133>200,300>=H133),H133*4.83,IF(AND(H133>75,200>=H133),H133*4.73,IF(AND(75>=H133,H133*3.33),H133*3.33))))))

    I have put the above formula but the total calculation always shows 125 extra meaning say if it is 1000 it shows 1125.

    I need your suggestion.

    Thank you.

    • Hi,
      Are you still looking for help with this problem ?
      Please send through a copy of the workbook that you are using to support@excelhelpdesk.com and we will investigate this to assist
      Regards
      Excel Help Desk Support Team

  2. It’s a pity you don’t have a donate button! I’d most certainly donate to this superb blog! I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account.
    I look forward to fresh updates and will share this website with
    my Facebook group. Talk soon!