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:
- Cell values are dates (C1)
- Formula is dynamic (C2)
- 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:
- Cell values are dates (C1)
- Formula is dynamic (C2)
- Formula includes a non-dynamic static text string
$AV$6
(C3)
- Cell values might also be strings that appear to be dates (C4)
- 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:
- 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")
- 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.
- 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
- 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
