Entering Address Information

Peter Datz

One of our users has a sheet that includes an Address field to store mailing address information.  They have created this as a Text/Number field an enter multiple lines of data (c/o, address line 1, address, line 2, city/state/zip).  The same Address information applies to mutliple rows of data, but currently they need to re-enter or find another row and copy this information.  I tried converting the field to a drop-down, but this only auto-populates the first row of data.

 

Apart from possibly splitting this into multiple fields, is there anything else we could do to handle this better?

Tony Davidson - SmartSheet Certified User 2016

They can just hover over the bottom right of the cell and drag it all the way down as far as needed for the most simplistic method.  I am sure a formula could achieve the same outcome.  Also, not sure if it would make sense but you could also use a web form to populate the information.  In my brain I would also structure this as separate items instead of one cell.  Tony

Peter Datz

Yes, the problem is that the rows with this contact information would not be contiguous... so she can't really drag the cells down.  She may have on back on row 100, but is not entering row 500.  She doesn't even necessarily now that she's entered something similar before, but if this behaved as a drop-down with suggested matches based on input she would be able to select from previous entries.

 

I agree with this being better desiged as separate fields, but the cat's out of the bag in this case... they have nearly 1000 rows of data now.

J. Craig Williams

A few gotchas here.

 

There is no formula for formatting text with a line-feed/carriage return so separating them out and then joining them back together may not look correct.

However, if the address information is going into a label for real old fashioned mail, then separately them will be a must. But you said apart from that.

 

If there are lots of duplicates, I would likely start by filtering - say on zip. That would narrow my search quickly.

 

And to your point about the cat -- 1000 rows would not take long to separate into its component pieces given someone that knows how to parse things. And not long is less than an hour, depending on how close the "rules" the persons entering the data followed. For such things, there are two costs - the costs to do it (short term) and the costs to NOT do it (long term). Both of those can be calculated.

 

Craig