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

  • Protonsponge
    Protonsponge Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!