R
Roadie
I have to make a file that shows an overview of the projects we have i
combination with financial data. We have around 2000 projects and th
financial data includes Plan, Actuals and Budget.
My input is a list like this:
ProjName Year Cd Total Jan. Feb. ..... Nov. Dec. A/B/P
X/0500-0 0000 41 0000 000 000 000 000 000 B
X/0500-0 2003 41 0000 000 000 000 000 000 B
X/0500-1 2003 01 0000 000 000 120 000 000 P
X/0500-1 2003 04 0000 000 000 140 000 000 A
X/0500-1 0000 41 1700 000 000 000 000 000 B
X/0500-1 2003 41 1700 000 000 000 000 000 B
X/0500-2 2003 01 0000 000 000 128 000 000 P
X/0500-2 2003 04 0000 000 000 000 000 000 A
X/0500-2 2003 04 0000 000 000 127 000 000 A
X/0500-2 0000 41 1030 000 000 000 000 000 B
X/0500-2 2003 41 1030 000 000 000 000 000 B
X/1001-0 0000 41 0000 000 000 000 000 000 B
X/1001-2 0000 41 6000 000 000 000 000 000 B
X/1002-0 0000 41 0000 000 000 000 000 000 B
X/1002-1 0000 41 2300 000 000 000 000 000 B
X/1002-2 0000 41 1620 000 000 000 000 000 B
X/1003-0 0000 41 0000 000 000 000 000 000 B
X/1003-1 0000 41 1800 000 000 000 000 000 B
X/1003-2 0000 41 9200 000 000 000 000 000 B
In total over 8000 lines. Each project is part of a project-class, ha
a responsible person and a responsible group.
I want to make column-charts with 3 series (A/B/P), months on th
X-axis, euro's on the Y-axis, for every category (project-class, resp
person, resp. group etc.)
What is the best way to that?
I've thought about SUMIF, but since I have too many columns wit
variations, that's not a good idea. Or can I use SUMIF with multipl
criteria?
I've thought about using PivotTables, but I'm not happy with th
PivotChart-options, so I'd probably have to make a lot of differen
PivotTables to get all the charts what I want.
My third option is DSUM, but I'm not sure how to prevent from copyin
the criteria endlessly to make different combinations.
The list above changes every day and I want to make a macro that ca
process the whole list, with the charts as output. Since the list i
8000 lines, for the performance I think it's important not to use to
manay formulas. Although, I can of course Copy/PasteSpecial Values i
the macro. Can anyone give me a hint what to use? Maybe a combination
Are there other options I don't know about?
Thanks in advance,
Anne
combination with financial data. We have around 2000 projects and th
financial data includes Plan, Actuals and Budget.
My input is a list like this:
ProjName Year Cd Total Jan. Feb. ..... Nov. Dec. A/B/P
X/0500-0 0000 41 0000 000 000 000 000 000 B
X/0500-0 2003 41 0000 000 000 000 000 000 B
X/0500-1 2003 01 0000 000 000 120 000 000 P
X/0500-1 2003 04 0000 000 000 140 000 000 A
X/0500-1 0000 41 1700 000 000 000 000 000 B
X/0500-1 2003 41 1700 000 000 000 000 000 B
X/0500-2 2003 01 0000 000 000 128 000 000 P
X/0500-2 2003 04 0000 000 000 000 000 000 A
X/0500-2 2003 04 0000 000 000 127 000 000 A
X/0500-2 0000 41 1030 000 000 000 000 000 B
X/0500-2 2003 41 1030 000 000 000 000 000 B
X/1001-0 0000 41 0000 000 000 000 000 000 B
X/1001-2 0000 41 6000 000 000 000 000 000 B
X/1002-0 0000 41 0000 000 000 000 000 000 B
X/1002-1 0000 41 2300 000 000 000 000 000 B
X/1002-2 0000 41 1620 000 000 000 000 000 B
X/1003-0 0000 41 0000 000 000 000 000 000 B
X/1003-1 0000 41 1800 000 000 000 000 000 B
X/1003-2 0000 41 9200 000 000 000 000 000 B
In total over 8000 lines. Each project is part of a project-class, ha
a responsible person and a responsible group.
I want to make column-charts with 3 series (A/B/P), months on th
X-axis, euro's on the Y-axis, for every category (project-class, resp
person, resp. group etc.)
What is the best way to that?
I've thought about SUMIF, but since I have too many columns wit
variations, that's not a good idea. Or can I use SUMIF with multipl
criteria?
I've thought about using PivotTables, but I'm not happy with th
PivotChart-options, so I'd probably have to make a lot of differen
PivotTables to get all the charts what I want.
My third option is DSUM, but I'm not sure how to prevent from copyin
the criteria endlessly to make different combinations.
The list above changes every day and I want to make a macro that ca
process the whole list, with the charts as output. Since the list i
8000 lines, for the performance I think it's important not to use to
manay formulas. Although, I can of course Copy/PasteSpecial Values i
the macro. Can anyone give me a hint what to use? Maybe a combination
Are there other options I don't know about?
Thanks in advance,
Anne