Transformer
06-02-2013, 01:28 PM
UsefulGyaan Has Posted the Following On 06-02-2013 01:54 PM:
A reference that refer to the same range on multiple sheets is called 3D reference.Sometimes it is very useful. e.g.**We have a workbook that has sheets for each day(Sunday,Monday..) and a sheet named Total where i need cumulative total of each sheet. So i will be needing to refer same range of each sheet.So rather than referring to each sheet range separately and then using a sum formula, there is an easy way to do it.
=SUM(FirstSheet:LastSheet! Cell Address)
So to get total of pendrives sold during the week, the formula will be =SUM(Sunday:Saturday!B2). Same can be done for all the items. Here it will sum across all the sheets from Sunday to Saturday.
http://usefulgyaan.files.wordpress.com/2013/06/3d-reference.png
http://usefulgyaan.files.wordpress.com/2013/06/3d-reference-sum.png
Did You Know:: 3D Reference in Excel (http://usefulgyaan.wordpress.com/2013/06/02/did-you-know-3d-reference-in-excel/)
A reference that refer to the same range on multiple sheets is called 3D reference.Sometimes it is very useful. e.g.**We have a workbook that has sheets for each day(Sunday,Monday..) and a sheet named Total where i need cumulative total of each sheet. So i will be needing to refer same range of each sheet.So rather than referring to each sheet range separately and then using a sum formula, there is an easy way to do it.
=SUM(FirstSheet:LastSheet! Cell Address)
So to get total of pendrives sold during the week, the formula will be =SUM(Sunday:Saturday!B2). Same can be done for all the items. Here it will sum across all the sheets from Sunday to Saturday.
http://usefulgyaan.files.wordpress.com/2013/06/3d-reference.png
http://usefulgyaan.files.wordpress.com/2013/06/3d-reference-sum.png
Did You Know:: 3D Reference in Excel (http://usefulgyaan.wordpress.com/2013/06/02/did-you-know-3d-reference-in-excel/)