Combination of free text and formula in one cell

Anastasija Popova

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?

Paul Newcome

If you enter data manually, it will override and delete the formula. The formula would then need to be re-entered 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.

Paul Newcome

If you removed it from a cell in the column, then it would no longer be a column formula. Smartsheet does have built in auto-fill 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.

Sean Morgan

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

Anastasija Popova

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?

Paul Newcome

If you enter data manually, it will override and delete the formula. The formula would then need to be re-entered 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.

Anastasija Popova

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?

Paul Newcome

If you removed it from a cell in the column, then it would no longer be a column formula. Smartsheet does have built in auto-fill 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.

Anastasija Popova

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!

Paul Newcome

Happy to help. 👍️

The auto-fill feature has been around in Smartsheet for quite some time, but it got pretty overshadowed when they recently rolled out the column formulas.

Anastasija Popova

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!