Validation function with helper column
I have 4 columns in a sheet. First column contains Ac/No of client. Second
contains conference calling line, weather unlimited or limited typed as U or
L. Third contains minutes client used. Fourth contains countries, like USA,
Canada, UK, AUS, NZ etc.
I use unlimited U calling lines for calling USA, UK and Canada and limited
L ( pre-paid card ) for ALL other countries¦.--- ( including USA, UK and
Canada )
A B C D
1 45921 U 51 USA
2 81345 L NZ
3 34821 U 2 CANADA
4 42301 U UK
5 34510 U 12 USA
6 36210 L AUS
Since limited L is more costly than unlimited U ( for usa, uk, and canada )
I want to restrict use of limited L until all 4 unlimited U are occupied.
I think I need to make some helper column and then use some validation
formula but cant make it work.
In above example, only 1 U is occupied right now ( B4 ) and 3 are free. (
Since there is only 1 cell next to U thats empty. ). So I should NOT be able
to enter L in column B right now IF country in D is either USA, UK or Canda.
TIA
|