Question: How do I create a reference (name) that refers to the same cell or range on multiple sheets?
Answer: By creating a 3D reference name
Why: A 3D reference is a useful and convenient way to reference several worksheets that follow the same pattern and contain the same type of data, such as when you consolidate budget data from different departments in your organization
Applies To: Excel 2003, 2007, 2010
1. Refer to the data given below. The Operations Budget figures for the North, South, East and West are captured on the respective worksheets as given below. The consolidated budget figure will be displayed on the summary worksheet
The budget figures captured on the South, East & West worksheets in the order of the months given above is;
South: $51 000, $32 000, $45 000, $74 000, $90 000, $88 000
East: $120,000, $95 000, $88 000, $93 000, $54 000, $34 000
West: $45 000, $34 000, $87 000, $89 000, $56 000, $77 000
2. Select C4:C9 on the North worksheet
3. Select as below
4. Select New on the Name Manager window and select as below
5. Select OK and then select the close button
6. Select cell G9 on the summary worksheet and enter the following formula; =Sum(RegionTotals)
7. Press the Enter Key
8. The consolidated Regional Total Value of $1,598,000.00 will be displayed
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
