Random Musings

How to reference a cell in another sheet in Google Spreadsheets

I've finally started using multiple sheets in Google spreadsheets. I found I was creating multiple spreadsheets, for tracking different items, but all containing the same tracking columns. So, I put them into one spreadsheet, and created one master sheet to track running totals compiled from each sheet.

So, if you need to do this, and want to reference a value in another sheet, it's actually quite simple. You just need the name of the sheet, and the cell location. So, if the sheet was named "Sheet2" (creative, huh?!), and the cell was C3, you could reference the value from any sheet with this formula:

=Sheet2!C3

However, I ran into one error, when I had an ampersand ("&") character in my sheet name. It gave a 'circular reference detected' error. I guess it thought the "&" was part of the equation. So just use alphanumeric characters when naming your sheets, and you should be fine.

Share

7 Comments

  1. If you use special chars, then use single quotes to wrap the names. For example, if your Sheet is called Alpha&Bravo, reference it as 'Alpha&Bravo'!C3

  2. It doesn't seem to work without single-quoting the sheet name:
    ='Sheet1'!A1
    ='Name of my sheet'!C99

  3. If I need to reference another spreadsheet? how that it would be?

  4. Can I refer to a value in another spreadsheet document?

  5. Thanks, this was a really helpful tip.

  6. Here is a question, how to access a google spreadsheet cell value to determine which graphic to load into an html spreadsheet using javascript?

  7. Perfection, google should give you a stock share, seriously…

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image