1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Formula Help Please, Excel 2007

Discussion in 'Business Applications' started by irvinbang, Feb 22, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Ok here is what I have and what I'm asking for. I hope that I have explained it all and asking the right questions.

    I have the QC spreadsheet and the Inspector Spread sheet that I will be working on here.
    There is a “Q Chart” spreadsheet that has a chart for all the corrections that are filtered from the QC spreadsheet.

    The “I chart” is going to be able to, once the code is made, to be able to filter the Inspectors name and then selecting the month in B3:B23 will auto populate the corrections for that inspector for that date and total them out accordingly.

    The Inspecor!C25:S25 will populate the “I Chart” once filtered out per inspector.

    QC Spreadsheet:
    K3: Will have a Due Date. It will always be the 13th of the month.
    • ]This date will be typed in manually.
    L3: Will have a name First name with the letter then last name always.
    • The names will be in a Data Validation list.
    R:AG ; Will have errors put into those range of cells.
    • These cells have Conditional formatting.
    AJ3:11 ; Have the total for that row of errors.
    R14:AG14 ; Will have the total for that column when filtered.
    • That’s all for QC Spreadsheet.

    Inspector Spreadsheet:
    A3:23 ; Will have inspector names as the same as QCL3.
    B3:23 ; Will have dates that you can pick from a Data Validation list.
    C3 Will have this code: =IF(B3=AG4,COUNTIFS(QC!$L3:L13,"T. Daniel",QC!$K3:K13,"01/13/2013"),IF(B3=AG5,COUNTIFS(QC!$L3:L13,"T. Daniel",QC!$K3:K13,"02/13/2013"),IF(B3=AG6,COUNTIFS(QC!$L3:L13,"T. Daniel",QC!$K3:K13,"03/13/2013"))))
    • I would like to have a way to shorten this formula if possible, Because once I add in more months to the formula it will be very long.
    • Meaning if I pick a date in B3:56 it will look in QC!L3:L13 for the Name, then look in QC!K3:K13 for the date and put the number of inspections for that Inspector name and date in C3:23, depending on what inspector row you’re in.
    • Now whenever you click a date in B3:B23 I want it to put the total errors from inspections of that inspector on the date chosen to auto populate the errors from the QC Spreadsheet page in the Inspector Spreadsheet D3:S3 for that inspector.

    Any ideas and help are very much appreciated and a big thanks ahead of time. Please let me know if there are any questions.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    cant you just use
    =COUNTIFS(QC!$L3:L13,A3,QC!$K3:K13,B3)

    so it looks in the cell A3 for the name and then looks in B3 for the date - rather than use a lookup table for the different dates ?
     
  3. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Yes that answers this question:
    Looks like i was trying to over think that formula.

    Any idea about it populating the corrections from QC onto the Inspector page?
     
  4. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    To better ask the question let me break it down for just one cell. Then i can tweak the other cells to work.

    Inspection Spreadsheet:

    A3 has inspector name
    B3 has date
    C4 has Competed inspections for A3 (name) and B3 (date)
    D3 i want to look in QC!L3:L13 for the name in A3, then look in QC1K3:K13 for the date in B3, then add up all the numbers that are left in QC!R3:R13
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    does this work?
    Code:
    =SUMIFS(QC!$R$3:$R$11,QC!$L$3:$L$11,A3,QC!$K$3:$K$11,B3)
    
     
  6. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    yeah works like that. i guess i was just putting them in the wrong order. Thanks alot this makes it alot easier.
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090655

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice