Can I show contents of a column based on cell focus in another?

[joezeppy]

Hi,

I have ten or so columns, each with a different Title in Columns C through L.

In Column A, I have a list of those Titles (downwards)

I would like to create a condition in Column B where if someone selects (or places focus) on one of the Titles in Column A, that the respective Column contents would show up in Column B.

Can this be done? How?

Any help appreciated!

Joe

 

[oldbrewer]

that the respective Column contents would show up in Column B.

what does this mean ? B 2 is empty click on B2 or put a 1 in it

then conditional format in C2 to L2 that if B2<>"" colour them red ???

 

[ExcelGuyDude]

I'm with oldbrewer, this is very confusing as to what you are trying to do.

If you want something to happen when a cell is focused on, then you will need to use VBA at the Sheets level (not modules).
On the VBA side, you will need to double click on the sheet you want to select. Then paste in a code to the affect of this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 And Target.Value <> "" Then
    On Error Resume Next
    Cells(Target.Row, 2) = Cells(2, Range("C1:L1").Find(Target.Value, Range("C1"), xlValues, xlWhole).Column).Value
End If

End Sub

What this does is checks if the "Target" cell (same this as using ActiveCell) is in column A (1) and the cell value is not blank.
Given that it's not blank, it will check for the value of the Target cell, find the column it relates to in range C1 to L1, then will grab the cell value in row 2 of that corresponding column.

I'm not even sure what purpose you have of doing this, but that's how I have used VBA code to manipulate cells that I select.

It seems like you could just put a formula in column B that will always have the result you want to show.

 

[West Man]

In A1 use data validation - list to have a dropdown of your titles in C1:L1. In B1 and copied down =CHOOSE(MATCH($A$1,$C1:$L1,0),C1,D1,E1,F1,G1,H1,I1,J1,L1).. This should populate column B with the values of the column with the selected title

 

[joezeppy]

that the respective Column contents would show up in Column B.

what does this mean ? B 2 is empty click on B2 or put a 1 in it

then conditional format in C2 to L2 that if B2<>"" colour them red ???

Thanks for answering and sorry if I wasn't clear. I'll expand below...

Hi,

I have ten or so columns, each with a different Title in Columns C through L.

In Column A, I have a list of those Titles (downwards)

I would like to create a condition in Column B where if someone selects (or places focus) on one of the Titles in Column A, that the respective Column contents would show up in Column B.

Basically, if someone clicks on a value in column A (one of the ten Titles that also go across the top row of the page and which have lists underneath them) I would like for that Column and list to appear in Column B. If they click on cell A3 (Category 1), the list in Column C (Category 1) would appear in Column B. If they click on cell A4 (Category 2), then the contents of Column D (Category 2) would appear in Column B, and so forth.

I'm trying to create an Excel tool that would make it easy to show the various lists in column B so the user won't have to scroll sideways to see the values. In fact I might even like to hide columns C through L once the tool works.


Can this be done? How?

Any help appreciated!

Joe

 

[joezeppy]

I'm with oldbrewer, this is very confusing as to what you are trying to do.

If you want something to happen when a cell is focused on, then you will need to use VBA at the Sheets level (not modules).
On the VBA side, you will need to double click on the sheet you want to select. Then paste in a code to the affect of this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 And Target.Value <> "" Then
    On Error Resume Next
    Cells(Target.Row, 2) = Cells(2, Range("C1:L1").Find(Target.Value, Range("C1"), xlValues, xlWhole).Column).Value
End If

End Sub

What this does is checks if the "Target" cell (same this as using ActiveCell) is in column A (1) and the cell value is not blank.
Given that it's not blank, it will check for the value of the Target cell, find the column it relates to in range C1 to L1, then will grab the cell value in row 2 of that corresponding column.

I'm not even sure what purpose you have of doing this, but that's how I have used VBA code to manipulate cells that I select.

It seems like you could just put a formula in column B that will always have the result you want to show.

Wow, thanks for this. It might be the way I'll have to go.

I'm not too familiar with VBA. I know how to get there and can cut and paste code in but I can't make much sense of what it all means.

I tried to cut and paste the code example you gave me in there but without much success.

In essence, I am trying to create an Excel tool that will take ten categories (would turn into more if I could get that many to work), each with their own list underneath them and allow a user to click on one of those categories in column A (they would be the same categories as listed across the top row of the page), then once they click on or place focus on (or should I say "selects a cell containing") one of the categories in column A, the respective column from C through L would appear in column B. I'm pretty sure it can be done but I think you are right that I'll need to figure out how to do it using VBA rather than some formula.

 

[joezeppy]

In A1 use data validation - list to have a dropdown of your titles in C1:L1. In B1 and copied down =CHOOSE(MATCH($A$1,$C1:$L1,0),C1,D1,E1,F1,G1,H1,I1,J1,L1).. This should populate column B with the values of the column with the selected title

I'll look this up. It sounds very cool and likely could be what I need, however I do want for the values in Column A to all appear rather than be part of a drop down.


I don't think that what I'm trying to do is really that hard. But I haven't used data validation that I can recall. I'll mess with it, do some research on my own about it and see if I can make this work. Thanks so much. I appreciate all these answers.

Sincerely,
Joe

 

[West Man]

Joe without using VBA, I think my solution is as close as you can get. The dropdown is easy to use. If you send me a personal message with your email, I can send you a sample workbook.

 

[ExcelGuyDude]

That makes more sense... So if you want the entire column to appear in column B whenever you select a "Column Name" cell in column A, you could do the following in VBA (Inserting at the sheet level).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ColNumber As Integer, LastRow As Long
    If Target.Count > 1 Then
        Exit Sub
    End If
    If Target.Column = 1 And Target.Value <> "" And Target.Row > 1 Then
        On Error Resume Next
        Columns("B").ClearContents
        ColNumber = Range("C1:L1").Find(Target.Value, Range("C1"), xlValues, xlWhole).Column
        LastRow = Cells(1, ColNumber).End(xlDown).Row
        Range("B1:B" & LastRow) = Range(Cells(1, ColNumber), Cells(LastRow, ColNumber)).Value
    End If
End Sub

 

[joezeppy]

Dear ExcelGuy,

I'd like to experiment with the VBA idea more. I thought it would be above my head but the code you provided has encouraged me because it worked!!!

However, it only seems to work for Cell 4-A (reproducing column E under column B as in the picture below when I select "Generic" in column A). The other cells in column A are not producing the desired result in column B.

Do I have to repeat the sample code ("if and end if" section) in the VBA window for each cell referenced or something in column A? I can't quite make out the pattern to make it work for all of the A Cells corresponding with columns (so the others appear in B too). Sorry.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ColNumber As Integer, LastRow As Long
    If Target.Count > 1 Then
        Exit Sub
    End If
    If Target.Column = 1 And Target.Value <> "" And Target.Row > 1 Then
        On Error Resume Next
        Columns("B").ClearContents
        ColNumber = Range("C1:L1").Find(Target.Value, Range("C1"), xlValues, xlWhole).Column
        LastRow = Cells(1, ColNumber).End(xlDown).Row
        Range("B1:B" & LastRow) = Range(Cells(1, ColNumber), Cells(LastRow, ColNumber)).Value
    End If
End Sub

Any ideas are appreciated. This may be just what I'm looking for.

Thanks Much!

Joe