Excel: How To Reference Cells in Other Worksheets
Posted by Rob Rogers in 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
About Rob Rogers
Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers
View more articles by Rob Rogers
The Conversation
Follow the reactions below and share your own thoughts.
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.?
April 13, 2009 at 10:42 am, joe said:
thank you u saved my life
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!
February 19, 2010 at 9:01 pm, Patrick said:
Thank you!
February 24, 2010 at 4:19 pm, Anonymous said:
What is Sheet Referencing?
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
August 13, 2010 at 7:54 pm, Jinlsui said:
you need ‘ ‘ forthe sheet name text
February 21, 2011 at 6:32 pm, John said:
you need to say:
=’Sheet Name’!Q28
or whatever it is. don’t forget the ‘ ‘
May 21, 2010 at 7:51 pm, ida said:
what can I use to view worksheet from 2007 in worksheet 2003
May 30, 2010 at 3:24 pm, James said:
how to set formulas for reference cells.
August 20, 2010 at 4:25 am, Pa'a said:
thank you for a direct and straight-forward solution to my problem!
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
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.
November 11, 2010 at 4:29 pm, Stephan Atanasov said:
Thanks for the tip! For me it worked just fine in Office Mobile.
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.
January 01, 2013 at 8:59 pm, makasi said:
thanks! it worked. you saved me!
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).
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…
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
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
June 25, 2012 at 11:25 am, Tshewangnovu said:
> copy cell referece from anther sheet by after multiple rows for subsequent referencing
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?
March 15, 2011 at 8:31 pm, Jminus said:
That’s what I’d like to know as well.
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
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?
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.
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 !
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
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?
September 23, 2011 at 10:49 am, Elle said:
Thank you! The Excel support website was not half as helpful as this article!
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!
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?
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!
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)?
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?
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?
July 11, 2012 at 5:53 am, Apurva said:
hey thanx alot……dis helped me alot….
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
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
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.
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!
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.
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.
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?
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
March 24, 2013 at 5:59 am, jim said:
Great sharing! you are excel expert !