updating cell with listbox

[Adrcon3]

Hi.
i have a list box "C4" (made from data>validation... option) which as a bunch of usernames. i also have another cell "F3" which has the following formula
=VLOOKUP(D11,INDIRECT("'C:\Documents and Settings\xp\Desktop\project\["&C4&".xls]Sheet1'!A1"),1,0)
in addition i have 2 excel files named "user1.xls" and user2.xls"

it works fine as it is. It retrives the cell A1 from which ever user is selected in the list box. The thing is that the file(user1.xls and user2.xls) needs to be opened so the cell F3 can retrive the inforamtion. Is there a way around this?
Thanks.

- i hope that made some sence :P

edit* just got an idea. i can set up a button which will open and retrive the information but i was wondering if this can be done when the user is selected from list. Again listbox is created from Data>Validation menu and not from any forms

 

[Andrew Poulsom]

You could use INDIRECT.EXT from MOREFUNC:

http://xcell05.free.fr/english/

 

[Adrcon3]

can't i do this without any add-ins?

 

[Andrew Poulsom]

Sorry, no. INDIRECT requires that the source workbook is open.

 

[Adrcon3]

umm what about if i use vba?
call sub when cell changes()
path = "C:\Documents and Settings\xp\Desktop\project\"
openfile = path & selectedcellname & ".xls"
activeworkbook.close
end sub

i need some help to translate this though