displaying the content of the first cell in a column

Iron Contributor

Morning

I need a way to show the content of the first cell of a column such that if I select or point to a cell (A10, for example) the content of cell A1 should appear as a  hint. any advice to do that using Google Sheets or Excel 365?

1 Reply

@ajl_ahmed 

For Excel 365…
Consider just freezing the top row , i.e., displaying the top row (or top several rows), regardless of where the user scrolls to. Or…


If the A1 cell content is constant, or changes so infrequently that sheet maintenance could be performed:

  • Select the relevant range that needs a hint and use Excel's Data Validation capabilities (even if you don't need to validate user input there):

hints_1.jpg

On the Data Validation dialog that appears, you can leave the "Allow" entry as "Any value"; on the next dialog tab, make sure the "Show input message when cell is selected" checkbox is checked, and put your hint text into the "Input message".

 

If the hint content changes frequently enough that doing worksheet maintenance is not practicable:

  • If you are willing to use VBA, code added to the Worksheet_SelectionChange event handler can use Data Validation capabilities:
    Dim objRelevantCells    As Range
    
    '----
    Set objRelevantCells = Intersect(Target, Range("A2:A100"))
    If objRelevantCells Is Nothing Then
        Exit Sub
    End If
    With objRelevantCells.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertInformation
        .InputMessage = "This is a hint regarding " & Range("A1").Value
    End With

(If you already have data validation on the range of interest, you should remove the .Delete and .Add statements.)