display current cell contents in another worksheet
 

I have used the following formula to display the contents of the current cell
(since last refresh - update using F9):

=INDIRECT(CELL("address"))

Can I do anything similar to display the current cell in a different
worksheet?

TIA.

display current cell contents in another worksheet
 

hi, andy !

I have used the following formula to display the contents of the current cell (since last refresh - update using F9):
=INDIRECT(CELL("address"))
Can I do anything similar to display the current cell in a different worksheet?

IMHO, main issue is the fact that CELL(...) info_WF changes accordingly to 'activecell' in activesheet

if you don't mind to use a UDF... [Chip Pearson: - http://tinyurl.com/26lw6l]

1) put the following on *every* WS code module:
Public currentCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
currentCell = "'" & Me.Name & "'!" & ActiveCell.Address
End Sub

2) the following goes on a standard code module:
Function currentCell_In(mySheet As String) As String
currentCell_In = Worksheets(mySheet).currentCell
End Function

3) you can use in your WS +/- as follows:
=indirect(currentCell_In("sheet1"))

OR... avoid indirect 'volatile' WF and make volatile your UDF...
a) in your standard code module:
Function currentCell_In(mySheet As String) As String
Application.Volatile
currentCell_In = Evaluate(Worksheets(mySheet).currentCell)
End Function

b) usage: currentCell_In("sheet1")

and/or modify, adapt, ... as needed ;)
hth,
hector.

display current cell contents in another worksheet
 

Thanks. I went in a different direction, but I appreciate the response and
know that others who find this will use your suggestion.

"Héctor Miguel" wrote:

hi, andy !

I have used the following formula to display the contents of the current cell (since last refresh - update using F9):
=INDIRECT(CELL("address"))
Can I do anything similar to display the current cell in a different worksheet?

IMHO, main issue is the fact that CELL(...) info_WF changes accordingly to 'activecell' in activesheet

if you don't mind to use a UDF... [Chip Pearson: - http://tinyurl.com/26lw6l]

1) put the following on *every* WS code module:
Public currentCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
currentCell = "'" & Me.Name & "'!" & ActiveCell.Address
End Sub

2) the following goes on a standard code module:
Function currentCell_In(mySheet As String) As String
currentCell_In = Worksheets(mySheet).currentCell
End Function

3) you can use in your WS +/- as follows:
=indirect(currentCell_In("sheet1"))

OR... avoid indirect 'volatile' WF and make volatile your UDF...
a) in your standard code module:
Function currentCell_In(mySheet As String) As String
Application.Volatile
currentCell_In = Evaluate(Worksheets(mySheet).currentCell)
End Function

b) usage: currentCell_In("sheet1")

and/or modify, adapt, ... as needed ;)
hth,
hector.