How to select Exact values under Dependent Rows?

[Jyotirmaya]

for example
I have two Sheets "DATA" "RAW DATA"

In DATA sheets

[TABLE="width: 500"]
<tbody>[TR]
[TD]
APPLE[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]16.5[/TD]
[/TR]
[TR]
[TD]BANANA
[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]111[/TD]
[/TR]
</tbody>[/TABLE]

In RAW DATA sheet in Column B I have to input APPLE/BANANA/ORANGE in various rows.

I want that if in B2 I have entered APPLE then in C2 I can enter only 10 or 15 or 16.5 if anything else I will enter then I will get error message that enter correct value. Similarly If I will enter Banana in column B of Raw Data sheet then I can only input values 35 or 20 or 36 in the column C

SO what will be the code for Column C of Raw Data sheet to do so ?​

 

[MickG]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Try this:-
Based on "Data" sheet Column "A" has "Fruits, and Column "B" Numbers.
Place this code in In sheet "Raw Data"
Right click sheet "Raw Data" tab, select "View Code", vbwindow appears , Paste code in window.
Close Vbwindow.
To run code Click any cell in sheet "Raw Data" Column "C" with a fruit in column "B".
If that fruit exists in sheet "Data" a validation list will appear in the target cell with the appropriate numbers to select from.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, Dn As Range, nStr As String
If Target.Column = 3 And Target.Count = 1 Then
    With Sheets("Data")
        Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
      End With
            For Each Dn In Rng
                If UCase(Dn.Value) = UCase(Target.Offset(, -1)) Then
                    nStr = nStr & IIf(nStr = "", Dn.Offset(, 1).Value, "," & Dn.Offset(, 1).Value)
                End If
            Next Dn
If nStr <> "" Then
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
    End With
End If
End If
End Sub

 

[pgc01]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Hi

Define for each fruit a named range with the allowed values.

Then use the named range in the dependent data validation.

For ex., for C2, use in the list: =INDIRECT(B2)
Copy the data validation down.

Now if you select in B2 Apple, then you'll have in C2 a dropdown with only the values in the Apple named range, in your ex.: 20, 35 and 36.

 

[Jyotirmaya]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Try this:-
Based on "Data" sheet Column "A" has "Fruits, and Column "B" Numbers.
Place this code in In sheet "Raw Data"
Right click sheet "Raw Data" tab, select "View Code", vbwindow appears , Paste code in window.
Close Vbwindow.
To run code Click any cell in sheet "Raw Data" Column "C" with a fruit in column "B".
If that fruit exists in sheet "Data" a validation list will appear in the target cell with the appropriate numbers to select from.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, Dn As Range, nStr As String
If Target.Column = 3 And Target.Count = 1 Then
    With Sheets("Data")
        Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
      End With
            For Each Dn In Rng
                If UCase(Dn.Value) = UCase(Target.Offset(, -1)) Then
                    nStr = nStr & IIf(nStr = "", Dn.Offset(, 1).Value, "," & Dn.Offset(, 1).Value)
                End If
            Next Dn
If nStr <> "" Then
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
    End With
End If
End If
End Sub

HELLO DEAR, THANKS YOUR CODE IS GREAT BUT There are some problem for me with this code. I know Data validation but actually there are 50 Main categories are there such as APPLE BANANA ORANGE and there are 15000 values are associated with that. So while selecting the values out of 15000 will take time. & data are being entered manually but I am getting errors such as that Under APPLE row there are 10 15 & 16.5 are there but when I wrongly enter any value it accept the value. SO I want to eliminate this problem.

 

[Jyotirmaya]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Hi

Define for each fruit a named range with the allowed values.

Then use the named range in the dependent data validation.

For ex., for C2, use in the list: =INDIRECT(B2)
Copy the data validation down.

Now if you select in B2 Apple, then you'll have in C2 a dropdown with only the values in the Apple named range, in your ex.: 20, 35 and 36.

Sorry I dont know how to define with the range, kindly can you explain ?

 

[pgc01]

Re: Excel Help.. How to select Exact values under Dependent Rows?

OK.

Do A simple test.

In DATA!Z1:Z3 write 20, 35 and 36.

In Ribbon->Formulas->Name Manager press New
Enter
Name: Banana
Scope: Workbook
Refers to: =DATA!$Z$1:$Z$3

This creates the named range Banana

Now in 'RAW DATA'!C2 in the data validation enter in the list:

=INDIRECT(B2)

Write in 'RAW DATA'!B2: Banana
If you select C2 you'll see that you have a dropdown with only the values 20, 35 and 36.

Try this simple test.

Here's an explanation of dependent lists in data validation:

http://www.contextures.com/xlDataVal02.html

 

[Jyotirmaya]

Re: Excel Help.. How to select Exact values under Dependent Rows?

OK.

Do A simple test.

In DATA!Z1:Z3 write 20, 35 and 36.

In Ribbon->Formulas->Name Manager press New
Enter
Name: Banana
Scope: Workbook
Refers to: =DATA!$Z$1:$Z$3

This creates the named range Banana

Now in 'RAW DATA'!C2 in the data validation enter in the list:

=INDIRECT(B2)

Write in 'RAW DATA'!B2: Banana
If you select C2 you'll see that you have a dropdown with only the values 20, 35 and 36.

Try this simple test.

Here's an explanation of dependent lists in data validation:

http://www.contextures.com/xlDataVal02.html

YES THATS WORKING.. But I have 1000+ values to enter. Is there not any way except data validation to eliminate entering wrong values in the C2 ?

If there is not any other solution then in data validation can I get the cell values by using keystroke of keyboard ? for example I need to enter value 1000 from the list then I have to click or use Alt+Down then I need to press the key upto reaching 1000 which will take time.

 

[pgc01]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Hi

If you want to enter the values manually and just make sure they are correct,then in 'RAW DATA'!C2 in the data validation instead of the list use the custom option and enter the formula:


=MATCH(C2,INDIRECT(B2),0)

If you try to enter a value different from 15, 25 e 26 you'll get an error pop-up

Does it help?

 

[Jyotirmaya]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Hi

If you want to enter the values manually and just make sure they are correct,then in 'RAW DATA'!C2 in the data validation instead of the list use the custom option and enter the formula:


=MATCH(C2,INDIRECT(B2),0)

If you try to enter a value different from 15, 25 e 26 you'll get an error pop-up

Does it help?

Yes thats ok. How to expand the code ? now its working in C2 only.


I tried =MATCH(C:C,INDIRECT(B:B),0) that didnt work

 

[pgc01]

Re: Excel Help.. How to select Exact values under Dependent Rows?

Hi

- Copy C2
- Select C3:C100
- Paste Special -> Validation