excel guru needed

  dunc1234 12:44 09 Oct 07
Locked

hi, this is probably simple to someone but i'm struggling.

i'm doing a very basic invoice program in excel with sheet1 being the invoice page and sheet2 storing the customer details, eg columns A1= company name, B1=street,C1=town,D1=county and E1=postode.

on the invoice sheet cell G2 has a dropdown list that relates to the company name on sheet2.

what i want to achieve is when a company name is selected from the dropdown list then the cells below auto-complete the rest of the address.

a big thanks to anyone who can help me on this

  silverous 14:48 09 Oct 07

It is doable but would probably be more suited to using access.

In the absence of that, you could use vlookups.

e.g. when you choose a company from the drop down, in the address cells have:

vlookup(g2,sheet2:$A$1:$E$600,2,FALSE)

(I'm assuming 600 addresses but you can increase that number). That will bring back the street.

You then put a similar formula but change the "2" to be 3,4,5 etc. to get back the other bits of the address?

  dunc1234 18:01 09 Oct 07

thanx silverous, with that and extra reseach got the formula working with this one

=INDEX($B$32:$B$34,MATCH(G2,$A$32:$A$34,FALSE),1)

the range in column A is the company name
the range in column B is the next field of street

cell G2 has the dropdown box where the company name is entered.

entering the formula in cell G3 produce the street field, enter in cell G4 the same formula but change range b32:b34 to c32:c34 gets the next field.

thanx again for getting me started on the solution i needed.

  silverous 19:50 09 Oct 07

Glad it worked, please tick the resolved box!

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Best iMovie for Mac tips 2017 | How to edit video with iMovie: 26 brilliant video editing tricks…