formula to filter results - please help

[dmclllop]

Hello dears,

I need help please to do the following formula

If multiple cells in the "Star" column matches each other in text and the matched cells has the options "in office" or "stock" in the "Status" column then mark all cells as {show}

If multiple cells in the "Star" column matches each other in text and the matched cells doesn't have the options "in office" or "stock" in the "Status" column then mark all cells as {hide}

The below image should clarify the need please i need help

[TheTimeSavingCo]

Unfortunately formula fields can't reference data from other records without some setup

If I were you, I would set up another table called "Star Collated" or something, and link your existing table to that table.  I would then paste all the values from the "Star" field into the linked field to the "Star Collated" table.

After that, in the "Star Collated" table, I would create a lookup field of the "Status" field which would show the statuses of the linked records.  For example, `Q-41923` would have a lookup value of "in office, ordered".

In the "Star Collated" table, I would then create a formula field that would check if the lookup field had the text "in office" or "stock", and it would output "Hide", if not it would output "Show"

And finally, in the original table, I would create a lookup field to display the data from the formula field we just created

I would also create an automation that would trigger in the original table whenever the "Star" field was not empty and the linked field to the "Star Collated" table was empty, and its action would be to paste the value of the "Star" field into the linked field to the "Star Collated" table

[dmclllop]

Can u please share with me a live example for what your suggesting ??

[TheTimeSavingCo · Accepted solution]

Sure, here

Link to base
You can duplicate the base to get the formulas etc

[dmclllop]

Really you have inspired me using lookups and i thank you for that, I got it done in different way though <

Thanks once more 😘