Worksheet reference ?

[Roch]

Hello, Is there any way when copying a worksheet to update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update the
sheet +1 to be automatic =Sheet2!A1+1

Thank

 

[Jason Morin]

Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

 

[Roch]

This formula give me a error

thanks

-----Original Message-----
Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, Is there any way when copying a worksheet to update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update the
sheet +1 to be automatic =Sheet2!A1+1

Thank
.

.

 

[Roch]

I just copy paste your formula to a cell and when i press
enter it give me a error?

Roch

-----Original Message-----
It works for me. When I copy Sheet2 and move it to the
end, it produces Sheet 2 (2). When I change the name to
Sheet3, the formula works. It translates to =Sheet2!A1+1.

Jason

-----Original Message-----
This formula give me a error

thanks

-----Original Message-----
Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, Is there any way when copying a worksheet to
update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the
same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update the
sheet +1 to be automatic =Sheet2!A1+1

Thank
.

.

.

.

 

[Anon]

Which error is it giving? Are your sheets actually named Sheet1, Sheet2,
etc.?

I just copy paste your formula to a cell and when i press
enter it give me a error?

Roch

-----Original Message-----
It works for me. When I copy Sheet2 and move it to the
end, it produces Sheet 2 (2). When I change the name to
Sheet3, the formula works. It translates to =Sheet2!A1+1.

Jason

-----Original Message-----
This formula give me a error

thanks

-----Original Message-----
Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, Is there any way when copying a worksheet to
update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the
same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update the
sheet +1 to be automatic =Sheet2!A1+1

Thank
.

.

.

.

 

[Roch]

The formula you type contain a error

Roch

-----Original Message-----
Which error is it giving? Are your sheets actually named Sheet1, Sheet2,
etc.?

I just copy paste your formula to a cell and when i press
enter it give me a error?

Roch

-----Original Message-----
It works for me. When I copy Sheet2 and move it to the
end, it produces Sheet 2 (2). When I change the name to
Sheet3, the formula works. It translates to =Sheet2! A1+1.

Jason


-----Original Message-----
This formula give me a error

thanks

-----Original Message-----
Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND ("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, Is there any way when copying a worksheet to
update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the
same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update
the
sheet +1 to be automatic =Sheet2!A1+1

Thank
.

.

.

.

.

 

[Roch]

I use excel 2000, and they are name sheet1, 2, 3

thanks

-----Original Message-----
The formula you type contain a error

Roch

-----Original Message-----
Which error is it giving? Are your sheets actually named Sheet1, Sheet2,
etc.?

I just copy paste your formula to a cell and when i press
enter it give me a error?

Roch
-----Original Message-----
It works for me. When I copy Sheet2 and move it to the
end, it produces Sheet 2 (2). When I change the name to
Sheet3, the formula works. It translates to =Sheet2! A1+1.

Jason


-----Original Message-----
This formula give me a error

thanks

-----Original Message-----
Try this:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND ("]",CELL
("filename",A1))+6,255)-1&"!A1")+1

Note - wb must be saved for this to work.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, Is there any way when copying a worksheet to
update
the reference from another worksheet
ex: sheet2 as a formula =Sheet1!A1+1, when i copy the
sheet2 in the new worksheet sheet3 the formula is the
same
=Sheet1!A1+1,
can we makem a reference to the last sheet or update
the
sheet +1 to be automatic =Sheet2!A1+1

Thank
.

.

.

.

.

.