Help: Using =IF and Replace

Nickelas Brady

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.

R

Nickelas

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.

Nickelas Brady

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

R

Nickelas