Using Consolidation function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I using the consolidation function to consolidate several departments each
month, the only issue is that the departments that need to be consliidated
each month changes, is there a way that I can use vba to read from a range of
cells in the worksheets all sheets that need to be consolidatedthat need to
be consolidated each month?

example: finance department month 1 sheet 4 to 10 should be consolidated
month 2 sheet 4, 8, 10 should be
consolidated
 
How about using a formula?

Say cell A1 On Sheet1 consolidates data from Sheets 2 to 7

=Sheet2!A1+Sheet3!A1+Sheet4!A1+Sheet5!A1+Sheet6!A1+Sheet7!A1

You can copy and paste this formula so that it gets extended to handle any
number of cells. However, this formula cannot be changed on a month to month
basis

You can fix that problem by creating say names
IncM1,IncM2,IncM3,IncM4 and so on till IncM7
Give them values of 0 or 1 depending on which sheets have to be summed
and which do not need to be summed and change your formula for A1 as
follows

=IncM1*Sheet2!A1+IncM2*Sheet3!A1+IncM3*Sheet4!A1+IncM4*Sheet5!A1+IncM5*Sheet6!A1+IncM6*Sheet7!A1

To create names go to Insert/Name/Define. Then type IncM1 in the Names in
Workbook box and =0 or =1 in the Refers To box and click on Add button.

Hope this helps.

Alok
 
Don't think this would work, as each sheet can be consolidated on to more
than one summary,
 
Back
Top