Data Validation List & Formula result in the same cell

[mir0mik]

Hi, new here. I'm working on an excel sheet and I came up with such problem:

- I would like to have two fields/cells let say A1 and B1
- In A1 I would like to insert a number (no of holes)
- In B1 I would like to show "low", "mid", "high" depending on some simple calculation/condition based on A1 value (0-100 holes = "low", 101-200 = "mid", 201+ ="high"

Now the probelm:

- I would like to be able to chose in B1 from a drop down list (data validation > list from named range)
- However I would like to be able to fill in value into the A1 cell and dependent on that fill the B1 with "low", "mid", "high"...
- I would like this functionality available any time

Is this possible to do in excel without VBA? So in other words, if I know that 150 holes mean "mid" I want to be able just select "mid" form a dropdown list in B1 or if I don't know what "mid" means but I know there is 150 holes I just add 150 in A1 and B1 will update it self with "mid"... If I put in A1 negative number I should get some warning/error message as it can be only positive number.

Thanks for any help!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try this formula in B1
=IF(A1="","",IF(A1<=0,"A1 must be greater than 0",LOOKUP(A1,{1;101;201},{"low";"mid";"high"})))

Hope this helps

M.

 

[mir0mik]

Hi Marcelo,

Thanks for quick reply. This works fine for a part of the problem. The issue for me is that when I insert the formula into B1 and then apply data validation to B1 it works until I chose something from the drop down list and then the formula is lost...

 

[Marcelo Branco]

Hi Marcelo,

Thanks for quick reply. This works fine for a part of the problem. The issue for me is that when I insert the formula into B1 and then apply data validation to B1 it works until I chose something from the drop down list and then the formula is lost...

Maybe I've misunderstood what you looking for, but i think you don't need Data Validation in B1 - the formula does insert the proper value in B1 whenever something is entered in A1. Why would you need anything more?

M.

 

[mir0mik]

Hi Marcelo,

I've simplified it to be able describe it easily there. I'm working/creating a cost calculator and one of the price adders is density of holes per area. And this calculator can be used by engineer or manager... one can add the "area" and "number of holes" and in that case the "density" will be filled automatically (let say with your formula). However, the other person don't know the exact "number of holes" yet but he know his "area" will be "low" density, so in this case I would like to be able to chose from a drop down list of (low, mid, high)...

The calculator can be use for checking if the price (more inputs) or for rough estimation (less inputs)...

 

[Marcelo Branco]

I'm confused

In post 1 you said

- I would like to be able to chose in B1 from a drop down list (data validation > list from named range)
- However I would like to be able to fill in value into the A1 cell and dependent on that fill the B1 with "low", "mid", "high"...
- I would like this functionality available any time

Now you are saying that is not necessary to enter a value in A1...

However, the other person don't know the exact "number of holes" yet but he know his "area" will be "low" density, so in this case I would like to be able to chose from a drop down list of (low, mid, high)...

M.

 

[mir0mik]

Hi Marcelo,

Sorry for the confusion, I'm not native speaker so maybe I don't used the right words. Let me try again.

- I would like to be able to chose in B1 from a drop down list (data validation > list from named range) and/or I would like to be able to fill in value into the A1 cell and dependent on that fill the B1 with "low", "mid", "high"...
- I would like this functionality to be available any time and vice versa...

The main question is - can I have a cell which is fed by some formula and at the same time the same cell have a drop down list (data validation > list > named range)? Because when I create the formula, then apply the validation is OK until I chose some data manually from the drop down and the formula get lost... Is there a way how to remain both? May be not and this can not be done in excel at all... I don't know...

 

[Marcelo Branco]

I think this is not possible without VBA

M.

 

[Marcelo Branco]

A possible solution using VBA

Create a range that contains
Low
Mid
High
and name it List

Put this code in the Sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 1 Then
        If Target = "" Then
            Target.Offset(, 1) = ""
            Exit Sub
        End If
        If IsNumeric(Target) Then
            Target.Offset(, 1).Validation.Delete
            Select Case Target
                Case Is < 1
                    MsgBox "Value in" & Target.Address & "must be greater than 0"
                    Target.Offset(, 1) = ""
                Case Is >= 201: Target.Offset(, 1) = "High"
                Case Is >= 101: Target.Offset(, 1) = "Mid"
                Case Else: Target.Offset(, 1) = "Low"
            End Select
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 2 Then
        If Target.Offset(, -1) = "" Then
            With Target.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=" & "[COLOR=#0000ff]List[/COLOR]"
            End With
        End If
    End If
End Sub

Hope this helps

M.