How to prevent formulas from changing if I add or remove lines

Question

How to prevent formulas from changing if I add or remove lines

Megan F 40

Hello! I have a spreadsheet where one tab is pulling information from another tab on the same excel. I need to be able to sort, filter, and add/remove lines without the formula data changing on tab 2. I had everything linked, but then when I added additional lines the formulas for everything below the new lines got shifted and messed up. I need to be able to lock everything so I don't have to keep redoing it. It's a massive spreadsheet. I have spreadsheets where it works fine and others where it doesn't. I am not sure what setting to change. How can I do this?

An example of the formula on tab 2 (MASTER FOF) that is pulling from tab 1 (MASTER C&D) is:

='MASTER C&D'!C6

Barry Schwarz 5,941 Reputation points

2026-07-21T21:18:57.7433333+00:00
Please provide some specifics.

If you sort C1:C10 and the data in C6 moves to C4, what should happen to your formula?

If you insert a row between rows 2 and 3, the data in C6 moves to C7. What should happen to your formula?

If you delete row 4 (the actual row, not just the data in the row), the data in C6 will move to C5. What should happen to your formula?

Can you show us a sample of the formula that works to contrast with the one that doesn't?
Megan F 40 Reputation points

2026-07-22T16:21:05.0933333+00:00

TAB ONE:

Tab 1 has 12 lines per fund - one for each month of the year. This tab isn't an issue because that is where I enter the data and it gets pulled into tab 2 where I enter additional data (I do need to have them separate). If I ever add a fund to tab 1, I also have to add it to tab 2, and then I would sort by name to alphabetize.

TAB TWO:

Tab 2 has 13 lines per fund because I added a total line at the bottom which has its own formulas. I still need to be able to sort and filter each column. If the lines get re-sorted, the data those lines are pulling for each fund from tab 1 need to keep pulling from the same cells even if they shift. So, if I have data pulling from C6 from tab 1 to tab 2, then it gets moved to C7, the data on tab two should be auto-updating to C7. Normally if you add/remove lines the formula auto-adjusts to correct the cell, but that isn't happening.

I made a really generic version of my spreadsheet and uploaded it here:

https://storage.to/lgAWrXVLK

Separately, I'm having the issue where I have the formula below in the last column so I can sort by lines that have data, but it is picking up the invisible formulas in the cells so it's saying all lines have data.

=IF(COUNTA(C3:T3)>0, "Data", "")
Ashish Mathur 102.2K Reputation points Volunteer Moderator

2026-07-22T22:55:14.1733333+00:00

In cell E3 of the Master FOF sheet, enter this formula and drag down

=XLOOKUP(A3&B3,'MASTER C&D'!$A$4:$A$51&'MASTER C&D'!$B$4:$B$51,'MASTER C&D'!C$4:C$51)

If the order of columns in both tabs is the same, then just drag this formula rightwards.

Hope this helps.
Megan F 40 Reputation points

2026-07-24T16:09:53.0133333+00:00

This did not work. I got a bunch of #N/A errors after the first column.
Barry Schwarz 5,941 Reputation points

2026-07-24T22:40:42.91+00:00

Show us the actual formula you are using and the contents of the cells it references.

3 answers

Your answer

Barry Schwarz 5,941 Reputation points

2026-07-21T21:18:57.7433333+00:00

Please provide some specifics.

If you sort C1:C10 and the data in C6 moves to C4, what should happen to your formula?

If you insert a row between rows 2 and 3, the data in C6 moves to C7. What should happen to your formula?

If you delete row 4 (the actual row, not just the data in the row), the data in C6 will move to C5. What should happen to your formula?

Can you show us a sample of the formula that works to contrast with the one that doesn't?
Megan F 40 Reputation points

2026-07-22T16:21:05.0933333+00:00

TAB ONE:

Tab 1 has 12 lines per fund - one for each month of the year. This tab isn't an issue because that is where I enter the data and it gets pulled into tab 2 where I enter additional data (I do need to have them separate). If I ever add a fund to tab 1, I also have to add it to tab 2, and then I would sort by name to alphabetize.

TAB TWO:

Tab 2 has 13 lines per fund because I added a total line at the bottom which has its own formulas. I still need to be able to sort and filter each column. If the lines get re-sorted, the data those lines are pulling for each fund from tab 1 need to keep pulling from the same cells even if they shift. So, if I have data pulling from C6 from tab 1 to tab 2, then it gets moved to C7, the data on tab two should be auto-updating to C7. Normally if you add/remove lines the formula auto-adjusts to correct the cell, but that isn't happening.

I made a really generic version of my spreadsheet and uploaded it here:

https://storage.to/lgAWrXVLK

Separately, I'm having the issue where I have the formula below in the last column so I can sort by lines that have data, but it is picking up the invisible formulas in the cells so it's saying all lines have data.

=IF(COUNTA(C3:T3)>0, "Data", "")
Ashish Mathur 102.2K Reputation points Volunteer Moderator

2026-07-22T22:55:14.1733333+00:00

In cell E3 of the Master FOF sheet, enter this formula and drag down

=XLOOKUP(A3&B3,'MASTER C&D'!$A$4:$A$51&'MASTER C&D'!$B$4:$B$51,'MASTER C&D'!C$4:C$51)

If the order of columns in both tabs is the same, then just drag this formula rightwards.

Hope this helps.
Megan F 40 Reputation points

2026-07-24T16:09:53.0133333+00:00

This did not work. I got a bunch of #N/A errors after the first column.
Barry Schwarz 5,941 Reputation points

2026-07-24T22:40:42.91+00:00

Show us the actual formula you are using and the contents of the cells it references.

Answer 1

Barry Schwarz 5,941

If the formula references the cell by address, either relative (e.g. B5) or absolute ($B$5) then:

When the cell moves as the result of inserting of deleting a row or column, the formula will be automatically adjusted to reference the same data at its new address.
When the data in the cell is changed as the result of a sort operation, then the formula is unchanged. The formula will access the new data in the cell. There is no automatic process to adjust the formula to the data's new location.

0 comments

Answer 2

Ashish Mathur 102.2K Volunteer Moderator

Hi,

You should fetch data into Tabl2 from Tab1 based on some sort of lookup function.

0 comments

Answer 3

Hendrix-C 19,970 Microsoft External Staff Moderator

Hi Megan,

For the best possible assistance, may I confirm whether your expected outcome is

To have the formula on tab 2 always pull from a specific physical cell, like "always return whatever is in cell C6, even if rows are inserted" or
You want to have a relative cell reference, like the formula linked with a specific data so if it's sorted, filtered or in a different cell than C6 the formula still correctly references that data.

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I look forward to hearing from you.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Megan F 40 Reputation points

2026-07-22T16:37:57.9133333+00:00

The lines from tab 1 fund A need to pull into the matching lines from tab 2 fund A, and so on. If I add a new fund (fund AA) in between the existing fund A and fund B, then the formulas pulling from the current fund B lines would have to change to fit the new cells that shifted down for fund B. Right now, if I do it and the lines shift, instead of continuing to pull the fund B lines, it will pull the new lines for fund AA. If the lines shift for the reference cells on tab 1 I need the formula to update on tab 2 to the new reference cells that the original data is now in. I am not great with formulas so I am sorry if I am not explaining this well.
Hendrix-C 19,970 Reputation points Microsoft External Staff Moderator

2026-07-22T22:24:13.0366667+00:00
Hi Megan,

Based on what you're describing, it's a cell reference problem. Locking cell or protecting the sheet may not solve it since the core issue is that Tab 2 is pointing to a physical cell location.

In this case, I would recommend using Excel table as it's the best setup for what you're doing:

For tab 1

Based on your description, I have a sample data like this:

Then click any cell in Tab1 data > press Ctrl + T > check my Table has headers > OK

Go to the Table Design tab > on the left of the ribbon, change the Table name to a different name, for example Table1

For tab 2

Perform the same step as done in tab 1 and also change the Table name to a different name, for example Table2

Now, to pull formula from tab 1 to tab 2, you can use this formula: =SUMIFS(Table1[Amount],Table1[Fund],[@Fund],Table1[Month],[@Month])

Then even if you sort, filter or add/remove any row, the data always references correctly based on the Fund and month value

About your problem with visible data

The problem you're currently having with the formula =IF(COUNTA(C3:T3)>0,"Data","") is because that COUNTA counts all formulas even if those formulas return a blank-looking result.

In this case, I suggest you can try this formula instead =IF(SUMPRODUCT(--(C3:T3)<>0))>0, "Data", "")

You can give it a try and let me know whether it works. If you have any other questions, please feel free to reach out again in the comments on this post. I'm happy to assist you further

Thank you for your understanding and cooperation. I'm looking forward to your reply.
Megan F 40 Reputation points

2026-07-24T16:15:40.02+00:00

n/a n/a
Hendrix-C 19,970 Reputation points Microsoft External Staff Moderator

2026-07-24T20:51:07.55+00:00
Hi Megan,

I figured out just now that you already shared a sample file in another comment, so I did download the file and tested my formulas there. I can confirm that my formulas do work properly in your Excel structure.

Note: I'm currently using Excel web so the interface may be different from desktop app, but the formulas will remain the same.

Your current data are just normal ranges, so you need to convert it to Excel tables by selecting the range A3:O51 in the MASTER C&D > press Ctrl + T > check "My Table has headers" and make it a table.

Do the same step with data in MASTER FOF. I named the table in MASTER C&D as Table1 and table in MASTER FOF as Table2 in the Table Design tab

In cell E3 of MASTER FOF, use this formula =SUMIFS(Table1[CAPITAL CALL 1],Table1[FUND],[@FUND],Table1[MONTH],[@MONTH])

You can use the same formula for other cells. For example, in column DATE 1, starts from cell F3, you can use this formula =SUMIFS(Table1[DATE 1],Table1[FUND],[@FUND],Table1[MONTH],[@MONTH]). You just need to change CAPITAL CALL 1 to DATE 1 and can apply the same formula structure to other columns too.

As for the Data checker in Helper formula, it also works well:

You can carefully follow my guidance again and let me know if you have any difficulties with the steps. I'll be happy to further assist.

Thank you for your cooperation and understanding.

Kind regards.
Hendrix-C 19,970 Reputation points Microsoft External Staff Moderator

2026-07-29T17:41:20.5266667+00:00

Good day Megan,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. If you need assistance with anything else, kindly respond to this post, and I will be happy to help.

I want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.