Need the '15 cells above' reference function I was looking at OFFSET, or INDEX maybe? But not sure.
There are 100 drop-downs going from E17 -> E116. Each one has the potential to draw from one of three tables, depending on how far down the list they are and what is chosen for the drop-downs above. drop-downs up to 4, 8, or 16 cells above, can influence whether or not you can choose a new item in the current drop-down.
Example I've been using in data validation that I'd like to clean up and make more crisp:
=IF(AND(C25="",C26="",C27="",C28="",B17<>"714-16",B17<>"715-16",B21<>"714-8",B21<>"715-8",B21<>1158,B21<>"714-16",B21<>"715-16"),Block3_4,"")
This line is from a separate section that also is affected by the adjacent column. The B cells are merged in groups of 4's as the items that go in them take up four spaces when used. The B's portion is what I'd be using except instead of looking at one cell, I want it to look at a range of cells, since the section these will go into are not merged. 715's are zone expanders on a fire alarm panel. they come in 4, 8, and 16 zone sizes. 714's are security expanders for the same system and also come in 4, 8, and 16 zone sizes. They can be addressed to any address in the 100 zone expansion bay, and when assigned, eat the 3, 7, or 15 following zone addresses so nothing else can be assigned to those consequent zones. The zone list is vertical, with zone descriptions in the next column. If a 714 is assigned to E22, for example, E23, E24, and E25 would not be able to be assigned as anything because they are the 2nd,3rd, and 4th zone on that expander. I need the data validation for each drop-down to check the previous 15 cells for 714-16 and 715-16, and the previous 7 cells for 714-8, or 715-8, or 1158 (wireless zone expander...) and so on... I could probably sit down and write out a REALLY long IF(AND(),.,.) formula, except that half of the users who will be using this spreadsheet are using openoffice or some other free spreadsheet program. So VBA, and more than a single nested IF are not going to work... Any ideas?
Bookmarks