Cascading a date & absolute value that references another dated sheet

Question

https://docs.google.com/spreadsheets/d/e/2PACX-1vTyyem2spUQAi44lPN3rk_AQiE1J-OS2nQtJbASYadyXZ8WmjHR-yNjY1fH2jRwttyUiOPtf6uL3xBs/pubhtml

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

Answer 1

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
Address
Row

Answer 2

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.