So it took some doing but I have managed to cobble together a working sample. This is from an earlier version of the workbook where I still had to work out some of the underlying functions.
fake.xlsm
The workbook is very codependent on a lot of things.. Yeah the underside is a bit messy but it does the job. The data tab is a repository used to populate all the semi variable data. Every week the source data changes and the user brings in the new source workbooks and runs the update sheet function which takes about 30 seconds to mine all the data from the three sheets. The newest version does all this automatically, the user just need to put the new workbooks in the same directory as the dueview workbook.
The sample file should be viewed from the DueView tab. The A1 dropdown list works. The sheet currently only gets data from the PM sheet. It took so long to sanitize the data while keeping some functionality that the A2 dropdown only works for the "PM" category. I had to take the criteria out of the formulas for A3 and A4 dependent drop downs as the named ranges got nuked in the sanitizing process. This is what the individual cell formula looked like before I removed the end part referencing A3 and A4.
PHP Code:
=IFERROR(HYPERLINK(CELL("address",INDEX(PM!$E$5:$E$3000,MATCH(1,IF(PM!$B$5:$B$3000=$G$3,IF(PM!$E$5:$E$3000=$C5,1))),0)),COUNTIFS(PM!$A$5:$A$3000,$A$1,PM!$B$5:$B$3000,$G$3,PM!$E$5:$E$3000,$C5,PM!$W$5:$W$3000,IF($A$3="ALL","*",$A$3),PM!$C$5:$C$3000,IF($A$4="ALL","*",$A$4))),0)
The C1 dropdown works but I only put one overdue date in the sample data to show the purpose of the selection. The C2 dropdown is looking at data in row 102. White text on white background.
Click on the ShowAllCells button on the right to see everything I am hiding.
If I had to push for one function it would be If a user clicks on cell O8 the script would pass values from C8 (Dates Column) O4 Type (this determines which sheet to search, PM, T1 or T2) as well as O3 for name. This is the tricky part thanks to my design stupidity of using merged cells because any cells selected in M,N,O columns need to look to M for name data...I think. I also need to pass the constants to the query of A1,A2 and A3 with ALL=* for A2 and A3.
O4 determines which sheet to search
C8 would be compared to column E
O3 would be compared to column B
A1 to column A
A3 to column W
A4 to column C
I would like to copy the results to the DueView tab starting at CL4 , looking for the first empty row and incrementing down one for every result. That way if a user click on two cells the second set of copied data would paste below the first set and so on.. It would be icing on the cake to be able to specify which columns to copy as there is a lot of useless data on the source sheets.
Is it possible to have the Worksheet_SelectionChange event targeted to a named non contiguous range? If so, I would probably run two versions of the above function as some of the columns on the T1/T2 sheets have data in different columns then the PM sheet.