dynamic validation range
Posted by Charles Parsons on March 27, 2001 9:09 AM
I have a list of some 500 names in Column A which are alphabetized. In Cell B1 I want to insert one of the names beginnig with A. In B2 I want to insert a name beginning with B.... In B26, insert one name beginning with Z. Since speed and spelling count, I want to offer a list by using validation, so that Cell B1 presents a list of just the names beginning with A,...,Cell B26 shows a list of names beginning with Z. If I were doing this only once, I would have no problem hand coding the appropriate ranges from Column A into each of the 26 validation setup windows. However, names will regularly be added or removed from the master list in Column A (which can be resorted immediately). Then the original offerings in the validation lists are no longer quite right. Can a macro automatically change the range that validation looks for?
Posted by mseyf on March 27, 2001 11:44 AM
this may be the long, difficult way, but unless someone has another method:
you can use dynamic range names as long as column A is sorted (assuming data starts in cell A1).
start out by going to Insert>Name>Define
in the 'Names in workbook' enter something like RangeA. in the refers to column enter:
=OFFSET(Sheet1!$A$1,0,0,MATCH("b*",Sheet1!$A:$A,0)-1,1)
the ranges for B thru Y will be a little different
for RangeB enter:
=OFFSET(Sheet1!$A$1,MATCH("b*",Sheet1!$A:$A,0)-1,0,MATCH("c*",Sheet1!$A:$A,0)-MATCH("b*",Sheet1!$A:$A,0),1)
for RangeC enter:
=OFFSET(Sheet1!$A$1,MATCH("c*",Sheet1!$A:$A,0)-1,0,MATCH("d*",Sheet1!$A:$A,0)-MATCH("c*",Sheet1!$A:$A,0),1)
note how the letters advance each month.
when you get to RangeZ, the formula is a little different:
=OFFSET(Sheet1!$A$1,MATCH("z*",Sheet2!$A:$A,0)-1,0,COUNTA(Sheet1!$A:$A,0)-MATCH("z*",Sheet1!$A:$A,0),1)
Use Ctrl-C and Ctrl-V to cut and paste. This will take a bit of time to set up, but you shouldn't have to change it once it is set up.
Good luck
Mark
Posted by mseyf on March 27, 2001 11:46 AM
all sheet references in the example should be Sheet1
Mark
Posted by Aladin Akyurek on March 27, 2001 12:32 PM
=================
Charles
What follows is heavy artillery.
Name the first sheet INPUT, the second MASTER, and the third BLACKBOARD.
The column A on MASTER contains the names of interest from A1 on.
Activate the option Insert|Name|Define, enter NAMES for Names in workbook and the following formula for Refers to:
=OFFSET(MASTER!$A$1,0,0,COUNTA(MASTER!$A:$A),1)
On BLACKBOARD, enter the alphabet in capitals in column A from A1 on.
In B1 array-enter:
=SUM(IF(UPPER(LEFT(NAMES,1))=A1,1,0))
and copy down this to B2:B26.
In C1 enter: =B1
In C2 enter: =IF(B2,B2+C1,0)
and copy down the latter formula to C3:C26.
In D1 enter: =IF(B1,"MASTER!$A$1:$A$"&C1,"")
in D2 enter: =IF(B2,"MASTER!$A$"&B2&":$A$"&C2,"")
and copy down the latter formula to D3:D26.
Activate C1 and name it 'Anames' via the name box, name C2 'Bnames', C3 "Cnames", so on.
On INPUT, enter in column A from A1 on the alphabet in capitals.
Activate cell B1 and the option Data|Validation. Select "List" on Settings tab. And enter as Source:
=Anames
Iterate the same proces for cells B2 to B26, with as source Bnames, Cnames... until done.
Note 1. Array-entering a formula means hitting CONTROL+SHIFT+ENTER at the same time to enter the formula.
Note 2. You can add/remove names in column A on MASTER at will, as long as you keep them sorted in ascending order.
Note 3. Nothing in the above machinery requires you enter the names in any particular case on MASTER.
Aladin
Posted by Aladin Akyurek on March 27, 2001 1:31 PM
Erratum...
The formula to be entered in D2 on BLACKBOARD should be:
=IF(B2,"MASTER!$A$"&MAX($C$1:C1)+1&":$A$"&MAX($C$1:C1)+B2,"")
If interested, you can get the workbook thru e-mail.
Posted by Aladin Akyurek on March 27, 2001 2:44 PM
Revised formulas on BLACKBOARD
This is embarrassing, but I have to make the necessary corrections.
On BLACKBOARD
In C2 enter:
=B2+MAX($C$1:C1) [ copy down as far as needed ]
In D2 enter:
=IF(B2,"MASTER!$A$"&C1+1&":$A$"&C2,"") [ copy down as far as needed ]
On INPUT
=INDIRECT(Anames) [ not just =Anames ]
====================
Posted by Charles Parsons on March 29, 2001 3:22 AM
Thank you AA and Mark
Gentlemen:
Learning that the validation window will accept "=Rangename" is one of those Homer Simpson "Do-oh!" moments. The code to adjust the ranges, however, is clearly a different story, and the fact that you created it within hours is awesome. Bravo!
BTW: AA, thank you for your offer, but I get what you are saying.
Charlie
