atlas

Share Button

Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets

Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2, Sheet3, Sheet4, and Sheet5) are set up the same way, with their regional total expense amount in cell B12.



On the Summary sheet of your workbook is “B12″ (without the quotes) in some cell, for this example cell E1 as seen in the following picture. Now, using the INDIRECT function to refer to that cell value in E1, and the ROW function to help refer to each of the four source sheet names, you can enter this formula as seen in cell B3…
=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)
…and copy it down as needed. This returns the value of cell B12 in each worksheet.



The trick is simply to plan ahead when you design your workbook to allow for a single formula to do the work of many formulas. And the beauty of this system is, if you change the location of the Total cell on the regional worksheets, such as if another expense item is added, simply modify cell E1 on the Summary sheet for that new Total cell address.

This entry was posted in Tom's Tutorials for Excel and tagged , , , , , . Bookmark the permalink.

8 Responses to “Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets”

  1. Nabil says:

    Hi Tom,

    Thank you for the formula. Is there any way to make it work even if the worksheets all have different names and don’t follow a pattern?

    Thanks

  2. Jenn H. says:

    This is the EXACT type of thing I am trying to do. I copied the format of =INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$E$1)
    and it worked on a blank worksheet…HOWEVER…on the worksheet I created, I have a tab named “List” & “Data” and the other tabs are numbered ‘1’ thru ‘100’ . I only want the info pulled from the numbered tabs/sheets (1-100) onto my summary sheet.

    1. It seems these named tabs are throwing off the formula and it does not work.
    2. How do I change the name from ‘Sheet” as per the formula example to the numbered tabs that I have?

    Please help. There has to be a way to where I don’t have to click on a cell press = and then go to the corresponding page/cell for 100 pages.

    • Tom Urtis says:

      Thanks for following my Excel blog Jenn.

      Yes, your question has a solution that would not force you to hit = and click on every cell of every one of the 100 sheets. However, I need to know how your workbook (I think you meant “in the workbook I created” when you wrote “on the worksheet I created”) is designed and what exactly you want to sum.

      For example:

      • Is it summing one single cell address on those 100 sheets?
      • Or are you summing a range of cells on those 100 sheets?
      • And (important) is it the SAME cell address and/or the SAME range on all 100 sheets?
      • What IS the cell or range address of interest on those 100 sheets needing to be summed?
      • Exactly how are those tabs named? 1? 2? 3? or are they Sheet1, Sheet2, Sheet3?
      • Are all those 100 tabs arranged among themselves without any intervening sheets, meaning, your List and Data sheets are maybe the two leftmost tabs and your other 100 sheets are arranged without any non related sheets between them.

      No matter how your workbook is arranged and no matter what sheets you want to include or exclude, the solution is possible. I am asking these questions to understand how your workbook is set up to see if a native formula will suffice, or do you need a User Defined Function with VBA to handle whatever craziness is happening due to the design that needs to be taken into account.

      – Tom

  3. Bhavin says:

    Hi,

    I have around 12 worksheet for dfferent functionlaity in one excel and each worksheet contains queries with status ‘Open’ and ‘Closed’.

    Now I want total open and closed in summary worksheet in same excel in below format:

    Functionality Open Closed
    Login 01 15
    Register 00 20
    Search 05 00

    Waiting for your reply. Thanks in advance.

  4. Sridhar says:

    Hi,
    The above trick is really amazing. Saved lot of time.

    I have one more query

    I have a excel file with 12 worksheets. My aim is to calculate the number of occurrences of 3 different words in all the worksheets

    I want to know if thr is any formula to get result like below
    word 1: 29
    word 2: 89
    word 3: 71

    Waiting for ur reply.

  5. Jennifer says:

    Hi Tom,

    Thanks for sharing this info. I am very new to Excel and learning a lot. I just started a position with a Biotech company that heavily uses Excel to crunch feasibility data using Excel. Being new to Excel, I do not know all the power it harnesses as of yet!

    Would you know how I can get specific cells of text from the same location on different spreadsheets into one area on a summary page. I do not know VBA, but am wondering if that is the only way to do this? I am having to cut and paste from each sheet to the one summary page, one by one. Tedious!

    Thanks jenn


Leave a Reply