Validation issue.

[Nimit Mehta]

I want to have a custom validation formula for the
following sheet.

Minutes Line
45 D3
32 V1
23 V1
D2
32 V1
D2

THis is a long coloumn with about 100 vertical entries of
minutes currently being consumed by customers.
D1,D2,D3,D4 ( 4 lines of Dialpad.com),V1,V2,V3,V4 ( 4
lines of VOIP device. ) are my 8 calling lines
respectively.
If the cell in coloumn A is empty next to it written D2 in
Coloumn B, it means call is in progress on line D2. I want
to have a validation that if call on line D2 is already in
progress i cannot enter D2 in coloumn B by mistake. Once
the Cell in coloumn A is filled with minutes, D2 should be
available for entry.
Thank You.

 

[JE McGimpsey]

One way:

Select B2:B65536 and enter this validation:


=IF(B2="D2", SUMPRODUCT(--($A$2:$A$65536=""),
--($B$2:$B$65536="D2"))=1, TRUE)

 

[Guest]

This wont work, because sumproduct "any value" multipled
by "D2" gives error message.

 

[JE McGimpsey]

Did you try it? Works find here with the test data shown.

Why do you think the SUMPRODUCT() is multiplying "any value" by "D2"?

What error message?