Dynamic Validation Lists
I am using XL 2002. In my workbook I have a sheet that stores the values
used for validation lists. One such list is SalesReps. This is a dynamic list. If I use a named range for the values in the list, and the list either grows or shrinks, I will need to use a formula for the RefersTo property so that when this list is referred to in data validation it will be accurate. Does anyone know how to code this or what formula I could use to make the RefersTo dynamic? TIA, -- Joe VBA Automation/VB/C++/Web and DB development |
Dynamic Validation Lists
Hey
I have build an dynamic validation list by using the lookup-formula in the cells wich are in use for the validation. By building stuctures like this: Level 1 contains the main-categories (example 1. Course 2. HelpDesk 3. Development .... etc Level 2 contains the underdoing issues like: 1. Excel Advanced 2. Word MOUSexpert .... etc By using lookup on the value in level 1, my formula respons the level 2 ussues in the range. If this means any sence to your problem, I can make a specific sample and mail it to you Regards Nils Morten Blakstad ConsEasy -----Original Message----- I am using XL 2002. In my workbook I have a sheet that stores the values used for validation lists. One such list is SalesReps. This is a dynamic list. If I use a named range for the values in the list, and the list either grows or shrinks, I will need to use a formula for the RefersTo property so that when this list is referred to in data validation it will be accurate. Does anyone know how to code this or what formula I could use to make the RefersTo dynamic? TIA, -- Joe VBA Automation/VB/C++/Web and DB development . |
Dynamic Validation Lists
Joe
Debra Dalgleish has instructions for this at......... http://www.contextures.on.ca/xlNames01.html#Dynamic Also check out her info on Data Validation lists. http://www.contextures.on.ca/xlDataVal01.html Gord Dibben Excel MVP On Tue, 17 Aug 2004 10:03:01 -0700, Joe wrote: I am using XL 2002. In my workbook I have a sheet that stores the values used for validation lists. One such list is SalesReps. This is a dynamic list. If I use a named range for the values in the list, and the list either grows or shrinks, I will need to use a formula for the RefersTo property so that when this list is referred to in data validation it will be accurate. Does anyone know how to code this or what formula I could use to make the RefersTo dynamic? TIA, |
Dynamic Validation Lists
Use the offset function to define a dynamic range.
Say your data is on Sheet1, A1:A10 From Insert:Name:Define: Fill in your data's Name and then in the REFERS TO Use =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) -----Original Message----- Nils, This isn't quite what I am looking for. The problem that I need to solve is this: If I have a list of 5 items in a column and I name the range that contains the columns, myNamedRange, when I add a value to the list (so that now I have 6 items), how do I dynamically update the RefersTo property of the myNamedRange to include the newest value in the list? Also if the list is reduced to four items, how do I dynamically update the RefersTo property of myNamedRange to only include the four items and not show a blank for the deleted fifth item? Thanks, Joe " wrote: Hey I have build an dynamic validation list by using the lookup-formula in the cells wich are in use for the validation. By building stuctures like this: Level 1 contains the main-categories (example 1. Course 2. HelpDesk 3. Development .... etc Level 2 contains the underdoing issues like: 1. Excel Advanced 2. Word MOUSexpert .... etc By using lookup on the value in level 1, my formula respons the level 2 ussues in the range. If this means any sence to your problem, I can make a specific sample and mail it to you Regards Nils Morten Blakstad ConsEasy -----Original Message----- I am using XL 2002. In my workbook I have a sheet that stores the values used for validation lists. One such list is SalesReps. This is a dynamic list. If I use a named range for the values in the list, and the list either grows or shrinks, I will need to use a formula for the RefersTo property so that when this list is referred to in data validation it will be accurate. Does anyone know how to code this or what formula I could use to make the RefersTo dynamic? TIA, -- Joe VBA Automation/VB/C++/Web and DB development . . |
All times are GMT +1. The time now is 04:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com