If and and or question

[Sneaky Pete]

Hello all,

I've got a question about the if, and, and or function.
This function has to look in 2 cells for a value and then give a specific result.
I will explain this by a example.

Part 1
In cell A1 the user has to choose from a listbox for a value A, B, C or D.
In cell A2 the user can also choose from a listbox for a value 1, 2.
Ok, if the user select in cell A1 A and in cell B1 1, then it should return a list 100, 200, 300, 400 and 500. If the value in cell B2 is 2 then it should return a list of 600, 700, 800, 900.

Part 2
When the user select B in cell A1 and 1 in B2, the it should return a list of 110, 210, 310, 410 or 510. If the value in cell B1 is 2 then it should return a list of 610, 710, 810, 910.

I've been trying this with a if(and(or)) function.
Only for the first part it works, when I put part 2 to the formula (to create a nested function) it gives a error like you got to many variables for this function. ?
Eventually I want the formula to return these values as input for further calculations.

Hope you can give me a solution.

Thanx in advance.

Sneaky Pete

 

[tlowry]

Welcome to Mr. Excel!

Here is some code that does what I think you want.

Function aGetList() As Variant
    Dim Getlist
    aGetList = Array("#N/A")
    Dim cat: cat = Join(Array(Range("A1"), Range("b1")), ",")
    Select Case cat
        Case "A,1"
            aGetList = Array("100", "200", "300", "400", "500")
        Case "A,2"
             aGetList = Array("600", "700", "800", "900")
        Case "B,1"
             aGetList = Array("110", "210", "310", "410", "510")
        Case "B,2"
             aGetList = Array("610", "710", "810", "910")
        Case Else
    End Select
End Function
Sub x()
    Debug.Print Join(aGetList(), vbCrLf)
End Sub

then it should return a list

I'm not sure what list you wanted. "aGetList" returns an array (a sort of list) It may be used to add tp CustomList.

 

[Sneaky Pete]

Thanx tlowry,

Didn't thought about using vba.
I'm going to try this.

 

[Sneaky Pete]

tlowry,

The Select Case Method should be the input for a data validation list in cell G4.
How can I create this?

Thanx in advance.

Sneaky Pete

 

[Eric W]

What are the remaining values you want for C1, C2, D1, D2?

 

[tlowry]

Here is some more code.

Feature added: Validation list for G4.

Sub setVal()
    Dim getlst
    getlst = aGetList()
    Select Case getlst(0)
        Case "#N/A"
            With Range("G4")
                .Validation.Delete
                .Value = "#N/A"
            End With
        Case Else
            With Range("G4").Validation
                .Delete
                Range("G4") = ""
                .Add Type:=xlValidateList, Formula1:=Join(getlst, ",")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
    End Select
End Sub
Function aGetList() As Variant
    Dim Getlist
    aGetList = Array("#N/A")
    Dim cat: cat = Join(Array(Range("A1"), Range("b1")), ",")
    Select Case cat
        Case "A,1"
            aGetList = Array("100", "200", "300", "400", "500")
        Case "A,2"
             aGetList = Array("600", "700", "800", "900")
        Case "B,1"
             aGetList = Array("110", "210", "310", "410", "510")
        Case "B,2"
             aGetList = Array("610", "710", "810", "910")
        Case Else
    End Select
End Function

 

[Sneaky Pete]

Thanx for the input!

When I use the code it only gives the value #N/A in cell G4.
It doesn't give a Validation list in cell G4.

Hope you can give me a solution to fix this.

Thanx in advance.

Sneaky Pete

 

[tlowry]

Works for me...

What data are in cells A1 and B1 (case matters here) when you run setval?

If you can see the image below, this is what I have:

or:

	A	B
1	B	2
2
3

<tbody>
</tbody>

Sheet1​

Please post what you have in the cells A1 and B1 and the procedure you are using to install, and run, this code; and the code as well.

 

[Eric W]

Depending on what other values you want, you can probably just use formulas without VBA. For example:

	A	B	C	D	E	F	G	H
1	B	2
2
3
4								610
5								710
6								810
7								910

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H4 =LOOKUP(A1,{"A","B","C","D"},{100,110,120,130})+LOOKUP(B1,{1,2},{0,500}) H5 =H4+100 H6 =H5+100 H7 =H6+100 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formulas in H4:H7 generate the values you want for A1, A2, B1, B2, and I guessed for C1, C2, D1, D2, but I can adapt that when you tell me what you want. You can also change the H4:H7 location to somewhere else if you want. Then just set up your data validation in G4 to a list with source H4:H7 and you have what you want.

 

[Sneaky Pete]

Thanx tlowry,

I made a typing error, so the code works!
To get it work you have to select the macro each time.
I would like to change the value of the validation list in G4 automatic when the value in cell A and/of B changes.

Finally the same formula has to work for the Range A26:A40 and B26:B40.

Hope you can give me a solution to manage this.

Thanx in advance.

Sneaky Pete