Excel2K: Is it possible to use dynamic named ranges in custom data validation formula?
 

Hi

The data validation formula below (NB! Semicolons in formulas aren't
errors - they accord to my regional settings) works:
=(IF($B4="";0;SUMPRODUCT(--($B$2:$B$65536=$B4);--($C$2:$C$65536=$C4);--($D$2
:$D$65536<$D4);--($E$2:$E$65536$D4)))=0)

When I replace range references in SUMPRODUCT components with dynamic named
ranges
SheduleDate=OFFSET(Shedule!$B$2;;;COUNTIF(Shedule! $A:$A;"0");1)
SheduleRoom=OFFSET(Shedule!$C$2;;;COUNTIF(Shedule! $A:$A;"0");1)
SheduleFrom=OFFSET(Shedule!$D$2;;;COUNTIF(Shedule! $A:$A;"0");1)
SheduleTo=OFFSET(Shedule!$E$2;;;COUNTIF(Shedule!$A :$A;"0");1)
(in column A are numbered all rows from A2 and down to last entry in column
B).

i.e. the data validation formula will be
=(IF($B4="";0;SUMPRODUCT(--(SheduleDate=$B4);--(SheduleRoom=$C4);--(SheduleF
rom<$D4);--(SheduleTo$D4)))=0)

then data validation doesn't work anymore. Same formula in adjacent cell
returns value FALSE ???!!! It looks like validation formula isn't evaluated.

Has someone an explanation for such behaviour?
Thanks in advance!

--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)