Auto populate cell information

Jon Brown

I have built a sheet for tracking production downtime in our facility.

I am looking to be able to streamline the amount of time it takes someone to entry sku specific information.

So say the item has the following traits (Sku Number: 1035) (Flavour: Orange) (UPC code: 1234567890) & (Brand: Smiths)

I would like to be able to make it so all the operator has to do is enter the SKU# (ie 1035) and the other information colunms for Flavour, UPC and brand auto populate by virtue of the fact that this particular sku was entered. (Perhaps from another table)

So if I then entered another Sku # the other information columns would auto populate with different data that pertains to the different sku.

Hopefully that is explained well enough that you folks know what I am trying to do.

Is this possible in smartsheet?

Thanks

Jon

Mike Wilday

There are probably a couple of routes to complete this task. You could create your table within the same sheet, hide the columns, and then reference that table in your smartsheet using the lookup formula to have the other data populated. 

Here is a help for the lookup function. https://help.smartsheet.com/articles/775363-using-formulas#lookup

One drawback is that lookup doesn't work across different sheets. Hence, the need to have the data in your current sheet, but it could be hidden so that your users don't see it on a regular basis. You could also create some nasty nested if statements, but that would be a cumbersome process for each column that you need to populate. Lookup might be the best option. 

steven.reed14881

I agree that Lookup is the right way to go here. One thing you should consider that I learned the hard way in my first usage with Smartsheet is the placement of your lookup table. Initially, I created my lookup table in some extra columns off to the right of the primary columns and then hid those columns on the template that I shared. What I did not anticipate was the impact when my users deleted or added a row.

All of a sudden, various sheets were not populating properly. When I investigated the issues, invariably were due to either deletion or addition of rows which disrupted the lookup table. So my solution was to move the lookup table to rows far below the expected data entry range. This way additions or deletions of rows has no impact on the lookup functionality any more.

Hope this helps!

Steve

Mike Wilday

Have you ever considered putting them at the top and then hiding those rows? I would imagine that the lookup table wouldn't be messed with at that point. Although sorting and the likes could also cause issues I guess. This is clearly a lacking feature of smartsheets. Good luck with a solution! Hopefully our comments have helped.

Mehmet Zengin

Just a small addition to Mike's answer;

In order to protect my lookup table data, I create a row ,generally at the bottom of the sheet, and type "Setup Row" on the primary column of this row. Below that row I enter all my table data and make them child rows of my "Setup Row". As a final touch, I lock "Setup Row". 

This way user are not available to touch them and sorting does not mess it.

J. Craig Williams

In general, in my experience, if deletion of rows becomes a problem, there is a problem with the process. Using Reports instead of the sheet itself can sometimes eliminate this.

Craig