automatic input company name & address pressing branch #

[MEI]

I have the database setup in Excel for our branches but instead of typing in
each branch location address each time I provide a quotation, I would like to
be able to type in the branch number and have the branch location address and
branch manager name automatically be inserted on the quotation form I have
set up in Excel. I purchased a Macros program but it's above my head.

 

[Stefano Gatto]

I wouldn't use a VBA macro to do this, but simply a number of concatenated
vlookups that return the different fields of your database of branch
addresses. Then the user would type into a cell the branch number and the
vlookup cell would immediately dispay the branch name&address corresponding
to that number. The seed number can remain displayed, but if you don't want
it to be visible on the form then simply have that cell outside of the
PrintArea or have its font color's the same as the cell's background's color.
You may post this question on the "worksheet functions" discussion group, to
have better idea.

 

[MEI]

Can you tell me step by step how to do this? I'm very new to this. Thanks
for any help you can give.

 

[Stefano Gatto]

In the cell where you want the address to appear, enter the following formula:

=VLOOKUP(M11,Customers,2,FALSE)&CHAR(10)&VLOOKUP(M11,Customers,3,FALSE)&CHAR(10)&VLOOKUP(M11,Customers,4,FALSE)&CHAR(10)&VLOOKUP(M11,Customers,5,FALSE)
(on one line)

M11 is the cell where you'll enter the number of the customer for which you
want to display its address. Choose another cell if you want.

Customers is a Name pointing on the 5-columns range containing your list of
addresses.

Column 1 will contain the address number (that you type in M11)
Column 2 will contain the name of the customer
Column 3 will contain the branch manager
Column 4 will contain the street address
Column 5 will contain the city + zip

To make the Name "customers", highlight the entire database range and use
menu Insert/Name/Define.

I hope this helps.