Excel - Populating a field by a combox box 1

[MrMcFestoe]

I have a worksheet called invoice at L16 i have a combo box that gets customer account number from a sheet called customers this works at last, what i am trying to do now is populate the address fields on the main invoice with the customer the combo box =

in the invoice the address label is at c d e 9 10 11 12 13 14 and the address in the customers worksheet is at b1, i have done this in access but excel is new to me and have no idea.

Any help or ideas thanks.

 

[SkipVought]

MrMcFestoe,

You have a worksheet called invoice.

At L16 you have a combo box that gets customer account number from a sheet called customers (where you have a list of customer account numbers and corresponding addresses)

On the invoice the address label is
columns c - e
rows 9 - 14

The lookup address is on the customers sheet in column b.

Is there just ONE column for address?

Does sheet customers, column b map to sheet invoice column c row 9?

What goes in columns d & e on row 9?

What goes in rows 10 - 14?

Is the account number in column A on sheet customers?

???

Enquiring minds need to know! ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MrMcFestoe]

Skip

Thanks
for the quick reply,

I see from your post i need fields

Name
Address line 1
Address Line 2
Address Line 3
Town
Postcode

In my customers work sheet instead of at the moment i have one field for all this.

The invoice has address label columns c - e rows 9 - 14 so it would be set out as above but i my customers worksheet there is only one feild for this information does this need to be split up ?

I made the address label three columns by 6 rows the columns have been merged so i suppose its c 9 to c 14.

Yep the acc number is in column a on the customers worksheet.

Thanks

If that does not make sense let me know,

 

[SkipVought]

One more piece of info -- where is the cell link from your combo box? Let's assume it ALWAYS in A1.

Then on your customer sheet, you'll need

AcctNbr, Name, Addr1, Addr2, Addr3, Town, PostalCode

So your lookup values are (assuming that your customers table has 999 rows)
enter in C9...

=vlookup(A1,Customers!$A$1:$G$999,Row()-7,False)

copy that formula from C9 to C14.


BTW, This will ONLY work in rows 9 - 14 since I am using Row()-7 to calculate the corresponding COLUMN on sheet Customers.





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MrMcFestoe]

Skip

Thanks for the replie yet again it was 12pm last night when we started this going to give it a try,

in you vlookup statement can you explain what the $ sign does, iam right i thinking its a varible for rows?

thanks

 

[SkipVought]

The $ sigh in an Excel formula makes the reference ABSOLUTE. For instance, if youo enter the formula in B1

=A1

and then COPY that formula to B2, the resulting formula will be

=A2

HOWEVER, if your formula in B1 were

=$A$1

then that same copy operation would yeild

=$A$1

in B2.

So

=vlookup(A1,Customers!$A$1:$G$999,Row()-7,False)

means that when you copy the formula to the next row, A1 will become A2 BUT Customers!$A$1:$G$999 will NOT change -- it is an ABSOLUTE reference.




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MrMcFestoe]

now i see i think, got your code working a big thanks, just one thing if one of the fields is empty it returns a 0 is there a way to deal with empty fields?

If the worksheet is protected then the above wont work is there any way around this to stop people changing things.

Sorry to be a pain,

 

[SkipVought]

is there a way to deal with empty fields?

=if(isblank(vlookup(A1,Customers!$A$1:$G$999,Row()-7,False)
),"",vlookup(A1,Customers!$A$1:$G$999,Row()-7,False)
)

You can insert formulas and then protect the sheet. That's the only way.

It's one of the drawbacks of distributed workbooks -- users can change things, including things you don't want them to change.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MrMcFestoe]

Skip

A star on its way, for the learning curve you have help me learn a bit more, only been using excel for about 2months.

Is there a auto number in excel like access for invoice numbers?

Enjoy the star,

 

[SkipVought]

There is no AutoNumber as in Access. What are you trying to do? Do you have a Table and an Invoice Form to populate from the Table? If so you might want to chsek out faq68-4223 Mail Merge in Excel.

Bu-Bye I'm off to the house!

Skip,

http://www.TheOfficeExperts.com

 

[MrMcFestoe]

Skip

Trying to have a unique number on a invoice so ther are no duplicates and wanted it to increment by one each time so records are not overwritten.

 

[SkipVought]

How do you propose to use this application? Is this a mass process or a let-me-enter-the-next-invoice, tap, tap, tap?

Short of a programming solution, you could enter this expression in A3 & copy down as far as you need, assuming that your Invoice Number starts with some value in A2...

=If(IsBlank(B3),"",A2+1)

Then, when you enter a value in column B, the "autonumber" appears in Column A. You just better not delete or overwrite column A formula

Skip,

http://www.TheOfficeExperts.com