Outline of spreadsheet.

I have a spreadsheet we use to add daily shipments of products. This is added to daily so is constantly changing. Some of these are shipped nationally and some internationally. We use a column with a drop down list of national & international to identify the type. Using a filter formula I pull the 'international' shipments to a separate sheet, in the same workbook, so we can track them.

=filter('DAILY ORDERS'!A:H,('DAILY ORDERS'!H:H="ioss")+('DAILY ORDERS'!H:H="international"))

We only ship 1 in 10 internationally I'd like to keep track and add notes in a separate sheet.

What I want to be able to do.
I would like to be able to add notes as the shipment progresses that stay locked to the shipment even as more shipments are pulled through by the formula.

The problem I've encountered.
In the separate sheet with the filter formula. As I write notes in a cell it over writes the whole filter formula and I get a #REF.

If I add a note to the side of the filter formula the note becomes out of sink with the shipment as the list is dynamic.

Is there a way to introduce a cell to add notes to that can be locked to the filter formula results sheet.

(I have considered simply using a filter on the master sheet and getting the people using it to select the filter to review the International orders. What I'm trying to do is closer to the set up they have currently so will be less of a change for them.)

1 Answer 1


You are describing how formula results get misaligned with manually entered data. Lance has given a thorough treatment of the row misalignment issue and how it can be dealt with in some cases.

Nevertheless, in your use case, filter views are probably the easiest solution.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.