Need Help on INDIRECT function!

[mgesh2002]

Dear Friends,

I am trying to fix an issue on the attached Excel file.

I am preparing this excel file for a Farm manager who will be updating daily farm activities.

Issue 1:
Sheet1!A1 has a drop down, if I change the value in A1, the values in Sheet1!B2:F6 should change. I have applied INDIRECT function to do the same.
The values are coming from Sheet2 and these are named ranges(A_1,A_2,A_3).
I am able to get the values for the first selection in the drop down(A_1) but the remaining drop down values are not working.

Issue 2:
Also when I select a value on Cell("A1"), the bottom table should be filtered according to the selected value. For example, if I select A_1 then the table should show details corresponding to A_1 .

Could you please direct me in the right way to solve these issues.

All your valuable time and efforts are highly appreciated.

Thanks,
Murugesh

 

[NARAYANK991]

Hi ,

See if your first problem has been resolved.

Narayan

 

[Chihiro]

Issue 2: You can use advanced filter. Provided that you add Header in "A1".
Each time you change dropdown. You can click on "Advanced filter" and it will adjust filter.

See attached for example.

To automate it you will need VBA.

 

[mgesh2002]

Hi ,

See if your first problem has been resolved.

Narayan

Yes Narayan. The first issue has been resolved. Thank you very much!

Now I am looking forward for a solution to the second issue.

Thanks,
Murugesh

 

[mgesh2002]

Issue 2: You can use advanced filter. Provided that you add Header in "A1".
Each time you change dropdown. You can click on "Advanced filter" and it will adjust filter.

See attached for example.

To automate it you will need VBA.

Issue 2: You can use advanced filter. Provided that you add Header in "A1".
Each time you change dropdown. You can click on "Advanced filter" and it will adjust filter.

See attached for example.

To automate it you will need VBA.

Hi Chihiro,

Thank you for the time you spent on my request. The Advanced Filter option works fine with only the existing record. If we add another record on the table then we need to change range on the List Range option.

The person who's going to use this Excel file will not have much experience on Excel. Hence it would be difficult to choose the Advanced Filter option.

So, can you please suggest me on the VBA code to attain the result?

I really appreciate all your valuable time.

Thanks,
Murugesh
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Here you go. As you change dropdown, filtered item will change. It will auto adjust range. To show all items, delete entry in A2.

The code is assigned to WorkSheet Change event on "Field" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim fRange As Range
'Only fire when Cell A2 changes
If Not Intersect(Target, Range("A2")) Is Nothing Then
    'Clear previously applied filter
    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
    'Find last row with data
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    'Use advanced filter to filter data in place
    'A1 contains exact match of criteria column header
    'Cell A2 is the actual criteria filtered on
    Set fRange = Range("A9:L" & lRow)
    fRange.AdvancedFilter xlFilterInPlace, Range("A1:A2"), , False
End If
End Sub