CODEWITHSUNDEEP
excelexcel-formulaformula
David Welch
David Welch

Reputation: 21

Excel formula refer to cell to the right, in frozen pane

I can usually find all the answers I need through Google searches. This one has me stumped; maybe it isn't possible.

My sheet is set up with B5 as its first unfrozen cell. How can I enter a formula in cell A3 that refers to the first cell that is currently showing in the top right pane? So in the worksheet's initial state, A3 would refer to B3, but if I scrolled right 10 rows, for instance, A3 would then refer to K3.

Upvotes: 1

Views: 227

Answers (1)

teylyn
teylyn

Reputation: 35915

Excel uses cell references in formulas. A cell reference does not change when you scroll. You would need VBA to determine the top left cell of a pane. This cell value can be written into a helper cell and you could reference that helper cell in the formula in the frozen pane.

The VBA would run as a Selection Change event, i.e. when the user clicks in a different cell or uses the keyboard to navigate. It would not work for scrolling with the mouse wheel, since that is not an event in the Excel object model. (Chip Pearson has some code for that, though, here).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer, pane As Integer
Dim PaneTop As String

pane = ActiveWindow.Panes.Count
PaneTop = ActiveWindow.Panes(pane).VisibleRange.Address
i = InStr(PaneTop, ":")
PaneTop = Left(PaneTop, i - 1)

Range("A3").Formula = "=" & PaneTop

End Sub

This function puts a cell reference to the top left cell of the pane into A3. Change it to include your formula, or write it to a different cell and let your formula refer to that cell.

Upvotes: 2

Related Questions