Spreadsheet to count outcomes of another spreadsheet every week

ive been set a challenge to create an excel document that will show me how many Y and N for a certain person each week.

We have a master Excel document, called Master. in this document there are 3 columns, name, date, and y/n.

I need to create another excel document that will let me, using a form, type in a name and select a week to give me results on how many Y for that person and how many N in that week, from the master document.

I hope this makes sense, I have attached two documents that will hopefully help.

Martin

Delete current code in Userform code module and replace with the following.
This is assuming "Master.xlsx" is located in the same folder as "Search.xlsm", so you will need to change the folder path.

Please Login or Register  to view this content.

Thank You
It was working however no i have added more names it seems to have stopped working with the error runtime error 13 Type Mismatch, on debugging it happens when it gets to the below code.

rs.Open "Select * From `Sheet1$`", cn, 3
myList = rs.GetRows
Set cn = Nothing: Set rs = Nothing
End Sub

Can not replicate the issue, it is working fine when add/remove data.

Need to see your "Master" workbook.

because of confidential information I couldn't show you the exact document we use that I have been trying to copy the code into and had to change some of the values as Sheet1 is actually name Raw and the Name column is operator name. To make this easier to understand I have made a similar document that hopefully will help. probably would have been easier for me to have done this in the first place but thought I could just copy the code and change certain values.


The document is set out exactly as the document im using, so the headings for each column is in row 6 and i used the exact headings for the columns, I have then highlighted the 2 columns that would be used in the code.

Thank You
Martin

I don't care about confidentiality, you can still make it with dummy data, but the layout MUST be EXACTLY the same as your original.

Do I continue assuming ALL BLANK rows above row 6?

yes that is correct and column A is blank

Sorry one more thing, if you can would it be possible to add another column of total Y for that person and a Total N between weeks 1 and 52

Change "Search_Click" sub routine to the following code.
It gives you Total Y in B9 & Total N in B10, and if you want total, just use the formula like =sum(b9:b10) in B11.

Please Login or Register  to view this content.

that's great ill use that to but I need the total of all the Y's out of the master document for the person chosen.

For example Total Y's in that document for Martin only and all N's for Martin
so the result would look something like

Name Martin
Week 20
Y 26
N 34


Year to Date
Y 432
N 672

Those numbers are nothing to me.
If you upload a workbook with the data results the same, it would help.

here is the search document explaining the results

That doesn't really help.
I need to see "example.xlsx" with the data, so I can check the results.

Hope these help

Change to

Please Login or Register  to view this content.

brilliant stuff! thank you for your help