conoslidate from different files with same format

[Guest]

Dear all

i am working with Excel XP edition and in a local network having one public
folder.
In this folder i am saving 14 files which all of them has the same format.

I am trying to consolidate some of their sheets.
I though to use the vlook up function including the indirect formula but
indirect will not work if the 14 files are open. if any of the files are
close i have a REF.

Does anyone have any good ideas how can i work in one workbook by pooling
data from the 14 files without to have all of them open?
The challenge here is that the next month i will create a new folder with
another 14 files, so i have to change all the links.
Any good ideas? Do i have to use a VB code to solve the problem?

Thanks in advance,

 

[K Sampath - Excel News]

Hi,
It depends on whether you require just to consolidate or consolidate as well
as keep the info in a tabular form for cross checking or further analysis

(1) Excel Magic Consolidator(MagicCons.xls)
by Mr David Hager is an excellent tool/idea for consolidation

(2) Since you have the same format using a VB procedure will do. You can
open the files one by one post the figures and close them. For posting the
figures simple for next loop will do the trick.

(3) If you do not want to open the file, you can put a link formula in one
of the cells of your current workbook and then take its cell value to your
destination. Link formulas will not require opening the file subject to
option settings

I have tried these and it takes just 10/15 seconds to consolidate around
600+ figures
The following code picks up figures from the source file and writes to the
consolidation sheet for 44 rows and 14 columns
report_length = 44
For i = 1 To 14
Report_Sub_Length = Report_Length * (i - 1)
For y = 0 To Report_Length - 1
ThisWorkbook.Sheets("Consolid").Range("Report_Target_Start").Offset(Report_Sub_Length
+ y, Source_Office_Serial).Value =
Source_Workbook.Sheets("Report").Range("Report_Source_Start").Offset(y,
i).Value
Next y
Next i

I am a beginner and I thought I will try my hand in replying to a query for
which I have already tried a solution. Perhaps another experienced person
will come out with a better solution
With regards,
K Sampath