Formula to return/display the contents of a particular cell from the active row

[jahsquare]

Hi,
I have a huge table with many (> 8000) rows. Each row has a group of static data (each of which includes sizeable blobs of text, which I have to view via the formula box at the top of the sheet) and a group of dynamic cells, which I edit as I read the static data. Typically I click on a static cell, read/analyze the data, then click over to the appropriate dynamic data cell and edit as needed.

The problem is that I have to use the formula box at the top to read the blobs of static text, and so I waste a lot of time clicking back and forth, because that static data does not remain displayed when I click over to the dynamic cells.

What I'd like to do is use the split-screen feature or some kind of dialog to display the static data no matter which cell in the active row is currently selected. Then I can bounce around to fill in my dynamic data, and the static text will remain on-screen. This will also allow me to move up and down through the rows more easily. Basically I would have the table visible on the left side of the screen, and on the right side in a split-window I would have the static data displayed from the active row.

Is there any simple way to do this?

Many thanks!

 

[NeonRedSharpie]

You could open up the watch window and then have this macro:

Sub addWatch()

With Application.Watches
    .Delete
    .Add Cells(Selection.Row, 1)
End With




End Sub

This will clear your current watch window and add in the cell of the active row in column A.

 

[jahsquare]

To simplify the question, maybe I should just ask: Is there a way to return a particular cell from the active row in a formula? Something like: =ActiveRow(ContentsFromCell)
I already have

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub

on the sheet, so hopefully it will recalculate properly.

 

[jahsquare]

Thanks, NeonRedSharpie. I have a few issues with this method -
Using the watch window, when I change the active row, the macro does not appear to recalculate. It displays the appropriate data for the current active row when I run the macro, but does not change when the active row is changed.
Also, It does not appear that I can view large blobs of text in this mode, anything beyond the one-line width of the field in the watch window is truncated with (...)

 

[NeonRedSharpie]

Thanks, NeonRedSharpie. I have a few issues with this method -
Using the watch window, when I change the active row, the macro does not appear to recalculate. It displays the appropriate data for the current active row when I run the macro, but does not change when the active row is changed.
Also, It does not appear that I can view large blobs of text in this mode, anything beyond the one-line width of the field in the watch window is truncated with (...)

You could copy the code into the selection_change event you already have. The only other thing I can think of is to make a giant merged cell and have a selection_change event populate that.

 

[jahsquare]

Yeah, I was thinking I could have a range of merged cells under the table act as a content-holder for the static data from the current active row, then use the Split view to keep that portion of the sheet on the screen while I move around in the table. Just not sure about the formula...

 

[NeonRedSharpie]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    With Range("M8") 'Change this to your merged cell
        .Value = Cells(Target.Row, 1)
    End With
End Sub

.


Try that.

 

[jahsquare]

This does the deed, thanks. Adding to this, would you do something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    With Range("BH8214")
        .Value = Cells(Target.Row, 1)
    End With
    With Range("BH8215")
        .Value = Cells(Target.Row, 2)
    End With
    With Range("BH8216")
        .Value = Cells(Target.Row, 3)
    End With
    With Range("BH8217")
        .Value = Cells(Target.Row, 4)
    End With
    With Range("BH8218")
        .Value = Cells(Target.Row, 5)
    End With
End Sub

To update multiple cells, or could I do something like

    With Range("BH8214:BH8218")
        .Value = Cells(Target.Row, 1:5)
    End With

Your suggestion does work and I'll be content to stick with it. I might try to speed things up as it's pretty sluggish. I also have several conditional formatting rules running, which are also slowing things down.

 

[NeonRedSharpie]

This does the deed, thanks. Adding to this, would you do something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    With Range("BH8214")
        .Value = Cells(Target.Row, 1)
    End With
    With Range("BH8215")
        .Value = Cells(Target.Row, 2)
    End With
    With Range("BH8216")
        .Value = Cells(Target.Row, 3)
    End With
    With Range("BH8217")
        .Value = Cells(Target.Row, 4)
    End With
    With Range("BH8218")
        .Value = Cells(Target.Row, 5)
    End With
End Sub

To update multiple cells, or could I do something like

    With Range("BH8214:BH8218")
        .Value = Cells(Target.Row, 1:5)
    End With

Your suggestion does work and I'll be content to stick with it. I might try to speed things up as it's pretty sluggish. I also have several conditional formatting rules running, which are also slowing things down.

You'll need to do the first one. You could do:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    
    For x = 1 To 5
        With .Cells(8213 + x, 60)
            .Value = Cells(Target.Row, x)
        End With
End Sub

But that might not be any faster. I'm guessing your code is slow because of the calculate. With (assuming) a range of 60x8200, calcs might be heavy.

 

[jahsquare]

Was able to get that to work, though it is not any faster:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Target.Calculate
   Dim x As Long
   For x = 1 To 5
       With Cells(8214 + x, 61)
           .Value = Cells(Target.Row, x)
       End With
   Next
   End Sub

Not sure this is the best bet, as now there's about a 5-7 second delay with each selection change. The table is just too big, I suppose.
Anyway, thanks for your time!