Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change another cell's contents from current cell??? | Excel Discussion (Misc queries) | |||
How to get current worksheet name to display in a cell? | Excel Worksheet Functions | |||
Display multiple tabs in current worksheet | Excel Worksheet Functions | |||
Can I lock cell contents after current date is past? | Excel Worksheet Functions | |||
Current contents of the cell pointer? | Excel Discussion (Misc queries) |