Hi guys,
Would be so chuffed if you could help me!
I have 3 workbooks, each containing about 50 worksheets. They each have about 300 lines of data with the same 'name' in coumn A and months across the top from C onwards. The worksheets are all assigned a 'division' number, and so are identifiable by their name.
So far... This is what I have done.
1.) I copied all the required worksheets to a new workbook.
2.) On a seperate sheet, each division has been designated a consolidation code.
3.) When a consolidation code is selected in a drop down menu, all unrequired divisions are automatically remvoed from the consolidation list.
4.) A seperate column takes the consolidation list and condenses it (removes blanks).
5.) I created a dynamic named range on the condensed consolidation list.
6.) I created a SUMPRODUCT code which sums every division with 3 different criteria.
The above WORKS! And it works well! HOWEVER, the workbook is cumbersome because of how large it is. I would much prefer being able to link to the worksheets in their original location, rather than having to link through to them.
Any ideas how I can do this?
Thanks,
crabby
Would be so chuffed if you could help me!
I have 3 workbooks, each containing about 50 worksheets. They each have about 300 lines of data with the same 'name' in coumn A and months across the top from C onwards. The worksheets are all assigned a 'division' number, and so are identifiable by their name.
So far... This is what I have done.
1.) I copied all the required worksheets to a new workbook.
2.) On a seperate sheet, each division has been designated a consolidation code.
3.) When a consolidation code is selected in a drop down menu, all unrequired divisions are automatically remvoed from the consolidation list.
Code:
=IF('P&L'!$A$1='Roll Up Sheet'!C5,'Roll Up Sheet'!A5,"")4.) A seperate column takes the consolidation list and condenses it (removes blanks).
Code:
=IFERROR(INDEX(SheetList,SMALL((IF(LEN(SheetList),ROW(INDIRECT("1:"&ROWS(SheetList))))),ROW(A1)),1),"")5.) I created a dynamic named range on the condensed consolidation list.
Code:
=OFFSET('Roll Up Sheet'!$G$2,0,0,COUNT('Roll Up Sheet'!$G$2:$G$115),1)6.) I created a SUMPRODUCT code which sums every division with 3 different criteria.
Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&DynamicSheetList&"'!$A$1:$A$400"),$A7,INDIRECT("'"&DynamicSheetList&"'!$C$1:$C$400")))The above WORKS! And it works well! HOWEVER, the workbook is cumbersome because of how large it is. I would much prefer being able to link to the worksheets in their original location, rather than having to link through to them.
Any ideas how I can do this?
Thanks,
crabby
