I've got a calendar in google sheets with each cell referencing a cell value pulled from weekly sheets. I'd like to be able to enter the cell reference for the first of the month, then have the rest of the dates autofill incrementally from that date. For example, first of the month would be referenced like
=('4/01/19'!A12), then the adjacent cell would point to '4/01/19'!B12, where values for the 2nd of the month have been entered. Since the sheet names will naturally vary week to week and month to month, I'm basically trying to do something like

a1 = 'sheet1'!a1
b1 = offset(the-cell-being-referenced-in-a1,0,1)

Is this possible?

Link to an example of the document: https://docs.google.com/spreadsheets/d/1aHLX8LcVpefi9mxHVeU_H6jB97RDhb4_eKD2h39FDzA/edit?usp=sharing

  • pls share a copy of your sheet
    – user0
    Commented Apr 13, 2019 at 21:56
  • Added a link to an example document.
    – Nick
    Commented Apr 13, 2019 at 22:04

1 Answer 1

  • paste in A2 cell:

    ={INDIRECT(TEXT(A1, "m/d/yy")&"!A12:G12")}


  • then select A2 cell press CTRL + C

  • select A8 cell and press CTRL + V and continue on A14, A20 etc.
  • 1
    That did it! That honestly did it better than I'd even hoped for. Thank you!
    – Nick
    Commented Apr 13, 2019 at 22:46

Your Answer

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

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