Database in Excel

[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

 

[Ashish Mathur]

Hi,

Please read up on the VLOOKUP() & INDEX(), MATCH() functions in the Help
menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Joel]

Yo may not need functions. Look at the Data - autofilter for a very simple
approach. You say you need a database. Does the mean read only or will the
users have to write to the database? If you only need to read the database
then a good solution is to have a master database file containing all the
data and then create additional workbooks that performs a query on the
database to obtaining only the data each area requires.

The problem with excel is that it is not designed as a muti-user
application. Access is designed for multi-users. So a shared excel workbook
has problems when set as a shared workbook. if two people try to write to
the workbook at the same times one user may get an error and then data may
not get saved. also there is probelms when one person writes and somebody
else reads the file. One user may get blocked.

The best thing is to limit the number of people who have write priviledges
to the workbook and update the workbook when nobody else is on-line. Perform
updates on a copy of the master workbook and then copy the updated workbook
to original workbook when nobody else is on-line.

 

[Normek]

Hi Andy Mc
What you do depends on what you want to do. If your database is small, you
can combine all your regions into one database sheet. If the data coming
from all your sites is large you may need to split it up into several
database sheets. If so try to make them the same structure, so you may be
able to use PivotTables. Read up about them and especially the help on
consolidating tables.
If you can use macros, this will make your life much easier, to build and
manipulate your database. Otherwise if you are using Excel 2003 or earlier
you can use the built in forms, this is not an option in Excel2007.
If you use macros you can use VBA Forms or input forms made from an Excel
spreadsheet.
As well as using the lookup functions ( such as Vlookup(),Index() and
Match()) you can access your data using the data filter menus (such as
autofilter), and SumProduct + Offset to extract your data.
Do a search on this site for the various functions, you will be surprised
how much valuable information you will find both from this site and from the
associated links.
It can be done.

 

[Andy Mc]

I was thinking of having just 1 or 2 people in charge of updating the
database, and only reading in Read Only mode by everyone. The database would
hold nearly 4000 names, mobile numbers, and their region/department
managers.

The cell ranges I have are A1:V3668.

Ideally I would have the raw data kept in a separate sheet or workbook and
everyone else views the data on a separate sheet or workbook.

I know this sounds like a tall order, and I guess a little complex.

I do not want it as a Auto filter as that can lead to an easier and simple
database format. But I am also trying to expand on want I can do. Pivot
Table is a better option, but not exactly the route I want to go.

Perhaps it might be better to upload the rough idea I have, without correct
data, to give you an idea of the way I am trying to go.

Many thanks for the ideas and advice, it is extremely helpful on ways I can
progress.

Also makes me think I am trying the impossible, but I will keep trying. I do
not like defeat. ;-)

 

[Andy Mc]

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 (file size = 2MB)
used I will try that next.

Many thanks to EVERYONE!!!

Andy Mc