How to Cross Reference a Cell Range in a Function and Use Defined Names for Simple Cross References - WindowsTips.net - Windows Tips and Tricks with Geek

Tuesday, February 8, 2022

How to Cross Reference a Cell Range in a Function and Use Defined Names for Simple Cross References

 

How to Cross Reference a Cell Range in a Function

Referencing a single cell is useful enough. But you might want to write a function (such as SUM) that references a range of cells on another worksheet or workbook.

Start the function as usual and then click on the sheet and the range of cells—the same way you did in the previous examples.

In the following example, a SUM function is summing the values from range B2:B6 on a worksheet named Sales.

=SUM(Sales!B2:B6)

Sheet cross reference in sum function

How to Use Defined Names for Simple Cross References

In Excel, you can assign a name to a cell or range of cells. This is more meaningful than a cell or range address when you look back at them. If you use a lot of references in your spreadsheet, naming those references can make it much easier to see what you’ve done.

Even better, this name is unique for all the worksheets in that Excel file.

For example, we could name a cell ‘ChicagoTotal’ and then the cross-reference would read:

=ChicagoTotal

This is a more meaningful alternative to a standard reference like this:

=Sales!B2

It’s easy to create a defined name. Start by selecting the cell or range of cells that you want to name.

Click in the Name Box in the top left corner, type the name you want to assign, and then press Enter.

Defining a name in Excel

When creating defined names, you cannot use spaces. Therefore, in this example, the words have been joined in the name and separated by a capital letter. You could also separate words with characters like a hyphen (-) or underscore (_).

Excel also has a Name Manager that makes monitoring these names in the future easy. Click Formulas > Name Manager. In the Name Manager window, you can see a list of all of the defined names in the workbook, where they are, and what values they currently store.

Name Manager to manage the defined names

You can then use the buttons along the top to edit and delete these defined names.

How to Format Data as a Table

When working with an extensive list of related data, using Excel’s Format as Table feature can simplify the way that you reference data in it.

Take the following simple table.

Small list of product sales data

This could be formatted as a table.

Click on a cell in the list, switch to the “Home” tab, click the “Format as Table” button, and then select a style.

Format a range as a table

Confirm that the range of cells is correct and that your table has headers.

Confirm the range to use for the table

You can then assign a meaningful name to your table from the “Design” tab.

Assign a name to your Excel table

Then, if we needed to sum the sales of Chicago, we could refer to the table by its name (from any sheet), followed by a square bracket ([) to see a list of the table’s columns.

Using structured references in formulas

Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:

=SUM(Sales[Chicago])

You can see how tables can make referencing data for aggregation functions such as SUM and AVERAGE easier than standard sheet references.

This table is small for the purposes of demonstration. The larger the table and the more sheets you have in a workbook, the more benefits you’ll see.

No comments:

Post a Comment