none
Accessing the dropdown menu of a table from vba RRS feed

  • Question

  • I am working with a table of patient data that changes quarterly.

    Each patient is categorized according to process type: Inpatient, outpatient, or Emergency

    There is an additonal category containing the name of the department the patient was seen - ex oncology

    I need to be able to sum the number of patients seen in each process and department.

    I had thought of just filtering for each and summing the rows, but the problem is the table is generated quarterly and so theres no way for me to know what process or department types will be in the table.

    I am wondering is there a way to reference the drop down menu on these columns and have a loop that will filter for each process type and then an additional loop to filter for each department? That way I don't have to know what processes and departments are in the table.

    The other piece is this program is being written in excel 2003.

    Wednesday, August 8, 2012 7:30 PM

All replies

  • Just assume the worst.

    For example, say the patient ID is in column A, the process type is in column B and the department is in column C.  In a given quarter, we don't know if anyone was served by obgyn, but it does not matter.  In some cell enter:

    =COUNTIF(C:C,"obgyn")

    If the values happens to be zero, just report it.

    Repeat the formula for all departments.  The same approach for the three process types:

    =COUNTIF(B:B,"Inpatient")
    =COUNTIF(B:B,"Outpatient")
    =COUNTIF(B:B,"Emergency")

    gsnu201208

    Wednesday, August 8, 2012 11:30 PM
    Moderator
  • I am wondering is there a way to reference the drop down menu on these columns and have a loop that will filter for each process type and then an additional loop to filter for each department? That way I don't have to know what processes and departments are in the table.

    The other piece is this program is being written in excel 2003.

    I realize that you are asking for VBA code to do this but it can be done very simply with a Pivot Table. They are not difficult to use and while the example below is produced in Excel 2010, you would be able to obtain a similar output in Excel 2003. (Assuming of course that I have interpretted you question correctly for the setup of your source data.)

    Would this suit your purposes?


    Regards, OssieMac

    Thursday, August 9, 2012 3:08 AM
  • Dear Feliz1,

    Have you applied the above suggestions.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 17, 2012 1:59 PM
    Answerer