Aggregating figures on three sheets to one main sheet.

YD76047

New Member
Joined
Sep 25, 2006
Messages
11
Greetings

I been working to solve a rather tricky problem, I was very lucky to find your forum, you have lots of interesting topics. :)
Anyway back to my question/problem, I've really hit a brick wall here.

I’m using three different excel sheets/files to gather some information.
The data in these files are on the same level of detail dimension wise.

What I now want to do is have a sheet or an excel file that looks up aggregates the figures in the other three excel files/sheets. I’m not sure at all what or if there is any function to help me do this. I know how to use vlookup but vlookup doesn’t aggregate the data. Also Pivotable is something I don’t want to use either due to that I want to read from all three sheets and that the pivot uses somewhat restricted formatting.

See my sketch below to visualize the files. Its just an example of what the file could contain. ^^

example_erik.gif
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Joined
Jul 30, 2006
Messages
3,656
Welcome to the board,

It is possible to get information from a closed spreadsheet/workbook.

What is the name and path of Site file1?
example: C:\My Documents\Excel\filename.xls

What is the sheet name where the data exists in the file?

What is the cell address of the data?


Have a great day,
Stan
 

YD76047

New Member
Joined
Sep 25, 2006
Messages
11
Hi Stanley,

My different site files are names as and with path:

O:\Sales06\North.xls
O:\Sales06\West.xls
O:\Sales06\South.xls

Each of these files have a sheet called "sales" with the data in the range: A6:AC6000

And the file which I want to so to say consolidate the data is:
O:\Sales06\Totals.xls with one sheet in it.

Thanks in advance for any help! :)
 
Joined
Jul 30, 2006
Messages
3,656
Do you want to pull ALL the information from the three workbooks, in the range A6:AC6000? That is a lot of information. This can be automated, but you could just manually copy the ranges into 'Totals.xls'.

Or, do you want to pull specific information from that range?


Have a great day,
Stan
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176

ADVERTISEMENT

I think Stan is on the right track. The problem is only difficult because of the data layout. If you lay out the data in standard database format first, the problem is a non-problem. Excel has built in funtionality that can easily handle databases of this size.

Dufus
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

Good news.

Reference:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

Can you understand what he is doing and is it relevant to my problem? Seems to me he only wants to get to data from a workbook which you just demonstrated possible!

I have adjusted the above UDF/macro code. With some test data, I was able to pull in a range of cells, A1:O101, from a closed workbook in less then 1 second.


My different site files are names as and with path:
O:\Sales06\North.xls
O:\Sales06\West.xls
O:\Sales06\South.xls

Each of these files have a sheet called "sales" with the data in the range: A6:AC6000

And the file which I want to so to say consolidate the data is:
O:\Sales06\Totals.xls with one sheet in it.

What is the sheetname of 'O:\Sales06\Totals.xls'?

Where in 'O:\Sales06\Totals.xls' do you want the data to go? What is the start address?

Do you want all the data connected, each site below each other?


Have a great day,
Stan
 

YD76047

New Member
Joined
Sep 25, 2006
Messages
11
Hi again!

Sorry for not replying until now. You know how it can get when you don't solve your problem fast enough, 5 other things come your way that you have to deal with.

Anyhow, I'm back on my mission (impossible? ;P ) again.

Stanley I think thats exactly what I want to do, take the data from the three sheets and put it into one sheet.
Though I want to aggregate it as well, I'm not sure I can do that though.

I'm going to start reading on the URL you posted now to see if I find the soultion there. :)
 
Joined
Jul 30, 2006
Messages
3,656
What is the sheetname of 'O:\Sales06\Totals.xls'?

Where in 'O:\Sales06\Totals.xls' do you want the data to go? What is the start address?

Do you want all the data connected, each site below each other?


Have a great day,
Stan
 

YD76047

New Member
Joined
Sep 25, 2006
Messages
11
Godmorning,

My sheet in Totals.xls is called "revenue".

I want the data to start in the A7 cell. And yes I want the different added sheet data to be connected to each other in the file. I dont know if different number of rows each time will effect this?
The number of rows in each site file can varie from time to time.
 

Forum statistics

Threads
1,136,308
Messages
5,674,990
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top