Database in Excel

  • Thread starter Thread starter Andy Mc
  • Start date Start date
A

Andy Mc

Hi guys,

Would anyone be able to give a little advise?

I am trying to make a database in Excel, due to License restrictions (and
cost), Excel is the only program we have a site license for.

The database needs to be able to hold regions of data, which will show
contact details for each region. In each region it would show around 20
Technicians, 2 Supervisors, 1 Manager, a Field manager, and finally the Head
of Department. I would also like to make it possible that they just enter
the Region Code or Tech ID and it bring up the region they work in.

I know it sounds like a tall order but if someone could give me some advise
as to which functions would be the best to use that would be a great help.
Or if you already have something like this give me a head start. But please
do not pass any actual data. I do not want people getting into trouble (Data
Protection Act).

I am also trying to learn more about Excel so I can eventually be able to
create something more complicated easily.

Kindest regards,

Andy Mc
 
Excel is very good for this kind of simple database. Say we use the first
row for column headers. We can use column A for the region ID ( 1 for
northwest, 2 for southeast,...). We can use column B for the employee type
( 1 for Field manager, 2 for Manager, 3 for Tech,...). We can use column C
for a unique emploee ID. We can use column D for employeee last name and
column E for employee first name.


You can then add any extra columns desired:

date of hire
phone number
contact info
mailing address

At this point you can use AutoFilter to query the data. You can display all
the people in a given region, etc.

The data does not even have to be sorted to do this.

For people not conversant with AutoFilter, a multi-record lookup can be used:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

Good Luck !!
 
Many thanks for those who gave advice both here and in other feeds for
VLOOKUP. I created my database in Excel 2007 using VLOOKUP and HLOOKUP. It
is spread over a couple of pages but works a treat!

Once I have figured out how to reduce the amount of space used I will try
that next.

Many thanks to EVERYONE!!!

Andy Mc
 
Back
Top