ExcelBanter - Create address database

You can use a macro to do this. However, do you want to save data in one
column only? It would be better to save each field in a different column,
such as
A Name; B Address; C City; D County; E Postal Code.
Not a good idea to have blank rows in a database!
Are all your customers one time customers? Surely you get return business,
and then need to start getting info from the database, rather than just
posting to it?

I would rather go for formulae that will first of all look up if a record
exists, and insert it if it exists. If it does not exist, then insert the
new record by all means. At this stage you can do it with a macro, which
will transpose the info into your database, and then sort and save the
database. Obviously, since you will have overtyped your formulae, you really
do not want to save your form at this stage! Instead, you should close and
re-open it. This cannot be done by a macro called from the form though. You
can also manually copy the new info, and paste special, transpose it into
your db.

For mine, I used formuale, which in the event of not finding a record,
requests that the record be added to the database first. I then manually add
the new contact details, save my database, and on going back to my template,
find the info filled in.
I type in the name of the contact in cell B4.

I used the following formula to check. I put this in an adjacant cell (K5
in my case), with font colour set to white
=IF($B$4="","",IF(ERROR.TYPE(VLOOKUP($B$4,Customer s,2,FALSE))=7,"Please
enter FULL ADDRESS DETAILS in customer
detail",IF(VLOOKUP($B$4,Customers,9,FALSE)<"","P O Box
"&VLOOKUP($B$4,Customers,9,FALSE),IF(VLOOKUP($B$4, Customers,2,FALSE)<"",VLOOKUP($B$4,Customers,2,FA LSE)&"
"&VLOOKUP($B$4,Customers,3,FALSE),IF(VLOOKUP($B$4, Customers,3,FALSE)<"",VLOOKUP($B$4,Customers,3,FA LSE),"")))))
K 6 - K 8 has the following formulae:
K6 :
=IF($B$4="","",IF(VLOOKUP($B$4,Customers,4,FALSE)= "",VLOOKUP($B$4,Customers,10,FALSE),VLOOKUP($B$4,C ustomers,4,FALSE)))
K7 : =IF($B$4="","",VLOOKUP($B$4,Customers,5,FALSE))
K8 :
=IF($B$4="","",IF(VLOOKUP($B$4,Customers,11,FALSE) ="","",VLOOKUP($B$4,Customers,11,FALSE)))

Where I actually want the detail to show (B5-B8 in my case) I used the
following formulae:

B5 :
=IF(ISNA(K5),VLOOKUP(B4,Customers,2,FALSE)&VLOOKUP (B4,Customers,3,FALSE),Sheet1!K5)
B6 : =IF(ISERROR(K6),K5,K6)
B7 : =IF(ISERROR(K7),K5,K7)
B8 : =IF(ISERROR(K8),K5,K8)

hth
"Jonny" wrote:

I have designed a template for cost sheets, quotations, orders etc that is
all linked in one file.

One tab in the file contains the clients address.

Every time I do a new quotation this must be filled in. the file is then
'saved as...' something rather than overwrite my template.

Is there a way to create a seperate file that would automatically add this
address to build up a database?

Please Note I have no idea how to use macro etc. I just use formulae.

Eg.
Adress Tab:

A B C D E

16 For the attention of:- (cell is merged horizontally)
17 Ste Jones
18 1 Wood Street
19 Durham
20 County Durham
21 DH1 1AA

This must then add on to a database in the same fashion. Next time I do a
quote the next clients address is inserted below this one, with maybe a line
to seperate them.

I would appreciate any help.

Regards,

Jonathan