Hi - advancing a cell reference every X cells?

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
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.
 
Upvote 0

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Why not actually create a Pivot Table, using the Pivot Table Wizard? I think it could do all this for you.
 
Upvote 0

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
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.
 
Upvote 0

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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.
 
Upvote 0

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
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.
 
Upvote 0

Forum statistics

Threads
1,191,134
Messages
5,984,863
Members
439,921
Latest member
Neocold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top