Meraz Al Nahian
The article will show you how to reference a dynamic component of a structured reference in Excel. Structured Reference allows you to reference one or multiple ranges of an Excel table using the name of Column Header in it. You don’t need to use the R1C1 or A1 cell reference in a formula while using the structured reference process. Hence, it’s an easy and understandable way to reference the cells that contain data in an Excel table. The structured reference is also easily recognizable to any user when the user works on formulas with structured references in it. This article will show you how to insert a dynamic component of a structured reference and some applications of it.
Download Practice Workbook
4 Ways to Reference a Dynamic Component of a Structured Reference in Excel
In the dataset, you will see the name of some Salesman who sold some products in a day. You can also see how much Sales they made that day.
We will be discussing how we can reference the dynamic components of this table as a structured reference later in this article.
1. Referencing a Dynamic Component of a Structured Reference in Excel with At (@) Symbol
The At symbol (@) can be used to reference a dynamic component in an Excel table. This example will show you how to make an absolute reference from the table. Suppose, you want to reference a single piece of data from a column. Let’s go through the process below to see how to do this.
Steps:
- First, type the following formula in the 5th row of any column in your Excel sheet and hit ENTER.
=Table1457[@Sales]
You can see that the operation shows the value of D5 as column D represents the Sales column and we selected a cell (F5) which is in the 5th row. Here, Table1457 is the name of the table, you can change it if you want. To do that, select any cell of the table then go to Table Design >> Table Name and put any name of your choice in it.
Keep in mind that, you will only get the values according to the row number of your Excel sheet. If you selected any cells of the 6th row such as F6 or G6 or H6 etc. and used the formula above, you would have seen the value of D6 which is $60.21. The table data are ranged from 5th to 11th row, so selecting any cells outside this range will return a #VALUE! (Error in Value) error.
This is one way to reference a dynamic component of a structured reference by using the At symbol.
Read More: Applications of Absolute Structured References in Excel Table Formulas
2. Referencing Dynamic Components of a Structured Reference Without Any Symbol
If you want to reference a table range, you can do this without any symbol. Let’s take a look at the following steps.
Steps:
- Suppose you want to reference the name of the salesmen. To do that, type the following formula first.
=Table14578[Salesman]
Here, you can see that we reference the range where the name of the salesmen was stored previously without any symbol. Table14578 is the name of the table. You can change the name of it following the description in this link.
- After that, hit the ENTER.
You can see the name of the salesmen after this operation. You don’t need any symbols to do this. Thus, following the process described in this section, you can easily reference multiple dynamic components of a structured reference in Excel.
3. Structured Referencing Using Pound Symbol
We can do the same reference which we previously showed in Method 2 using a Pound (#) symbol. Let’s take a look at the following steps.
Steps:
- First, type the following formula.
=Table145789[[#Data],[Salesman]]
Here, you can see that we reference the range where the name of the salesmen was stored previously using the Pound/Hash (#) symbol. Table145789 is the name of the table. You can change the name of it following the description in this link. The formula will return the stored data of the Salesman column as we mentioned Data with the Pound (#) symbol.
- After that, hit ENTER.
You can see the name of the salesmen after this operation.
Thus, you can easily reference multiple dynamic components of a structured reference in Excel using the Pound symbol.
4. Reference Data and Header Simultaneously of a Structured Reference
In this section, I’ll show you how to insert data from a table and the corresponding column header simultaneously in an Excel sheet. The previous methods showed only how to insert the data component of a table. Let’s go through the process below.
Steps:
- First, write down the formula like the following picture.
=Table14578910[[#All],[Sales]]
Here, you can see that we reference the range where we stored the Sales amount previously. The ‘[#All]’ refers to all the data in the table. As we mentioned the Sales column in the formula, it will return the data of the Sales column with its header. Table14578910 is the name of the table. You can change the name of it following the description in this link.
- After that, hit ENTER.
You can see the Sales data after this operation.
In addition, you can also do this similar operation by using the following process. Just type the formula anywhere on your Excel sheet and press the ENTER button.
=Table14578910[[#Headers],[#Data],[Sales]]
The formula will return the Sales data with the heading as we mentioned #Headers and #Data in the formula.
Thus, you can easily reference dynamic components of a structured reference with the column header.
Common Applications of Structured Referencing in Excel
In this section, I’ll show you some examples of the application regarding structured referencing. Let’s have a look at the following portions.
1. Application of SUM Function
Suppose you want to determine the total Sales amount. To do that,
- First, write down the formula below in cell D12 and press ENTER.
=SUM([Sales])
The formula here uses the SUM function and calculates the total Sales as we referenced the Sales column in the formula. There is another advantage of using the data as a table. Click on the drop down icon in the cell where you wrote the formula. You can see other options of using different functions.
2. Application of COUNTIF and INDIRECT Functions
You can also use structured reference with the COUNTIF and INDIRECT functions. Let’s have a look at the following description.
- Type the formula in cell B12.
=COUNTIF(INDIRECT("Table14["&B4&"]"),"*")
The formula will count the number of data entries in column B. Here we referenced the column header with the Ampersands (&) and Cell Reference (B4).
Read More: How to Use IF Function and Structured Reference in Excel
3. Application of Arithmetic Formula
We can also make an arithmetic formula to calculate the average sales amount per quantity done by the salesman using the structured reference. Let’s get to the process below.
- First, make a column to calculate the average and write down the formula in cell E5.
=[@Sales]/[@[Sales Qty]]
The formula will automatically fill the cells in the corresponding column. Here we used the At (@) symbol to reference dynamic components of the structured reference.
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
Suffice to say, you will learn some basic ideas of referencing a dynamic component of a structured reference in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
