10

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?

Improve this question

8 Answers 8

Reset to default
10

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

Improve this answer
2
  • 1
    Tried this and got the error "Function INDIRECT parameter 1 value is '03-22-21!'. It is not a valid cell/range reference." How do you get the close quote before bang?
    – motorbaby
    Mar 28, 2021 at 19:06
  • Same problem as @motorbaby in comments. Fixed by quoting the cell reference like this =indirect(E26&"!"&"$AV$6")
    – Stew-au
    Jun 1 at 7:23
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
Improve this answer
1
  • Now I want to know why you're building a list of words that end in "stop." :)
    – Patrick
    May 13, 2022 at 2:36
0

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

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

enter image description here

Reference

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

Improve this answer
1
  • 1
    That file link is dead now
    – boardtc
    Mar 29, 2020 at 15:21
0

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!

Improve this answer
2
  • This worked for me after adding an additional column containing =TO_TEXT(B12) so that the formula would read a date format as a text string. But it only worked for one row. How can I get the formula of the inner INDIRECT to read it as cell so it can be copied?
    – motorbaby
    Mar 28, 2021 at 19:14
  • 1
    Figured it out: Took out the inner INDIRECT. So, have 3 columns: 1 with the date, 2nd with =TO_TEXT(B12) to translate the date into a string, and 3rd column gathering the data into one cell as =INDIRECT(B13&"!$B$7") . Thanks everyone.
    – motorbaby
    Mar 28, 2021 at 19:25
0

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.

Improve this answer
0

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

Improve this answer
-1

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

Improve this answer
1
  • If you are going to reference answers and comments you need to include the links. The issue Motorbaby referred to was tied to quoting ="B3" will not update when offset however =B3 will. (E26) and E26 are not interpreted differently therefore the inclusion of brackets () has no bearing.
    – Blindspots ♦
    Feb 14 at 18:27
-3

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

="Quick numbers from "&indirect("B1")&" projects"
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.