Summarizing the data from all the files in a folder
 

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

Summarizing the data from all the files in a folder
 

Hi Raj

It really depends on how you want your data.

Do you want the sum to be a direct consolidation of the the cells as in

a1=north.xls!sheet1.a1 + south.xls!sheet1.a1.....

Or a totals line report

a1=North
b1=north.xls!sheet1.a50
a2=South
b2=south.xls!sheet1.a50

You then decide if you wnat the links hard coded or done with VBA.

I find the easiet way is to have sheet with a list of the files and you then
go through the list updating each sheet and then you have a control list to
display done or error.



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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

Summarizing the data from all the files in a folder
 

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

"Martin Fishlock" wrote:

Hi Raj

It really depends on how you want your data.

Do you want the sum to be a direct consolidation of the the cells as in

a1=north.xls!sheet1.a1 + south.xls!sheet1.a1.....

Or a totals line report

a1=North
b1=north.xls!sheet1.a50
a2=South
b2=south.xls!sheet1.a50

You then decide if you wnat the links hard coded or done with VBA.

I find the easiet way is to have sheet with a list of the files and you then
go through the list updating each sheet and then you have a control list to
display done or error.



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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

Summarizing the data from all the files in a folder
 

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

"Martin Fishlock" wrote:

Hi Raj

It really depends on how you want your data.

Do you want the sum to be a direct consolidation of the the cells as in

a1=north.xls!sheet1.a1 + south.xls!sheet1.a1.....

Or a totals line report

a1=North
b1=north.xls!sheet1.a50
a2=South
b2=south.xls!sheet1.a50

You then decide if you wnat the links hard coded or done with VBA.

I find the easiet way is to have sheet with a list of the files and you then
go through the list updating each sheet and then you have a control list to
display done or error.



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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

Summarizing the data from all the files in a folder
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Raj" wrote in message ...
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

"Martin Fishlock" wrote:

Hi Raj

It really depends on how you want your data.

Do you want the sum to be a direct consolidation of the the cells as in

a1=north.xls!sheet1.a1 + south.xls!sheet1.a1.....

Or a totals line report

a1=North
b1=north.xls!sheet1.a50
a2=South
b2=south.xls!sheet1.a50

You then decide if you wnat the links hard coded or done with VBA.

I find the easiet way is to have sheet with a list of the files and you then
go through the list updating each sheet and then you have a control list to
display done or error.



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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

Summarizing the data from all the files in a folder
 

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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

"Martin Fishlock" wrote:

Hi Raj

It really depends on how you want your data.

Do you want the sum to be a direct consolidation of the the cells as in

a1=north.xls!sheet1.a1 + south.xls!sheet1.a1.....

Or a totals line report

a1=North
b1=north.xls!sheet1.a50
a2=South
b2=south.xls!sheet1.a50

You then decide if you wnat the links hard coded or done with VBA.

I find the easiet way is to have sheet with a list of the files and you then
go through the list updating each sheet and then you have a control list to
display done or error.



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Raj" wrote:

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