Creating a custom filter using a drop down menu using Index Function

[VB_Noob]

Hi All,

I'm creating a custom filter where it lists the data based on the criteria selection in a pull down menu. The custom filter seems to be working when all the data is in the same sheet.

But now I want to move the source data into sheet2 and the pull down menu and show filtered data on sheet1. I'm using the index function and it is now working. Please take a look at the IFERROR INDEX function below and let me know what is wrong?

IFERROR(INDEX(Sheet2!C2:E21,C3,COLUMNS($C$3:C3)),"")

I got this sample code from the internet and is there a better way to create a custom filter?

 

[Khalid NGO]

Hi,

Can you please post a sample file with some data and expected result?

Regards,

 

[VB_Noob]

Hi All,

Please find attached file. Basically I want to see the form at the top of the form to display the data record per my input of the filter on the top right. Once the form shows the data and it should allow me to update the record using the form. I don't want the simple table layout because it is not very easy to browse and view when there will be up to 100 data fields in the sheet. Is there a way to do this?

Thanks

 

[Marc L]

Hi !

Easier is to just add a filter on Patient ID column of source table !
(no needs any form, no needs any update procedure, no needs …)

Or directly use the Patient ID B3 cell to load data
and think about an update button.
Better is to separate form and data table to different worksheets …
​

 

[VB_Noob]

A filter on patient ID column of source table is not something I want. Because there will be up to 100 data fields in that table. And some of them are long text field while other data fields need to be formulated to spits out data based on input data of adjacent cells. I don't want to have a very long horizontal table.

Also I want to minimized number of sheets within a workbook as there will be many more sheets to be created within a workbook.

 

[Marc L]

As a beginner starter (you must complete the code for some form cells)
paste this code to the worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$N$3" Then
            V = Application.Match(Target.Value, Range("A21", [A20].End(xlDown)), 0)
            Application.EnableEvents = False
        If IsNumeric(V) Then
            V = 20 + V
            [B3].Value = Target.Value
            [D3].Value = Cells(V, 2).Value
            [F3].Value = Cells(V, 3).Value
       
        Else
            If Target.Value > "" Then Beep
            [B3,D3,F3,H3,B5,B7,B9,B11].Value = ""
        End If
            Application.EnableEvents = True
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[VB_Noob]

Thanks for your code Marc!
So you are making a loop with counter of 20? If I have more data fields, then I would need to increase that 20 to whatever the number of time of the loops?

I still want to be able to update the date using the form. Is it possible?

 

[Marc L]

A loop ‼ Where ?! (Not the best idea to use any loop here …)
Just using the MATCH Excel worksheet function.

To update you just have to code the reverse of worksheet Change event
via a specific button … So first achieve this event.
​

 

[VB_Noob]

Isn't the following code generates a loop for 20 times?
If IsNumeric(V) Then
V = 20 + V
'Then assigning value from button table to the cells of top form?
[B3].value - Cells (V, 2).Value

So I won't be able to update the data using the form on the top of the sheet without a "Update" button, right?

 

[VB_Noob]

Sorry, forgot to attach my file.

 

[Marc L]

Nope, no loop ‼ (worse idea !)
Just read about MATCH function in Excel inner help …
Instead of this function you can use the Find method
to see in VBA inner help.

To update, you can watch out cell address in the same Change event
so without any button …
​

 

[VB_Noob]

I get an error message stating "End If without block if". I see I have four if and four end if. What's wrong.

Also the code is not working. The code supposed to check if cell "D3" of the top table changed its value. If so, then "patient name" field of the table at the button of the sheet will change to "D3" of the table at the top of the sheet. I've also attached file. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$N$3" Then
            V = Application.Match(Target.Value, Range("A21", [A20].End(xlDown)), 0)
            Application.EnableEvents = False
        If IsNumeric(V) Then
            V = 20 + V
            [B3].Value = Target.Value
            [D3].Value = Cells(V, 2).Value
            [F3].Value = Cells(V, 3).Value
            [H3].Value = Cells(V, 4).Value
            [B5].Value = Cells(V, 5).Value
            [B7].Value = Cells(V, 6).Value
            [B9].Value = Cells(V, 7).Value
            [B11].Value = Cells(V, 8).Value
            Else
            If Not Intersect(Target, Range("D3")) Is Nothing Then
                Cells(V, 2).Value = [D3].Value
               
               
           
         
        Else
            If Target.Value > "" Then Beep
            [B3,D3,F3,H3,B5,B7,B9,B11].Value = ""
            Application.EnableEvents = True
            End If
            End If
        End If
    End If
       
       
   
   
End Sub

 

[Marc L]

Well, you did not understand any logic
as the code still watches only N3 cell !
Your error comes from If / End If block not respected …

As you also had some difficulties with the easy MATCH function to catch
the source ID row, the next demonstration uses the VBA Find method,
maybe a bit more difficult but easy once you read its VBA inner help
and there is nothing to mod (just use it like it is !) :​

Private Sub Worksheet_Change(ByVal Target As Range)
  Const C = "B3,D3,F3,H3,B5,B7,B9,B11,N3"
    Dim V, Rg As Range
    V = Application.Match(Target.Address(False, False), Split(C, ","), 0)
    If IsError(V) Then Exit Sub
    Application.EnableEvents = False
Select Case V
    Case 2 To 8
      If [B3].Value > "" Then Set Rg = Me.UsedRange.Columns(1).Find([B3].Value, , xlValues, xlWhole)
        If Rg Is Nothing Then
            Beep
            Range(C).Value = ""
        Else
            Rg(1, V).Value = Target.Value
            Set Rg = Nothing
        End If
    Case 9
      If Target.Value > "" Then Set Rg = Me.UsedRange.Columns(1).Find(Target.Value, , xlValues, xlWhole)
        If Rg Is Nothing Then
            If Target.Value > "" Then Beep
            Range(C).Value = ""
        Else
            [B3:B11].Value = Application.Transpose(Array(Rg, "", Rg(1, 5), "", Rg(1, 6), "", Rg(1, 7), "", Rg(1, 8)))
             [D3:H3].Value = Array(Rg(1, 2), [E3], Rg(1, 3), [G3], Rg(1, 4))
                    Set Rg = Nothing
        End If
End Select
    Application.EnableEvents = True
End Sub

You should click on bottom right Like !
​

 

[VB_Noob]

Thanks. I will need to do some reading on the functions.

Is VBA inner help a section on Microsoft website?

 

[Marc L]

As Match & Transpose are Excel worksheet functions
to see in the Excel help and not within the VBA one …

« inner » means the help inside Excel VBA !
And you can find the same help on MSDN website …
​

 

[VB_Noob]

I'm still trying to mod the first program that you provided to give me what I need. I don't want to jump into the more complicated Find method before I can understand the first program. The range("A21", [A20].End9xlDown)),0) basically tells the program where to look for the source data and match with the data of the target value? A21 is the column and A20 through end of the sheet as the row?

 

[Marc L]

As A21 is a cell …
The [A20].End(xlDown) is when on worksheet you select cell A20
then you hit the combo keys Ctrl and down arrow
so operating this manually what is the new active cell ?
​

 

[VB_Noob]

The last cell which contains a value (text or numeric).

 

[VB_Noob]

I apologize if my questions sound stupid as I am trying to understand the codes and do not want to use it without understanding it. I have a few questions below. Please correct if I misunderstood the code..

Application.EnableEvents = False - I guess it is a good habit to use this to make sure no codes get to executed by accident? Why is this statement appears right after the below code? Wouldn't it stop executing the code?

V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)
The code will take the value of target address (cell: N3) and search it in the records that are located from row A21 to the last record on the sheet. I noticed there is no mention of column location. The zero means the code doesn't specify the number of returned result? What if I put 1 or 2 instead of 0?

If IsNumeric(V) Then
V = 20 + V
[B3].Value = Target.Value
[D3].Value = Cells(V, 2).Value
[F3].Value = Cells(V, 3).Value
-Above code is to check whether the variable V contain any numeric value or text value (string)? If V contains a numeric value, then change the value of V to 20 and use it as the location of the row (A20). The code then copies the value of cell (Row 20 and column 2) and paste it to cell B3, and repeat the operation for the rest of rows and columns.

Else If Target.Value > "" Then Beep
[B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""
- Above code basically says if the target value is not empty, then listed cells value should assign to nothing?? I'm not understand this.....

And I also noticed the first IF statement is written before application.enableevent=false. Therefore, an end if is not needed for that IF statement? There are only two "end if" while total three If statements in the program.

 

[Marc L]

The last cell which contains a value (text or numeric).

Yes !


Now it's about my initial code, not about your mod :

Application.EnableEvents = False - I guess it is a good habit to use this to make sure no codes get to executed by accident? Why is this statement appears right after the below code? Wouldn't it stop executing the code?

The procedure as a worksheet event is called when a cell value changes,
disabling events before changing any other cell is necessary to avoid
another call of this same event, like a snake trying to bite its own end …

As Match & Transpose are Excel worksheet functions
to see in the Excel help and not within the VBA one …

V = Application.Match(Target.Value, Range("A21", [A20].End(xlDown)), 0)
The code will take the value of target address (cell: N3) and search it in the records that are located from row A21 to the last record on the sheet. I noticed there is no mention of column location. The zero means the code doesn't specify the number of returned result? What if I put 1 or 2 instead of 0?

Wrong ! Of course there is a column location : A21 …
As it's the MATCH worksheet function so just read why 0 in Excel help.
So you'll see why with any other value than 0 this function may fail …

If IsNumeric(V) Then
V = 20 + V
[B3].Value = Target.Value
[D3].Value = Cells(V, 2).Value
[F3].Value = Cells(V, 3).Value
-Above code is to check whether the variable V contain any numeric value or text value (string)? If V contains a numeric value, then change the value of V to 20 and use it as the location of the row (A20). The code then copies the value of cell (Row 20 and column 2) and paste it to cell B3, and repeat the operation for the rest of rows and columns.

After reading MATCH Excel help you'll understand
this function returns only a numeric value when the value matches
and an error when the value is not found …
Just train with a MATCH formula directly within a cell.
And V is not change to 20 !
Place a break point in code (via F9 key) and when the code stops
you can progress in code codeline by codeline via F8 key
and see the variable value in VBE Locals window.
As V must finally contain the found ID row # …

Else
If Target.Value > "" Then Beep
[B3,D3,F3,H3,B5,B7,B9,B11].Value = ""
- Above code basically says if the target value is not empty, then listed cells value should assign to nothing?

Wrong ! Just if Target is not empty so sounds a beep …

And I also noticed the first IF statement is written before application.enableevent=false. Therefore, an end if is not needed for that IF statement? There are only two "end if" while total three If statements in the program.

Yes as it is very not necessary to do anything if the condition is false …
The reason why my second demonstration is more efficient.
There are 2 syntaxes of If statement as you can see in VBA help,
a single codeline (without End If) and
a block of codelines (If … Then as first codeline and End If as last) …
​

 

[VB_Noob]

For some reason the step into feature doesn't work for me. I inserted the break point in the code and tried click on "step into" under Debug or by pressing "F8" key. It gives me a beep sound.