I have a worksheet in which I transfer stock from sheet to sheet.To
create a new sheet I simply copy the last one and change date.
My formula is f/example c2 of the current =f2 of the one before. How can
I copy this directly without having to update each time.
Much obliged

On Jun 6, 3:47 am, dimitry wrote:
I have a worksheet in which I transfer stock from sheet to sheet.To
create a new sheet I simply copy the last one and change date.
My formula is f/example c2 of the current =f2 of the one before. How can
I copy this directly without having to update each time.
Much obliged

Dimitriy,

the following method might be cumbersome, but I believe it is doing
what you are asking for. Its components:

1. It requires a list of the spreadsheets in a separate area. E.g. in
Sheet1!A1:A4. In that list you have the sheet names in the order in
which they will be produced.
2. Your formula right now is, say, ='Other sheet'!F1. Instead, you
should use:
=INDIRECT("'"&INDEX(Sheet1!$A$1:$A
$3,MATCH(MID(CELL("filename"),FIND("]",CELL("filename"))
+1,LEN(CELL("filename"))),Sheet1!$A$1:$A$3,0)-1)&"'!F1")
3. When you are ready to procude a new sheet, right-click on the sheet
tab that you want to copy, choose Move or Copy... and make a copy of
the sheet in the end.
4. Rename the produced sheet as per your list.

HTH
Kostis Vezerides

vezerid escribió:
On Jun 6, 3:47 am, dimitry wrote:
I have a worksheet in which I transfer stock from sheet to sheet.To
create a new sheet I simply copy the last one and change date.
My formula is f/example c2 of the current =f2 of the one before. How can
I copy this directly without having to update each time.
Much obliged

Dimitriy,

the following method might be cumbersome, but I believe it is doing
what you are asking for. Its components:

1. It requires a list of the spreadsheets in a separate area. E.g. in
Sheet1!A1:A4. In that list you have the sheet names in the order in
which they will be produced.
2. Your formula right now is, say, ='Other sheet'!F1. Instead, you
should use:
=INDIRECT("'"&INDEX(Sheet1!$A$1:$A
$3,MATCH(MID(CELL("filename"),FIND("]",CELL("filename"))
+1,LEN(CELL("filename"))),Sheet1!$A$1:$A$3,0)-1)&"'!F1")
3. When you are ready to procude a new sheet, right-click on the sheet
tab that you want to copy, choose Move or Copy... and make a copy of
the sheet in the end.
4. Rename the produced sheet as per your list.

HTH
Kostis Vezerides

Thank you very much. Worked like a Charm
Dimitry

Glad it worked! To tell you the truth I thought the method will
stumble somewhere. Thanks for the feedback.

Kostis

On Jun 8, 4:17 pm, dimitry wrote:
vezeridescribió:

On Jun 6, 3:47 am, dimitry wrote:
I have a worksheet in which I transfer stock from sheet to sheet.To
create a new sheet I simply copy the last one and change date.
My formula is f/example c2 of the current =f2 of the one before. How can
I copy this directly without having to update each time.
Much obliged

Dimitriy,

the following method might be cumbersome, but I believe it is doing
what you are asking for. Its components:

1. It requires a list of the spreadsheets in a separate area. E.g. in
Sheet1!A1:A4. In that list you have the sheet names in the order in
which they will be produced.
2. Your formula right now is, say, ='Other sheet'!F1. Instead, you
should use:
=INDIRECT("'"&INDEX(Sheet1!$A$1:$A
$3,MATCH(MID(CELL("filename"),FIND("]",CELL("filename"))
+1,LEN(CELL("filename"))),Sheet1!$A$1:$A$3,0)-1)&"'!F1")
3. When you are ready to procude a new sheet, right-click on the sheet
tab that you want to copy, choose Move or Copy... and make a copy of
the sheet in the end.
4. Rename the produced sheet as per your list.

HTH
Kostis Vezerides

Thank you very much. Worked like a Charm
Dimitry