Wednesday, November 14, 2018

Create a reference to the same cell range on multiple worksheets

Create a reference to the same cell range on multiple worksheets

A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D 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.

You can use a 3-D reference to add up budget allocations between three departments, Sales, HR, and Marketing, each on a different worksheet, by using the following 3-D reference:

=SUM(Sales:Marketing!B3)

You can even add another worksheet, and then move it into the range that your formula refers to. For example, to add a reference to cell B3 in the Facilities worksheet, move the Facilities worksheet between the Sales and HR worksheets by dragging its tab, as shown in the figure:

Inserting another sheet in a consolidation
 

Because your formula contains a 3-D reference to a range of worksheet names, Sales:Marketing!B3, all worksheets in the range are part of the new calculation.

The examples below explain what happens when you insert, copy, delete, or move worksheets that are included in a 3-D reference. Each of these examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6:

  • Insert or copy    — If you insert or copy worksheets between Sheet2 and Sheet6 (the endpoints in this example), Excel includes all values in cells A2 through A5 in the constituent worksheets in the calculations.

  • Delete —    If you delete worksheets between Sheet2 and Sheet6, Excel removes their values from the calculation.

  • Move —    If you move worksheets from between Sheet2 and Sheet6 to a location outside of the referenced worksheet range, Excel removes their values from the calculation.

  • Move an endpoint —    If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to include the new worksheets between them unless you reverse the order of the endpoints in the workbook. If you reverse the end points, the 3-D reference changes the endpoint worksheet. For example, you may have a reference to Sheet2:Sheet6. If you move Sheet2 so that it's after Sheet6 in the workbook, the formula will adjust to point to Sheet3:Sheet6. If you move Sheet6 in front of Sheet2, the formula will adjust to point to Sheet2:Sheet5.

  • Delete an endpoint —    If you delete Sheet2 or Sheet6, Excel removes the values on that worksheet from the calculation.

Follow these steps:

  1. Click the cell in which you'll enter the function.

  2. Type a = (equal sign), followed by the name of the function (see the table below), and then an opening parenthesis.

  3. Click the tab for the first worksheet that you want to reference.

  4. Hold down the Shift key then click the tab for the last worksheet that you want to reference.

  5. Select the cell or range of cells that you want to reference.

  6. Complete the formula, and then press Enter.

You can use the following functions in a 3-D reference:

Function

Description

AVERAGE function

Calculates average (arithmetic mean) of numbers.

AVERAGEA function

Calculates average (arithmetic mean) of numbers; includes text and logicals.

COUNT function

Counts cells that contain numbers.

COUNTA function

Counts cells that are not empty.

HARMEAN function

Returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals.

KURT function

Returns the kurtosis of a data set.

LARGE function

Returns the k-th largest value in a data set. For example, the fifth largest number.

MAX function

Finds largest value in a set of values.

MAXA function

Finds largest value in a set of values; includes text and logicals.

MEDIAN function

Returns the median, or the number in the middle of the set of given numbers.

MIN function

Finds smallest value in a set of values.

MINA function

Finds smallest value in a set of values; includes text and logicals.

PERCENTILE function

Returns the k-th percentile of values in a range, where k is in the range 0..1. Provided for compatibility with earlier versions of Excel.

PERCENTILE.EXC function

Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

PERCENTILE.INC function

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.

PERCENTRANK function

Returns the rank of a value in a data set as a percentage (0..1) of the data set. Provided for compatibility with earlier versions of Excel.

PERCENTRANK.EXC function

Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.

PERCENTRANK.INC function

Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.

QUARTILE function

Returns the quartile of a data set, based on percentile values from 0..1. Provided for compatibility with earlier versions of Excel.

QUARTILE.EXC function

Returns the quartile of a data set, based on percentile values from 0..1, exclusive.

QUARTILE.INC function

Returns the quartile of a data set, based on percentile values from 0..1, inclusive.

PRODUCT function

Multiplies numbers.

RANK function

Returns the rank of a number in a list of numbers: its size relative to other values in the list. Provided for compatibility with earlier versions of Excel.

RANK.EQ function

Returns the rank of a number in a list of numbers: its size relative to other values in the list.

RANK.AVG function

Returns the rank of a number in a list of numbers: its size relative to other values in the list.

SKEW function

Returns the skewness of a distribution.

SMALL function

Returns the k-th smallest value in a data set.

STDEV.S function

Calculates standard deviation based on a sample.

STDEV.P function

Calculates standard deviation of an entire population.

STDEVA function

Calculates standard deviation based on a sample; includes text and logicals.

STDEVPA function

Calculates standard deviation of an entire population; includes text and logicals.

TRIMMEAN function

Returns the mean of the interior of a data set.

SUM function

Adds numbers.

VAR.S function

Estimates variance based on a sample.

VAR.P function

Calculates variance for an entire population.

VARA function

Estimates variance based on a sample; includes text and logicals.

VARPA function

Calculates variance for an entire population; includes text and logicals

Follow these steps:

  1. On the Formulas tab, click Define Name (in the Defined Names group).

  2. In the New Name popup window, enter the Name for your reference. Names can be up to 255 characters in length.

  3. In the Refers to list, choose the equal sign (=) and the reference, then press the Backspace key.

  4. Click the tab for the first worksheet that you want to reference.

  5. Hold down the Shift key and click the tab of the sheet of the reference.

  6. Select the cell or range of cells that you want to reference.

Learn more

To learn more about moving a worksheet within a workbook, see Move or copy a worksheet. To learn more about creating and using defined names, see Define and use names in formulas.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

No comments:

Post a Comment