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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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! :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top