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.

  • garrison

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

    • michael

      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

      • Luke

        Use INDIRECT to encapsulate the reference to the cell:

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

  • joe

    thank you u saved my life

  • Michael

    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!

  • Patrick

    Thank you!

  • Anonymous

    What is Sheet Referencing?

  • Anonymous

    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

    • Jinlsui

      you need ‘ ‘ forthe sheet name text

    • John

      you need to say:

      =’Sheet Name’!Q28

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

  • ida

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

  • James

    how to set formulas for reference cells.

  • Pa’a

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

  • Ruben

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

    • Sunny

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

  • Stephan Atanasov

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

  • Techbelgaum

    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.

    • makasi

      thanks! it worked. you saved me!

  • Anya0306

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

  • Ryan_mireles

    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…

  • Covertcop

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

  • Bill

    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.

    [email protected]

    • Tshewangnovu

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

  • Jay_M

    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?

    • Jminus

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

    • jwh

      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

      • http://www.c-a-m.com Bobby “Rob” Crabtrey

        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?

      • Jim

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

  • http://pulse.yahoo.com/_F4Q4P47DCH7P4Z2WZFNI4NXQC4 Amanda

    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 !

  • Kevin Harris

    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

  • Andy2012

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

  • Elle

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

  • Sandra Santos

    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!

  • Paul Wolodkin

    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?

  • Kim Braun

    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!

  • Lennart Ohlsson

    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)?

  • Cherise

    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?

    • ace

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

  • Apurva

    hey thanx alot……dis helped me alot….

  • saleem

    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

  • David

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

  • mags

    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.

  • sasa

    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?

  • sasa

    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!

  • G-man

    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.

  • Amigotoe

    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.

  • Jack Matskiw

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

  • Dude

    I figured out the problem.

    It’s not:
    =’SheetName’!A1

    and it’s not:
    =SheetName!A1

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

  • jim

    Great sharing! you are excel expert !

  • JOSH

    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.

  • Anika

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

  • Some noobs…

    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.

  • rajesh

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

  • aucarter

    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.

  • sai

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

  • SHAALAN ALI

    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

  • elia Lomitu

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