MsOf10 check daily production using formula & cond. format-VBForums
Results 1 to 3 of 3

Thread: check daily production using formula & cond. format

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2011
    Posts
    133

    check daily production using formula & cond. format

    based on my data entry, i'm creating a report to check what was my daily production and if there is no production then mark the day as red. here is the formula:
    =IF(SUMIF(B9:B73,AZ9,E9:E73)<>0,SUMIF(B9:B73,AZ9,E9:E73),"")

    where:
    B9:B73 is product manufacturing date
    formula is checking AZ9 (date here) then checking E9:E73 for number of item manufactured - based on this i'm getting total items for that date in cell BB9

    here i'm also using conditional format to mark cell red if there is no production on that date. Conditional formula is: =LEN(BB9)=0 then mark red

    i have 2 issues here:
    1 - i need to get 0 in BB9 if there is no production
    2 - i have set a daily target to 200 and checking the difference in next cell BC9 that what was difference on this date. Using this formula on BC9: =IF(BB9="","",BB9-BH5) but i'm getting the cell blank if it is receiving 0 from BB9 but i need to see -200 here

    I think if somehow we can get 0 in BB9 (as mentioned in issue 1) then BC9 (issue 2) will resolved automatically.

    need help please. thanks.

  2. #2
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: check daily production using formula & cond. format

    you have an idea that has some milage, but your application is a bit off

    you need to think about what you are setting up and shat you are testing

    its not just the where but the contents that matter

    in the setting stage you are testing for somthing that is not 0 and summing if that is the case and outputting a string (null) equivelant

    you are then testing for the contents of bb9 to see if it has "NO LENGTH"

    but for some reason you want to put 0 into it the contents "zero" has length equal to 1

    i think there is a fault in the thought process here...

    you then jump back into the bb9 should be empty ship and test appropriately, your code then works perfectly and does not return a value as per instructions!

    the question must be.. if 2 out of 3 are fine, why do you want bb9 to be 0 (ever)?

    hope that helps ( more if needed )

  3. #3
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: check daily production using formula & cond. format

    oops brain funk...

    didn't get the english comprehension correct in your last post ( thinking of someone else issues at the same time)

    you can always put o instead of "" in the setting part
    and change the conditional format to if=0

    hope that helps

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.