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: , , , , , , , , , , , ,
30 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

      • Laura says:

        Hello, i got the same problem as Jenn had.
        If you still can help with this issue, my workbook have 44 sheets with specific names, for instance “01_TRIAGEM_CAMINHÕES” and “05_PONTO_DE_ÔNIBUS” but it doesn’t follow any logic, and there is a sheet named “RESUMO” (summary) where I want to list what’s in the cell L154 of each sheet.
        The list in “RESUMO” should start at cell M15.
        I also have already used a Macro for the sheet “RESUMO”, and i have tried to use other macros but it seems like I can’t make more than one macro run in one sheet (i don’t understand much of VBA)
        No chances I can change the sheets’ names eather.
        I want to learn how to do it automatically because there’s going to be more workbooks like this where the reference of each sheet (L154) might change.
        Thanks,
        Laura

        • Tom Urtis says:

          Your problem is solvable but some understanding of the logic in your workbook would be needed.
          • To confirm, is it that *every* worksheet except the RESUMO worksheet would need to be calculated.
          • You want to list what is in cell L154 of each worksheet but then you say that cell address may change. Will the cell address change for every worksheet the same way or will it be a different cell for different worksheets.
          • Why will cell L154 change — is it at the bottom of a list that will always be in column L, or will it be in the last column of any (any means any) data on each worksheet, or is there some other logic involved.

          • Laura says:

            Hi, the cell does not change for each worksheet. It will surelly change in the next workbook, but the formula will be similar so I’ll just need to change the reference.
            Anyway, I have already figured it out. Made a macro that works finally \o/
            Thanks

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

  12. Stephanie says:

    Hi Tom,
    What about extracting data from same cell same worksheet in different workbooks?
    Is it possible to do it?

    I got 650 files. Each have a worksheet called Sheet1 lets say and I would like to extract the same cell location from each file.

    I am okay with VBA tho I have no experience in that! Thanks

    • Tom Urtis says:

      If the workbooks are all in the same path and folder, you can loop through all of them with VBA to extract the data you need. And the workbooks would not need to be opened at the time (but you could open them programmatically to do what you want); not that you’d want to have 650 workboosk open all at once.

      This example on another blog post shows how to open and close all Excel workbooks in a specified folder. You don’t need to open the workbooks to extract the info; you *can* loop through closed workbooks with a formula that refers to each workbook name and its Sheet1 worksheet name. But in any case, see this example to get the ball rolling. The place in the code where a message box says workbooks is open and your code would go here is where the running sum would take place that the code would keep track of with each workbook, and ultimately place the final sum where you want it.
      http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-open-modify-and-close-all-workbooks-in-a-folder/

  13. Tina says:

    Hi Tom,

    Could you please help on this VBA issue:
    In Excel workbook, I have two sheets called “Master” and “Template”. In “Master” sheet, Column A = EmplID (cell A5:A50), Column B = EmpName (associate with each EmplID in col. A). I had the VBA code to auto-add worksheets per the EmplID list (cell A5:A50) in “Master” sheet. Running the VBA will copy “Template” sheet and generate 45 new sheets that has the sheet label = EmplID in column A/ “Master” sheet

    Sub CreateAndNameWorksheets()
    Dim c As Range

    Application.ScreenUpdating = False
    For Each c In Sheets(“Master”).Range(“A5:A50”)
    Sheets(“Template”).Copy After:=Sheets(Sheets.Count)
    With c
    ActiveSheet.Name = .Value
    .Parent.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:= _
    “‘” & .Text & “‘!A1”, TextToDisplay:=.Text
    End With
    Next c
    Application.ScreenUpdating = True
    End Sub

    ​Now is the question: How to set the VBA to copy the EmpName that associate with EmplID to cell A1 of each new sheet
    FOR EX.
    Master sheet
    Column A Column B
    100000 Name0
    100001 Name1
    100002 Name2

    (cell A1 in new created sheet “100000” should have “Name0”; new created sheet “100001”, cell A1 = Name1, etc…)

    Your prompt help will be greatly appreciated, Tom. Thank you
    Tina

    • Tom Urtis says:

      Hello Tina – –

      Without testing your code, just looking at it, perhaps after this line…
      ActiveSheet.Name = .Value
      …you could do this:
      Range(“A1”).Value = .Offset(0, 1).value

      Feel free to post back with a yay or nay if that helps.

  14. Tina says:

    Dear Tom,

    I got the error message:
    Run-time error “1004”:
    Method “Range” of object’_Global” failed

  15. Tina says:

    your code line. Thank you. Tina

    • Tom Urtis says:

      This should work. Something else is going on with your workbook. I actually told you about two code lines. One was the event code line for the Workbook level sheet change event, and the other code line being Range(“A1”).Value = .Offset(0, 1).value that would go between the lines
      ActiveSheet.Name = .Value
      and
      .Parent.Hyperlinks…

      Are you sure you placed those lines in the right places?

  16. Tina says:

    Hi Tom,

    Below is the code with your code line in between the lines you advised… Did not work. Still show the same error message and highlight your code line.
    (Thank you for taking your time with my issue. Greatly appreciated your help, Tom). Tina

    Sub CreateAndNameWorksheets()
    Dim c As Range

    Application.ScreenUpdating = False
    For Each c In Sheets(“Master”).Range(“A5:A50”)
    Sheets(“Template”).Copy After:=Sheets(Sheets.Count)
    With c
    ActiveSheet.Name = .Value
    Range(“A1”).Value = .Offset(0, 1).value
    .Parent.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:= _
    “‘” & .Text & “‘!A1”, TextToDisplay:=.Text
    End With
    Next c
    Application.ScreenUpdating = True
    End Sub

    • Tom Urtis says:

      This code, being a macro, belongs in a standard module. Assuming that is the case, and also there are no duplicate values in range A5:A50 of the Master sheet (because duplicate worksheet names are not allowed in the same workbook), then I cannot tell without seeing the workbook. So if there are no duplicates in B5:B50 and the macro exists in a standard module (not worksheet or workbook or class module) as it should, then something else is going on and you can send me the file at tom@atlaspm.com so I can take a look.

Leave a Reply

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

*