Filter by formula

NateP

I have several columns which I typically have a formula in nearly every row (like you would use a column formula) but I've decided to have the formula be cell based because every once in a while, I need to input a manual number instead of deriving from a formula. My current situation is that I'm using reference manager to look up values from another sheet. Sometimes those values do not exist yet but will in the future as employees enter their data. Once they do, it would be nice to have a filter that is simply "uses a formula" or "doesn't use a formula" and it would show or not show the manual entries. Then I would know which ones I need to investigate or change to a formula. I know the little blue triangle shows external references but I have nearly a thousand rows to sort through so filtering would help a lot.

Genevieve P.

Hi @NateP

There currently isn't a way to apply a filter to only show the rows that contain a formula, the blue arrow is the only indicator at this time. Please provide your feedback to the Product team so they can hear about your use-case!

Thank you,

Genevieve

Kelly Moore

Hey @NateP

This work-around will not help with your current problem but it could help the future entries...

Have you considered providing a helper column(s) for manual entries? Then your formula becomes IF there is an entry in [manual entry], use [manual entry], otherwise use [formula]. This would take some of the work off of you in maintaining formulas in the column, as well as designating which cells had manual entries.

cheers

Kelly

NateP

Thanks for the help ladies. I think that the filter feature in smartsheet is good but I definitely would like to see some enhancements so its a little more in depth like the automations.

@Kelly Moore This seems like a good idea as it will override the manual data once real-time data is populated. I have quite a few columns to add now but I like that it'll be obvious which columns have manual data but I'm going to switch the order to look for the formula first and if 0, refer to the manual data field. Then once data is populated for the formula, it'll override the manual data and fill in. Then I don't have to do future work to change values...totally automated.

NateP

For posterity, here is the formula I ended up using and it works beautifully. Now I'm going to go through and fill out my estimated times but once real-time data in my other sheet is added, it'll override my estimate.

=IF(AVERAGEIF({Part # reference 1}, SKU@row, {Machine time/part}) <> 0, AVERAGEIF({Part # reference 1}, SKU@row, {Machine time/part}), [Estimated time/part]@row)