How can I validate contact names I have pasted into grid sheet?

Simon Cowx

Hello I am a user of Smartsheet and Resource Management for Smartsheet. I have these linked together to allow resources allocations in SS to be shown in RMS. However I have an issue. I want to put leave in as a project, but I need to paste leave information into an SS plan.

I can do this but the pasted names are not recognised as a contact until I click on the name and selected the suggest name from the contact lists. For example I past the name "Simon Cowx" in to the the contact list column, but this is not recognised as an existing contact until I click on the resource name suggested by Smart sheet

How can I validate all the names as existing contact/resources with out having to manually go though each row?

Genevieve P.

Hi @Simon Cowx

Text pasted into a Contact column will be read as text, as you've found, and not automatically converted into a Contact type of value.

What I would do in this instance is have a second, Text/Number column where I'm pasting in the text values for the contacts. Then, depending on the number of contacts you have, we could either use an Assign People workflow to update the Contact column with the correct contact, or use a formula to get the right data.

I personally would lean towards the formula route. We'd set up a "reference sheet" somewhere with two columns: one with all the text names and one with the corresponding Contact value for that name.

Then we could use an INDEX(MATCH formula in your actual sheet that looks at the reference sheet to bring back the contact value based on the matching text value. Something like this:

=INDEX({Contact Column Reference}, MATCH([Text Name]@row, {Text Name Reference}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve

Simon Cowx

Thanks Genevieve, I'll give the formulae approach a try.