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

ChrisWolfeENG · 04/23/21

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

Uploaded Apr 23, 20219.13 KB

Formula Error2.PNG

Uploaded Apr 23, 202114.2 KB

Paul Newcome · 04/23/21

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 · 04/23/21

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 · 04/26/21

=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 · 04/26/21

Happy to help. 👍️

Paul Newcome · 04/23/21

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 · 04/23/21

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 · 04/23/21

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 · 04/26/21

=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 · 04/26/21

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 · 04/26/21

Happy to help. 👍️

Formulas and Functions

Formulas and Functions

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

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA