Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).)

share|improve this question
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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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