excel guru needed

  dunc1234 12:44 09 Oct 2007
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 2007

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 2007

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 2007

Glad it worked, please tick the resolved box!

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

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Adobe shows still-in-development tools, including automatically colourising black-and-white photos

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?