Forum Discussion
vtyree
Jul 08, 2020Copper Contributor
Entering a date on multiple sheets automatically
I'm trying to put in a formula to enter dates automatically on my sheets. For example: I have 31 sheets for the month of July. I want to have the date on each sheet in the same cell. Is there a way t...
- Jul 08, 2020
TheAntony
Jul 08, 2020Iron Contributor
vtyree , you can extract the day from the sheet name using this formula:
=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255)
Then use the Date function to get the whole date:
=DATE(2020,7,MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255))
- mtarlerJul 08, 2020Silver Contributor
Ah says the man when you open his eyes. I didn't catch the 31 sheets for July as in 31 days in July. Good catch TheAntony . That solution is good IF the sheets are named 1, 2, 3, etc...
If not you can use this formula in the Name Manager to define a Name like "PrevSheet" as a reference to the same cell in the previous sheet:
=INDIRECT(INDEX(GET.WORKBOOK(1),SHEET()-1)&"'!R"&ROW()&"C"&COLUMN(),FALSE)
then:
- in sheet 1 type 1
- highlight sheets 2-31
- type in =PrevSheet+1