Remember Me?

#1
December 2nd 04, 11:29 AM
 Arvi Laanemets Posts: n/a
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?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post mikelee101 Charts and Charting in Excel 2 December 16th 04 11:07 PM WP Charts and Charting in Excel 1 November 28th 04 05:19 PM

All times are GMT +1. The time now is 05:14 PM.