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:

INDIRECT(cell_reference_as_string, [is_A1_notation])

INDIRECT syntax for cell reference from string in google sheets

  • 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).

base dataset

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

=INDIRECT(D3)

cell reference from string in google sheets using 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:

cell reference from string in google sheets using INDIRECT animated

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.

double string reference using INDIRECT

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.

multiple worksheet used to fully utilize INDIRECT function

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

month worksheet - January

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)

sheet and cell reference from string in google sheets

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))

using cell range from string in google sheets with SUM and INDIRECT function

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

using drop-down to cycle months

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.

all the named ranges in the month worksheets

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

=SUM(INDIRECT(B3&"!"&B12))

using named ranges to reference cell range

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


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.

dataset for method 2

We use a simple IF formula for our output.

=IF(D3>H3,B3&" has had more expenses.", F3&" has had more expenses.")

using cell reference for a string and concatenating with another string

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo