Filter function, but with constant column next to function

[Forest7980]

Hi All,

Recently, I have discovered the magic of the 'filter' function in excel, however I have ran into an issue with the fact sometimes we use an extra column beside the function to notate this.

If for some reason, the previous data that arose from the Filtered data, then changes, this causes that notational data to be out of whack, due to rows moving up or down, due to deleting's or inserting of new rows relevant to the filter. See below

Angle.png

The Y against the 2nd bottom line was against the bottom line, but has been pushed up, as a result of a line being deleted from the file in which the filter is being pulled from. This settled column is the only manual inputted column in the sheet.

How do I prevent this from occurring?

Thank you,

[Gregb11]

You can't enter data next to a result from dynamic formulas because of the fact that they are dynamic. If you want to mark the records in some way, you should probably consider using Power Query or using an Excel Table that you can update.

[Forest7980]

That makes sense.

Never had occurred to me, until the problem arose, but was hoping, none the less.

Thank you

[NixaMKD]

Hi all,

I am facing the same problem with dynamic filter function and additional column next to it. Example A to T is the filter function's range, U, V, W, and X are the additional columns. The value on the additional columns is added manually against the rows from the filter function data. The value on columns A is a unique value for each row. Is it possible the unique value from column A to be tied up with the A to X range, so regardles rows movingup and dows due to deleting/adding new data on the data source? Maybe using VBA if there is no other way?

I am very grateful for your help and support.

Thank you,

[TMS]

Not sure what wasn't clear about the original response and explanation from Greg. Data entered next to a filtered output is not connected to the filtered data in any way. If you change the filter, the additional data is orphaned.

The best/only solution is to use AutoFilter on the original data and add your extra columns there. Then, when you extract data with the FILTER function, you copy the extra data with it.

[NixaMKD]

Thank you for confirming and making it clear for me.

Regards,

[AliGW]

@Forest7980

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

@NixaMKD

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[NixaMKD]

Thanks, AliGW, for notifying me about the rules about hijacking.
I will know for know..

Regards,