data from many files

Dear all

I hope you can help on this one.
I am trying to load data from several hundred files.
I am aware of consolidation and formula to set it up but is there a way I can take a list of filesnames eg
fileone
filetwo
thirdfile

and enter these into a formula to extract a given column without setting up each file manually?

Ta

Kevin
desired outcome.xlsx
fileone.xlsx
filetwo.xlsx
thirdfile.xlsx

Just to clarify a little further

rather than this code entered into A1
=[fileone.xlsx]Sheet1!$A$1

I would like to enter this
=[B1]Sheet1!$A$1
where B1 would hold the file name fileone.xlsx

Obviously this doesn't work
Is there a way with formulas that it can be done?

Regards Kevin

hi all

I have researched this and found out how to do it with indirect function but it is impratical to have all files open.

I have decided to use VB code and I have had some success.

The problem
I have many files with vertical lists of numbers all in the same column F.
I would like to merge all these files so that each list appears in its own column with a file name at the top.
eg
file 1
3
4
5
6

file 2
9
8
7
6

desired outcome
file 1 file 2
3 9
4 8
5 7
6 6

thanks in advance