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
fficeffice" /><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>
fficeffice" /><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>
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
></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 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
></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>
