The problem is to create a new sheet, copy the content of an existing sheet, and change references to for cells to a second existing sheet. As Satya Mishra says, that could be done manually by changing 'Day03'!
to 'Day04'!
after copying to a new sheet, Day05
.
To make the change automatically when you copy from the previous sheet to a new one, let's restate the problem: Create a new sheet and copy the content of the previous sheet. Use the name of the new sheet to make references to the previous named sheet.
Here's a formula in the current sheet that gets the contents of cell F33
from the previous named sheet. For example, if the current sheet is Day05
, the formula gets F33
from sheet Day04
.
=INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33"))
How the formula works
The formula uses INDIRECT
to get a cell value from the previous sheet. If the current sheet is Day05
and we want cell F33
, INDIRECT
needs a string that references cell F33
in sheet Day04
. The string we need is 'Day04'!F33
.
- Get the sheet name for the current sheet.
Day05 =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)
- Modify the formula to get the number at the end of the sheet name by changing +1 to +4. This skips the 3 characters "Day".
05 =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)
- Subtract 1 to get number portion of the previous sheet.
4 =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1
- Use
TEXT
to pad the number with a leading zero.
04 =TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#")
- Use
CONCATENATE
to make the new sheet name with a reference to cell F33
.
'Day04'!F33 =CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33")
Using the formula
Wherever you want a cell value from the previous sheet, use the formula in place of the cell reference, and change the last string in the formula to the cell reference. For example, to use cell C4
to show the Description (notice C4
in the formula):
="Saldo from "&TEXT(INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","C4")),"DD.MM.YYYY.")
The result is Saldo from 01.06.2016.
Conditions and caveats
- Each sheet name must end with two numbers, e.g.,
Day02
, Day03
, etc.
- The formula won't work until the new sheet has its correct name. A wrong sheet name causes
#VALUE!
errors.