Referencing a row/cell that changes

[davidpierce]

HI all,
I have a sample attached, and in X9 I have the formula =F3+AF4-(F3-D10) which F3 is the Start Date of Project, AF4 is the position of the scroll bar and F3-D10 is the calculation of the start date and lowest date in schedule. This formula starts my calendar date at the formula calculation, =F3+AF4-(F3-D10) and row 10 is the last row in the Freeze Pane.

So, what I am wondering is there a way to reference the next row after the "Freeze Pane" or row 12 in this sample keeping in mind it will move when scrolled. So, if I scroll to row 30 (In this sample) and row 30 is directly under row 10 "the Freeze Line" then my formula would change to =F3+AF4-(F3-D30) which would move my bar chart to that day in X9 so it will follow the dates in D when scrolling down or up. What ever date in "D" directly below Row 10 is, that would be the formula.

Any ideas on this. I am not sure if I can reference this in this way.
Thanks to all,
Dave

http://www.excelforum.com/showthread...t=#post4082653

[Kaper]

to see which cell is first visible you can use:

Please Login or Register  to view this content.

but to include it in the formula which will change after scroll event handler for scrolling shall be used. Probably there is one, but I never used it.

[davidpierce]

Thanks Kaper, I will continue with this then. Maybe I can find a solution.
Appreciate you taking a look at it.
Dave

[davidpierce]

Hi all,
I got this code to work for bringing up a msgbox for the row and Cell I was looking for.

Please Login or Register  to view this content.

So, does anyone have any ideas how I could make this change on scroll instead of change on page, and instead of a message box, put the value of D?? in a different cell like "(Q2)" for an example.

Any ideas please and thank you
Dave

[Kaper]

Hi,
The latter part is easy - in the event handle something like

Please Login or Register  to view this content.

instead of msgbox
I used msgbox just to show that this value returns proper address.

The key problem is that scrolling event is normally not controlled by Excel. Probably such workaround could be used: http://www.cpearson.com/excel/DetectScroll.htm but I've not explored it further.

[davidpierce]

Thanks Kaper,
That worked like a charm, I will do some research today on the link and see if I can get this to work on scroll. I will let you all know if I figure it out.
Thanks

[davidpierce]

Is there a way to offset or something the rows, as per sample, I have two rows per one line of tasks. Col "D" would only be the even numbers of rows. Like" 12, 14, 16, and so on ", When I use the scroll bar or mouse to scroll with, then I get like 12, 15, 18 and when odd number of row, then I get a date of 1/0/1900
Thanks

[Kaper]

you mean in formula?
if row shall be rounded down (upwards in the sheet), for example 13->12:

Please Login or Register  to view this content.

if shall be rounded up (down the screen) i.e. 13->14:

Please Login or Register  to view this content.

[davidpierce]

Thanks
You are just to good at this. Wish I had your knowledge! Make mountains move for sure.
Thanks,

I have been reading up on that link, way past my experience level but working on it. I cant put on my laptop at work as don't have admin to add a dll. What a pain so have to wait till I get home today to really try it out.
Thanks
Dave

[davidpierce]

Well didn't have any luck at all with that link. I couldn't even get the .dll to register? I tried copy paste, drag and drop, and nothing, gave an error message every time I tried to run it.

If anyone has any ideas on firing the above code with the scroll, please let me know would you?
Thanks

[Leith Ross]

Hello davidpierce,

You can return the row and column easily using the following code...

Please Login or Register  to view this content.

[davidpierce]

Thank you Leith Ross, and please forgive me as I am not very good at VBA. I can figure out a few things but some of these things just baffle me.

I have this code that gives me the result I need, I just want it to fire when I scroll instead of having to change by doing something in worksheet. Is there a way to fire this code while scrolling?

Please Login or Register  to view this content.