Problem with blank values in control/check table

[Benjaminmin]

Hi,

I am building a table for staffing purposes, and I basically only need to controls to "validate"/approve the table.

1. The number of staff put in must equal the required amount of staff (E column)
2. The same staff cannot be put on twice for the same time period (which I have done by the OR(F7=G7,F7=H7,G7=H7) as part of the validation)

My problem is that sometimes I need 0 staff (when its closed), but when I leave it blank obviously it wont approve the table because of rule #2 above, since it thinks the blanks are the same staff member.

I have tried with some ISBLANK statements, but it wont work.

Any input is highly appreciated!

Thanks

 

[timfritsch]

In cell I7, try: =IF(OR(COUNTIF(F7:H7,"*")<>E7,AND(E7>0,F7=G7,F7=H7, G7=H7)),"Fail","Ok")


Tim

 

[Benjaminmin]

In cell I7, try: =IF(OR(COUNTIF(F7:H7,"*")<>E7,AND(E7>0,F7=G7,F7=H7, G7=H7)),"Fail","Ok")


Tim

Hi Tim,

Thank you for your answer.

That certainly solves the problem about the blank cells, but it doesnt seem to pick up an error if I have the same name twice for some reason.

Best

 

[Benjaminmin]

Hi Again,

I modified my string to: =IF(AND(E7=0,COUNTIF(F7:H7,"*")=0),"OK",IF(OR(COUNTIF(F7:H7,"*")<>E7,F7=G7,F7=H7,G7=H7),"FEJL","OK"))

That solves the problem about the blank cells, and checks for same name twice. HOWEVER, for some reason I can't figure out, it does not work when the staffing count is set to 1 i.e. if it is set to one and I put one name, it still returns "fail".

Any ideas are much appreciated.

Thanks for you help

 

[Benjaminmin]

Sorry for the multi-posting, I managed to solve it now, with the following (very messy) code:

=IF(AND(E7=0,COUNTIF(F7:H7,"*")=0),"OK",IF(AND(E7=1,COUNTIF(F7:H7,"*")=1),"OK",IF(OR(COUNTIF(F7:H7,"*")<>E7,F7=G7,F7=H7,G7=H7),"FEJL","OK")))

If anyone has a nicer/cleaner way to do this I am still very interested in seeing it

Thanks anyway