# Cascading a date & absolute value that references another dated sheet

I'd like to be able to click and drag to auto-populate the cells instead of having to F2 each cell and have to change each date. The D4 cell looks like ='8/1'!\$V\$49, I'd love to be able to click and drag so it becomes ='8/2'!\$V\$49, ='8/3'!\$V\$49...and so on. Please and thank you!!!

• Hi, welcome to WebApps. Your spreadsheet has been "published" as html which means that the data and formula can't be identified, and volunteers can't make a copy of the spreadsheet for testing. Would you please share a copy of your spreadsheet to anyone with the url. You might care to share a as a "viewer" only so that you original sheet isn't accidentally changed. Aug 3 at 11:17

Try this in cell D4:

`=indirect(address(2,5,4,true,"8/"&row()-3))`

• `INDIRECT` returns the value of the cell specified by the address
• `ADDRESS` returns a cell reference
• the 5th (and optional) parameter of `ADDRESS` is a sheet name
• `"8/"&row()-3)` - recognises a sheet name that begins with the characters "8/" and is followed by a number. The number is created by the row of the current cell, 4, (cell is D4) less 3 = 1. So the sheet name = "8/1"
• drag that down and the row number will increment by one, so the sheet name will progressively become "8/2", "8/3", etc.

Documentation

## Indirect & Concatenate

Assumptions:

1. 'sheetName' is in column 'A', and that does not change (\$A).
2. Each row has a different 'sheetName'. I will use \$A[i] where 'i' refers to the applicable row.
3. 'sheetNames' are in the format 'M/D'
4. Goal is a concatenation of the 'sheetName' (\$A[i]) & 'cellRef' (\$V\$49)
5. Formula will updated as it is pasted or dragged across multiple rows.

The Gotcha
The fly in the ointment is the data in column 'A' which isn't text but is actually a big number (dateTime) displayed as a short text string (M/D).

``````  Where you see     8/1  |     8/2  |     8/3

Formula sees   44,774  |  44,775  |  44,776
``````

So, that number needs to be converted to actual text before concatenating.

``````  Basic conversion formula    =TEXT(value,"format")
So in your case             =TEXT(\$A[i], "M/D")

EXAMPLE
-------
=TEXT(\$A[i], "M/D")    //   \$A[i]  =  8/1
=TEXT(44774, "M/D")    //    8/1   =  44,474
="8/1"                 //   "8/1" !=  44,474

``````

Now that we have the 'sheetName' we can concatenate it with the 'cellRef'

``````  Concatenation formula

= quote (') & 'sheetName' &  quote (') exclMark (!) & 'cellRef'

= "'" &TEXT(\$A[i],"M/D") &"'!" &\$V\$49
OR
= "'" &TEXT(\$A[i],"M/D") &"'!\$V\$49"   // since 'cellRef' never changes

= "'"&TEXT(44774,"M/D")&"'!\$V\$49"    //  \$A[i] = 8/1 (Aug 1, 2022)
= "'"&"8/1"&"'!\$V\$49"
= "'8/1'!\$V\$49"
``````

All that remains is to wrap it in an INDIRECT formula.

``````Indirect Formula   = INDIRECT(textString)     // A1 Notation (default/unspecified)
So in your case    = INDIRECT(ConcatFormula)
``````

Assumptions

1. Cell '\$V\$49' on sheet '8/1' equals "This is 8/1"
2. And '\$A[i]' equals the date '8/1'
``````                   = INDIRECT("'" &TEXT(\$A[i],"M/D") &"'!\$V\$49")
= INDIRECT("'8/1'!\$V\$49")
= "This is 8/1"
``````

NB
If you do not a 'sheetName' in '\$A[i]' without a sheet with that name. Your formula will generate an error.    