Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Help: Using =IF and Replace

Hello All,


I need help making a formula that will replace a cell with Static Information with a pre-assigned phrase when the information present in a row's range (IE [Room Assignment]22:[Room Assignment]50) matches cell's information.


I'm lost on how to make this happen, and hope the solution is simple.


I have a limited availabilitie of rooms, and I have 4 financial liaison(s). The rooms are listed above form entries so all managers can see rooms with special conditions.




Any help would be very appreciated thank you so much.




Smartsheet Help 1.jpg

Smartsheet Help 2.jpg


  • Greg Gates
    Greg Gates ✭✭✭✭✭

    If I understand your situation correctly, you should definitely be able to do this!


    In the space on the left side of your sheet where you have the lists of rooms, I would replace each one with a formula that checks for the existence of the relevant dropdown option in your room assignment column. That's as clear as mud, so let me give an example :)


    In the cell that currently just says "SM L1" I would replace it with something like this formula:


    =IF(COUNTIF([Room Assignment]22:[Room Assignment]50, "SM L1 - Bearadise king + queen bunks") > 0, "SM L1 - Sold", "SM L1")


    Basically, this formula checks your Room Assignment column for the exact wording provided. If there's at least one cell that has that dropdown value, it will set the cell to "SM L1 - Sold." Otherwise, it will leave the text as "SM L1." Use a similar formula for every other room you have available.


    That formula assumes that there's only one dropdown option per room in your list. If that isn't the case though, you could modify slightly and use the COUNTIFS formula instead to check for multiple dropdown options.


    I hope that helps! Let me know if it doesn't solve your issue.

  • God Bless, OMG you solved it! Man your awesome.




This discussion has been closed.