Need some help with acouple of things with excel

[Adrcon3]

HI.
I'm preaty new to excel and i have a problem that i can't seem to solve.
here is my setup:
I have 1 file userlist.xls that contais 100 different names(the number of users will change over time as more register). Also i have a mainSheet.xlt which contais 1 listBox with a range A2 to A101. In these cells(a2-a101) i have the following:
='C:\Documents and Settings\xp\Desktop\[userlist.xls]Sheet1'!D2
='C:\Documents and Settings\xp\Desktop\[userlist.xls]Sheet1'!D3 and so on untill A101.
what i have done is expand this list to A500 now as well as the listbox range but since all the cells contain the link above they are not considered Blank cells therefore listing 400 white spaces(Note: Ignore blanks option is checked).
Does anyone have any Idea how can i get around this problem?
Thanks in Advance

PS: i have tryed the following:
check B2 for example, =if(Len(A1)<>1,A1,"") but it still detects B2 as None Blank cell

 

[acw]

Hi

Try creating a dynamic defined name in mainsheet.xlt.

Name: Lister
refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTIF(Sheet1!$A:$A,">0"),1)

You can then have the listfill range for your listbox as the defined name lister.


HTH

Tony

 

[Adrcon3]

Thanks for the reply but as i said i'm a beginer and well i did create a defined name in my mainsheet (insertet>Name>Define.. right?)
but i don't know how to applyed it as my range.
What i did: click on listbox than Dara>Validation... Under "Source:" i typed =Lister . dunno if thats right or not but i get an error that says "The Source returns an error. Do u wish to continue."

 

[acw]

Hi

What sort of listbox do you have? Is it from the control toolbox or forms toolbar, or are you in a cell, and selecting data, validation?

If the last, then the allow should be list, then the source should be =lister.

To check that there isn't a problem with the defined name, go insert, name, define, highlight the name lister, then select the icon at the end of the refers to box. This should take you to the range that the name covers.

Tony

 

[Adrcon3]

yeah thats what i did and it won't work
here is an example what i have http://adriansdesigns.freehostia.com/example.zip
[/url]

 

[Adrcon3]

Never mind i figured it out. Thanks a ton man