Question on my Timesheet Tool

[golf4]

Hi, everyone -

I think some have seen my Timesheet tool (developed with the expert help from people here and on mrexcel.com), but I've run into a brick wall. One of our federal housing programs is changing drastically, and I've found I need to really change around our tool. UUUGGGG.... these programs are turning my hair even greyer than it is now!!!

Our current Timesheet has the user manually enter their hours per day (reguklar hours, OT, various leaves, etc.). When the Timesheet is printed, the total hours are broken down, %-wise, between our various housing programs. The MAJOR change is that, now, HUD is requiring hours (employee costs) to be broken down by development (building) in addition to %-wise as above. The easiest way to do this would be through our work order system, i.e. 2 hours working in Building A, 3 hours working in Building B, etc. The wall I've run into is how to transpose these types of hours into our Timesheet tool.

I've created a data entry sheet where the user would enter work order numbers, the applicable date of the work order, and the hours spent in each building. Column A is work order numbers, Column B is the date of the work order, Column C through Column W are the columns related to each building.

The question I have is what would be the best way to transpose the hours entered in the data entry sheet into the Timesheet tool. CLEAR AS MUD, HEH???

If it would make it easier to see what I'm talking about, I could zip the tool up and post it or e-mail it out to those interested in taking a look at my problem. I would appreciate any assistance on this one.

Thanks for the help,

Golf

[Zack Barresse]

... If it would make it easier to see what I'm talking about, I could zip the tool up and post it or e-mail it ...

Sure. I'd take a look Frank. I think I see what you're asking, the one thing I'm not too clear on is how you want the final product layed out. Is this going to be contained in the same file, on a different sheet? If/when you post a sheet, can you put some erroneous data in there so we can see what a typical entry looks like? If not, I understand.

[golf4]

Hi, Zack -

Thanks for the quick reply. I put a response, and further explanation together along with a zipped version of the Tool. It appears that it's too large to post here, so I sent it on to your e-mail address. Hope that's ok.

Thanks for the help,

Frank

[Zack Barresse]

... Hope that's ok ..

Of course, Frank!

Do you have the "Object 73.wav"? It fails at that line for me upon opening..

[Zack Barresse]

Hey Frank,

Some questions. On your DATA-ENTRY-HOURS sheet, this is where you are currently entering the data, correct? From column C to column I, that data needs to be transposed into your TIMESHEET sheet.
Q: Will your entry sheet ever go below 31 rows of information (days)? It currently goes down to row 199.
Q: Is this going to be run once per file, for multiple people, for multiple times in the future possibly..?
Q: Would you like a link to the original sheet the values came from? (Btw, MD has an excellent Transpose Kb entry that leaves links to the original content.)

On your TIMESHEET, in AH8, you can alter your formula(s) to ...

=SUMIF(C8:AG8,">0")

..then copy down as needed.

[golf4]

Hi, Zack -

I have a feeling "Object 73" (tied to Workbook_open) got messed up when I inserted a new worksheet in front of the Timesheet. I went ahead and sent you the wav file.

Thanks again,

Frank

[golf4]

Hi, Zack -

I think I worked out a work-around for my issue. I gave this strip a shot, and it seemed to work:

=SUMIF('DATA ENTRY-HOURS'!B5:B200,1,'DATA ENTRY-HOURS'!C5:C200)

This may be a round-about way to do it, and it looks as if I'll have to use a couple absolute values, but this should work (at least I think it should).

Thanks for the help. Hope it's ok to pick your brain again in the future........

Take care,

Frank