How to emulate data base in MS Excel?

[RonMcK]

Hi, All,

First, I know that MS Access or FileMaker Pro would be a better tool for this project. I need to stick with MS Excel because that is the tool available to the people using the data. Added complication/limitation: most of the users of my solution will be on Macs running MS Excel 2004.

We need a workbook with a tracking sheet for each grade in our science program. Each sheet lists all of our science activities and all of the items used in each activity. Is there a way to craft a solution that allows the user to add an activity (a header line or row) and, then, to add as many items (detail rows) as needed. In addition, I want the user to be able, later, to insert/add new items (detail rows) and delete items (detail rows), as well as edit items. And, when the user deletes an activity (row), any related items (detail rows) would automatically be deleted, as well. All of this suggests using VBA user forms to do the heavy lifting.

To promote consistency and reduce entry time, I'm considering using a Item Detail table (separate Excel worksheet) containing a unique row for each kit item; the main sheet will populate the individual activity's item (detail row) fields from the kit item row fields.

My questions:
• Is there a better way to do this that is more elegant and involves less effort?

• Also can you point me at examples of how others have solved similar problems?

Thanks!

[Bob Phillips]

Ron,

Have you looked at the Data>Form function?

[RonMcK]

Bob,

Thanks, no, that slipped by me. This is the sort of 'dope slap' I was looking for. I'll re-think this starting with that as the tool.

I see the problem as analogous to a Sales Journal with a header line for each invoice and under it detail lines for each item on the invoice, with the page containing multiple invoices with varying number of detail lines.

In using Data > Form, I will need to train the primary user (particularly) to enter item numbers on separate lines from the activity header line, since, Data > Form will have to show all fields on a line. The other challenge with Data > Form is whether I have too many columns with data.

One solution to that last problem is in one of John Walkenbach's books or on one of his disks. Do you have a recommendation to circumventing the too many columns issue (remember XL2004)?

Thanks, again, Bob!

[Bob Phillips]

What you could do is to split the data into separate tables and have the users maintain that, and have some VBA behind it to maintain the links.

[RonMcK]

Bob,

Thanks, that's where I was heading with my opening query. One supporting file with all the inventory of items that can appear in kits. One main file where the user enters the activity (header) records, with some VBA code to add/insert detail lines.

Once the user enters an item number on a detail line and tabs out of the field, the VBA can retrieve all of the related data in the supporting file and add it.

In the alternative (perhaps, just thinking as I type), could the item detail fields (other than the item number) be VLOOKUPS pointing back to that supporting table? If so, any change in the detail fields of an item would immediately populate the items detail lines on the main sheet.

If I used the first, I would need to add a mechanism so the user can update lines (singly or all) where any of the underlying item detail has changed. The benefit, though, is that the user can chose to freeze item detail in a book, while allowing on-going maintenance of the supporting file. It can evolve and grow.

If I use the second, then, I have to determine how the user can freeze item detail information for one (or more but not all) books. Perhaps, a macro to edit > copy > paste special > values to break the 'linking' for that item?

Thanks,

[Bob Phillips]

Forms are very rudimentary Ron, and as soon as you start getting complex I think they lose much of their usefulness.

One thing (similalrly, just thinking out loud) you could try. Consider a Customer table, a Customer Sales and a Products table. You would use Forms to maintain the Customer and Product tables, simple id and details. For the Customer Sales, you would inject a DV of customer names, and a VLOOKUP to get the ID, and similarly for the Produccts. The Sales data would be a Form, or maybe just event driven input.

Does that make some sense?
is to have

[RonMcK]

... you would inject a DV of customer names ...

Bob,

It must be time for me to quit for today, I'm drawing a blank on decoding 'DV'.
[DOH!] Never mind, that's Data > Validation. Still time to quit.

Your suggestions make sense. I'll work with them over the weekend to see what I can pull together with minimal effort on my part.

Have a wonderful weekend!

Thanks,