Resources to help use Excel as database, create user forms

**oOarthurOo** · 08-07-2009, 05:00 PM

Greetings,

I have been given a project whereby I need to track information on persons (i.e., name, DOB, PIN, address) who reside on sites (e.g., towns, population) and who will be receiving items (i.e., item 1, 2, comments). I will frequently be asked to provide information on % complete at each location, reasons for why items not received, etc. I have been told to use excel to perform this tracking.

I am wondering two things:

1. Advice on best practices, or resources that may be of use in terms of designing the workbook. For example, should I try to normalize the excel file as I would with Access, or will this make queries more difficult to perform?

2. On a related note, I was thinking to try and design a user form to input all the necessary info, and perhaps also to generate specific repeated requests for updates on information.

So far, I have found this forum, which seems most excellent. As well as a book called "VBA for Dummies". It has so far proved quite helpful, which must mean I'm quite a Dummy.

I am wondering if members know of good tutorials, articles, etc. that would be useful to me as I go about designing and implementing this project.

Many thanks,
AM

**oOarthurOo** · 08-10-2009, 09:37 AM

What I have learned so far on my own, is to keep all the data in one worksheet, and to create a list. However, it seems like I'll need to recreate this list everytime I add a new record.

Suggestions are still welcome, but I'll try to post back here with what I learn and decide to do.

**oOarthurOo** · 11-23-2009, 03:50 PM

Here's what I've learned so far:

1. For a database, use a database program like Access. If you must use excel, as I must, then the following works for me:

2. Create a sheet called reference. On this sheet will go the static info, data that doesn't change too much or too often. In the leftmost column put the data around which most things are related. For example, I'm tracking various sites, what we send to them, who we send, for how long, etc. So the site name goes in the leftmost column.

3. Create sheets to use for inputing data that changes more frequently. E.g., sales, purchases, shipments, received.

4. Use vlookups for all information on the reference page.

5. Organize your data by columns, don't use row "headings" or create tables. Everything is organized by columns, with column headings in the first row or so.

6. Use pivot tables.

7. If you really want tables, displaying totals and such, put them above the column headings and data. Or just use a separate sheet.

8. Given how much vlookups you'll do, use names to make entering formulas easier. Also, on your reference sheet you might want to create some lists and name them for data validation purposes. Using drop-down lists for things like customer names and site names avoids mistakes down the road.

Above is just my two cents, but it works for me and answers my original question. Haven't done any work on forms, but with a smart layout I'm not really missing them either.

EDIT: Mods, I can no longer edit my original post to mark as solved, but in my opinion it is. Please edit to reflect that if you wish.