+ Reply to Thread
Results 1 to 18 of 18

Spreadsheet to count outcomes of another spreadsheet every week

  1. #1
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

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

    Need to see your "Master" workbook.

  5. #5
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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?

  7. #7
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    yes that is correct and column A is blank

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    Please Login or Register  to view this content.
    Last edited by jindon; 05-21-2019 at 06:46 PM. Reason: Missed condition for SQL string

  9. #9
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    Thank you works great
    Last edited by mtwa1990; 05-22-2019 at 03:32 PM.

  10. #10
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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.

  12. #12
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    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

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

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

  14. #14
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    here is the search document explaining the results
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

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

  16. #16
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    Hope these help
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,931

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    Change to
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Spreadsheet to count outcomes of another spreadsheet every week

    brilliant stuff! thank you for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting On Large Spreadsheet - 4 possible outcomes
    By meseleto in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-23-2019, 01:39 PM
  2. VBA: Need to create reusable spreadsheet to track data per week for each week of year
    By TiffanieB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2018, 04:35 AM
  3. Replies: 3
    Last Post: 04-07-2018, 04:02 AM
  4. Macro to reset spreadsheet after a week
    By Geogeo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-09-2015, 11:16 AM
  5. [SOLVED] One spreadsheet requiring 3 different outcomes
    By RobinPrice in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-26-2014, 04:53 AM
  6. Macro to create new week spreadsheet
    By Mac521 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2008, 01:40 AM
  7. pick up data from a spreadsheet with a different name each week
    By inno101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2008, 06:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1