Is it possible to stop a formula from changing when adding rows between linked cells?

Indirect address with the table name at the end you provided did work on separate table thanks, however it doesn’t update with changes. I moved a few thousand indirect address formulas same sheet as my data entry top left of the table where nothing will move or disrupt formulas. I had no way of stopping formulas from changing after adding rows or columns. A complete mirror of dara using indirect address formula didn’t alter when the values were moving away from the cells. There are lots of extra indirect address sells to mirror the data, wherever the data stops moving to then I a search ranges Extract the data from all of the indirect cells.

in other tables, same thing top left corner I use simple links =A1 to pull the data from the frozen indirect address cells into the other tables, creating a static mirror of the data again top left corner of every table then distributed to other formula once in the tables. this is how I bridged the data between tables when the values are moving around. as long as the value moves within the 3000 rows I created they get filtered out with search formula go where they’re needed. I condensed all my data together in one column. Mostly the only way to get the data out is by searching it. It doesn’t matter where it goes. As long as it’s in the table. The search will find it saves a lot of space putting all the data together. It’s kind of like an Internet connection with all the data flowing through the same column. Otherwise, I would need too many indirect address formulas to catch separated data. I like to think of the indirect addresses as sort of a router where all the data goes gets processed, and then transferred over the network to another table to be received by other receiving formulas, and then distributed throughout the table to individual formulas. Probably a completely unnecessary step although I don’t know any other way to stop the formulas from changing without isolating them with indirect address stops, making a mirror copy in an area that is static?

I know this is definitely the wrong way to do spreadsheet I needed the spreadsheet functional and it worked. Thank you for your help. It would be awesome if there were a way to prevent formulas from changing.

I found it.

instead of using

=$A$2

i can use

=INDIRECT(ADDRESS(2,1))

Any better options?

Is it possible to use that for a different table??

Do you know how to link indirect to a different table?

I’ve also tried,

=INDIRECT(ADDRESS(table::1,6))

=INDIRECT(table::ADDRESS(1,6))

=table::INDIRECT(ADDRESS(1,6))

=INDIRECT(ADDRESS(table::1,table::6))

none of these worked?

i got everything working finally. Found what I think is a bug that is while GROUPS are turned on data in mirrored cells will not update new values in correct position. I’m going to leave a detailed explanation and send Apple a bug report. Quick explanation formula in cell A1 mirrors cell A100. When row 100 moves to position row 200 cell A1 continues to show values for row 200 but formula in cell A1 shows mirroring A100. The values that should now be mirrored in A100 is not loading while values in A200 are showing up in A1. If formula in A1 shows (row 100, column 1) it should be pulling values from A100. The more I move around rows the more scrambled the data gets like a puzzle making all the mirrored cells useless. If I turn off Groups, immediately turn Groups back on everything is in their right place, all values now matching the formulas in their cells that is until I start moving groups again. For some reason when groups move it doesn’t update. It means every time I add, move, delete a group I have to go to the group menu turn it off, then turn it back on to update that the groups have changed. I can completely replicate the problem. I think it’s a bug. I’ve worked around it.

what I designed in iOS Numbers is very similar to iOS Reminders app, has note taking in each group, customizable grouping, more indentations, filtering and color rules based on values, tags and priority filtering in to different tables. Calendar notifications for spreadsheet row events that show upcoming events in a summary table of tasks and events coming due starting with events

Today, Tomorrow, 2,3,4,5,6 days.

This week, next week, next 2 weeks.

This month, next month, next 2 months.

Remaining this year and events next year.

Recurring events like birthdays.

Shopping items for grouped tasks or project group are tagged to show up in shopping tables just doing what Tags normally do so I can get all items from multiple projects by Tagged and sorted by store names. Example I build many projects and wanted all shopping items sorted in to a list. I put a list of things related to each project, each project as a separate group. In the project groups I put shopping items as they are part of a specific project group, then Tag the shopping items with store name. When I am ready to shop for items I have all the items I want to buy sorted by store name in a different table.

I placed a new thread in Numbers titled,

“Possible bug, Groups, Indirect. Cell moves. Identified but why?“

Link

Possible bug, Groups, Indirect. Cell move… - Apple Community

with specific issues I was experiencing related to this thread if anyone was interested.

Thanks that was useful. :)