Look for a formula within a cell

DJB

I'm looking for a way to error check my sheet to find instances of missing formulas. EG someone overwrites the formula(s), and then adds a row, which now is missing the formula(s). Or by adding a blank row (EG Parent) before adding new rows of data. (smartsheet will copy the formulas down to new rows if the previous two rows have the formulas.)

I tried using filters, but apparently they don't look within formulas. It does work if the formula always results in a character of some sort, (i can filter by blanks) but not if the result is blank (EG VLookup on a blank cell). A filter would be ideal.

I also could not find help on using a formula. In a formula, I was hoping for something like =countif(contains "=",cell@row) or something where it counts the cells that have a formula, whether the formula result is blank or a value.

Lucas Rayala

Hi @DJB there's currently not a great way to reference the formulas -- I actually use the API and Python code to do something similar. Question, however -- can you use column formulas instead of the individual drag/copy method? Here's some info on column formulas (they make life a lot easier, but may take a little bit to figure out):

Use column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center

Lucas Rayala

Hi @DJB there's currently not a great way to reference the formulas -- I actually use the API and Python code to do something similar. Question, however -- can you use column formulas instead of the individual drag/copy method? Here's some info on column formulas (they make life a lot easier, but may take a little bit to figure out):

Use column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center

DJB

@Lucas Rayala Oh wow. It looks like this will help. Thanks! Sorry for my late response. I missed your response earlier.

Lucas Rayala

Hi @DJB - glad you got back to this then! When you create a column formula, just remember you can only do MyColumn@row references (vs MyColumn12 if you want the 12th row).

If you need to reference a value in a single row in a column formula, you can create an entry in the Summary tab (Example: MyEntry) that points to the individual cell in the sheet (i.e. =MyColumn12). In your column formula, you can point to that Summary tab entry using the hashtag format, i.e.:

=IF(OtherColumn@row=MyEntry#, "True", "False")