Master Contact List for use across sheets in a workspace

Paul Newcome

Ugh. I can't even begin to tell you how many times I have forgotten to change the column type until something isn't working right. Haha. I'm glad you were able to get it figured out.

Rsteer

I share the same ORIGINAL question as @Cheryl Crouse and @Paul Lambert . (Caveat/background: I'm a Smartsheet account admin, but a fairly novice user.)

@Paul Newcome's solution works to simply look up the email associated with a name, but it does not seem to really be creating a contact list entry that can be used the way @Cheryl Crouse, @Paul Lambert, @Bill Rigney, @Andria Hoyle, and I would like. (Although see my parenthetical caveat at the end -- but even that doesn't negate the first point below.)

😕 Most importantly, this approach can't populate a drop-down list in a column header, so if multiple sheets share that same drop-down list (staff, contacts, stakeholders, etc.), each sheet and column has to be individually changed each time the personnel list changes. Extremely time-wasting. Not only is the drop-down potentially an important user-interface feature, it is ALSO the only way to use those people as "lanes" in a card view. Simply looking up an email to fill a cell is not the same.

It should be a fairly obvious feature for Smartsheet that the drop-down setting in a column header (of contact-list type) would allow reference to a 3-column sheet containing email, first name, and last name. You know, choose one option: reference a contact sheet or manual entry of a list. (In fact, even other data types should allow the drop-down to be populated by reference to a sheet.)

😕 Although you can designate a column to be of type "Contact List", which helped @Luis Salguero make his formula work by having the formula field and reference target field be of the same type, the contact-list field type is actually a (somewhat puzzling but cool) compound data type that combines a first name, last name, and email. It appears to me that the lookup function in Paul Newcome's solution would only populate the email element, leaving the two name elements in every entry blank. If that's true, it seems you could just as well give the columns with the emails (both the formula and the lookup target) the data type of "text" and get the same effect. (I think I'm assuming here that the target cells in the source sheet are really just emails; if they are true contact-list items, maybe all the properties are copied into the formula cell -- that's not clear from the examples that only show a resulting email.)

Jeff Hickey

Maybe I'm misunderstanding the thread here and if so, my apologies, but I thought the original post was talking about was updating a master contact list (available contacts to select from)? The thread and the suggestion re: INDEX/MATCH formula, appears to assume that at least one variable is input (e.g. first name) and the formula fills out the needed field (e.g. email, in this example). How does one know which "first name" to choose from? the Contact List of first names should be presented to the user to select from and, the way I understand the original question, it's this list that we're looking to maintain centrally. Also, if using the index/match formula, does this refresh records that had already been updated (i.e. email found and filled in) or does it only find the requested field when that field is blank originally?