Can I get current #row or #column in cell formula?

[Guest]

For example, I freeze pane for row 1, and want A1 cell to display "Text1"
when windows is scrolling within row 2~100, and display "Text2" when it's
scrolled beyond row 101, can I use such formula in A1:

=if(GetCurrentTopRow() < 100, "Text1", "Text2")

is there such a function?

 

[Guest]

use =ROW() to get the row number

 

[Ken Johnson]

Hi ryany,
I don't know of any way of achieving this without using an event
procedure.

The following Worksheet_SelectionChange Sub pasted into the code module
of the worksheet works.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveWindow.ScrollRow < 101 Then
Let Range("A1").Value = "Text1"
Else: Let Range("A1").Value = "Text2"
End If
End Sub

To get the code in place follow the following steps:

1.Copy the above code
2.Right Click the worksheet tab then select "View code" from the
contextual popup menu.
3.Paste the code into the blank code module.
4.Edit the "Text1" and "Text2" strings.
5.Return to Excel worksheet by pressing Alt + F11 or going File>Close
and Return to Microsoft Excel.

Ken Johnson