Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I get current #row or #column in cell formula?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I get current #row or #column in cell formula?
use =ROW() to get the row number
-- Gary's Student "ryany" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I get current #row or #column in cell formula?
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 FileClose and Return to Microsoft Excel. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |