In excel, you have the option to "lock" certain rows and/or columns so they scroll with the page (similar to position: fixed; in CSS).

What I want to do is have a certain cell, lets say A5, to display whatever value it's currently over. So, for example

enter image description here

Here, the row A is locked, so whenever we want to scroll down, we see this:

enter image description here

So, instead of seeing the values of the locked row, A in this case, I want to see B1:B6.

Of course, this is just a simplified example (which, needless to say, would be horribly pointless!).

(It should preferably be possible to do this with a formula, since my VB really isn't the best)


tl;dr: Do anyone know of any built-in function (or if you have constructed a formula) which enables me to show, in this case, the values of the cells that looks to be exactly underneath the locked row/column/cell?

Basically, whenever the user scrolls the page (and therefore the view, except for the locked row, changes) the locked row/column/cell should show (or contain, doesn't really matter) the values of the cell that is underneath it

(I have a document where a locked row (lets call it 'A') contains a couple of headings, but further down the document there is even more headings. I want 'A' to change to the values of the last encountered headings in the document whenever the user scrolls and a heading comes under A (read: whenever A hides a (sub-)heading).)

  • Are your 1-6 and A-H marks meant to be the Row and Column labels you always see in Excel? Perhaps a more specific example would make more sense? – Yeodave Sep 27 '11 at 13:55
  • My fault, sorry. I missed that I cut the picture at the wrong place, see the changes. – Marcus Hansson Sep 27 '11 at 14:11

I think you are going to need to use VBA to get the current scroll position. See Stack Overflow HERE.

You cannot use a formula to read the state of the User interface. You will need to use VBA for this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.