how to put all xle. file together?

[Guest]

I have sales report of every month from Jan to Dec in 12 seperate files. My
intention is to put them in one work sheet with item numbers as right culumn
and months as row on top. Do I have to open all 12 files copying and posting?
or is there a smart way to do it? Can Access be useful so solve this problem?
Please advise.
Thank you

 

[vezerid]

yalano,

what you are asking can most likely be done, without the files being
open. In general, when the reference to anothe workbook includes the
full path, the workbook need not be open. The INDIRECT function will
likely be used here and, depending on the structure of the 12 files,
other functions like maybe OFFSET, INDEX, VLOOKUP etc.

The exact formula will be dependent on how your individual monthly
reports are organized. Since you want the consolidation sheet to have
12 columns, one for each month, ,most likely you will need an auxiliary
table, say F1:F12, holding the names of the files for the 12 months.

A construct that might prove useful as part of a formula could be the
following expression:

VLOOKUP(A2,INDIRECT(fullpath&"["&INDEX(F1:F12,COLUMN()-COLUMN($A2))&"]Sheet1!A:C"),2,0)...

Write back with more detail for a suitable solution.

HTH
Kostis Vezerides