Using Consolidation function

[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

 

[Guest]

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

 

[Guest]

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