Hello experts! I have a small problem that maybe one of you knowledgeable people might be able to help me with.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
></o
>
Let us assume that the current user logged in to Windows is jtrimmer. What it is that I am trying to accomplish is use data validation to call a named, formula-defined, dynamic range specific to the windows user that is logged on. I have the various list names formatted as username.list. Since the list length changes all the time, the list named "jtrimmer.list" (I assume) must be defined by a formula.
I used =OFFSET(Sheet3!$A$1,1,0,COUNTA(Sheet3!$A:$A)-1,1). <o
></o
>
<o
></o
>
<o
></o
>
<o
></o
>
The worksheet must know which user is currently active to select the correct list so the following was written:<o
></o
>
<o
></o
>
Now I have the username "jtrimmer" in cell Z1 called by the defined function =UserNameWindows()<o
></o
>
Now that I have the lists and user defined I want to use Data Validation to allow the appropriate list with the source =INDIRECT($Z$1&”.list”) but this is not working and I do not know why.<o
></o
>
<o
></o
>
This works fine if I use the name manager to define the list as static with the formula =Sheet3!$A$2:$A$4 and/or it works fine if I hardcode the data validation source with =jtrimmer.list but for this application the list must be dynamic and change when the user changes.<o
></o
>
<o
></o
>
Here is a sample of my worksheet lists:
Excel 2007
<o
></o
>
Does anyone know what I am doing wrong here? Or is this just a limitation to Excel's built in Data validation?<o
></o
><o
></o
>




<o


Let us assume that the current user logged in to Windows is jtrimmer. What it is that I am trying to accomplish is use data validation to call a named, formula-defined, dynamic range specific to the windows user that is logged on. I have the various list names formatted as username.list. Since the list length changes all the time, the list named "jtrimmer.list" (I assume) must be defined by a formula.
I used =OFFSET(Sheet3!$A$1,1,0,COUNTA(Sheet3!$A:$A)-1,1). <o


<o


<o


<o


The worksheet must know which user is currently active to select the correct list so the following was written:<o


Rich (BB code):
Function UserNameWindows() As String<o:p></o:p>
UserNameWindows = Environ("USERNAME")<o:p></o:p>
End Function<o:p></o:p>


Now I have the username "jtrimmer" in cell Z1 called by the defined function =UserNameWindows()<o


Now that I have the lists and user defined I want to use Data Validation to allow the appropriate list with the source =INDIRECT($Z$1&”.list”) but this is not working and I do not know why.<o


<o


This works fine if I use the name manager to define the list as static with the formula =Sheet3!$A$2:$A$4 and/or it works fine if I hardcode the data validation source with =jtrimmer.list but for this application the list must be dynamic and change when the user changes.<o


<o


Here is a sample of my worksheet lists:
Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | jtrimmer.List | bjones.List | jdoe.List | ||
2 | x-23 | x-23 | x-34 | ||
3 | x-34 | c-23 | x-45 | ||
4 | x-45 | x-21 | c-7 | ||
5 | x-43 | y-12 | |||
6 | c-54 | ||||
Sheet3 |
<o


Does anyone know what I am doing wrong here? Or is this just a limitation to Excel's built in Data validation?<o



