Help

Re: formula to filter results - please help

Solved
Jump to Solution
885 1
cancel
Showing results for 
Search instead for 
Did you mean: 
dmclllop
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

Capture.JPG

1 Solution

Accepted Solutions

Sure, here

Screenshot 2023-02-20 at 2.50.15 PM.png

Screenshot 2023-02-20 at 2.50.12 PM.png

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

See Solution in Thread

4 Replies 4

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

Screenshot 2023-02-20 at 2.50.15 PM.png

Screenshot 2023-02-20 at 2.50.12 PM.png

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 😘