Matching data & display in dropdown

[paradise]

Hi,

I have a list of data in sheet1 and the expected solution to be required in another sheet being highlighted.I have simply used vlookup.Momentarily it is displaying only one data without dropdown.I want in dropdown multiple options.

Pls find attached file.

 

[Nebu]

Hi:

Find the attached.

Note: sort the data in sheet1

Thanks

 

[paradise]

The problem of mine is that I cannot sort it.The data in Sheet1 in B & C column will go on increasing daily.Hence I cannot do the sorting work.

Therefore,the kindly revise the formula accordingly including the condition as stated.

 

[PCosta87]

Hi,

A slightly different approach (please refer to attachment)

Hope this helps

 

[Nebu]

Hi:

Pull a pivot as shown on sheet2 and refresh it every time you add new data to your list the pivot will sort itself and you can use the same formula I suggested before.

Thanks

 

[paradise]

Hi,

A slightly different approach (please refer to attachment)

Hope this helps

No its not working when I add further data in Sheet1 B & C column as it goes on increasing day by day,in next sheet it is not updating in E column as well as in subsequent in D column its not working.Pls note D4 is not static,it goes on increasing D4,D5,D6 & so on.... and accordingly E4,E5,E6 & so on... should go on updating the list which is not been done here.

 

[paradise]

Hi:

Pull a pivot as shown on sheet2 and refresh it every time you add new data to your list the pivot will sort itself and you can use the same formula I suggested before.

Thanks

Thanx for the response.My data is actually large,I am expecting without any help column or method.Hope it would ease the work of mine going To & Fro.

 

[Nebu]

Well. I have selected the source data for the pivot only for the range you have provided in the sample file. You will have to increase the range of source data for your pivot if you want the pivot to update(you can even convert your range into a table and assign that table as pivot data source .Also increase the range for the Offset formula in your data validation, it will work fine. I am assuming that you have basic knowledge in excel and understand the concept of range.

Edit: If you are looking for solution without helper columns or pivot , you will have to resort to VBA, but I feel you really don't need vba for this simple query.

Thanks

 

[paradise]

yeah changing range in not a big issue.I will be also welcome for vba too so that no additional sheet is made which is done in this case.As stated earlier my data is large more than 5000 rows & goes on increasing due to which problem arise.

 

[Nebu]

Hi:

Use the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

If Not Intersect(Target, Range("E4:E" & Sheet3.Cells(Rows.Count, 4).End(xlUp).Row)) Is Nothing Then

Dim rng As Range, c As Range
Dim col As New Collection

MyList = vbNullString
i& = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Sheet2.Range("B1:B" & i)
j& = 1
k& = ActiveCell.Row

For Each c In rng
    If c.Value = Sheet3.Range("D" & k).Value Then
        col.Add Sheet2.Cells(c.Row, 3)
        MyList = MyList & col(j) & ","
            With Sheet3.Range("E" & k).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=MyList
            End With
        j = j + 1
    End If
Next

End If

Application.ScreenUpdating = True
End Sub

Thanks