How to select all

[AhmedRady]

Dears,
i create drop list by using data validation + used Vlookup (VLOOKUP(cell of drop list;the below range;2;FALSE) :

[TABLE="width: 500"]
<tbody>[TR]
[TD]E[/TD]
[TD]drop list ( i need used "All" )[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]vlookup ( i need all numbers appears )[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl65"]

[/TD]
[/TR]
</tbody>[/TABLE]
it's working fine ((( when i select A number 1 appears and so on ))) but i need when select ALL (all data in column 2 appears )


[TABLE="width: 500"]
<tbody>[TR]
[TD]all[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhmedRady]

Any one can help

 

[DanteAmor]

When you select "All" you want all the numbers to appear.
You must explain exactly how all the information is:
- Where is the drop-list
- What type of drop-list, is a data validation
- Where are the data of the drop-list
- Where are the data and their respective number
- And where do you want the result?
- Can it be with a macro?

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[AhmedRady]

- Appreciate your note maybe because i still beginner so i can't ask the question in the right way

-Where is the drop-list
in the same sheet column C row 2
-What type of drop-list, is a data validation
yes
- Where are the data of the drop-list
in the same sheet 2 columns (A2:A7, B2:B6)
- Where are the data and their respective number

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]code[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]data validation- list (A column is the source)[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD](VLOOKUP(cell of drop list;A2:B7;2;FALSE)[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]all[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

- And where do you want the result?
in the cells under C2 ( under- list of data validation )
- Can it be with a macro?
yes by anyway

 

[DanteAmor]

- Appreciate your note maybe because i still beginner so i can't ask the question in the right way

-Where is the drop-list
in the same sheet column C row 2
-What type of drop-list, is a data validation
yes
- Where are the data of the drop-list
in the same sheet 2 columns (A2:A7, B2:B6)
- Where are the data and their respective number

- And where do you want the result?
in the cells under C2 ( under- list of data validation )
- Can it be with a macro?
yes by anyway

Put the following code in the events of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "C2" Then
        If Target.Count > 1 Then Exit Sub
        Range("C3:C" & Rows.Count).ClearContents
        If LCase(Target.Value) = LCase("all") Then
            x = Range("B2", Range("B" & Rows.Count).End(xlUp)).Rows.Count
            Range("C3").Resize(x).Value = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
        Else
            Set f = Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                Target.Offset(1).Value = f.Offset(, 1).Value
            End If
        End If
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


--------

My file test:
https://www.dropbox.com/s/tosj3m7uaf2e67c/test all.xlsm?dl=0

 

[AhmedRady]

thanks Very much , it's working fine
how you solve it . i hope to be at that level which enable my implement my ideas

 

[DanteAmor]

thanks Very much , it's working fine
how you solve it . i hope to be at that level which enable my implement my ideas

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "C2" Then
        If Target.Count > 1 Then Exit Sub

'clear area
        Range("C3:C" & Rows.Count).ClearContents

'if selected "all"
        If LCase(Target.Value) = LCase("all") Then

'copy data from range B2-down to c3
            x = Range("B2", Range("B" & Rows.Count).End(xlUp)).Rows.Count
            Range("C3").Resize(x).Value = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
        Else

'another selection, I use the find method and I take the data from the right
            Set f = Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                Target.Offset(1).Value = f.Offset(, 1).Value
            End If
        End If
    End If
End Sub