Combination of free text and formula in one cell
Dear community,
Do you think there is a way that will allow me to enter free text in a cell which has a formula in case the formula does not return any value? Or a workaround?
I linked two sheets.
Sheet 1:
"Report No.1"  range, "Country+Disease1"(text) match
Sheet 2:
"Report No.2"  my search value, "Country+Disease2"(text) v lookup function column.
If the Sheet 1 has the same Report no, then Country+Disease2 is prepopulated with data from the Sheet1. But it there is no report number to match in the sheet1, I need to be able to enter a new value in Country+Disease manually. If the formula is there, I cannot.
Could you help me, please?
Best Answers

If you enter data manually, it will override and delete the formula. The formula would then need to be reentered once there is a match. There are a number of ways that you can flag a row to let you know that there is a match and the formula needs to be entered to pull.

If you removed it from a cell in the column, then it would no longer be a column formula. Smartsheet does have built in autofill rules where as long as you have at least two rows with the formula in it above and/or below the new row, it should automatically grab the formula.
Answers

Hello @Anastasija Popova ,
There are two ways that this could be done:
1) You could use the IFERROR function so that if produces an error such as #NOMATCH, you can have it enter text. See more on this function here: https://help.smartsheet.com/function/iferror, and the following example of how IFERROR can be used:
=IFERROR([Free Text]@row / [Free Text 2]@row, "No Calculation Performed")
2) You could use the JOIN function to add text onto the result of the Formula. See more on this here: https://help.smartsheet.com/function/join, and the following example of how JOIN is used:
=JOIN([Free Text]@row:[Free Text 2]@row, " ")
Let me know if you have any questions!
Regards
Sean

Dear @Sean Morgan,
Thank you so much for the answer ( and sorry it took me so long to check it).
I do not think it suits me though (or maybe I misunderstand the answer). I need to be able to enter any free text in a column with existing formula, not a specific prefilled text as in the provided examples (e.g. "No Calculation Performed" or " ").
So that if my search formula returns nothing, I can write in the same cell any text I want freely. Is that possible?

If you enter data manually, it will override and delete the formula. The formula would then need to be reentered once there is a match. There are a number of ways that you can flag a row to let you know that there is a match and the formula needs to be entered to pull.

Dear @Paul Newcome thanks!
I actually have the column formula and I need it to stay as such. Any way to overwrite a column formula in a certain cell?

If you removed it from a cell in the column, then it would no longer be a column formula. Smartsheet does have built in autofill rules where as long as you have at least two rows with the formula in it above and/or below the new row, it should automatically grab the formula.

Dear @Paul Newcome This is so simple, I am ashamed I asked this question.
Thank you so much! I did not know about this feature in Smartsheet of automatic fill in of the formula. Brilliant!
Very helpful, thank you so much!

Happy to help. 👍️
The autofill feature has been around in Smartsheet for quite some time, but it got pretty overshadowed when they recently rolled out the column formulas.

Indeed, you are right. It worked like that for me. I had options of cell formula or column formula and it did not come to my mind that there can be something in between. Happy to learn!
Help Article Resources
Categories
Check out the Formula Handbook template!