• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need the current row number after the freeze line

J

jaw1of5

New Member
Is there a formula that will dynamically give the current row number directly below the freeze line as the data is scrolled?
 
Hi ,


Can you please explain why you need this information , and how you plan to use it ?


The current row number directly below the freeze line will change only if you are using the window and scrolling up or down. Why do you want to know the row and column numbers through a formula ?


If you are interested to find this out through VBA , then the following will tell you this :


ActiveWindow.VisibleRange.Row


ActiveWindow.VisibleRange.Column


Narayan
 
I have a spreadsheet where I have Jan-Dec in Columns B-M listing quantities per month.

Column A list the items. The Items are spaced by year. example:


2011 Jan Feb Mar ...

item 1 124 233 169

item 2 344 310 336

item 3 223 111 111


2010

item 1 123 563 540

item 2 933 157 232


I have the freeze pane set at B2 so the months will show when scrolled down. The year will scoll of the screen. While it's not a key item I would like to change the heading in A1 to match the year that is nearest the headers. I can do this if I can figure how to identify the row number currently at the freeze line. As I said not key item but it would make it look better.
 
No. what I want is when the user scrolls down and the year 2011 in A3 scrolls off the screen to display the year (2011) in A2. Then when year 2010, which would be in A8 reaches the freeze line to change A2 to 2011.


This way the user always knows which year is being displayed at the top. I know they can check the following year and subtract but it would look better if the year was displayed and currently I'm not sure the years will be displayed numerically.


Thank you for your assistance.
 
My apologies. The second line of my post should be:


Then when year 2010, which would be in A8 reaches the freeze line to change A2 to 2010.


not:


Then when year 2010, which would be in A8 reaches the freeze line to change A2 to 2011.


Late night, didn't proof-read well.
 
Hi ,


That is exactly what the uploaded file was doing ; only instead of displaying the current year in A2 , it was displaying it in B1.


Check this version :


http://speedy.sh/FhMpX/Current-Year.xlsm


Narayan
 
If you put the year 2010 in A8 I need the year in A2 to change to 2010 when A8 reaches the freeze line and goes off the screen
 
Hi ,


I had intentionally introduced a lot of blank rows between the last row of data pertaining to 2011 , and the first row of data pertaining to 2010.


Why don't you delete these blank rows so that the year 2010 comes to cell A8 , and then test it out ?


Narayan
 
Narayank991,


It shows the year when the sheet is openned but doesn't change it. I tried deleteing the 2011 from A2 then scrolling but it dosen't show up again. I was hoping there was a cell formula I could use but it looks like I'll have to go VBA.


Thanks for all your work on it.
 
Narayank991,


Finally got your sheet to work. I didn't realize i had to change the active cell. This does work but unforunantly my users probably won't be clicking on the cells, they will just scroll down to see the data. Is there a way of just monitoring the row number and changing A2 when a specific row number reaches the freeze line?
 
Hi ,


The problem is not in monitoring the scroll row , for which the ScrollRow property is available in VBA. The problem is how to recognize that the user is scrolling !


The procedure is event driven , and there are only a few possibilities to detect an event viz. a Calculate event , a cell Change event , and a cell SelectionChange event.


In case you don't change the Active cell , there is no way to find out what the user is doing.


Even if you scroll up / down using the mouse , you will finally have to click on a cell to trigger the event , and consequently the procedure.


Let me see if there is any way to get what you want.


Narayan
 
I thought that since the top row number is shown when the scroll bar is moved that there might be a fucntion that tracks the row number.


Thank you for working on this
 
You must log in or register to reply here.
Back
Top