+ Reply to Thread
Results 1 to 7 of 7

Document Tracker - IF / AND Formalae

  1. #1
    Registered User
    Join Date
    05-11-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    Document Tracker - IF / AND Formalae

    Hello,

    I'm trying to create a simple document tracker,
    It has a number of columns with Yes / No statements which track the document through its creation, issue and return from the client.

    (F7) is document created status field

    I'm using a cell to track the status of these fields
    (M7) =AND(F7="Yes",G7="Yes", H7="Yes", I7="Yes", J7="Yes", K7="Yes",L7="Yes")
    so the cell displays a 'True' or 'False' statement, (this shows the document as closed out / finished)

    I'm hiding this cel and using another to display the same information but as a Yes / No statement
    (N7) =IF(M7=TRUE, "Yes", "No")

    I then have 2x cells counting the number of Yes / No from the N7 column,

    The problem i have is if i copy the formula into cells that have empty or no statements in F7, N7 will show a No statement and the counter will still add it to the tally (so if 20x rows had the formula but i only had 10x docs of which 2x were closed, it would display 18x open on the tracker).

    Is it possible to have an 'AND' statement in N7 that looks at F7 so that if it is showing "No" or "[BLANK]" then the cell is empty and the counter works as intended.
    Or if there's another simpler way to do this im open to suggestions.

    Any help is appreciated

    Richard.
    Attached Files Attached Files
    Last edited by Ricardo_Diez; 05-11-2017 at 09:59 AM.

  2. #2
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Document Tracker - IF / AND Formalae

    Hi there - would you be kind enough to attach the file so we can work from there?

    Go Advanced > Manage Attachments

    Thanks
    If myself or others have helped, please add to our reputation by pressing the 'Star' icon below this.

  3. #3
    Registered User
    Join Date
    05-11-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    Re: Document Tracker - IF / AND Formalae

    Post updated with example worksheet showing where ive got to so far

  4. #4
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Document Tracker - IF / AND Formalae

    See attached.

    Many thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-11-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    Re: Document Tracker - IF / AND Formalae

    Hi Luke,

    Thanks for that, i tweaked the code slightly

    M Column
    =IF(AND(F15="",G15="",H15="",I15="",J15="",K15="",L15=""),"",IF(AND(F15="Yes",G15="Yes",H15="Yes",I15="Yes",J15="Yes",K15="Yes",L15="Yes"),"True","False"))
    =IF(AND(F16=" ",G16=" ",H16=" ",I16=" ",J16=" ",K16=" ",L16=" ")," ",IF(AND(F16="Yes",G16="Yes",H16="Yes",I16="Yes",J16="Yes",K16="Yes",L16="Yes"),"True","False"))

    N Column
    =IF(M15="","",IF(M15="TRUE","Yes","No"))
    =IF(M16=" "," ",IF(M16="TRUE","Yes","No"))

    For the drop list to work i had put a space in the cell, so the "" command doesn't work, it needed to be " ".

    Thanks
    Richard

  6. #6
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Document Tracker - IF / AND Formalae

    Glad I could help - as long as you have what you need now.

    If you feel I have helped, please consider adding to my reputation

    many thanks

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Document Tracker - IF / AND Formalae

    Why not .???

    =IF(COUNTIF($F7:$L7,"")=7,"",IF(COUNTIF($F7:$L7,"Yes")=7,"True","False"))

+ 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. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  2. System idle time tracker / Break Tracker
    By reetika05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-10-2014, 07:26 AM
  3. macro or formalae to do soend bucketization
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2013, 09:48 AM
  4. Document Repository/Tracker Scorecard
    By neox in forum Excel General
    Replies: 0
    Last Post: 01-30-2013, 07:03 AM
  5. time tracker-a running tracker date wise
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2008, 03:08 PM
  6. Replies: 5
    Last Post: 10-01-2008, 08:30 AM
  7. document tracker using Excel not Access
    By boyds_5 in forum Excel General
    Replies: 0
    Last Post: 04-09-2005, 02:06 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