Summarizing the data from all the files in a folder

[Raj]

Dear All,

I am in requirement of your expert help. I have multiple files in a folder
which have exactly the same formats, Sheet names and structure. I need to
summarize the data in a new file extracting the data from each of the files
in the folder.

For example, each of the file in the folder has one sheet called Sales, so
in the desired file I want to have the result as summation of sales from
individual files. The file are named on the basis of the regions so they do
not have any consistency.

Formulae or a macro to enable me doing the same would be of great help.

Thanks in Advance
Raj

 

[Raj]

And a bonus shall be if we can give the user the flexibility of selecting the
folder in which the files are saved and the program considers all the files
in the selected folder. This would be something great.

regards
Raj

 

[Raj]

Thanks Martin

It did give me an idea... But I would like to know more of it.

I don't want it hardcoded I want it variable and preferably using VBA.

In order to give an insight I shall give a sample of 1 file here.

Sales

Cost of Sales

Margin

Margin %

Each file has one sheet constituting the above info. I want in the
consolidated file the total sales, total cost of sales, total margin and
margin % to be calculated in the final sheet depending upon these figures. I
shall further clarify that the file names are not consistent and number of
files may not be the same always, it will vary month on month basis depending
upon the actual scenario.

A little more help would do my job.

Thanks in advance.

Regards
Raj

 

[Ron de Bruin]

See this page Raj
http://www.rondebruin.nl/summary2.htm

 

[Martin Fishlock]

Raj:

It is quite complex to move from a fixed link to a variable link.

What you need to do is write a macro/userform that does the followings:

1. asks the user to select the files to use.
2. you then work through each of the summary cells making the new formula
and putting it in the summary sheet.

An alternative is to have a sheet with a list of file names on it that you
work through on clicking a macro button to achieve 2.

You can also have a list of the cells to change this makes it more dynamic
to allow the chaning of the cells by the user rather than adjusting the user
code.

Have fun with your project.