Excel: How To Reference Cells in Other Worksheets

For an Excel formula that needs to reference a cell in a different worksheet in the same workbook, use the following format for your formula.


SheetName!CellAddress (Precede the cell address with the worksheet name, and follow it with an exclamation point).

Note: For worksheet names that include one or more spaces, you will need to enclose it in single quotation marks. The following is a formula that refers to a cell on a sheet by the name of Monthly Sales:

=B4*’Monthly Sales’!A12

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

57 Responses to “Excel: How To Reference Cells in Other Worksheets”

  1. November 05, 2008 at 2:46 pm, garrison said:

    how to reference the name of worksheet as a valuable, ie. from the content of another cell, thx.?

    Reply

    • November 23, 2013 at 5:10 pm, michael said:

      hello,

      I am trying (many variants of this) and just cannot get it to work.

      Am trying to reference the data in cell D1 of the Home Page worksheet
      which is the drop down list of worksheets in the document.

      =HYPERLINK(‘$D$1′!A1, goto)

      If indeed I manually insert for example =HYPERLINK(‘Inventory’!A1, goto)
      this works. But I need to insert the data which has been (previously) selected in the D1 drop down list.

      Thank you very much.

      michael

      Reply

      • December 25, 2013 at 3:01 pm, Luke said:

        Use INDIRECT to encapsulate the reference to the cell:

        HYPERLINK(INDIRECT(‘$D$1′!A1), goto)
        >

        Reply

  2. April 13, 2009 at 10:42 am, joe said:

    thank you u saved my life

    Reply

  3. October 22, 2009 at 2:02 pm, Michael said:

    can the worksheet be referenced other than by name? i’d like to get data from one sheet to the left, regardless of its name. i’m using =INDIRECT(ADDRESS… etc to enter a value in a cell, and a macro to name the worksheets based on that cell. it all works fine, but when i copy the sheet data to a new sheet, i have to replace the worksheet name in the cell formula, which kind of defeats the purpose of all the coding. thanks!

    Reply

  4. February 19, 2010 at 9:01 pm, Patrick said:

    Thank you!

    Reply

  5. February 24, 2010 at 4:19 pm, Anonymous said:

    What is Sheet Referencing?

    Reply

  6. May 12, 2010 at 1:22 am, Anonymous said:

    What if the cell contains a name and you want to have that coming back in different sheets? So you fill out the name in a cell on sheet1 and with a formula in the other sheets it will appear there as well.
    I have tried =sheet1!A1 but it won’t work.
    Can someone help me please? Thanks

    Reply

    • August 13, 2010 at 7:54 pm, Jinlsui said:

      you need ‘ ‘ forthe sheet name text

      Reply

    • February 21, 2011 at 6:32 pm, John said:

      you need to say:

      =’Sheet Name’!Q28

      or whatever it is. don’t forget the ‘ ‘

      Reply

  7. May 21, 2010 at 7:51 pm, ida said:

    what can I use to view worksheet from 2007 in worksheet 2003

    Reply

  8. May 30, 2010 at 3:24 pm, James said:

    how to set formulas for reference cells.

    Reply

  9. August 20, 2010 at 4:25 am, Pa'a said:

    thank you for a direct and straight-forward solution to my problem! :)

    Reply

  10. September 20, 2010 at 8:59 am, Ruben said:

    How to reference a whole range? I have tried like =SheetName!A1:A50 but it is not working

    Reply

    • October 02, 2012 at 5:17 pm, Sunny said:

      > Select and copy what you want to reference, then highlight where you want it referenced. Right click, paste special, paste link.

      Reply

  11. November 11, 2010 at 4:29 pm, Stephan Atanasov said:

    Thanks for the tip! For me it worked just fine in Office Mobile.

    Reply

  12. November 13, 2010 at 6:30 am, Techbelgaum said:

    if i calculate 5.65*SQRT(68.00) i am getting result as 46.59. after rounding up 46.59 the round up value will be 47. i want to calculate 64.3- 47. but the formula is taking as 64.3-46.59. it should take as 47. pls help me in this.

    Reply

    • January 01, 2013 at 8:59 pm, makasi said:

      thanks! it worked. you saved me!

      Reply

  13. December 08, 2010 at 5:49 pm, Anya0306 said:

    Techbelgaum-

    Use this formula: =64.3-ROUND(46.59,0)
    The second argument for the ROUND function tells how many digits to round it to (in this case, 0 tells to round it to the nearest integer).

    Reply

  14. January 21, 2011 at 7:28 pm, Ryan_mireles said:

    I need to use a cell reference from one sheet to multiple sheets inside my Worksheet.. How is this accomplished?
    I set up my first Sheet as TOTALS, and the rest of the tabs are Totals from other other data… I want my first sheet to Total all the Totals…

    Reply

  15. February 08, 2011 at 5:13 pm, Covertcop said:

    Have 2 worksheets in one workbook. Want data from cell in sheet 1 to populate cell in sheet 2. how is that done

    Reply

  16. February 21, 2011 at 1:27 am, Bill said:

    I have 2 sheets
    a. is a typical list – with Work Order as the Key in column 1
    b. is a form which we need to be populated from one single row in (a). Ideally the row we are last positioned on when we looked at (a)

    We are trying to give the user a nice easy way to print on one page a single work order form.

    bill@myzan.com

    Reply

    • June 25, 2012 at 11:25 am, Tshewangnovu said:

      > copy cell referece from anther sheet by after multiple rows for subsequent referencing

      Reply

  17. March 11, 2011 at 9:20 pm, Jay_M said:

    I used the formula for referencing cells and it worked, but if the cells are blank it returns a zero, how do I get it to leave the cell blank if the referenced cell in the other sheet is blank?

    Reply

    • March 15, 2011 at 8:31 pm, Jminus said:

      That’s what I’d like to know as well.

      Reply

    • March 15, 2011 at 10:13 pm, jwh said:

      Put this in the cell you want the data to display

      =IF(A1=”",”",A1)

      This is stating if cell A1 is empty, return empty, otherwise return whatever the value in A1 is. You can reference cells in other sheets

      Reply

      • June 06, 2012 at 3:08 pm, Bobby "Rob" Crabtrey said:

        This works fine when referencing cells in the same worksheet but not when referencing a different worksheet in the workbook. I’ve tried several different functions [ISBLANK(), ISTEXT()], etc. to no avail. I’ve ensured that both columns are in the Text format but still get a “0″ returned to the calling cell when the called cell in blank.

        I have 2 worksheets in this workbook. One Named “By Due Date” and the other is “By Work Order” obviously named according to their sort order. I had them both sorted by Order before applying the formula and ensured they contain the exact number of rows and columns. Here’s the formula currently in the cells of the calling worksheet: =’By Work Order’!I148

        This is the formula I enter in the worksheet named “By Due Date” after reading your post: =IF(‘By Work Order’!C148=”",”",’By Work Order’!C148) and now it displays the formula instead of the value referenced in the called sheet.

        I know it has something to do with the format of the cell reference but I can’t figure out exact what’s missing. Any ideas?

        Reply

      • June 14, 2012 at 4:47 pm, Jim said:

        > =TRIM(’By Work Order’!I148) will convert result to text and the result will be empty if source was empty.

        Reply

  18. March 16, 2011 at 10:29 pm, Amanda said:

    Can you help – I had a spreadsheet which i then copied into a new file – and from this copied data it links into new worksheet within the file. Now I have ref coming up in one of the worksheets as it still thinks its pointing to the old (pre copy) spreadsheet – how can i make it point to the data on the worksheet in this spreadsheet?? Driving me Insane !

    Reply

  19. April 16, 2011 at 11:13 pm, Kevin Harris said:

    I am familiar with the use of links to other worksheets and workbooks. However, today I wanted to link to a cell that contained text information, and I could not make it work. (I got a zero; the source cell contents were apparently being interpreted as a number, even though the cell has text format specified for it.)

    I obviously cannot make the receiving cell text format, because the =’worksheet’!cell would then be interpreted as text.

    I tried to see if I could use the INDIRECT function, but without success.

    Any suggestions would be much appreciated! (I would prefer not to have to resort to macros; I am looking for a simple solution that I can use in the future as well.)

    Thanks

    Reply

  20. April 17, 2011 at 5:04 am, Andy2012 said:

    How do I create a formula to populate cells in a column to link to a related source cell in a another worksheet?

    Reply

  21. September 23, 2011 at 10:49 am, Elle said:

    Thank you! The Excel support website was not half as helpful as this article!

    Reply

  22. September 27, 2011 at 6:20 am, Sandra Santos said:

    Hi! Maybe you can help me!
    I need to link one cell value for two different cells, in different worksheets. For example: worksheet 1, cell C3, i insert may name, automatically, i want to change the text in worksheet 2, cell D4 and worksheet 3, cell B2. And the same must happen if i change any other value in any other cell. If i change the cell D4, worksheet 2, it must automatically change worksheet 3, cell B2 and worksheet 1, cell C3.
    I am trying to create a VBA code, but it`s impossible to get what i want!

    Tks!

    Reply

  23. December 01, 2011 at 4:57 am, Paul Wolodkin said:

    I want to reference a range of cells to another worksheet within the workbook.

    Example: Sheet 1, Name, Birthday, Age (Ref to) Sheet 2 (with same column headings)

    Sheet 1 is Titled NINNOWS 9-16 Mos. and Sheet 2 is titled VOLUNTEERLIST

    How do I do this in Excel 2010?

    Reply

  24. March 05, 2012 at 12:15 am, Kim Braun said:

    I have 2 sheets populated with dates and text with the contents of sheet 1 referencing to sheet 2. When I go to sort sheet 1, the referenced cells do not “follow” the sort. My data is then in the wrong cells. Is there a way to sort data on a sheet that has data referenced from another sheet?
    Help!

    Reply

  25. May 14, 2012 at 3:16 pm, Lennart Ohlsson said:

    I would like to add the value, say 10, in one cell in workbook A and the value, say 20, in one cell in workbook B to get the value 30 in one cell in workbook C.
    In Excel 97-2003 workbook I did this by entering a plus-sign in the cell in workbook C, go to the cell in workbook A and klick on the cell and enter a plus-sign , then go to workbook B and klick on the cell there
    and Enter. In the cell in workbook C (a xls-file) I get the formula:
    =+’[Test A.xls]Sheet1′!$A$1+’[Test B.xls]Sheet1′!$A$1.
    How to do the similar in Micro Soft Excel Workbook (a xlsx-file)?

    Reply

  26. June 20, 2012 at 6:42 pm, Cherise said:

    I am learning to reference sheets. My system has been converted from 2003 to 2007. I am following the 2003 instructions and I am unable to reference my sales income sheet 4 to sheets 1-3 in the same workbook. The instructions say use this formula while I am in sheet 4: =sheet1!b2+sheet2!b2+sheet3!b2 and press enter. When I follow these simple instructions, a Update Values window appears and my sell reads #REF! Help! What is going on?

    Reply

    • June 22, 2012 at 5:35 pm, ace said:

      if you’ve renamed the sheet then “sheet1″ should be the name of your sheet.
      help?

      Reply

  27. July 11, 2012 at 5:53 am, Apurva said:

    hey thanx alot……dis helped me alot….

    Reply

  28. July 18, 2012 at 8:00 am, saleem said:

    Please find below table

    B C D

    Part Price A Price B
    Bold 10 60
    Screw 15 70
    Scale 5 80
    Tape 20 90
    Meter 30 100

    When i type the Part name in a cell, (e.g. Screw) by VLOOKUP formula i get the value.
    =VLOOKUP(E4,B4:D8,3,FALSE)
    But I want find out by putting value (e.g. 80) i should get corrseponding Text. How to do this, please help me.
    Thanks

    Reply

  29. September 21, 2012 at 2:29 am, David said:

    My need is variable access to another sheet.
    ie: =B4*’Monthly Sales’! where a and b are vaiables based on other factors

    Reply

  30. September 26, 2012 at 12:55 pm, mags said:

    I have a workbook with a lot of different sheets. All the sheets are the same format but different info in each cell. All the sheets are labeled by m-d (1-3,1-4,1-5,etc. minus weekends and holidays). I created a different sheet to aggregate information from the same cells from each sheet. For example I want cells B8,B9,B10 from every sheet. I can not figure out a formula that easily pulls all this information into the aggregate sheet (Master Sheet). Please let me now if you can help.

    Reply

  31. October 13, 2012 at 12:18 am, sasa said:

    i have many sheet in a workbook. For example the sheet labeled by date 0101, 0102, 0103….etc
    Then i have a summary part on a sheet. May i is there any shortcut or formula that i can easy to have a quick look by clicking on the 0101 from summary sheet then street away is goes to the 0101 sheet that i looking for?

    Reply

  32. October 13, 2012 at 12:36 am, sasa said:

    I have many sheet in a workbook, all the sheet are labeled by 0101,0102,0103 etc…(date). i create a summary in the same workbook as well, is the any formula or shortcut key can i use to just a click of 0101 on the summary sheet then it direct to the specific sheet 0101 i refer to? Please help, thank you!

    Reply

  33. October 16, 2012 at 5:59 am, G-man said:

    I have a multiple sheet workbook.

    I’m struggling with a formula to calculate the total in the summary page of all the even cells on another page.

    Reply

  34. January 04, 2013 at 7:12 pm, Amigotoe said:

    To copy the text in a cell from one tabbed page to another in Excel 2007 (only if you are getting a 0 instead), Try using =T(Sheet2!A2). Change Sheet2 and A2 as necessary.

    Reply

  35. February 08, 2013 at 11:52 am, Jack Matskiw said:

    Is there a way to reference a particular cell to a different program? eg: cell B4 referenced to Pictures?

    Reply

  36. February 22, 2013 at 1:41 am, Dude said:

    I figured out the problem.

    It’s not:
    =’SheetName’!A1

    and it’s not:
    =SheetName!A1

    What works is:
    =$SheetName.$A$1

    Reply

  37. March 24, 2013 at 5:59 am, jim said:

    Great sharing! you are excel expert !

    Reply

  38. May 28, 2013 at 6:39 am, JOSH said:

    I have done this over and over but it gives me the #REF! error i have made sure everything is in its place but it still wont work. The major thing is last week it was fine.

    Reply

  39. July 10, 2013 at 10:24 am, Anika said:

    Awesome! Thank you!! This answer is very straight-forward and is exactly what I was looking for.

    Reply

  40. August 22, 2013 at 2:24 pm, Some noobs... said:

    Hi, Can i reference a cell in another worksheet like this?

    Example:
    Worksheet A
    A1 Data1
    A2
    A3 Data2
    A4
    A5 Data3

    Worksheet B (I want to copy by referencing the cells in worksheet A)
    B1 ‘Worksheet A’!A1
    B2 ‘Worksheet A’!(A1+2) ~ meaning i want to copy cell ‘Worksheet A’!A3
    B3 ‘Worksheet A’!(A3+2) ~ meaning i want to copy cell ‘Worksheet A’!A5… note that A3 in this formula comes from the formula in B2 by skipping 1 row in Worksheet A.
    B4 ‘Worksheet A’!(A5+2)

    The reason for this is so that i can skip the A2 and A4 cells and be able to drag the formula in Worksheet B to extend it without the need to point manually each cell that i want to copy from Worksheet A.

    Reply

  41. August 26, 2013 at 11:09 am, rajesh said:

    I am not able to do the vlookup, message i am getting #N/A, i have similar format on both the sheets

    Reply

  42. September 30, 2013 at 2:53 pm, aucarter said:

    Thanks for the info. I needed a quick answer and you answered it.
    By the way, I noticed the Sheet would not reference properly if the Sheet name was a number.
    But after adding a letter in front, it worked well.

    Reply

  43. December 30, 2013 at 1:01 pm, sai said:

    good morning sir from sai i have interest excel work but i have no materiel place i sand my excel formulas book

    Reply

  44. February 10, 2014 at 5:34 am, SHAALAN ALI said:

    it worked thanks for this help but just one last thing
    if i want to keep it updated how should i do it?
    meaning that once i enter data in 1st sheet it directly add to the last sum on the 2nd sheet.

    thank you

    Reply

  45. March 17, 2014 at 5:44 am, elia Lomitu said:

    Is it possible to link formula between one sheet and another? how?

    Reply

Leave a Reply