atlas

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.

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

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

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