Transform user entry data to a more tabular form

We use a spreadsheet to keep track of hours (or work units) for a individual per day based on a contract and code. I've attached an "Example" spreadsheet. Each individual has an identical spreadsheet ("Entry" sheet) and tracks the hours or units per day. I would to transform the data entered by the user to a more tabular form ("Data" sheet) so I can combine the information and manipulate in a pivot table (similar to the "Pivot" sheet). I can't get my head around the best way grab the entry data and put it into the data table without moving a lot of null or empty values -- open to suggestions or a push in the right direction. It is a pain to cut and paste or hand enter the data to roll it all up.

Thank you!Example.xls

We have 20 to 50 people but I thought I would start small and work on one individual at a time to see if I could extract the information on a single sheet within a single workbook. Ultimately I would like to select a number of spreadsheets and import the data in a single workbook. One bite of the elephant.

Yes to the macro but only for a single input right now.

I am customizing my copy of the spreadsheet by adding a macro to extract data from the "Entry" sheet (tab) in Example.xls and then adding a "Datasheet" worksheet, and transforming the user information to a more tabular set (the "DataSheet" sheet in the example is how I want the data to look). I am working small on a single workbook just to get the logic on how to manipulate the data from the entry sheet. Not worried about the multiple workbooks yet since I'm not sure how I want aggregate the data (send individual spreadsheets or grab remotely from network).

The user information (more graphic in nature) is a contract number, work or job code (actually a WBS number), and then the units entered in the appropriate date columns . The problem I am having is how to grab the name, then the date, and then the associated contract number, code, and hours and drop them onto the new worksheet in that order. It would seem you could loop through the cells but I'm hung up on getting the date and hours correct.

I probably should ask my question differently.

I have a range (C5:I12). The range contains a variable amount of values depending on the week. What is the best way to capture and copy the specific cell value and the value of the corresponding column heading (found in C4:I4) and static information in the row located in the corresponding column B for the target value row.

I've tried using the a FOR EACH cell in the range and can grab the unique values and addresses but am having difficulty getting the values of it column heading and row headings. The address (cell.address) is in the form $C$5 and I would prefer something more like cells(5,3) so I could use OFFSET or the cell reference to grab the header values.

I got the output I was looking for -- not elegant and not programmer quality but functional for my needs.

Now to work on how I want to aggregate the data.

Hope this helps someone doing something similar.Example2.xls

Please Login or Register  to view this content.