Excel worksheet change or display list of data based on given cell text

[harinsh]

Hello Team,

I am planning to build one dashboard using work sheet change but not sure how to work with this option. My objective is to display list of fields based one cell value.

For example in A2 cell if you select "Fruit" then below automatically list of fruits should reflect and it should either refer some range or defined in the code. If I select vegetables then it should reflect only vegetables.

Which option to use to get this kind of requirement please suggest me.

Thank you,

 

[My Aswer Is This]

This can be done.
So you enter Fruit in A2 and you want a list of Fruits entered in the cells below A2

So where do you have this list of Fruits now?
And where is this list of Vegetables now?

You need to say Fruits are now in column F starting in row(1)

You need to say Vegetable are now in column G starting in row(1)

Or Fruits are in a named range named Fruits
​

 

[harinsh]

Hi ...Thanks for your reply...here I attached picture URL and this is how it should display ....

https://ibb.co/kSJHLT

 

[My Aswer Is This]

Hi ...Thanks for your reply...here I attached picture URL and this is how it should display ....

https://ibb.co/kSJHLT

I never click on links. Are you not able to tell me in words what you want.

I asked where can we find these lists of items.
Seems like a simple answer to me.

 

[harinsh]

Hi,

This item can be anywhere in the workbook but I need to get the list below validation cell. For example ...A2 has selection then my list should reflect in A4 onwards ....not sure how to paste excel data in Mr.Excel query box

 

[My Aswer Is This]

Not sure what your wanting.

Do you want a script to search through your entire workbook looking for fruits and list them all under range A2?
Well how do you think the script could do this?

There are probable hundreds of fruits and how the script would know what words are fruits or vegetables would be a task I would not know how to do.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[harinsh]

I am sorry if I confused with my query ...I might need to put my question in ontherwords....when I say fruits ..the data or table only contains fruits ...when I select the drop down through validation ...only those needs to reflect...similarly vegitables....no need to search excel entire sheet or workbook.

assume you have data in sheet 2 with list of fruits and vegetables in column A and column B respectively and in sheet 1 if you access via validation ... in cell A1 then from A2 list should reflect ....

 

[My Aswer Is This]

Try this:
You must enter either Fruit or Vegetables into Sheet(1) Range A1

This script will copy the values in Column A or Column B into column A of Sheet 1

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Sheet1 tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/29/18 4:40 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim Lastrow As Long
Dim Lastrowa As Long
Dim Lastrowb As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
Sheets(1).Cells(2, 1).Resize(Lastrow).ClearContents
    If Target.Value = "Fruit" Then Sheets(2).Cells(2, 1).Resize(Lastrowa).Copy Sheets(1).Cells(2, 1)
    If Target.Value = "Vegetables" Then Sheets(2).Cells(2, 2).Resize(Lastrowb).Copy Sheets(1).Cells(2, 1)
End If
Exit Sub
M:
MsgBox "You did not enter ""Fruit"" Or ""Vegetables"" in  Range(""A1""))"
End Sub

 

[harinsh]

Hey Superb...this is what I need.....I would like to know when values get display in Sheet 1 based on Sheet 2 list...is there any way to bring same format of the cells (example color of font, size, etc...").....Thank for your help

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/30/18 6:50 AM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim Lastrow As Long
Dim Lastrowa As Long
Dim Lastrowb As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
Sheets(1).Cells(2, 1).Resize(Lastrow).Clear
    If Target.Value = "Fruit" Then Sheets(2).Cells(2, 1).Resize(Lastrowa).Copy Sheets(1).Cells(2, 1)
    If Target.Value = "Vegetables" Then Sheets(2).Cells(2, 2).Resize(Lastrowb).Copy Sheets(1).Cells(2, 1)
End If
Exit Sub
M:
MsgBox "You did not enter ""Fruit"" Or ""Vegetables"" in  Range(""A1""))"
End Sub