Lookups

Si Spence

i'm new to lookups in SS and am struggling to see where to put the data tables so that they don't interfere with the main body of the SS.  I have 50 customers in a dropdown menu and i want to look up their corresponding; payment terms, account number etc

See:

https://app.smartsheet.com/b/publish?EQBCT=0de28f1de0ba4a69b4a32721ec790cfd

Doing this I have to hide columns but because i can't hide rows my SS starts after row 50.

Doesn't work at all well like this.

Any ideas please?   Thank you.

Si.

Mehmet Zengin

Hi,

Here how I manage such a situation.

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 close the "Setup Row" (so it doesn't show like a lot of rows used within the sheet) and  I lock it.

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

Mehmet Zengin

I have added two screenshots to illustrate it.

I color the "Setup Row" red so that users know they shouldn't go below that row in order to keep lookup formulas running.

Mike Wilday

Mehmet's solution is a good one. You could do the same thing on the top of your sheet to keep your "lookup" rows at the top of your sheet as well. I prefer this type of data at the top in cases where web-forms are being used to add data and it's set up to add to to the bottom of my sheets. 

To make child rows, simply use the indent button on your toolbar.  See screenshot for clarification.

Si Spence

Thanks Mike,  I have moved the data table to the top and this is a neat build that works well.  Thank you for the suggestion.

 

I don't suppose you know why formulas and look-ups built into a SS don't work when the info is input via a webform?

Thanks again.  Si.

L_123

Si

They don't work due to how the individual cells are identified. The cells are identified by a serial number rather than location, which means that locations can update when new information is added. You can make the information show up where you want to if there is any sort of pattern to the data being input that is 100% predictable, for example I have audit grades being put in one of my sheets and I know that each cycle contains 24 rows. I used that to set the location of my math. The biggest thing about making math work on these types of sheets is using entire column references, and limiting the individual cell references to cells on the same row as the math.