How to make a named range in excel not shift when copying a formula with a reference to this range?

Question

excel

On one sheet - a table with the name "new", in it the columns "program", "branch", "project", "plan", "fact".
On another sheet I make a formula like:

=СУММЕСЛИМН(новая[план];новая[филиал];RC1;новая[программа];R4C)

Then I copy this formula to the next cell to the right of the one I copy from, and excel automatically replaces the columns from the "new" table in the formula with the neighboring ones. Who asked him, the infection?
It turns out this formula:

=СУММЕСЛИМН(новая[факт];новая[проект];RC1;новая[филиал];R4C)

I searched in the Internet - everything is about separate named ranges. And I have a hefty table in reality, with a bunch of columns, what do I need to create two hundred named ranges manually? Write scripts?

How to fix such references to ranges in a named table so that when copying the formula to an adjacent cell, excel does not change these fixed references?

Answer 1

To create absolute links to table rows/columns in Excel, the following tricks are used:

To create an absolute reference with structured references you need to add an additional and duplicate column reference.
t_Data
Duplicating the column reference will anchor the reference when dragging across columns. Here are the full formulas with relative and absolute references.
Relative: =SUMIF(t_Data[Color],[@Color],t_Data[Q1 Units])
Absolute: =SUMIF(t_Data[ [Color]:[Color] ],t_Summary[@ [Color]:[Color] ],t_Data [Q1 Units])
In the absolute formula you'll notice that I anchored the first two arguments. The [@Color] column needs to be anchored as well.
To anchor a row reference you need to put the @ symbol before the duplicate column reference and wrap it all in brackets. The table name is also required, even when the reference is in the same table as the formula. So the row reference looks like: t_Summary
[@[Color]:[Color]]
More details:
https://www.excelcampus.com/tips-shortcuts/absolut... Follow
the same add-in link for Excel, which allows you to switch absolute / relative addressing by pressing F4 when editing a formula in the same way as it is done for regular links.
Disclaimer: I haven't tested the functionality of the add-on.