Formula problem

[Robyn Bellanger]

I am trying to come up with a formula for the following:

I have a range of five cells in a column which are store numbers. The 5
cells represent Monday thru Friday (the labels of Mon thru Fri are in a
separate column and won't need to be a part of the formula).

example:

Monday 452 (c1)
Tuesday 453 (c2)
Wednesday 453 (c3)
Thursday 452 (c4)
Friday 453 (c5)

This is a schedule showing which store numbers a person is supposed to be in
on these days. The person above, however is assigned three stores: 452,
453, and 454. In the blank cell below this range (c6) I want to put a
formula that will flag with "Error" or "Conflict" if one of the stores is
not scheduled for the week. So the example above would flag that the person
is not scheduled in store 454 for the week.

Help??

 

[Rob]

Hi Robyn,

Just in case you don't get another answer, a really crude method would be
the following formula in c6. The problem I see is with this (and maybe
others), that it won't tell you more than one store number omitted.
=IF(ISNA(VLOOKUP(452,C1:C5,1,FALSE))=TRUE,452,IF(ISNA(VLOOKUP(453,C1:C5,1,FA
LSE))=TRUE,453,IF(ISNA(VLOOKUP(454,C1:C5,1,FALSE))=TRUE,454,)))
Hope someone else can do better!
Rob

 

[Domenic]

Not sure if this will do...

=IF(COUNT(C1:C5)=5,IF(SUMPRODUCT(--(ISNUMBER(MATCH({452,453,454},C1:C5,0))))<3,INDEX({452,453,454},MATCH(0,COUNTIF(C1:C5,{452,453,454}),0)),"Al
3 Stores Assigned"),"Entries Not Complete")

OR

=IF(COUNT(C1:C5)=5,IF(SUMPRODUCT(--(ISNUMBER(MATCH({452,453,454},C1:C5,0))))<3,"No
Scheduled for Stor
"&INDEX({452,453,454},MATCH(0,COUNTIF(C1:C5,{452,453,454}),0)),"All
Stores Assigned"),"Entires Not Complete")

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps

 

[Robyn Bellanger]

Neither worked. No matter what is entered as far as stores scheduled it
returns "Entries not complete"

 

[Robyn Bellanger]

Sorry, didn't work.
"Rob" wrote in message news:%[email protected]...

 

[Domenic]

Neither worked. No matter what is entered as far as stores schedule
it
returns "Entries not complete"

Even when all five entries are made?

Are you making sure that you enter the formula usin
CONTROL+SHIFT+ENTER, and not just ENTER

 

[Aladin Akyurek]

Either:

=IF(ISNUMBER(MATCH(FALSE,ISNUMBER(MATCH({452,453,454},C1:C5,0)),0)),"Conflict","OK")

which you need to confirm with control+shift+enter instead of just wit
enter.

Or:

=IF(ISNA(LOOKUP(2,1/(ISNA(MATCH({452,453,454},C1:C5,0))))),"OK","Conflict")

which is to be confirmed with enter.

 

[Robyn Bellanger]

Neither worked. No matter what is scheduled, it always shows "conflict"

 

[Robyn Bellanger]

Yes, I used Control+Shift+Enter and got "Entries not complete". Using your
formula, do the stores have to be scheduled in the order they appear in the
formula? If this is the case, it won't work. Sometimes one store will be
scheduled Tues, Wed and then next week, Mon, Fri and so on. The only thing
I care to flag is if a manager forgot to schedule a store.

 

[Domenic]

Using your
formula, do the stores have to be scheduled in the order they appear in
the
formula?

No, not at all.

Sometimes one store will be
scheduled Tues, Wed and then next week, Mon, Fri and so on.

That's fine. It doesn't matter which day of the week a store is
scheduled.

Once all five entries are completed, if there's a store that hasn't
been assigned for a day of the week, my formula will return the store
number. However, if there are two stores not assigned (I don't know if
that's a possibilitiy), only the first one it finds will be returned.
If all three stores have been assigned, the formula will return "All 3
Stores Assigned".

Having said that, not surprisingly, Aladin's formula works well and is
more efficient. I'm not sure why you're having problems, though.
Hopefully Aladin can shed some light...

 

[Aladin Akyurek]

Maybe there is an untol aspect of the problem you want to solve...

452
453
453
452
453

I get conflict.

When

452
453
453
452
454

I get OK.

Neither worked. No matter what is scheduled, it always show
"conflict"

"Aladin Akyurek" <[email protected]> wrot
in
message news:[email protected]...

Either:

=IF(ISNUMBER(MATCH(FALSE,ISNUMBER(MATCH({452,453,454},C1:C5,0)),0)),"Conflic
t","OK")

which you need to confirm with control+shift+enter instead of jus with

enter.

Or:

=IF(ISNA(LOOKUP(2,1/(ISNA(MATCH({452,453,454},C1:C5,0))))),"OK","Conflict")

which is to be confirmed with enter.

 

[Robyn Bellanger]

Apparently there is a problem in me trying to adjust the formula for use
with different store numbers in different ranges. I typed the formula in on
a blank spreadsheet exactly as you gave it to me and it works. However,
when I try to apply it to my original spreadsheet, using different store
numbers, and a range of e223:e227 it doesn't work. Any suggestions?

Maybe there is an untol aspect of the problem you want to solve...

452
453
453
452
453

I get conflict.

When

452
453
453
452
454

I get OK.

 

[Robyn Bellanger]

I finally got it! I had the numbers entered as text. Once I converted them
to a number, it worked great! Thanks for your help and patience

Robyn

Maybe there is an untol aspect of the problem you want to solve...

452
453
453
452
453

I get conflict.

When

452
453
453
452
454

I get OK.