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

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

Sure, here

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

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

Thanks once more 😘