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,

 

[harinsh]

Hey awesome!!!!!!!!! I could this see one single column list in second sheet if I want to increase the column ...for example if I want to get two columns "A" & "B" ...I could see you defined in your code Cells(2, 1) in case range selection what should modify in the code? Please suggest.....your help really great solution for me....

 

[My Aswer Is This]

So are you saying if you enter Fruit in A1 you will see a list of fruits in Sheet 1 column A
And if you enter Vegetables in B1 you will see a list of Vegetables in Sheet 1 Column B

So you will never enter vegetable in A1
And will never enter Fruit in B1

If this is not what your asking please explain more.

 

[harinsh]

for example if you enter Fruit in A1 then I should get Furit name along with price in column b similarly if I select Vegetable then I should get the Vegetable list beside price in next column...for example below...

You have list of prices in sheet 2 column B = > to be more specific ....in sheet 2 column A contains fruits list, column B contains Fruits prices ....column C contains Vegetables name, and column D contains Vegetables prices ...

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
[TD="width: 64"] Fruits -
[/TD]
[TD="width: 64"]Select
[/TD]

[TD="class: xl65"]Fruits[/TD]
[TD="class: xl65"]Price
[/TD]

[TD="class: xl66"]Mango
[/TD]
[TD="class: xl66, align: right"]25
[/TD]

[TD="class: xl66"]Banana
[/TD]
[TD="class: xl66, align: right"]54
[/TD]

[TD="class: xl66"]Papaya[/TD]
[TD="class: xl66, align: right"]68[/TD]

[TD="class: xl66"]Grapes[/TD]
[TD="class: xl66, align: right"]12
[/TD]

</tbody>

 

[My Aswer Is This]

Try this:

'Modified 6/30/18 9:15 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
Sheets(1).Cells(2, 1).Resize(Lastrow, 4).Clear
If Target.Value = "Fruit" Then Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
If Target.Value = "Vegetables" Then Lastrow = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row

    If Target.Value = "Fruit" Then Sheets(2).Cells(2, 1).Resize(Lastrow, 2).Copy Sheets(1).Cells(2, 1)
    
    If Target.Value = "Vegetables" Then Sheets(2).Cells(2, 3).Resize(Lastrow, 2).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]

Hi ..its working perfect...I just want to know one more last thing ...if you could help ..so that I can customize the data for effect use of this solution....if same data in sheet2 instead of column ...if its in rows...means ....Sheet 2 data A column till 10 rows pertains to Fruits and from 12th row onwards vegetables ..how can I define the fixed range instead of last row.....

Thank you ...this might be last question for this tread ...your really superb....

 

[My Aswer Is This]

So are you saying you want to define a fixed Range as Fruit and a fixed range as Vegetables.

And then if you choose Fruit in A1 the range named fruit will be put in A2 and below

And I guess you want the prices put in column B

So now if you choose vegetables what do you want to happen. Will Fruit still stay in Column A or should they be removed

And if you want both Fruit and Veg in column A.

So after doing that what will happen the next time you choose Fruit
So nothing now will be in sheet 1 column C and D is that correct?

If this is not what you wanting then explain so my small brain can understand

 

[My Aswer Is This]

Since it sounds like you are working on a plan.
Would it be possible it will not always be Fruit and Vegetables.

Maybe later it will be Meat and Dairy and other things.

So then I would write the script a different way

I would tell the script;

What ever is in Range A1 then copy this range and put in column A

So if Meat was in A1 The defined range named Meat would be copied in column A

The script would put the named range Meat in column A one row below the last value in Column A

 

[harinsh]

Thanks again ...I am waiting to see your reply ....see when I select Fruit then only Fruit name and price should populate from A2 (A column => Name & B column => Price) and when I select Vegetable only Vegetable (A => Name & B column => Price) should populate.

Your code is working but my question was only I want to define the range because the Fruit and Vegetable want to define in the same column in Sheet 1 (tomorrow this list will not change) hence I can bring some more columns which is common for both items.. if require in future.....

Need only Range instead of selecting entire column ....

So are you saying you want to define a fixed Range as Fruit and a fixed range as Vegetables.

And then if you choose Fruit in A1 the range named fruit will be put in A2 and below

And I guess you want the prices put in column B

So now if you choose vegetables what do you want to happen. Will Fruit still stay in Column A or should they be removed

And if you want both Fruit and Veg in column A.

So after doing that what will happen the next time you choose Fruit
So nothing now will be in sheet 1 column C and D is that correct?

If this is not what you wanting then explain so my small brain can understand

 

[My Aswer Is This]

No I do not understand.

Are you saying the last script I sent you is not doing what you wanted or are you saying your changing what you want.

I thought you said

Fruits were in column A of sheet 2 and fruit prices were in column B of sheet 2
My script looks in sheet 2 starting in row 2 and copies all the data in that column to sheet 1

Are you saying now you do not want it to copy all the data in that column?
So if not how does the script know when to stop.


And the same with column A to B and for Vegetable C to D

 

[harinsh]

instead of entire column can we define the range...is it something possible? When I say define range ....for example in sheet 2 column A contains 20 rows so, when I select in Sheet 1 cell A1 ...only those 20 rows should populate ...in your last code entire column will populate ....if second product or item if its start from 23 row to till 30 row ..then only those 7 items should populate ...assume first one Fruit and second one Vegetables.....

The reason why I am asking this .....my row header same for both product and if want to add in columns or increase the column ...it would help me in future ....when we define last row can't we define ...from cell to end cell ...like A2:A20 ....