Returning active cell
 

I have a report tab that prompts a user to enter a row number in a cell.
When that row number is entered values are populated from a data tab.

I would a user to be able to click on the data tab and then have the a
formula display the row, column, or cell that the user last clicked. So when
a user clicks on a cell and then goes to a different worksheet, that last
cell clicked remains highlighted. Is there a formula that identifies that
highlighted cell?

Let me see if I can rephrase your question and you tell me if I'm right.
Sheet1 is the active sheet.
Some cell is the active cell, say F17.
The user selects Sheet2.
You want some cell in Sheet 2 to display F17.
Is that what you want?
If this is what you want, it can be done with a short event macro, but not
with a worksheet function. The macro would look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("Sheet2")
.[A1] = ActiveCell.Address(0, 0)
End With
End Sub

This macro has to be placed in the sheet module for Sheet1. Basically what
this macro does is to place the address of the active cell of Sheet1 into
cell A1 of Sheet2. It doesn't matter how many times the active cell
selection is changed in Sheet1. When Sheet2 is selected, the last selected
cell address from Sheet1 will be in A1 of Sheet2. HTH Otto
"Ted Metro" wrote in message
...
I have a report tab that prompts a user to enter a row number in a cell.
When that row number is entered values are populated from a data tab.

I would a user to be able to click on the data tab and then have the a
formula display the row, column, or cell that the user last clicked. So
when
a user clicks on a cell and then goes to a different worksheet, that last
cell clicked remains highlighted. Is there a formula that identifies that
highlighted cell?

Thanks Otto, you are right in what I was trying to accomplish. I wanted to
do that without a macro, but I guess it can't be done. Thanks for your help!

"Otto Moehrbach" wrote:

Let me see if I can rephrase your question and you tell me if I'm right.
Sheet1 is the active sheet.
Some cell is the active cell, say F17.
The user selects Sheet2.
You want some cell in Sheet 2 to display F17.
Is that what you want?
If this is what you want, it can be done with a short event macro, but not
with a worksheet function. The macro would look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("Sheet2")
.[A1] = ActiveCell.Address(0, 0)
End With
End Sub

This macro has to be placed in the sheet module for Sheet1. Basically what
this macro does is to place the address of the active cell of Sheet1 into
cell A1 of Sheet2. It doesn't matter how many times the active cell
selection is changed in Sheet1. When Sheet2 is selected, the last selected
cell address from Sheet1 will be in A1 of Sheet2. HTH Otto
"Ted Metro" wrote in message
...
I have a report tab that prompts a user to enter a row number in a cell.
When that row number is entered values are populated from a data tab.

I would a user to be able to click on the data tab and then have the a
formula display the row, column, or cell that the user last clicked. So
when
a user clicks on a cell and then goes to a different worksheet, that last
cell clicked remains highlighted. Is there a formula that identifies that
highlighted cell?