How to automatically populate a cell with same info from another cell on the same row

ChrisWolfeENG

Hello,

First time SmartSheet Community Member!

I have a sheet with specific rooms locations in separate columns.

What I am trying to do is automatically populate the Room column with anything that’s populated in any of the Building Room columns to the right on that same row.

I thought a VLOOKUP was the best to achieve this. I am not well versed in formulas at all, and researching online has been somewhat helpful, but I’m still having an issue.

The formula I am trying in the Room column is:

=VLOOKUP([Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, "$", Rooms1, 2)

**The Room column is actually the 2nd column**

Where if anything is populated in the columns Building 1st Floor Rooms thru Building 3rd Floor Rooms on the same row, automatically take that text and populate it in the Room column on that same row.

I’m getting a #CIRCULAR RFERENCE error indicating that the formula is referencing itself. If I change any of the parameters, I get a #UNPARSEABLE error and hit a stopping point.

A screenshots are attached for reference. If there is a better way to achieve this, I am interested in learning. Any and all assistance is greatly appreciated!

Formula Error.PNG

Formula Error2.PNG

Paul Newcome

Try this...

=JOIN(COLLECT([Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, [Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, @cell <> ""))

Terri Leong

An IF statement should produce the results you want. In the "Rooms" column, enter =IF(NOT(ISBLANK([Building 1st Floor Rooms]@row)), [Building 1st Floor Rooms]@row, IF(NOT(ISBLANK([Building 2nd Floor Rooms]@row)), [Building 2nd Floor Rooms]@row, IF(NOT(ISBLANK([Building 3rd Floor Rooms]@row)), [Building 3rd Floor Rooms]@row, "")))

Just make sure you enter the columns in this formula in the order you want them evaluated. In the above, the formula looks for if someone has entered a 1st floor room before moving on to see if they have entered a 2nd floor room. So with this formula if someone enters both a 1st Floor room and 2nd Floor room, the formula will bring back the 1st floor only.

Good luck!

Paul Newcome

=JOIN(COLLECT([Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, [Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, @cell <> ""))

We use the JOIN function to pull together each of the "Floor" columns. We use the COLLECT function to filter which columns are pulled.

So the first range is the range we want to pull.

[Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row

We don't want to pull any blanks, so we repeat the range.

[Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row

And use the criteria of "is not equal to blank".

@ cell <> ""

@cell just tells the formula to evaluate the range on a cell by cell basis.

<> " means not equal to blank. I personally find it easier than NOT(ISBALNK(@cell)).

Paul Newcome

Happy to help. 👍️

Paul Newcome

Try this...

=JOIN(COLLECT([Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, [Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, @cell <> ""))

ChrisWolfeENG

Paul, this is awesome! This does exactly what I'm trying to achieve. Thank you!

I would love to know how to read this from a very beginner's perspective. What does the @cell <> "" do in this instance of the formula?

Thank you again for your time and expertise! This is perfect.

Terri Leong

An IF statement should produce the results you want. In the "Rooms" column, enter =IF(NOT(ISBLANK([Building 1st Floor Rooms]@row)), [Building 1st Floor Rooms]@row, IF(NOT(ISBLANK([Building 2nd Floor Rooms]@row)), [Building 2nd Floor Rooms]@row, IF(NOT(ISBLANK([Building 3rd Floor Rooms]@row)), [Building 3rd Floor Rooms]@row, "")))

Just make sure you enter the columns in this formula in the order you want them evaluated. In the above, the formula looks for if someone has entered a 1st floor room before moving on to see if they have entered a 2nd floor room. So with this formula if someone enters both a 1st Floor room and 2nd Floor room, the formula will bring back the 1st floor only.

Good luck!

Paul Newcome

=JOIN(COLLECT([Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, [Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row, @cell <> ""))

We use the JOIN function to pull together each of the "Floor" columns. We use the COLLECT function to filter which columns are pulled.

So the first range is the range we want to pull.

[Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row

We don't want to pull any blanks, so we repeat the range.

[Building 1st Floor Rooms]@row:[Building 3rd Floor Rooms]@row

And use the criteria of "is not equal to blank".

@ cell <> ""

@cell just tells the formula to evaluate the range on a cell by cell basis.

<> " means not equal to blank. I personally find it easier than NOT(ISBALNK(@cell)).

ChrisWolfeENG

Hey Paul & Terri,

Thank you so much for the clear and concise explanation and insight! This helps out greatly. Thank you!

Best Regards,

Chris

Paul Newcome

Happy to help. 👍️