Discussion Locked

Excel2000 database question

0 helpful
15 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello
I want to maintain a small contact databse in Excel, I have set up a worksheet, for example as such:

Name,Address.Phone,Fax,Email,Contact,Company

I have set it up across like this, so that later on, it can be brought into a proper database.

For now, From this list in excel, I want to be able to print reports of contacts in a more readable manner ie)

Name:
Address:
Phone:

Name:
Address:
Phone:

This way I can print different reports showing only the fields I choose.

Is there any easy way to do this?
Can any provide me detailed instructions on how to do it?

Thanks

15 replies

by badermsb

You mention your goal is to bring this data into a proper data, so why not start there? MS Access is very easy for a novice to create a flat file, basic queries and reports that would easily give you what you want.

by douglasb

You would be better off to create the desired fields in an Access Database. You can then create queries to sort your data, and base report design on these queries. With a query, you can sort your address list by city, state, company, name, zip code, etc. You can always use mail-merge in word, basing the merge on the information in your database.

by mikex

Yes there is the easy way and it is:

Put your headers on the top of the worksheet. arrange it like a table with borders
Fill in the data that you need bellow the headers.
When you need to print the different type of report simply click on the column (the gray cell with Letter) => (=> = next)right click and select hide column

Remark: You can use the CTrl key to select different columns which aren't in sequence

by borg7of9

Yes, I know I can do this in Access, but the question was how to do this all in excel, as this is for a unique situation. Does anyone know how to do this in excel?

by borg7of9

Thanks, I know how to hide columns, but when I print the data, I want it print it in a list format, down, and not across as it is entered.

by noemib

You can create your database in Excel and use Word to create reports, form letters, labels, envelopes etc, by using the Mail Merge feature. This will allow you to select only the fields you want to show.

Create and save your Excel database.
Open Word and click on Tools > Mail Merge
In step 1 select the type of document you wish to create
In step 2 click Get Data > Open Data Source and browse to the Excel database you created earlier. Make sure to change the "Files of type" to "MS Excel" so that you can see the file. Select "Entire Spreadsheet"

Hope this helps!

by ajones17

This is a bit laborious but is a way of doing it wholly within Excel.

First, add a new column to your database list, call it Ref. Put the number 1 by the first person, 2 by the second person, highlight these two numbers and drag the fill handle (bottom right corner) down to give everyone a unique number. Now highlight all your data including column headings and give it the range name 'addresses'. Set up a second worksheet so first six rows read
Ref Name
1 Address
Phone
Ref Name
=A2+1 Address
Phone
Copy the range A4:A6 down the spreadsheet, ensuring you highlight a multiple of 3 rows. When you paste you should have a unique reference for each 3 rows. Now highlight cells B4:B6 and drag the fill handle to autofill down column B. Now enter the following formulas as follows
Cell C1: =DGET(Book1!Addresses,2,A1:A2)
Cell C2: =DGET(Book1!Addresses,3,A1:A2)
Cell C3: =DGET(Book1!Addresses,4,A1:A2)

Where 2, 3 and 4 represent the column numbers within your database containing Name, Address and Phone.

Now highlight these 3 cells C1:C3, copy them and paste them over a multiple of 3 rows down the worksheet. You should now have a list of your data.

by ajones17

You can't see it formatted properly in my answer so I'll write out the contents of cells A1 to B6:

A1="Ref"
B1="Name"
A2=1
B2="Address"
A3=BLANK
B3="Phone"
A4="Ref"
B4="Name"
A5=A2+1
B5="Address"
A6=BLANK
B6="Phone"

by gfelts

Save the information in a named area such as EMP_INFO then use VALIDATION to sort it and pull up the information with a drop down window. In the Validation Criteria select LIST then in the source box put =EMP_INFO. You will have to use VLOOKUP with VALIDATION.

by paulvs1_

how do you get the boxes to link to another box as in make it add up with another one

This discussion is closed to new replies.