Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 2nd 04, 11:29 AM
Arvi Laanemets
 
Posts: n/a
Default 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)




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 16th 04 11:07 PM
Named dynamic ranges, copied worksheets and graph source data 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.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017