I working on a baseball scoresheet. I want cell Q2 to read the cell contents
in column H of the active row, as I move the active cell up and down the rows
(i.e., if active cell is Y36, I want Q2 to read data in H35 (note it is
offset by one row).)

Cells G6:G53 = players names (batting order)
Cells H5:H52 = current data for the players, based on VLOOKUP formula,
which updates with each at-bat
Columns K:CE = results of each at-bat as the game is played

I don't believe I can use the active cell as a reference within a formula,
so I'll probably need a VB macro....any ideas? I saw a similar question on
this forum, but wasn't quite what I was looking for.

pskwaak wrote...
I working on a baseball scoresheet. I want cell Q2 to read the
cell contents in column H of the active row, as I move the active
cell up and down the rows (i.e., if active cell is Y36, I want Q2
to read data in H35 (note it is offset by one row).)

Cells G6:G53 = players names (batting order)
Cells H5:H52 = current data for the players, based on VLOOKUP
formula, which updates with each at-bat

So if Bobby Roberts were in G6 and Tommy Thompson were in G7, Tommy's
batting average would be in the same row as Bobby's name? If so, there
may be better ways to handle layout.

Anyway, if you could live with pressing [F9] to recalcute formulas,
try the following formula in Q2.

=INDEX(H:H,CELL("Row")-1)

Otherwise, use this same formula in Q2 and add a SelectionChange event
handler like the following to the worksheet's VBA class module (right-
click on the worksheet's tab, click on View Code, then paste this
event handler into the code window).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Application.Calculate
Application.EnableEvents = True
End Sub

Great! This is exactly what I was looking for.
For the offset data, each player's entry in column G consists of three
cells, top one for name, bottom two for additional statistics...so actually,
players' names are at every third cell in column G. The active cell in the
rest of the scoresheet is one row below the required data row. There may be
a better setup as I progress with this project, but your solution really
helped!
Thanks again

"Harlan Grove" wrote:

pskwaak wrote...
I working on a baseball scoresheet. I want cell Q2 to read the
cell contents in column H of the active row, as I move the active
cell up and down the rows (i.e., if active cell is Y36, I want Q2
to read data in H35 (note it is offset by one row).)

Cells G6:G53 = players names (batting order)
Cells H5:H52 = current data for the players, based on VLOOKUP
formula, which updates with each at-bat

So if Bobby Roberts were in G6 and Tommy Thompson were in G7, Tommy's
batting average would be in the same row as Bobby's name? If so, there
may be better ways to handle layout.

Anyway, if you could live with pressing [F9] to recalcute formulas,
try the following formula in Q2.

=INDEX(H:H,CELL("Row")-1)

Otherwise, use this same formula in Q2 and add a SelectionChange event
handler like the following to the worksheet's VBA class module (right-
click on the worksheet's tab, click on View Code, then paste this
event handler into the code window).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Application.Calculate
Application.EnableEvents = True
End Sub