Can I autofill a column with a value found in the column?
I have the following columns: [Building and Room], [Room Type]. In any given row in [Room Type] - there will be a value of "Standard" or "Non-Standard".
Is there a way to look at values in [Room Type] and then autofill the rest of the cells in that column with that same value?
The tricky thing is that "Standard" and "Non-Standard" pertain to a certain [Building and Room] location. For example Room 123 = "Standard", but Room 234 = "Non-Standard".
I won't know if location (Room 123) is a Standard or Non-Standard room until after an inventory of that room is complete.
The other tricky part is that I'm also including columns that calculate price rates based on that "Standard" or "Non-Standard" designator.
I've created a smartsheet form whereby I designate that a room is "Standard" or "Non-Standard" - but that form is not completed until after the inventory is completed.
I suppose that the "Standard" or "Non-Standard" designator could be cut and paste into the correct row once it's been determined that the space is standard vs. non-standard, and then my calculations would work - but I was hoping that there was a more automated way.
Answers
-
Hello @AidenM,
If I read correctly, you would like to be able to specify "Standard" and "Non-Standard" in [Room Type], then based upon that selection have a series of columns auto-populate with data?
To achieve this, you could have a separate "Reference Sheet" that details what you would like in those extra columns and then make use of INDEX and MATCH to populate your "Working Sheet". An advantage of setting it up this way would be that any changes to the "Reference Sheet" would be reflected in the "Working Sheet".
Reference Sheet Demo - This is just a data sheet really detailing the information you want to associate with "Standard" and "Non-Standard"
Working Sheet Demo - Using a Cross Sheet Reference, an INDEX/MATCH formula can retrieve the information from the "Reference Sheet" into your "Working Sheet" based upon the information in [Room Type] .
If we take [Demo Info 1] for example, the formula is retrieving "Std 1" from the "Reference Sheet" when [Room Type] is "Standard" and "Non-Std-1" when [Room Type] is "Non-Standard"
=INDEX({Reference Sheet - Demo Info 1}, MATCH([Room Type]@row, {Reference Sheet - Room Type}, 0))
If you were to make the cross sheet references using the INDEX/MATCH function for all the other columns you require, you should be able to retrieve all the relevant information against the selection in [Room Type].
I hope that is along the lines of what you were looking for and something in the above is helpful to you in some way.
Protonsponge
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!