Drop down list of filenames

[martiniextradry]

Is it possible to get a cell to have a drop down list of filenames?

Then can it have another drop down that gives the filenames in the subdirectory just chosen by that drop down?

Finally, can a formula then read both those selections for a formula?

ie C:\Documents and Settings\My Documents\{first drop down selection}\[{second drop down selection}.xls]

Many thanks

 

[Ruddles]

Yes, with a fair bit of coding. I wouldn't bother. Look at GetOpenFileName as that's designed to give you the same end result as you're decribing with no coding effort and no maintenance issues:-

Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")

You'd need to declare a string variable to hold the filename and then test whether the user hit Cancel, so you might end up with code looking somehting like this:-

Dim sFilename As String
 
sFilename = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
If sFilename = "False" Then Exit Sub
 
[COLOR=green]' continue[/COLOR]
MsgBox "You chose " & sFilename
[COLOR=green]' open the file or do whatever else you wanted to do with the filename[/COLOR]