Look for a formula within a cell
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.
Best Answer
-
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
Answers
-
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 Oh wow. It looks like this will help. Thanks! Sorry for my late response. I missed your response earlier.
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!