I'm sure there are a hundred ways to ask this, so if there's an answer and you can just send me to it, that would be great.
However, using dozens of different ways to ask I haven't found an answer.
Simple version:
To enter time series data, say monthly forecasts, I have several columns of 'description' data, and then columns for the dollars.
That is very intuitive to enter the data (and my real-world example is more complicated, but I think the principle is the same), but not as useful / flexible for running pivot tables against.
To make that data more useful in a pivot table, I'd have the same 'description' columns, then add one column for month, and one column for the dollars.
That is NOT very intuitive for data entry, nor for doing some of the quick calculations the first option gives (like easily getting totals per month; and with filters on the description, totals per month per person, or per project, or whatever the descriptive data is).
The question is, what is the best way to do this?
Almost every example of "budgets" I've seen have the months across the top, not in a column.
But every example I've seen for pivot tables (especially in Collie's Powerpivot book), the months are always a column.
What makes is more complicated in my case, is that we have two months for each column:
"Hours" for each month, so if the budget is for a person, we put in hours, and the dollars are calculated in the second column for each month. If the the budget is for some other expense (not hourly), the hours column is left blank, and the dollars are just typed in.
++Hopefully that's a good verbal description of the current setup and what I'm trying to do (get from an easy to use data entry model to an easy to "pivot" data model). ++
For the visual folks, here's a simple version of what I have (easy to enter):
Name Project OtherInfo JanHrs JanCost FebHrs FebCost Etc.
Bob Proj1 Dept 52 5,200 25 2,500
Paint Proj2 Color 0 55 0 350
What I "should" have for pivot tables (easy to analyze):
Name Project OtherInfo Month Hours Cost
Bob Proj1 Dept Jan 52 5,200
Bob Proj1 Dept Feb 25 2,500
Paint Proj2 Color Jan 0 55
Paint Proj2 Color Feb 0 350
Just looking for ideas that are already out there somewhere (a link is fine).
Thanks for any and all help!
JW
However, using dozens of different ways to ask I haven't found an answer.
Simple version:
To enter time series data, say monthly forecasts, I have several columns of 'description' data, and then columns for the dollars.
That is very intuitive to enter the data (and my real-world example is more complicated, but I think the principle is the same), but not as useful / flexible for running pivot tables against.
To make that data more useful in a pivot table, I'd have the same 'description' columns, then add one column for month, and one column for the dollars.
That is NOT very intuitive for data entry, nor for doing some of the quick calculations the first option gives (like easily getting totals per month; and with filters on the description, totals per month per person, or per project, or whatever the descriptive data is).
The question is, what is the best way to do this?
Almost every example of "budgets" I've seen have the months across the top, not in a column.
But every example I've seen for pivot tables (especially in Collie's Powerpivot book), the months are always a column.
What makes is more complicated in my case, is that we have two months for each column:
"Hours" for each month, so if the budget is for a person, we put in hours, and the dollars are calculated in the second column for each month. If the the budget is for some other expense (not hourly), the hours column is left blank, and the dollars are just typed in.
++Hopefully that's a good verbal description of the current setup and what I'm trying to do (get from an easy to use data entry model to an easy to "pivot" data model). ++
For the visual folks, here's a simple version of what I have (easy to enter):
Name Project OtherInfo JanHrs JanCost FebHrs FebCost Etc.
Bob Proj1 Dept 52 5,200 25 2,500
Paint Proj2 Color 0 55 0 350
What I "should" have for pivot tables (easy to analyze):
Name Project OtherInfo Month Hours Cost
Bob Proj1 Dept Jan 52 5,200
Bob Proj1 Dept Feb 25 2,500
Paint Proj2 Color Jan 0 55
Paint Proj2 Color Feb 0 350
Just looking for ideas that are already out there somewhere (a link is fine).
Thanks for any and all help!
JW
