Hi - advancing a cell reference every X cells?

[Parafly9]

I'm creating a pretty technical (for me, anyway) spreadsheet that tracks time on projects and then aggregates it into a master sheet.

Anyways - I am using a seperate workbook for every project, and the idea is that the data from these workbooks will flow up into an "overview" page.

My current issue is I am trying to create a pivot chart from my data so that I can sort out things by month, day, week, etc., and also by functional departments. I have my "entry" page in a 2-axis spreadsheet - employee names on the left and days on the top. In order to accurately put this into a pivot table I need to linearize this.

I created a new worksheet which is supposed to automatically take this data and put it into a single axis.

I am trying to make it so that I can automatically create this list. The drag function does not work. I have setup 13 employees, so basically after I reference the 13 employees I need to move over a column and start at the top.

Original Spreadsheet:
--------Day1--Day2--Day3--Day4--etc.
Emp1
Emp2
Emp3
Emp4

"Pivot table" Spreadsheet
Emp----Day
Emp1--Day1
Emp2--Day1
Emp3--Day1
Emp4--Day1
Emp1--Day2
Emp2--Day2
Emp3--Day2
Emp4--Day2

etc..

I would like to be able to drag the Pivot Table spreadsheet down but I can't figure out how to advance the column every time I reach Emp4.

Any ideas? Thanks.

 

[Parafly9]

Also, to provide more info, there is a lot more going on than just those two cells - I also have things like Week Number, Month Number, (so I can sort by those) and assocaited items with employees like Rate, Department, and Title.

Right now I have the first set of information (i.e. my first column) pulled into the Pivottale spreadsheet with an array formula tranposed to make it in a row vs. in a column like it is on the main sheet.

 

[Gerald Higgins]

Why not actually create a Pivot Table, using the Pivot Table Wizard? I think it could do all this for you.

 

[Parafly9]

Why not actually create a Pivot Table, using the Pivot Table Wizard? I think it could do all this for you.

Apparently the wizard needs everything arranged in a nice column like this.

The problem is I have multiple variables with each employee that won't track to the pivot table unless I arrange the data differently.

 

[Gerald Higgins]

Apparently the wizard needs everything arranged in a nice column like this.

I don't think that's correct.

If you've got the data set up like you say (employees down the first column, days along the top) then you can use a Pivot Table to get it into the format you say you want - I've just done it myself.

As for the other bits of data, I think I'd need to understand better what they look like and what you want to do with them.

 

[Parafly9]

Apparently the wizard needs everything arranged in a nice column like this.

I don't think that's correct.

If you've got the data set up like you say (employees down the first column, days along the top) then you can use a Pivot Table to get it into the format you say you want - I've just done it myself.

As for the other bits of data, I think I'd need to understand better what they look like and what you want to do with them.

Hmm, I had tried running the Pivottable but it wouldn't work for me.

It's not a strait 2x2 chart; I think that might be the issue. So, On the top, I have the following:

Project Stage----Early
Project Month----Jan
Project Week-----1
Month-------------1
Day of Week-----Mon
Day---------------1/1/08

And then I have employees listed on the left but in this order:

Rate.... Dept... Title... EMPLOYEE NAME....

And then basically to the right of EMPOYEE NAME and underneath DAY is where the hours would be entered.