Worksheet reference ?

  • Thread starter Thread starter Roch
  • Start date Start date
R

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
 
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
 
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
.
Click to expand...
.
Click to expand...
 
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
.

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

Roch said:
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
.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
The formula you type contain a error

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

Roch said:
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
.

.

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
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.?

Roch said:
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
.

.

.

.
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top