Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Consolidation function
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 -- Helping Is always a good thing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Consolidation function
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+I ncM4*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 "QuietMan" wrote: 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 -- Helping Is always a good thing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Consolidation function
Don't think this would work, as each sheet can be consolidated on to more
than one summary, -- Helping Is always a good thing "Alok" wrote: 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+I ncM4*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 "QuietMan" wrote: 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 -- Helping Is always a good thing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidation | Excel Discussion (Misc queries) | |||
Consolidation | Excel Discussion (Misc queries) | |||
Consolidation | Excel Worksheet Functions | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
Consolidation | Excel Programming |