Google Sheets – Reference Adjacent Cell on Another Sheet

formulasgoogle sheets

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

Best Answer

  • paste in A2 cell:

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

    0

  • then select A2 cell press CTRL + C

  • select A8 cell and press CTRL + V and continue on A14, A20 etc.