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


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.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
17 comments on “Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
  1. 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

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

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

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

    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.

  7. 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
    20BD
    23GH
    24Ck
    while Dept 2 Variable will be
    45KH
    49LP
    53NM
    …….

    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
    45KH
    49LP
    53NM

    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

  8. Marco says:

    Hi everyone.
    I’m wondering if anyone could help me.

    I have around 800 workbooks with about 30 sheets each. They all have different filenames and sheet names. And they are in different folders.
    The only thing they have in common is that all the data is in the same cell position.
    In order to organize the data I need some kind of function/code wich can retrieve the data on command.

    For example:
    In each file there is a value that only appears on that file. That value is always in the same cell position. So by entering that value is it possible for the code to retrieve all the data present in the file that has that value?

    Could you guys help me with this?
    Bare in mind that I have no knowledge of VBA coding. So if possible could you walk me through it?

    Thanks in advance for your help.

  9. Gary W Sumner says:

    Hi all:
    I have been looking for what should be a simple answer, but haven’t seen it yet so I am asking for it out right.
    I want to have a sheet look in a specific cell location in its prior sheet that is a sum of its column, it needs to be dynamic, cannot just copy/paste because value in prior cell location can change and it needs to update the next sheet.
    Thanx in advance for any help :)

    • Tom Urtis says:

      Please clarify with examples…
      – what ‘prior sheet” means.
      – which column on which sheet is being summed.
      – what “update the next sheet” means.

      Basically, provide an explanation of your workbook, and an example of what results you expect.

  10. Scott Danos says:

    HELP: I have a long spreadsheet with fixed column names. I want to sort the data in different worksheets within this workbook. My question is “How to make a change in the first worksheet that will cascade to other worksheets in this workbook even though the data are sorted in different ways?” So I want to add data in cell A2 in the “master” worksheet, but this might be sorted into cell A3245 in the second worksheet and be cell A629 in another worksheet.

  11. Tina says:

    Hi Tom,
    Please help. I have the formula below and it works fine for the month of January. However, I would like to set it up so I can drag copy the formula across the column instead of go to single cell and change (for ex. AL:AL (for Jan) to AM:AM (for Feb), etc…)

    =SUMPRODUCT(SUMIFS(INDIRECT(“‘”&TO&”‘!AL:AL”),INDIRECT(“‘”&TO&”‘!B:B”),$B53,INDIRECT(“‘”&TO&”‘!C:C”),$C53))

    TO = range for (Sheet1, Sheet2, Sheet3, etc…)
    AL = January column; AM = February data column; etc…
    B:B = employee name column
    C:C = employee task #

    Also, not all the sheet has January in column AL or February in column AM (though all the months are in the same row (Row 17) for all sheets , how can I twist the formula to look for Jan month of all sheets and total them instead of stating AL:AL or AM:AM as it may grab wrong month data.

    Thank you very much in advance, Tom.

    • Tom Urtis says:

      Thanks for following my blog examples.

      I’m not sure without seeing your workbook design, but in my blog example I referred to cell addresses by their row and column position relative to where the formula is. I see that you have hard-entered column AL which is column 38. If for example your formula is in cell D53, then you’d refer to column AL as ADDRESS(row(), column() + 34). The column refs would correspond to the relative position of the formula and therefore increment by 1 (meaning AM, AN and so on).

      Hope that helps, as my first observation from a distance.

Leave a Reply

Your email address will not be published. Required fields are marked *

*