Is there a formula or function that allows text to be removed and replaced elsewhere on the Sheet?

Sydney R.

Hard to explain just in the question box.

We have a staffing schedule for our company which allows us to see who is currently on jobs and who is coming up for availability for an upcoming job. We are trying to figure out if there is a type of formula or function that would allow us to type an employees name in next to the project name and will also remove that employee from the employees available portion of the sheet (see attachment).

Smartsheet Question.pdf

This may not even be possible. I have a feeling we may manually have to do this, but wanted to see if anyone had experienced anything similar. I thought about the Replace formula but we aren't taking names from a different sheet so I am unsure if that would work or not.

Any help an/or advice would be much appreciated!

Paul Newcome

Is this all on the same sheet or on different sheets?

Sydney R.

@Paul Newcome It is all in the same sheet. I'm not even sure if this is possible, I am just not extremely knowledgeable in all of the capabilities in Smartsheet.

Paul Newcome

My suggestion would be to insert a checkbox column. On the rows where you have the available people listed, you would use a formula like this one to check the box whenever a person's name is listed in the top portion (meaning they are unavailable):

=IF(COUNTIFS([Employee Name]:[Employee Name], @cell = [Employee Name]@row) > 1, 1)

Then you would set up conditional formatting to change the text color to white on rows where this box is checked. Or strikethrough so that people know "James" is on the list but not currently available. Or any other conditional formatting of your choice.