Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .