How to replace in google spreadsheets (google spreadsheet) in a range of cells relative links with absolute ones?

Question

Google Sheets

And so, there is a table with two sheets. On one sheet, the lines refer to the lines on the second sheet, something like this:

Now on the second sheet, a change occurs: a new line is added to the middle of the list.
Because all links on the first sheet of links are relative, all links are shifted down by 1:

To prevent this from happening, you need to replace all links in the formulas on the first sheet with absolute ones. But I won’t know how to do it, because. in reality, there are links with complex formulas, in a row there are 6 cells with formulas that I wrote once, and then I multiply them down by dragging. If you replace in one line with absolute values, then reproduction, of course, will not work. The total number of lines is about 1000.
Where to dig?

Answer 1

In general, I solved this with a macro, which every time you enter the table and add a row to the sheet, updates all the formulas in the tables.

Answer 2

With absolute links, addresses will move out in the same way.
To prevent this from happening, you just need not to insert a new line. To do this, in your example, select 4,5,6 (C5:C7) and copy a little lower (to C6). Those. first Ctrl+C and then Ctrl+V .
In this case, Ctrl + X is impossible , because. this will be a move, and when moving, the addresses are remembered and stored in the referring cells.

Answer 3

Found 2 solutions
1. ARRAY FORMULAS
2. IMPORT RANGE