I've been banging my head for 6 months now trying to fix our Excel hours reporting spreadsheet for my job. The problem is that I have to track hours on our drilling motors by assigning activities to them (Rotating, Circulating, taking surveys). These motors get changed out when they break or acquire too many hours on them. I have to track how many hours accrue on each individual motor in addition to how many feet each motor drilled. That's on one sheet.
Then, I have another sheet used as a daily log that allows users to input the activities on each motor used for that day (sometimes 2 are used in 1 day when we change them out). This sheet separates the hours out by what the user selects for the activity (Rotating, circulating, taking surveys), and tracks it day by day. This is a very condensed explanation of what the program does, but gives the general idea.
I find Excel to be an extremely difficult program to use for this because of the data tracking involved. I'd like to program a database driven visual basic program to tackle it, but they won't let me. My question is, is there a better way of doing something like this such as using a database with Excel? There's just so much data associated with so many sheets that I need a better organization tool than Excel to accomplish what I need to do.
Thanks for any help you can provide.
Excel can be used as a database,but the data has to be arranged correctly in a table format with a Header Row & no empty rowsor columns within the data
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Yea, I've done that before, but I need to make multiple tables and relate the data from the tables, so I'd need more of a relational database.
Can you use Excel with Access?
Absolutely. I even have a spreadsheet integrated with oracle & mssql servers using php to translate. Excel with VBA is a very powerful tool.
Last edited by NBVC; 04-19-2010 at 11:42 AM. Reason: Remove quote of whole post.
Is there any place I could go as a beginner to learn how to do it?
SpeedingLunatic, please refrain from quoting whole posts as it takes up space needlessly. Only quote relevant specifics and only if not obvious what you are replying to.
Thank you.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I have excel databases with several tables that relate to each other
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
How do you implement updates to the database? I would assume you would have to do it in the sheet activate sub, or is it different?
Why sheet_activate,I use UserForms for adding data
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Hmm...not sure if I could do it through user forms. I have a sheet for each daily that the user is on the job, and then a sheet for the specific motor they're using for that day, so there would be multiple sheets if multiple motors are used on a job with each motor associated with however number of days it was used, and each day associated with 1 or 2 motors, depending on whether or not the motor was changed out during the day.
Hopefully that makes sense.
I guess I could use forms to input the data, but how would I display it for each daily and motor report?
It sounds like you have sheets used as forms,not a database. A database would collate the information into a Table
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
That's right. The user enters a begin time, activity code, and then an end time on the daily sheet. Based on that activity code, the motor will accrue the hours the user entered. A whole day would give 24 hours on a motor. A motor that gets changed out will have a partial day's hours, with the new motor having the other part of the day. All this data gets transferred over to a motor sheet where the total hours are calculated.
The problem is when the user creates a new daily. This is done through the use of a button that triggers a macro to create a new sheet. The hours are really hard to carry over accurately, especially when more than 1 motor is used during the day because I'm just using the data on the current sheet and carrying it to the new sheet. This is especially troublesome when I need to carry the data over to the motor sheet, and create a new motor sheet when the motor is changed out.
I'm now attempting to do it with forms and tables, but I've never done it like this before. Are there any examples of how to organize and implement the programming of something like this?
Last edited by Patronedheart; 04-19-2010 at 03:07 PM.
Can't you just have a database with four columns: motor ID, motor on date/time, motor off date/time, feet drilled?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Shg's suggestion is a database,which is what you said that you wanted.There's no point generating daily sheets they can't constitute a database
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
That's how I'm beginning to implement it, shg. I'm going to create buttons to create a new daily and a new motor report, and a user form will pop up to input all the info in the tables.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks