Atlas Logo

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…
…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. Bookmark the permalink.

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

  1. Sarav says:

    Dear Tom,

    I was searching for my issue, i came upon to your site. it looks wonderfull, and i can learn a lot from your thank you for your service and support to all of us

    I am creating an excel workbook
    each worksheet is a collection of data of one employee. so worksheet will be having the name of the employee.
    in a worksheet, in column C16 to c54 there will be Variables and in column g16 to g54 the values for these variables are captured. this variable will be common for a set of employees belonging to a department. then for another set of employees belonging to another department these variables will change
    for ex Dept 1 variable will be
    while Dept 2 Variable will be

    i need to count the value of variables against these from the various sheets all named after the employee!
    these set of variables will be sharing the Same Cell location in their respective sheets
    for ex for Dept 1 employee Sam , the sheet name will be SAM column C16 will have 20BD
    where as for Dept 2 Employee Mark, the sheet name will be MARK column C16 will have 45KH
    In the consolidated sheet, will be like this listed one below the other
    C16 onwards G16 onwards
    20BD Count of 20BD from all employees
    23GH Count of 23GH from all employees
    24CK Count of 24CK from all employees

    The Variable 45KH will be in the cell C19 in the consolidated sheet while in the employees sheet it will be in C16.

    I tried IF , Indirect …. but i couldnt get this done
    can you please suggest me a solution

    Many thanks

  2. Huw says:

    Hi Tom,

    Similar to the queries above, could you help with my issue…

    I want to copy out cell D17 from every one of a number of worksheets (the number varies so a flexible formula would be ideal here), which are randomly named – see below
    For example I would like a final summary table to contain the following information acquired from the worksheets of the same name.

    Worksheet D17
    KBD01 11 AC 1.6
    KBD01 11 AS 1.4
    KBD01 11 BC 1.7
    KBD01 11 BS 1.5

    Not familiar with VB so a formula if one exists would be greatly appreciated and save me a HUGE amount of time since I have a number of spreadsheets to do this for.

  3. 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?


  4. 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

    • FARHAAD says:

      Good day

      I need to make a sheet that collects data from cell e57 on each sheet named MAY 1 through MAY 31,when trying the formula all values are showing 1 except MAY 21 shows 4 and from MAY 26 just show reference

  5. Bhavin says:


    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.

  6. Sridhar says:

    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.

  7. 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