M
mousetrap
Issue 1:
I have a table which has Job Number on the left, with inner division
by Revenue/Cost Group, then individual Cost or revenue codes. Along
the top are year and month columns.
I have manually selected which cost or revenue codes to include in
"Revenue" group and "Cost" group by highlighting etc and it all looks
OK at the moment. There are about 60-70 cost/revenue codes so this is
a bit of a pain.
The cost/revenue codes are alphanumeric and are not consecutive in our
chart of accounts. Codes are usually of the format LNNNLLL. Individual
jobs use a subsection of the codes available.
We are concerned that when we do another data download, any
infrequently used or new codes which don't have a value on any of the
jobs at the moment will appear as separate groups and have to be added
into "Revenue" or "Cost" manually (because they have not previously
been assigned to a group, or didn't appear in the pivot table based on
the previous data download). We're looking for a way of automating
this so it doesn't have to be fiddled about with each month.
Other info:
For the above pivot table, there are just two code groups "Cost" and
"Revenue", but in another table I need to do, cost will be further
split into 3-4 major costs and one group for "other".
Issue 2:
Currently I have the "Revenue" figures for each job coming through as
negative in the data download and the costs as positive. The subtotals
for "Cost" and "Revenue" are being summed to create a calculated
"Margin" field for each job.
This worked out fine until I constructed a pivot chart and now, of
course, I have the Revenue figures below the X-axis... I had thought
to change the sign of the Revenue amounts in the data sheet before
they pull through into the pivot table, but there doesn't seem to be
any "subtract" calculated field available.... (and for the more
complicated pivot table I would need each of the cost headings to
subtract from the revenue one to get to "Margin".
So I'm not sure if there's a way of "fixing" this so the "Revenue"
subtotals get reversed for the charts only? Or any other cunning way
of achieving what I want in the pivot table so that the chart comes
out right?
Would welcome any suggestions of ways that I might tackle these
issues. Thanks.
mousy
--
I have a table which has Job Number on the left, with inner division
by Revenue/Cost Group, then individual Cost or revenue codes. Along
the top are year and month columns.
I have manually selected which cost or revenue codes to include in
"Revenue" group and "Cost" group by highlighting etc and it all looks
OK at the moment. There are about 60-70 cost/revenue codes so this is
a bit of a pain.
The cost/revenue codes are alphanumeric and are not consecutive in our
chart of accounts. Codes are usually of the format LNNNLLL. Individual
jobs use a subsection of the codes available.
We are concerned that when we do another data download, any
infrequently used or new codes which don't have a value on any of the
jobs at the moment will appear as separate groups and have to be added
into "Revenue" or "Cost" manually (because they have not previously
been assigned to a group, or didn't appear in the pivot table based on
the previous data download). We're looking for a way of automating
this so it doesn't have to be fiddled about with each month.
Other info:
For the above pivot table, there are just two code groups "Cost" and
"Revenue", but in another table I need to do, cost will be further
split into 3-4 major costs and one group for "other".
Issue 2:
Currently I have the "Revenue" figures for each job coming through as
negative in the data download and the costs as positive. The subtotals
for "Cost" and "Revenue" are being summed to create a calculated
"Margin" field for each job.
This worked out fine until I constructed a pivot chart and now, of
course, I have the Revenue figures below the X-axis... I had thought
to change the sign of the Revenue amounts in the data sheet before
they pull through into the pivot table, but there doesn't seem to be
any "subtract" calculated field available.... (and for the more
complicated pivot table I would need each of the cost headings to
subtract from the revenue one to get to "Margin".
So I'm not sure if there's a way of "fixing" this so the "Revenue"
subtotals get reversed for the charts only? Or any other cunning way
of achieving what I want in the pivot table so that the chart comes
out right?
Would welcome any suggestions of ways that I might tackle these
issues. Thanks.
mousy
--