Did You Know :: 3D Reference in Excel

Posted: June 2, 2013 by Transformer in Excel
Tags: , ,

UntitledA reference that refers 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 we need a cumulative total.

3D Reference

We need to refer to the same range in 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.

3D Reference Sum

We can use Average,Max,Count etc. functions in 3D reference.


Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s