Fixing addresses within data tables

Fixing cell addresses is an old topic. However, when you create a structured table (data table) and try to copy a formula that points to a cell inside it, the rules of the game change. That is, the address will not be updated as desired. These addresses can also be fixed, and in the text that follows you will learn how to do this.

On the left is a table (SalesTR) with list of sales transactions. In the columns you can see the dates, categories, cities and quantities. If we want to summarize the data depending on the given category and city, shown in the table headers, we will write the following formula:

=SUMIFS(SalesTR[QTY];SalesTR[CATEGORY];$G3;SalesTR[CITY];H$2)

In order to successfully update cell addresses in headers, the heading containing the category names has a fixed column ($G3) and the one containing the city names has a fixed row (H$2). However, when we try to copy this formula to an adjacent cell, an error will occur. The solution is to fix the columns using the following syntax:

[[<column name>]:[<column name>]]

That is, if we apply this in the formula:

=SUMIFS(SalesTR3[[QTY]:[QTY]];SalesTR3[[CATEGORY]:[CATEGORY]];$G3;SalesTR3[[CITY]:[CITY]];H$2)

By simply copying the formula, we will get values for the other categories and cities that are being analyzed.