Excel: How To Reference Cells in Other Worksheets
Contributed by Rob Rogers 
Tagged: Microsoft Excel 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
You may also like -
Excel: How To Hide Formulas from Other Users
Excel 2007: Use Descriptions in Complex Formulas
How To Copy Excel Column Widths
Using Autosum with Microsoft Excel
Excel: Calculating Elapsed Time
How To Email an Excel Workbook
Excel 2007: How to View Twitter Updates (Tweets) in a Worksheet
Excel: How To Create a Conditional Format
garrison said on November 5, 2008
how to reference the name of worksheet as a valuable, ie. from the content of another cell, thx.?
joe said on April 13, 2009
thank you u saved my life
Michael said on October 22, 2009
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 said on February 19, 2010
Thank you!
Anonymous said on February 24, 2010
What is Sheet Referencing?
Anonymous said on May 12, 2010
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
ida said on May 21, 2010
what can I use to view worksheet from 2007 in worksheet 2003
James said on May 30, 2010
how to set formulas for reference cells.
Jinlsui said on August 13, 2010
you need ‘ ‘ forthe sheet name text
Pa'a said on August 20, 2010
thank you for a direct and straight-forward solution to my problem! :)
Ruben said on September 20, 2010
How to reference a whole range? I have tried like =SheetName!A1:A50 but it is not working
Stephan Atanasov said on November 11, 2010
Thanks for the tip! For me it worked just fine in Office Mobile.
Techbelgaum said on November 13, 2010
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.
Anya0306 said on December 8, 2010
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 said on January 21, 2011
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 said on February 8, 2011
Have 2 worksheets in one workbook. Want data from cell in sheet 1 to populate cell in sheet 2. how is that done
Bill said on February 21, 2011
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
John said on February 21, 2011
you need to say:
=’Sheet Name’!Q28
or whatever it is. don’t forget the ‘ ‘
Jay_M said on March 11, 2011
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 said on March 15, 2011
That’s what I’d like to know as well.
jwh said on March 15, 2011
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
Amanda said on March 16, 2011
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 said on April 16, 2011
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 said on April 17, 2011
How do I create a formula to populate cells in a column to link to a related source cell in a another worksheet?
Elle said on September 23, 2011
Thank you! The Excel support website was not half as helpful as this article!
Sandra Santos said on September 27, 2011
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 said on December 1, 2011
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?