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

jahsquare

Board Regular
Joined
Jan 22, 2014
Messages
51
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could open up the watch window and then have this macro:

Code:
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.
 
Upvote 0
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
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub
on the sheet, so hopefully it will recalculate properly.
 
Upvote 0
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 (...)
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
Code:
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.
 
Upvote 0
This does the deed, thanks. Adding to this, would you do something like this:
Code:
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
Code:
    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.
 
Upvote 0
This does the deed, thanks. Adding to this, would you do something like this:
Code:
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
Code:
    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:

Code:
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.
 
Upvote 0
Was able to get that to work, though it is not any faster:
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top