Microsoft Excel - pulling data from multiple tabs to a single tab

[Bill Sublette]

I have daily timesheets for each individual Supervisor for each day. Infomation on those timesheets are the employee name, the hours they worked and th job location (which varies per day). They can also work at different locations per day as well. I am tying to pull the employee name, the job location, and the hours at that location from the timesheet and put it in a database on a different tab.

For example if John Smith worked 5 hours running conduit and another 5 hours pullig cables, I need to be able to capture that in a database. I tried an If/Not statement and that didn't even come close. Besides if the employee didn't work I certainly don't want them to show up in the databast.

The hard part is for it to recognize the value in the "hours" field and then for it to look at where those hours were worked. I have no idea how to proceed and I really don't know if this clarified matters either.

Thanks,

Bill

[NeoPa]

Bill, if you really need help then I suggest you post the question clearly. At the moment it has too many references to undefined terms and very little that makes much sense without looking at the attachment. The question isn't supposed to be in an attachment. It should be in the post. An attachment doesn't hurt per se, but it's not the question.

[TheSmileyCoder]

In sheet "Ash Monday" I expect that Fields F4 through 04 are meant to indicate different worksites?

Are these sheets filled in manually directly in Excel? If so you might easily run into problems with typos for instance. Just a word of warning.


Looking at it, it seems quite a bit of VBA code would be needed, to first read in the worksites, then import the hours worked, and properly tie them to the correct worksites. Even so, it would be very specific to the current layout of the excel sheet, and if something changes (someone inserts extra rows/Columns) the code might easily break.

So in short, its not impossible, but it does seem like quite a bit of work to create and maintain the code.

[Bill Sublette]

Yes they are filled in manually. The worksites are from a dropdown filled from the "DTA WORKSHEET" tab as well as the names on the "Ash Monday" tab. Once we have the formulas figured out, I'll lock down the specific cells so no one can change the data. The only data that is filled in is the time in the boxes and then just pick the location from the dropdown.

I'll do whatever it takes. I'm not very familiar with VBA and I don't think my 2 semesters in C++ is going to help me much on this. Any assistance is greatly appreciated.

Thanks!

[NeoPa]

Handling work like this is probably easier to manage in Access than Excel, but I certainly appreciate how much easier it may be for users to work within Excel, and Excel certainly can do a good job if handled correctly. Excel has other advantages too in that it can present certain data more easily and understandably. It just makes it harder to manage as the requirements get more complicated. It does have an object structure that enables you to do what you need, but I would suggest that using it would put the onus on you, the designer, to be very careful about what you plan to do and how you plan to do it.

This isn't something I'd generally recommend for anyone before intermediate level at least. As Smiley says, there's a whole lot of planning and work involved, and there's only so much hand-holding you can expect - even from our experts here. That said, as long as all the related questions are asked clearly enough, I'm sure you'll get help and answers in time. Quickly enough to make it practical to handle in a commercial environment I don't know, but certainly there's a willingness to help here generally which you can take advantage of.

[Bill Sublette]

I wholeheartedly agree about Access being so mch easier in this situation... All I'd have to do is create a basic form and it'd do everything I need it to. Unfortunately, my Supervisor is dead set on trying to make Excel do everything even if Excel isn't made to do all of the crap he wants it to do. I know I'm asking for a lot here and if I could figure out how to get it done, I'd do so, but I truly can't seem to get it to work and now that my database just crapped the bed from doing manual entries, I'm in dire straights here!

[NeoPa]

I hear your problem Bill, and I'm not trying to make this your 'fault'. I'm simply saying it as I see it. This seems to be the situation you're in and what you need to consider before trying to go forward. I don't think we can help with dealing with your boss's approach/attitude I'm afraid.