0

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

1
  • 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.
    – Tedinoz
    Aug 3 at 11:17

2 Answers 2

0

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

0

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.

enter image description here

enter image description here

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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