MS Excel question - DPChallenge Forums

Canon RF 24-70mm f/2.8 L IS

02/27/2016 04:43:45 PM · #1

Hello clever people, I need some help with Excel, please.

One of my most boring jobs at work is to enter peopleâ��s details into a spreadsheet.

I have five columns:

1. Name
2. Email
3. City
4. State/county
5. Country

To save time, I wish that the recurring ones would just populate automatically, for example if I type Melbourne, it should then enter Victoria / Australia, or if I type Sussex, it should finish England.

I realize I have to somehow set this up for each city/state, but for the most common ones, it would be worth taking the time to do that.
Is this done via a formula - some sort of â��if/thenâ�� magic?

I would appreciate if someone could point me to a tutorial for this.
Thanks a bunch. Karin

kirbic

Canon EOS R5

02/27/2016 05:01:03 PM · #2

There are a number of ways to skin that cat. The one thing that can kill nearly all of the potential solutions is if there is more than one correct state/country for the corresponding city. In the US, this is so common as to make an auto-fill nearly useless. Don't know if that is true or not for your data.

What version of Office are you working with? There are solutions that would be more workable with newer versions (2013, 2016) than with older ones.

02/27/2016 05:17:22 PM · #3

Thanks Fritz....... I was crossing fingers to be hearing from you :-)

I have two versions - 2010 and 2013. I prefer the 2010 one, but will be happy to use 2013 if that fixes it.

I had considered the problem re identical names and decided it wasn't much of an issue for me. I'd be quite happy to just set up a number of the most common ones, and if something did double up, I'd still have a 50ish% chance of it being right, and if it is wrong, I'll correct it.

I'm glad you say there ARE ways....... I look forward to learning about them. Thank you!

02/27/2016 06:25:08 PM · #4

Have you considered using a pick-list instead? Once you click into the box, just type the first letter of the state you want. Not as fast as auto-populate, but it works. And, since you'll already have the list built, you can change to auto-populate later if that works out.

Also, I would think ahead to sorting the data later & make separate fields for first name
& last name.

02/27/2016 06:27:18 PM · #5

.

Message edited by author 2016-02-27 18:28:23.

02/27/2016 07:00:54 PM · #6

I don't know what a pick-list is, but yes, I am making use of the other thing (i.e. start typing and let it fill in). As you said.... not as fast as auto populate, hence I'd like to find a better way.

02/27/2016 07:37:34 PM · #7

Oh, sorry! You might refer to it by another name. If you've ever bought anything online, & entered your shipping address, you've probably encountered a pick-list when you click into the box for your state & a list appears for you to pick from.

02/27/2016 08:34:58 PM · #8

Never mind, my clever hubby figured it out for me - something about a "look-up" table on another sheet. And it works!!!! Woooo hoooo!