Can I "loose" a column?

[adambc]

I have a Description (text) column and a Value (numeric) column ...

The Description column has a Dropdown that picks up it's list from the Description column (1) of a lookup list on another Worksheet (so that users can select what they want in plain English) ...

The Value column is populated from the Dropdown selection by way of a VLOOKUP against the Value column (2) of the lookup list above ...

Hope that makes sense so far?!!!

I have too many columns and have been looking for a way to "loose" the Description column as follows ...

- when the user makes a selection from the Dropdown, instead of the VLOOKUP result populating the Value column, it "overwrites" the selection in the Description column with the corresponding value (which is a number used to effect a calculation so must be formatted as such) ...

I have found several similar "solutions" but cannot get any of them to work - as a last resort before I give up (!) does anyone have a definitive solution to this (I do NOT want to use a ComboBox for a number of reasons!)?

Many thanks ...

 

[jasonb75]

The only way to do it would be with a Worksheet_Change event (vba) to evaluate the lookup formula in the background and overwrite the dropdown selection.

You will not be able to do it with a formula alone.

 

[adambc]

@jasonb75

That's fine, but can you help me with that please (I assume it would be a Sub on the target Worksheet ie where I want the value to display?)

 

[jasonb75]

Sure, I would need your existing lookup formula and the worksheet range that the dropdowns are located in.

 

[adambc]

Sorry, have misled myself - and therefor you!

This could have been a VLOOKUP and may need to be to do what I want to do(?), but in fact I done the following ...

In column S, I have a dropdown linked to the following list on another sheet ...

Impact

No adverse effect (1)

Minor (2)

Adverse (3)

Serious (4)

Catastrophic (5)

In column T I have the formula (for row 3) ...

=IF(S3 = "No adverse effect (1)",1,IF(S3 = "Minor (2)",2,IF(S3 = "Adverse (3)",3,IF(S3 = "Serious (4)",4,IF(S3 = "Catastrophic (5)",5,"")))))

As I've said, I want to "loose" one of the columns, but have the same dropdown from the same list, but when a selection is made, the same cell is populated with the corresponding value ie 1, 2, 3, 4 or 5?

But not sure how I can do that?

Many thanks ...

 

[jasonb75]

I think I have this right,

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(c, Range("S:S")) Is Nothing Then
    For Each c In Intersect(Target, Range("S:S").SpecialCells(xlCellTypeAllValidation))
        c.Value = CLng(Mid(c.Value, Len(c.Value) - 1, 1))
    Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

 

[adambc]

Works perfectly, THANK YOU ...

PS; now need to work through the code to understand how it works!!!

 

[jasonb75]

You're welcome

Some of the code is more than likely not necessary, the loop (For Each c) is only needed if you change a group of validation cells together, for example by copy and paste.

 

[adambc]

@jasonb75 - feeling quite smug (I am a newbie to VBA)!

Realised it would be so much easier if I could make your routine work against a VLOOKUP rather than parsing the input - so rejigged your code as follows ...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

On Error Resume Next
Application.EnableEvents = False
If Not Intersect(c, Range("S:S", "U:U")) Is Nothing Then
    For Each c In Intersect(Target, Range("S:S", "U:U").SpecialCells(xlCellTypeAllValidation))
        'c.value = CLng(Mid(c.value, Len(c.value) - 1, 1))
        c.value = CLng(Application.VLookup(ActiveCell.value, Sheets("Look Up Tables") _
        .Range("G2:H6"), 2, False))
    Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

But there's a BIG/HUGE but ... I would never have got there without your help, so THANK YOU again ...

PS; there are so many places where I can use this - users always want plain English selections, but complain when there's another column "there's too many columns!" that stores the "real" (numeric) value - and more!!!