Validation list based on cell value

[drom]

Hi and Thanks in advance!

If I put within the yellow cell (C3) the value 40:

• I would like to have in C4 (blue cell) a dynamic validation updated to show me the possibilities BBB, RRR and CCC (See E3 and F3)

If I put within the yellow cell (C3) the value 15:

• My Validation list should allow me to get only the value AAA (See E4 and F)

If I put within the yellow cell (C3) the value 78:

• I would like to have in C4 (blue cell) a dynamic validation updated to show me the possibilities XXX and YYY (See E5 and F5)

[

Modelo 01.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2					If I put:
3		CAPITAL			40	Show me in a validation list in C4 (Blue Cell) BBB, RRR and CCC
4		DISCOUNT VALIDATION			15	Show me in a validation list in C4 (Blue Cell) only AAA
5					78	Show me in a validation list in C4 (Blue Cell) XXX, and YYY
6		CAPITAL	Discount
7		10	AAA
8		20	BBB
9		20	RRR
10		20	CCC
11		50	XXX
12		50	YYY
13
14
15
16
Sheet2

 

[skillilea]

Here is a good video that shows you how to do what you are trying to accomplish.
It uses indirect. Make your column headings your 40,15,78

 

[drom]

Yes I know this
But I am Using values and my yellow cell will not match the Capital
I will like to get the previous ones not the next ones
SO ig I put 40, XXX, YYY will be wrong
for the value 40 I need BBB, RRR and RRR
In my case I have to use the table as shown in my post range("B6:"C12")

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in C4 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Value
        Case Is = 40
            With Range("C4").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="BBB,RRR,CCC"
            End With
        Case Is = 15
            With Range("C4").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="AAA"
            End With
        Case Is = 78
            With Range("C4").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="XXX,YYY"
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

 

[drom]

Hi and Thanks
I do not want to use macros
I know hot to get this done using macros, creating one array of possibilities and populating the datalist

But I guess there is a way of doing this using, Match, indirect, offset and or ???

 

[mumps]

I believe that if you want to use a validation list, you would need to use a macro like the one I suggested.