dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
509
share rss forum feed


Rob
In Deo speramus.
Premium
join:2001-08-25
Kendall, FL
kudos:2
Reviews:
·Comcast

[Excel] Looking for suggestions...

Hello All,

My organization is preparing for the new fiscal year in July, and as such, I've created a new budget template that each area will fill in to calculate their projected revenues and operating expenses for their area. The excel worksheet I created then summarizes that information in a summary worksheet that organizes their revenues and expenses in an easy to read report.

What I am looking to do is then take each of those reports, and consolidate them into one report to provide to senior management so they can review the total revenues and expenses for the entire organization.

Any suggestions on the easiest way to do this?

Thanks,

Rob
--
CheckSite.us | YourIP.us | Reverseip.us


Cudni
La Merma - Vigilado
Premium,MVM
join:2003-12-20
Someshire
kudos:13
It is not easy to advise when we don't know the layout of the final report. I would imagine it will be up to you to reference and maybe feed various cells and formulas from those various sub reports into that big one

Cudni
--
"what we know we know the same, what we don't know, we don't know it differently."
Help yourself so God can help you.
Microsoft MVP, 2006 - 2012/13


Rob
In Deo speramus.
Premium
join:2001-08-25
Kendall, FL
kudos:2
Reviews:
·Comcast
Thanks for the reply.

I'm going to have about 30 (or more) excel documents that are sent back to me from the different departments. I'm trying to see how I can:

1. Take the summary worksheet from each excel document and combine just those into 1 document (so that 1 excel document will have 30 worksheets -- one for each department).

2. Take the contents of each summary worksheet and combine the data into one consolidated report. So for example, D11 tells me the total salaries for certain employee classification. I want to sum every worksheet's D11 so that I can see that across all 30 departments, we'll spend $x total in salaries.

Here's what I'm thinking:

Using a macro that will rename the budget summary tab to the department ID that they enter in the excel sheet. This is so when I consolidate each worksheet into 1 workbook, there is no duplicate worksheets.

I'm thinking maybe I can write a macro that will extract the budget summary sheet from each document (so I just have to open each document and run the macro).

Then I can combine each worksheet into one workbook.

I'm wondering if there is a macro that then can take the data in certain fields across multiple worksheets and sum it into one cell.
--
CheckSite.us | YourIP.us | Reverseip.us

bbear2
Premium
join:2003-10-06
94045
kudos:5
reply to Rob
Rob, You didn't say which version of Excel you are using so I'm going to assume 2010 and let you take it from there. Here's your basic setup, you can modify as needed.

Create your template file that you want everyone to will out. Call it File1.xlsx. Copy this 29 times and call each of them File2.xlsx, File3.xlsx, ... File30.xlsx. Now you have 30 identical files that people will fill in.

Create one more file: FileMaster that you want to summarize into. Open all 31 files at once. Do a Sum of the cell that you want to add for each of the files. It would look something such as this when done.
=[File1.xlsx]S1!$B$4+[File2.xlsx]S1!$B$4+[File3.xlsx]S1!$B$4. Note this is only for 3 files, but you get the gist of it and would continue the same for 30. Also, it is only for one cell and you would need to do it for each cell. Remove "$"s as needed to maintain relative cells, copy, drag and you will get the new cell without having to manually sum again.
I've tested it an it does work on my small 3+1 file test.


markofmayhem
Why not now?
Premium
join:2004-04-08
Pittsburgh, PA
kudos:5
Workbooks.Open(FileName:=SrcName, ReadOnly:=True)

This will open the workbook, you can set it to a variable to deal with it easier.

Do an array, loop through it, opening each workbook and worksheet into a named range of values.

not actual code thought puke:

DIM WB as workbook
DIM WS as worksheet
DIM SrcName as String
DIM array: some array as range type

for i loop

SrcName = "C:\Users\Me\This" + i +".xlsx"
WB = Workbooks.Open(FileName:=SrcName, ReadOnly:=True)
WS = WB.Worksheets("name of the tab they all filled in")

array[i] = WB.WS.Range("A1:Z99") or whatever the range of data is needed.

Once the array is filled with range stuff, then you can "capture" any field and sum it with loops:

for i loop
Sum1 = Sum1 + array[i].Range("B4").Value
ThisWB.ThisWS.ThisRange.Value = Sum1

The idea here is:
1. We open each workbook, look at each statically named worksheet, and capture the same RANGE in all of them.
2. We use these RANGE variables to extract .Value
3. We run this in the master workbook, worksheet; setting the value here to be the sum from the previously opened workbooks and worksheets that we extracted RANGE data from.

It won't be fast, could puke often, and should have MANY On Error handlers: but is KISS.
--
Show off that hardware: join Team Discovery and Team Helix


Rob
In Deo speramus.
Premium
join:2001-08-25
Kendall, FL
kudos:2
Reviews:
·Comcast
reply to bbear2
Hi bbear, that's a great suggestion. Unfortunately for my situation it won't work. I need to combine the summary worksheet into one workbook before merging the data.
--
CheckSite.us | YourIP.us | Reverseip.us

bbear2
Premium
join:2003-10-06
94045
kudos:5
said by Rob:

Hi bbear, that's a great suggestion. Unfortunately for my situation it won't work. I need to combine the summary worksheet into one workbook before merging the data.

If you need to combine all the worksheets into one workbook before merging the data, you can do the same as above (open all files) then right click on worksheet to copy/move, and copy/move it to the main/summary file. Then you'll have all the worksheets into one workbook to start.