List suburbs after entering postcode

**JoeyGirl** · 02-16-2011, 02:38 AM

Hi Everyone!

I've been given some fantastic help on here in the past so I thought I would throw this problem at you guys seeing as Google has come up dry.

I have been working on a pretty big file for the last few days, and I haven't been able to crack the method for this so I've been putting it off until last - and now last is here!

What I have is an "information" sheet where the results from a series of lookups are displayed based on a set of characteristics determined by the user. All of these inputs have a unique match in their relevant data sheets so I haven't had any problems - However with postcodes there can be any number of sububs within each and so the lookup formula I have been using for my other factors won't work.

What I would like to see is a manual input field for postcode and next to that a drop down list that will give me the names of all suburbs with that postcode.

Hopefully this will be simple enough, or exist somewhere else that I just haven't managed to find

Thanks in advance for any help you might be able to give!

**JBeaucaire** · 02-16-2011, 02:49 AM

'Sub DV List from a single larger list
I have a technique for creating smaller sublists from a larger list, all operating in the same cell. The page here leads to three different examples of ways to use it...
Sub DV Lists from a Larger DV List

There's a sample workbook there to make it easier for you to test it out, see it in action before you start to adapt it.

You would put your post codes in column a, the suburbs in column B. In my example you would have the unique column A values listed in column C, but you don't really need that for your project. Just allow free entry of postal code, and as long as it is in the sorted lists you created the drop down will appear.

Give it a try.

**JoeyGirl** · 02-16-2011, 04:30 AM

Hi JB!

This is exactly what I was looking for! I've tried copying what you've done in your workbook into mine except my DV box is evaluating to an error. My formula is:

=IF(TRIM(Sheet1!$B6)="", "No Postcode Entered", INDEX(suburbs, MATCH(Sheet1!$B6,postcode, 0)) : INDEX(suburbs, MATCH(Sheet1!$B6 & "zzz", postcode)))

Where B6 = the postcode to look up, suburbs = the index/match formula for column B on the location spreadsheet and postcode = the index/match formula for column A on the location spreadsheet. My postcodes are in ascending order. If it helps, the output box is supposed to go into D6.

I am going home now but will pick up with this again in the morning. Is there anything you can suggest that I am doing wrong?

Thanks!

**JBeaucaire** · 02-16-2011, 12:15 PM

Post up your workbook so I can see what you've done so far if you get stuck. Be sure to point out where you're working on the formula if it's not obvious.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**JoeyGirl** · 02-16-2011, 09:36 PM

My workbook is 33mb big, so I've created a second one with just the key factors (not even 1mb) but I'm having trouble uploading that!

Database error
The Excel Help Forum database has encountered a problem.

--------------------------------------------------------------------------------

Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the www.excelforum.com home page, then try to open another page.
Click the Back button to try another link.

The www.excelforum.com forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience.

I think I am just going to have to keep fiddling with it.. I'll try to upload the sample workbook again later.

Thanks

**JBeaucaire** · 02-17-2011, 11:57 AM

Try zipping the smaller wb.

**JoeyGirl** · 02-17-2011, 09:30 PM

Aha! I got it... I dont know what I did, I just kept deleting my attempts and trying again and then it just worked!

Fantastic formula JB - I can already see how the mechanics of this are going to be useful in a whole host of other workbooks

Thanks again!