How can I use text from a cell as part of a formula?

Question

I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example:

I have a cell containing a date 17.02.2014. I need to use that date in a formula within another cell:

='17.02.2014'!$AV$6  

However this date needs to be pulled dynamically, for this example from E26. I would have expected it to look something like:

='&E26&'!$AV$6 

What is the correct syntax?

Answer 1

Please try:

 =indirect(E26&"!"&$AV$6)  

Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).

Answer 2

I just landed here with this issue and the solution was still giving errors. Example:

  A                B                      C
1| String        | INDIRECT()             | Result 
-+---------------+------------------------+-------------
2| fred          | =INDIRECT(A2)&"rick"   | #REF
3| fred          | =INDIRECT("A2")&"rick" | fredrick

So the instructions "must be and actual string" meant for me that the string is the string "A2" that represents the cell.

I hope it helps the next bloke or darling that comes upon this need.

Unfortunate thing about this is it don't scrape down, auto magically update the cells. So it's a pest.

Update: This works with dragging down on many cells

    A             B                        C
1| String  | INDIRECT()               | Result 
-+---------+--------------------------+-------------
2|non      | =INDIRECT("A2:A")&"stop" |nonstop
3|short    | =INDIRECT("A2:A")&"stop" |shortstop
4|pit      | =INDIRECT("A2:A")&"stop" |pitstop
5|door     | =INDIRECT("A2:A")&"stop" |doorstop
6|back     | =INDIRECT("A2:A")&"stop" |backstop

Answer 3

The INDIRECT function will only accept a range, as explained in the Drive Help:

The thing with functions like NOW() and TODAY() is, that they do just that !!

The way to go is by referencing indirectly, like shown in the screenshot (C5 vs. C2).

Screenshot

Reference

https://support.google.com/drive/answer/3093377

Answer 4

Sorry so late. But I got this to work for me in a similar matter:

=INDIRECT(INDIRECT("B13")&"!C2")

"translating" to your needs, I think

=INDIRECT(INDIRECT("E26")&"!$AV$6")

would do the trick!

Answer 5

For some reason, none of the solutions worked for me to consolidate Google Add-on Timesheet data from multiple sheets onto one sheet Then I tried this...

      A                B                      C
1| name of sheet | convert date to text  | value from sheet
-+---------------+-----------------------+------------------
2|     03-15-21  | =TO_TEXT($A2)         | =INDIRECT($B2&"!$B$7")
3|     03-22-21  | =TO_TEXT($A3)         | =INDIRECT($B3&"!$B$7")

Had to convert the date to text first. (Those using the Timesheet add-on, note that the value in column C points to the same cell on every sheet; the first cell containing combined time data is B7.)

Column C's formula gives null instead of !REF if the sheet is not yet renamed as the date or the sheet does not yet exist.

Answer 6

Question Distilled:

What is the correct syntax to dynamically use the value of a cell (containing a date) as the sheet name in a formula.

Conditions:

1. Cell values are dates (C1)
2. Formula is dynamic (C2)
3. Formula includes a non-dynamic static text string$AV$6 (C3) *

^{* This assumption is based on the reference that the OP seeks to build: '17.02.2014'!$AV$6}

Remarks:

• Questionner refers to 2 cells: E26 in the active sheet (date) and 17.02.2014!$AV$6 is the cell they are ultimately trying to reference;
• I have used a sheet named 17.02.2014 and also kept the same cell $AV$6 on that sheet;
• I have changed E26 (E26:E?) to D2 (D2:D10) (largely irrelevant);
• The value I have placed in 17.02.2014!$AV$6 is "17.02.2014!$AV$6". In other words, a successful reference would return that value. It is an arbitrary decision that I preferred to a string like Success or I am the destination cell etc. Just noting in case the clever among you wonder why INDIRECT("17.02.2014!$AV$6") = 17.02.2014!$AV$6
  

INDIRECT

INDIRECT is the right function for this question. The INDIRECT function returns a cell reference specified by a string.

Syntax:  =INDIRECT(cell_reference_as_string, [is_A1_notation])

Samples: =INDIRECT("Sheet2!"&B10)

         =INDIRECT("A2")

         =INDIRECT("R2C3", FALSE)

INDIRECT & Dates

All other things being equal, the INDIRECT formula for this question would be =INDIRECT(E26&"!$AV$6"), but that will not work with dates in addition to strings that look like dates depending on the locale.

INDIRECT's problem with dates

Why is there a problem:

Sheets stores dates as numbers representing the number of days that have elapsed since December 31, 1899. The date 17.02.2014 is stored as 41,687 and 17.02.2014 12:00 PM is stored as 41,687.5 (1/2 a day = 12 hours).

Where 'E26' is the date '17.02.2014',

  =INDIRECT(E26&"!$AV$6") 
  =41687!$AV$6
  =#REF!  //  It is not a valid cell/range reference.

A formula should address the original conditions and extending the formula to address some closely related use cases can make the formula less prone to failure.

Extended Conditions:

1. Cell values are dates (C1)
2. Formula is dynamic (C2)
3. Formula includes a non-dynamic static text string $AV$6 (C3)
4. Cell values might also be strings that appear to be dates (C4)
5. Date formats might not exactly match tab naming format (C5)

Basic Formula:

 =INDIRECT(TEXT(D2, "dd.mm.yyyy")&"!$AV$6")

+-----------------------------------------+
| To return an arbitrary value if formula |
| errors out, you can wrap it in IFERROR  |
+-----------------------------------------+

 =IFERROR(
    INDIRECT(TEXT(D2, "dd.mm.yyyy")&"!$AV$6"),
    "✗"
 )

Formula Notes:

1. Sheet Naming Format
   This drives the TEXT format you choose. If your sheets are named yy-mm-dd then you use TEXT(D2, "yy-mm-dd"). In this case the desired format was TEXT(D2, "dd.mm.yyyy")
2. Specific Date Format
   Using a TEXT format that matches the sheet name TEXT(D2, "dd.mm.yyyy") instead of simply using TO_TEXT eliminates another potential error path where a date is correct (underlying number) but the visual representation (date format) is different. For example, the dates 17.2.2014 and 17.02.2014 are identical (41,687 and 41,687) but the strings "17.2.2014" and "17.02.2014" are not equivalent. This approach means the formula will work for all dates regardless of their format.
3. Text Strings
   It is sometimes difficult to visually differentiate dates, and strings that look like dates. To make things even more confusing, Sheets will often coerce a string to a date when the string is similar to the default date format (locale settings):
   • ISNUMBER(date_string) will always return FALSE;
   • ISDATE(date_string) will return TRUE if date_string is similar to default date formats;
   • date_string ÷ 1 will return a number where ISDATE(date_string) = TRUE & ISNUMBER(date_string) = FALSE
4. Error Handling
   The IFERROR function in the formula allows a way to deal with strings that are not similar to the default date format. For example, if some dates are stored as text using one locale, then accessed using a different locale, the strings might just need a simple switch:

             string: "2.17.2014"
default date format: 'dd.mm.yyyy'

# Basic Formula (basic error text)

=IFERROR(
   INDIRECT(TEXT(string, "dd.mm.yyyy")&"!$AV$6"),
   "✗")
=✗


# Formula (advanced--but not exhaustive--error handling)

=IFERROR(
   INDIRECT(TEXT(string, "dd.mm.yyyy")&"!$AV$6"),
   INDIRECT(TEXT((LAMBDA(dt, 
      DATE(INDEX(dt,,3),
           INDEX(dt,,IF(INDEX(dt,,1)>12,2,1)),
           INDEX(dt,,IF(INDEX(dt,,1)>12,1,2))))
      (SPLIT(string,REGEXEXTRACT(string,"[^\d]")))),"dd.mm.yyyy")&"!$AV$6"))

=Cell Reference: '02.17.2014!$AV$6'

Testing Screens:

The screenshots below show a series of tests of various formulas against a list of dates and strings.

Heading	Options	Description
Format	"Text" or "Date"	Is the value stored as a string or a date
Like Default		Is the format of the string or date similar to the default format for the locale
÷1		Can the value be divided by 1 (i.e. is a date or a string that can be coerced to a date)
ISNUM		ISNUMBER function tests if the value is a number (date) or string
ISDATE		ISDATE function tests if the value is a date or a string that can be coerced to a date
Reference	17.02.2014!$AV$6 or	Formula success returns 17.02.2014!$AV$6 and failure returns

Basic Formula

Formula with advanced error handling

 

Older Answers to the Question

_{Answer from 2021-03}

_{Answer from 2020-12}

_{Top-rated answer from 2014-02}

• Answer 2017-02: Not an answer to the question.
• Answer 2017-12: Not an answer to the question.
• Answer 2014-02: A bit better than "not an answer" (has resources)

Answer 7

I was having the same issue that motorbaby was having, the suggested formula worked for just one row at a time, it wouldn't allow me to copy and paste to other rows, E26 remained even though I did not anchor it with "$". I tried the fix using the suggestion he made, but it didnt work for me. But this did:

=INDIRECT((E26)&"!$AV$6")

Answer 8

Code example for single cell (B1 containing a number):

="Quick numbers from "&indirect("B1")&" projects"