Filter Formula that Adds to the Next Free Row

Question

I have a spreadsheet with two tabs:

• Tab 1 is a master call log (includes 10+ columns with various information: Name, Email, Age, Phone, Type, Type2, Survey Taken, etc.)
• Tab 2 I'd like to copy certain columns within a row if a particular column says "Yes"

Example: If the word "Yes" is typed in the 'Survey Taken' column, I'd like Name, Type, and Type 2 transferred to Tab 2 (not Email, Age, Phone, etc.)

We currently have a FILTER formula set up. When a caller's information is transferred to Tab 2, our staff puts in notes at the end of their information.

We are running into issues because Tab 1 is a very dynamic sheet in that we add multiple rows/calls to it daily, and the 'Survey Taken' column is consistently changing (ex. someone calls on 2.17.25 and we enter their information to the spreadsheet, but they may not take the survey for a month or two). Once we type "Yes" in that field, it inserts a new row to coincide with where it falls within Tab 1 (ex. If caller B is the row in question, then A-B-C), which messes with all the notes for callers A & C that are already there.

Is there a formula that allows new "Yes" entries to go to the end of the sheet vs between rows (ex. If caller B is the row in question, A-C-B) so that we are safe to add notes after each name/info transferred?

Answer 1

Hi Amber M

Thank you for reaching out to Microsoft Q&A Forum and sharing your experience.

What you are seeing is expected behavior when using FILTER or other dynamic array formulas.

A FILTER formula always recalculates and returns results in the same relative order as the source data. When a row in Tab 1 changes from “No” to “Yes”, the formula re‑evaluates and inserts that record based on its position in the master log. Because of that, formulas cannot “append” new results to the bottom or preserve manually entered notes next to previous results.

Unfortunately, there is no formula‑only solution that can both dynamically pull matching rows and lock the existing output order to match your manual notes. However, you may consider these to fix this workflow:

1, The automated solution

Move your "Notes" column to Tab 1 (Master Log). Instead of typing notes on Tab 2, add a column to Tab 1 for "Follow-up Notes". Then, update your FILTER formula to include that column in the result. This way, the notes are physically attached to the caller's row in the source data, so they will move with the caller no matter how the list sorts.

2, The manual solution

If you cannot add columns to Tab 1, you must stop using the FILTER formula. When a "Survey Taken" status changes to "Yes", you must manually Copy the row from Tab 1 and Paste Values into the next empty row on Tab 2. This creates a static record that will never shift, making it safe to type notes next to it.

Please let me know how things go on your side as your update can also assist others in the community who may be facing the same issue.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

To achieve your goal of transferring data from Tab 1 to Tab 2 based on the condition in the 'Survey Taken' column, while ensuring that new entries are added to the end of the list in Tab 2, you can use a combination of Excel functions. Here’s a suggested approach:

1. Use a Helper Column: In Tab 1, create a helper column (let's say Column K) that will generate a unique identifier for each row where 'Survey Taken' is "Yes". You can use the following formula in K2 (assuming your data starts from row 2):
   =IF(J2="Yes", ROW(), "") This formula checks if the 'Survey Taken' column (assumed to be Column J) has "Yes" and returns the row number if true, otherwise it returns an empty string.
2. Create the Transfer Formula in Tab 2: In Tab 2, you can use the following array formula (entered with Ctrl + Shift + Enter) to pull the data: In A2 (for Name):
   =IFERROR(INDEX('Tab 1'!A:A, SMALL('Tab 1'!K:K, ROW(1:1))), "") In B2 (for Type):
   =IFERROR(INDEX('Tab 1'!C:C, SMALL('Tab 1'!K:K, ROW(1:1))), "") In C2 (for Type 2):
   =IFERROR(INDEX('Tab 1'!D:D, SMALL('Tab 1'!K:K, ROW(1:1))), "") Drag these formulas down as needed. This setup will ensure that whenever a "Yes" is entered in the 'Survey Taken' column, the corresponding Name, Type, and Type 2 will be copied to the next available row in Tab 2, without disrupting existing notes.

This method allows you to keep your data organized and ensures that new entries do not interfere with the notes already present in Tab 2.

---

References:

• Filter a table by row position