RonMcK
10-01-2010, 08:28 AM
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!
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!