Cell Reference From String in Google Sheets

Today, we will look at how to get cell reference from a string in Google Sheets. Our main methods revolve around the INDIRECT function. We have also added a simple alternative for getting a string from a cell reference later in this article if that is what you were looking for.

Let’s get started.

How to Get Cell Reference From String in Google Sheets

1. Using INDIRECT Function to Reference Cell from a String

The INDIRECT function is the perfect creation to pull cell references from a string in Google Sheets or any other spreadsheet application.

The syntax:

• cell_reference_as_string: The references of the cell or cells are inputted as a string. Any combination of columns and rows can be used.
• [is_A1_notation]: Optional. By default, it is TRUE. Indicates if the reference is in A1 form (COLUMN-ROW) or R1C1 form (ROW-COLUMN).

In the following dataset, we have a column of values and a string. While it denotes a cell reference, B5, it is still a string (left-aligned).

Now, let’s see what happens when we put this string in the INDIRECT function.

The INDIRECT function takes the strong value in D3 and references cell B5 (which was the string value). Let’s try with other string cell references:

For our last change seen in the image above, we did a double string reference to cells. Therein lies the magic of the INDIRECT function.

With the basics out of the way, let’s look at some examples where the INDIRECT function truly shines.

Examples with INDIRECT

I. Reference Another Worksheet

We have created a few more worksheets to present our next example. In the Expenses worksheet, we have a few cells that will take string inputs from the user.

The Month worksheets include January, February, March, and April. They all have similar layouts.

Our task is to find the total expenses by month. For example, the total expense for January can be found in cell C7. So, we have to reference that in the Expenses worksheet.

• Sheet Name: January
• Cell Number: C7
• Formula: INDIRECT(B3&"!"&B6)

We can also add a range of cells as a string, it will still work.

• Sheet Name: January
• Cell Range: C3:C5
• Formula: =SUM(INDIRECT(B3&"!"&B9))

For our final touches, let’s automate the Sheet Name with a drop-down menu.

Read More: Reference Another Sheet in Google Sheets (4 Easy Ways)

II. Using Named Ranges

Another way to reference cell ranges is by using the Named Range option of Google Sheets.

As you can see, we already have a few Named ranges in this workbook. These are essentially the cell range of C3:C5 for all Month worksheets.

Now if we input the Named range as a string and apply the formula:

We break down the capabilities of this function in more detail in our Dynamic Cell Reference in Google Sheet article. We welcome you to read it.

Read More: Indirect Sheet Name in Google Sheets (Easy Steps)

Similar Readings

• Find Cell Reference in Google Sheets (2 Ways)
• Reference Another Tab in Google Sheets (2 Examples)

2. Cell Reference to a String using Ampersand (&)

Now, You might be looking for a way to combine strings with cell references instead. For that, we have a much simpler remedy, and it involves using the wildcard ampersand (&).

What ampersand does is it concatenates two or more sections of values. These values can be cell references, cell ranges, or even strings.

Let’s keep it simple and show the example in the same worksheet. However, we have added some modifications to it. The idea is to compare the expenses of two months and give an output stating which month had higher expenses.

We use a simple IF formula for our output.

Using ampersand (&), we have combined string from a cell reference and a regular string.

Read More: Relative Cell Reference in Google Sheets

Final Words

We hope that the methods we discussed to reference cells from a string in Google Sheets come in handy in your daily spreadsheet tasks. While the INDIRECT function seems complicated initially, it can be a great tool when organizing large sets of data.

Feel free to leave any queries or advice you might have for us in the comments section.

Related Articles

• Pull Data From Another Sheet Based on Criteria in Google Sheets (3 Ways)
• Reference Another Workbook in Google Sheets (Step-by-Step)
• Google Sheets: Use Cell Value in a Formula (2 Ways)
• Lock Cell Reference in Google Sheets (3 Ways)
• How to Query Cell Reference in Google Sheets
• Return Cell Reference in Google Sheets (4 Easy Ways)