How to refer to data in the same file but different Google Sheet tabs
| |

How to refer to data in the same file but different Google Sheet tabs

I want to show you a neat way to link or use data from one spreadsheet to a different one within the same file. Now, this is really helpful especially when you are working on different spreadsheets and somehow they are related to each other. If there’s data in one sheet that you want to point to from another sheet, this is the way to do it.

So the question is, can you link data that you find on one spreadsheet and use it on another spreadsheet automatically? The answer is yes. Google Sheets has the ability to cross-reference data from one spreadsheet to another by simply using some short formulas designating where that particular data is coming from.

Rather than using copy and paste to transfer a duplicate copy of that data over to the next sheet, Google Sheets allows us to work on one sheet while at the same time “refer” to data from other sheets in the same file!

Want to learn how? Let me show you.

How to refer to data from another sheet tab within the same spreadsheet file

Let’s say you are working on a sheet (remember, both sheets have to be within the same file) that we’ll label “Target” because that’s what we’re working on and you want to link data that comes from another sheet we’ll label “SheetofData”:

  1. Highlight the cell in the Target sheet that you wish to show the value from the “SheetofData” sheet.
  2. Type in “=” (type in everything inside the quotation marks).
  3. Immediately following the = sign, type in the name of your sheet tab. In this case, our’s is named “SheetofData”
  4. Then type in “!”
  5. Finally, type in the cell location where that data is located in alphanumeric format. ie. A4.
  6. Press Enter.

Having issues? Let me explain a few must-knows below

There are a few things I didn’t know about until now and I believe these tips could really help you out. Here’s what I’ve learned.

What is the syntax or structure of the formula we’ve just created?

After selecting and highlighting the cell in the “Target” sheet, the equal sign “=” starts the formula. It tells Google Sheets that a formula is about to begin.

The moment you type in “=”, you’ll notice a shift in color or structure in your textbox. This means that now Google is going to expect some code or any value according to any formula or function you are about to type in. In this case, you’re in luck. The next thing you type will further tell Google what it is your trying to look for.

Type in the name of the sheet that has the data we are looking for. It’s the one we want to refer to. For me, I typed in “SheetofData” exactly. The name of the sheet absolutely must match what you write in the formula. In this case, we are looking for data in a sheet named “SheetofData.”

By typing in the name of another Sheet, it will automatically tell Google that you are wanting to refer to that particular sheet.

I want to show you later what to do in case you named your Sheet with 2 or more words.

Then, add the exclamation mark “!”

What is the exclamation mark “!” for?

The exclamation mark is generally known as a separator. It allows Google Sheets to know when it’s the end of a value or section of the formula and the beginning of another part of it.

In the examples above, an exclamation mark signifies that the name of the tab is completed and now, Google is expecting to get the cell location input from you next.

Exclamation marks are not at all specific to this one formula. It is used on a wide range of formulas depending on the need to selectively separate values and input.

What are the differences between tabs, sheets, files, and workspaces?

Let’s talk about terminology. When I say “tab”, I am referring to the spreadsheet that you can shuffle through located at the bottom of the sheet. These tabs are simply just multiple instances of different spreadsheets that are somewhat related to each other. They are related because they are all located within the same file. Oftentimes, Spreadsheet owners will create them within the same file or workspace.

Go ahead and look at the bottom of your spreadsheets file. You’ll notice that there’s a plus sign button. Clicking on that will create another spreadsheet. Clicking on it once again will create another. You can create as many as you want and shuffle through them. However, they are all in the same file.

Sheets tab below Google Sheets
Multiple sheet tabs below Google Sheets

This is like having a binder with several different divided sections in it. The file is the entire divider while the tabs are each a divided section of that binder.

Sometimes these tabs can also be known as sheets within a file or workspace. You’ll hear the term being used very loosely.

A file is basically a workspace that contains one or more sheets within it. How do you know for sure what a file is? A file is basically designated with a file type at the end of it. For Microsoft Excel, a file is anything ending with “.xls” or “.xlsx” or even “.xlsm” and “.xlsb”! Yeah, they got a ton. As for Google Sheets, you have “.gsheet”.

When you see any of these file type extensions, chances are, you are looking at the entire file itself and occasionally, they might have more than one sheet inside.

Having multiple tabs or sheets often is useful for people who have multiple sheets that are somewhat related in one way or another. Take for example a tab for income and a tab for expenses. You can decide to have both individual sheets located in the same file/workspace to keep things organized. And since both sheets refer to the same company, it would be much more advantageous sometimes to keep them both together.

Do not put spaces in formulas!

Every step above is a single, non-space line of text. Do not add any spaces in between any of the steps that require your input. It should look something exactly like this:

Google Sheets value referencing formula
Google Sheets value referencing formula example

Make sure you understand that almost all spreadsheet formulas do not work with spaces. Putting a space between any formula will often result in some type of syntax error that will turn red when you enter it.

HOWEVER, you must understand that there are exceptions to the no spaces rule.

What if the name of the sheets tab is 2 or more words?

Now I know that there often comes a time when the name of the tab is actually 2 different words with an intentional or necessary space in between. It can be more than just 2 words even. You can lay out an entire title and description for a particular tab if that’s what you really want.

I mentioned in the section above that there should not be any spaces. However, if you absolutely prefer to keep the name as separate words, you’d have to tell Google Sheets that they are together.

How do you tell Google that these 2 or more words are together as a single value in a formula?

In order to make Google Sheets “think” that the 2 or more words are really just one name is to simply place a single quotation mark before and after the full name of the tab. It’s that easy. Just surround the multi-word value with a pair of single quotation marks. In our case, if the word “SheetofData” were separated into multiple words like “Sheet of Data”, we would have to write it like so:

Using single quotations to make multi-words a single value in Google Sheets
Using single quotations to turn multi-words into a single value in Google Sheets

Notice that it doesn’t matter how many words I’ve named my Sheets. As long as it is surrounded by single quotation marks, then Google Sheets will automatically be able to find it and accept it as being part of the value for its reference formula.

One thing I want to make clear is that I suppose if you didn’t want to think about it too much. You can start training yourself to automatically place the names in between single quotes whether its two or more words or just one word. Yes, you can put single quotes around a single word as well. It makes no difference.

In fact, I would recommend just automatically surrounding every single value no matter how many words it has with single quotes. It’ll make it easier for you if you get in the groove with this consistency.

You’ll thank me in the long run.

Reference formula is not working? Check the spelling

I had spent a few extra minutes at one point learning about this troubleshooting the formula. I figure if I ran into this problem, then perhaps you might too.

I was typing in the formula starting with the = sign and then onto the name of the other sheet tab with the data I wanted to extract and use. For some reason, I got an error message the minute I completed the formula.

The problem turned out to be me misspelling the name of the sheet. I’m a clutz and I might be the only one with this issue. But I can’t stress how important it is to make sure you spell out the name exactly as it shows on that sheet.

Also make absolute sure that the sheet even exists there in the tabs list. I know this might sound pretty simple and even fundamental.

Conclusion

Imagine all the possibilities when you know and understand something like this. You can do so, so much more. I know this might seem pretty basic but it opens up a room full of possibilities for more complicated projects in the near future.

I would argue that this is one of the fundamentals of being proficient with Google Sheets, or any spreadsheet program for that matter.

I’ll keep writing about articles that I think might be of value to learn or even know about for Google Sheets. We’ll continue to build upon this foundation and work our way into some kind of amazing Google Sheets program in the near future.

Other interesting articles: