Data Consolidation (Excel 2003)

Topic

New Reply

This is my first visit after quite a long absence, and I have a problem I want to present to the Masters of Excel here. I’m looking for a non-VBA solution (if at all possible) to the following: my user has a workbook with many worksheets, around 100 or so. Each worksheet is laid out the same as all the others. What she needs to do is sum specific cells in a master worksheet, which would be quite simple using a 3D formula (FL1:FL100!$H$3).

But here’s the kicker: before summing each cell from each worksheet she needs to multiply each worksheet’s cell content by a cell on the master worksheet. The cell on the master worksheet would contain either a 0 or a 1. That way she can sum only the sheets needed in a particular scenario.

So it would work something like this: SUM(FL1!$H$3*Master!E5,FL2!$H$3*Master!E6,FL3!$H$3*Master!E7… and so on. The ones and zeros in the E column on the Master worksheet are generated by a switch and correspond to each FL worksheet. The idea is that the user can vary conditions on the Master worksheet and generate immediate results from the other worksheets accordingly.

Any ideas on how to approach this?

Thanks,

Reply | Quote

Replies

I do not think it is possible with 1 formula. It is too complicated for the array. It would be “theoretically possible”, but whenever I create the array it crashes excel (see post 234459 for a similarly complicated 1 that did the same thing)

I would suggest that you add an array formula in Master in a column next or by E5 – E106 (eg F5-F106, which “grabs” the info from the other sheets. In Master!F5 enter:

=SUM(INDIRECT("'FL"&ROW(INDIRECT("1:3"))&"'!$H$3"))

select F5:F106
ctrl-shift-enter
This one formula will create an array in the cells F5:F106 of the values in sheets of cells H3
Then add this formula where you want the sum:

=SUMPRODUCT(Master!E5:E106,Master!F5:F106)

This should be the sum that you are after.
Steve

Reply | Quote

Hi Steve,

Thanks for your quick and thoughtful response. Unfortunately, I omitted a crucial part of the problem, which is that the desired sum in H3 of all the sheets is only one cell of many to be summed. The table that needs to be summed extends from column H to column R and from row 3 to about row 50. I used the H3 example solely to illustrate the problem.

I do think you are right that there doesn’t seem to be a better solution than just creating a lengthy formula, as long as it doesn’t exceed 1,024 characters.

Reply | Quote

If you give a more “complete” example and any other limitations, I might be able to come up with another approach. I don’t think you can do it in 1 formula: you will need some intermediate cells. You could use an additonal “intermediate” sheet to “suck” all the info from the various sheets into 1 sheet (using some indirect function) in a setup that will allow a more direct calc. This sheet could be hidden.

Then your “formula” on the master, could just be a reference to the hidden sheet. I think in many ways intermediate calcs kept on the spreadsheet could actually be faster and use less memory than trying to make a “megaformula” or an array formula. You might use more “real estate” (but again, it could be hidden) but it is more straightforward.

Steve

Reply | Quote

Hi Steve,

I have attached a simplified version of the file. I had to pare it down to get it under 100K, but I think it will illustrate the issue adequately. The Master sheet shows the switches for the various other sheets in column E, which is the multiplier. The formulas will be entered in the tables beginning in column J. I only included two sample worksheets, but I think you’ll get the idea. Some of the cell references are different than in my earlier messages because of modifications I made since yesterday, but the issue hasn’t changed.

I would be very interested to see if you can think of a hidden sheet approach to simplify the formulas.

Thanks,

Reply | Quote

You are correct this is is more complicated than you originally posted. Here are some thoughts. Most involve some “revampling”, some are total revamps, others are new sheets, and I will mention a VB approach (I know you said you would prefer non-VB).

The approach you take will be your decision, I can only give my opinions/recommendations. I will help as I can, even if you choose ways I would not use. I have worked in manufacturing enough that I know how to deal with people deciding to do things differently than you think is best

Bear in mind, I admit, that I don’t really understand what your goals are for the workbook or what some of the limitations are (I might not even understand the setup exactly), so you will have to excuse any errors from “ignorance”.

I am not a big proponent of having multiple “identically-formatted” sheets (and it looks like you have over 100). I see lots of “problems” with them:
If you ever want to change the format you must do it with all the sheets
it severely limits your use of “builtin” excel features (filters, pivots, etc). The 3d formulas as you see are very limited and restrict you.

Major revamp: “Ideally”, I would envision, 2 -4 worksheets:
1) the datasheet
This would combine all the 100+ datasheets into 1. I see putting it into a “table” Columns:
“sheet name” (FLx, NJx, etc)
Category (Revenue, expense, etc)
SubCategory (Potential Rental Revenue, Absorption & Turnover Vacancy,Rent Abatements, etc)
Year
Amount
[note: I would not include any values from “calculated categories” only the data, if year amounts for the future are calculated, perhaps they should be calculated when you need them, not stored in the data]
You might need a “calculated column” using vllokup or something to determine whether the data is included or not (based on your “flags”)

The datasheet created in this way can be used to create a pivot table
2) sheet 2 is the summary created from the pivot table. A pivot should be able to give you something very much like you “master sheet”. If you use the “sheetname” as a page field, you should also be able to generate something very much like your 100+ sheets by selecting a particular “sheetname” (you could also break this into 2 sheets: 1 for the “total summary”(=master) and one for a “selected” (at runtime) worksheet.

so from these 2(or 3 sheets) you could view (or print) what you currently have in 100+ sheets

3) The last sheet would be the “flag” sheet. I would add the flag selection on a different sheet and not include it with the master.

I think the above gives the most diversity and power to do the most with it.

Revamp2: Less multiple sheets
The biggest problem with the calcs you want is the setup. Your flags are sheetnames but you don’t have an easy way to do this. So instead of having a large number 100+ of the small sheets, create a smaller number of larger sheets. Use the SubCategory (Potential Rental Revenue, Absorption & Turnover Vacancy,Rent Abatements, etc) as the sheets and the current sheetnames(FLx, NJx, etc) as rows in the sheets. This would allow you to do the “3d sums” since each of the sheet data is setup like the flags. You could have calc’d columns in each of these sheets to muliply by the flag and then use the 3d sum to combine them

“Revamp3”
If you are tied to your sheet layout as is, you can add the “subcategory” sheets I described in Revamp2 to your existing layout. This is not so much revamp as additions. You would reads and use the existing datasheets, to put it into a different format and multiply the flags at the same time. Master would then read these new sheets and not even use the original data.

“Revamp3a”
An approach similar to what Phil suggested. If you go this approach instead of intermediate columns between the existing columns, I would put a new table in the columns to the right. I would add in each sheet a 1 cell reference (eg in A1) to the “flag cell”, then in this new table (staring in col U for example) have A1 multiplied by each of the values in the table (U13 would be =H13*$A$1) and then copy this down and to the right creating the values after “flagging”)

The reference to the flagcell, could be “grabbed” by using VLOOKUP with sheetname (available thru CELL function with manipulation) so that all values in A1 could be the same formula.

Then use the 3dSUms to grap these values in MASTER

[You could also create 100+ more sheets to hold the intermediate data, though I would choose Revamp3 over this approach, just because it would be less sheets]

“Revamp3b”
Instead of new intermediate columns, if you do not need the values directly in the 100+ sheets, you could add the flag reference to A1 asn discussed in 3a, but have all the values in the sheet multiplied by A1 (now no intermediate columns needed)

Macro approach:
You could keep it entirely as you have it now and have a macro. It could read the sheetnames and sheetflags from master, loop thru this list, if the flag was not “0” read the data table from that sheet add it a “total” and continue thru the loop. After looping through all the sheets and summing the various info spit it out into the date table in the format desired in master.

If you need further clarification, or I have missed some points, please post back.
Hope this helps,
Steve

Reply | Quote

You are correct this is is more complicated than you originally posted. Here are some thoughts. Most involve some “revampling”, some are total revamps, others are new sheets, and I will mention a VB approach (I know you said you would prefer non-VB).

The approach you take will be your decision, I can only give my opinions/recommendations. I will help as I can, even if you choose ways I would not use. I have worked in manufacturing enough that I know how to deal with people deciding to do things differently than you think is best

Bear in mind, I admit, that I don’t really understand what your goals are for the workbook or what some of the limitations are (I might not even understand the setup exactly), so you will have to excuse any errors from “ignorance”.

I am not a big proponent of having multiple “identically-formatted” sheets (and it looks like you have over 100). I see lots of “problems” with them:
If you ever want to change the format you must do it with all the sheets
it severely limits your use of “builtin” excel features (filters, pivots, etc). The 3d formulas as you see are very limited and restrict you.

Major revamp: “Ideally”, I would envision, 2 -4 worksheets:
1) the datasheet
This would combine all the 100+ datasheets into 1. I see putting it into a “table” Columns:
“sheet name” (FLx, NJx, etc)
Category (Revenue, expense, etc)
SubCategory (Potential Rental Revenue, Absorption & Turnover Vacancy,Rent Abatements, etc)
Year
Amount
[note: I would not include any values from “calculated categories” only the data, if year amounts for the future are calculated, perhaps they should be calculated when you need them, not stored in the data]
You might need a “calculated column” using vllokup or something to determine whether the data is included or not (based on your “flags”)

The datasheet created in this way can be used to create a pivot table
2) sheet 2 is the summary created from the pivot table. A pivot should be able to give you something very much like you “master sheet”. If you use the “sheetname” as a page field, you should also be able to generate something very much like your 100+ sheets by selecting a particular “sheetname” (you could also break this into 2 sheets: 1 for the “total summary”(=master) and one for a “selected” (at runtime) worksheet.

so from these 2(or 3 sheets) you could view (or print) what you currently have in 100+ sheets

3) The last sheet would be the “flag” sheet. I would add the flag selection on a different sheet and not include it with the master.

I think the above gives the most diversity and power to do the most with it.

Revamp2: Less multiple sheets
The biggest problem with the calcs you want is the setup. Your flags are sheetnames but you don’t have an easy way to do this. So instead of having a large number 100+ of the small sheets, create a smaller number of larger sheets. Use the SubCategory (Potential Rental Revenue, Absorption & Turnover Vacancy,Rent Abatements, etc) as the sheets and the current sheetnames(FLx, NJx, etc) as rows in the sheets. This would allow you to do the “3d sums” since each of the sheet data is setup like the flags. You could have calc’d columns in each of these sheets to muliply by the flag and then use the 3d sum to combine them

“Revamp3”
If you are tied to your sheet layout as is, you can add the “subcategory” sheets I described in Revamp2 to your existing layout. This is not so much revamp as additions. You would reads and use the existing datasheets, to put it into a different format and multiply the flags at the same time. Master would then read these new sheets and not even use the original data.

“Revamp3a”
An approach similar to what Phil suggested. If you go this approach instead of intermediate columns between the existing columns, I would put a new table in the columns to the right. I would add in each sheet a 1 cell reference (eg in A1) to the “flag cell”, then in this new table (staring in col U for example) have A1 multiplied by each of the values in the table (U13 would be =H13*$A$1) and then copy this down and to the right creating the values after “flagging”)

The reference to the flagcell, could be “grabbed” by using VLOOKUP with sheetname (available thru CELL function with manipulation) so that all values in A1 could be the same formula.

Then use the 3dSUms to grap these values in MASTER

[You could also create 100+ more sheets to hold the intermediate data, though I would choose Revamp3 over this approach, just because it would be less sheets]

“Revamp3b”
Instead of new intermediate columns, if you do not need the values directly in the 100+ sheets, you could add the flag reference to A1 asn discussed in 3a, but have all the values in the sheet multiplied by A1 (now no intermediate columns needed)

Macro approach:
You could keep it entirely as you have it now and have a macro. It could read the sheetnames and sheetflags from master, loop thru this list, if the flag was not “0” read the data table from that sheet add it a “total” and continue thru the loop. After looping through all the sheets and summing the various info spit it out into the date table in the format desired in master.

If you need further clarification, or I have missed some points, please post back.
Hope this helps,
Steve

Reply | Quote

Hi Steve,

I have attached a simplified version of the file. I had to pare it down to get it under 100K, but I think it will illustrate the issue adequately. The Master sheet shows the switches for the various other sheets in column E, which is the multiplier. The formulas will be entered in the tables beginning in column J. I only included two sample worksheets, but I think you’ll get the idea. Some of the cell references are different than in my earlier messages because of modifications I made since yesterday, but the issue hasn’t changed.

I would be very interested to see if you can think of a hidden sheet approach to simplify the formulas.

Thanks,

Reply | Quote

Hi Steve,

Thanks for your quick and thoughtful response. Unfortunately, I omitted a crucial part of the problem, which is that the desired sum in H3 of all the sheets is only one cell of many to be summed. The table that needs to be summed extends from column H to column R and from row 3 to about row 50. I used the H3 example solely to illustrate the problem.

I do think you are right that there doesn’t seem to be a better solution than just creating a lengthy formula, as long as it doesn’t exceed 1,024 characters.

Reply | Quote

I do not think it is possible with 1 formula. It is too complicated for the array. It would be “theoretically possible”, but whenever I create the array it crashes excel (see post 234459 for a similarly complicated 1 that did the same thing)

I would suggest that you add an array formula in Master in a column next or by E5 – E106 (eg F5-F106, which “grabs” the info from the other sheets. In Master!F5 enter:

=SUM(INDIRECT("'FL"&ROW(INDIRECT("1:3"))&"'!$H$3"))

select F5:F106
ctrl-shift-enter
This one formula will create an array in the cells F5:F106 of the values in sheets of cells H3
Then add this formula where you want the sum:

=SUMPRODUCT(Master!E5:E106,Master!F5:F106)

This should be the sum that you are after.
Steve

Reply | Quote

This uses a hidden column for each FL worksheet and entry. The way I understand your problem, you are looking to add specific cells across all worksheets (in your example file FL1:FL2), but only if the corresponding Master sheet cell E13:E102 equals a 1 (Master sheet cell E13 is a 1 so add all results from sheet FL1). See if my spreadsheet would work for you.

yoyo-phil

Reply | Quote