I have a file that contains project info. Each project (row) has a unique
name, a unique system ID and an assigned program ID.

Some projects have sub-projects aligned under them and in order to be able
to group these together, the main project and the sub-projects will all be
assigned the same program ID (in order to consolidate them). For example:

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project B 6790 3456 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project C 7000 9990 main project
Project A3 8001 1234 sub project
of Project A
Project B1 8002 3456 sub project
of Project B

I need to report by project name and have all associated sub-projects
(could be a lot) display directly under its main project. (Note: There are
other fields included in reporting such as department, mgr in order to
separate the reports for distribution.)

The common denominator is Program ID which allows the projects to be grouped
by program in order to consolidate financials. Grouping would look
something like this...

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project A3 8001 1234 sub project
of Project A
Project B 6790 3456 main project
Project B1 8002 3456 sub project
of Project B
Project C 7000 9990 main project

However, I have been trying to use pivot reports (with report 6 format) but
I am not able to achieve the proper layout.

Is there any way to get around this? I think I may need the common
denominator to be the project name €“ but not sure how I can do this either.


Any help would be greatly appreciated. Thanks.

Hi

On Report sheet, use p.e. data validation list to select a project;
Create a named formula, which returns ProgramID for selected project on
Report sheet;
On your data sheet, add a column to left of your table (the new column will
be probably A);
Into this column, enter some formula, which returns nothing, when ProgramID
doesn't equal with one returned by named formula, and 1, 2, 3, etc for 1st,
2nd, 3rd etc. row with ProgramID equal to value returned by named formula.
Something like:
A2=IF(OR($B2="",$D2<RepID),"",COUNTIF($D$2:$D2,Re pID))
(You can hide this column, to avoid any confusion for user)

On your Report sheet, use VLOOKUP to return data into 1st, 2nd, 3rd, etc row
from data sheet, something like
A5=IF(ISERROR(VLOOKUP(ROW()-4,Data!$A$2:$E$1000,3,0)),"",VLOOKUP(ROW()-4,Data!$A$2:$E$1000,3,0))
B5=IF(A5="","",VLOOKUP(ROW()-4,Data!$A$2:$E$1000,4,0))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MLK" wrote in message
...
I have a file that contains project info. Each project (row) has a unique
name, a unique system ID and an assigned program ID.

Some projects have sub-projects aligned under them and in order to be able
to group these together, the main project and the sub-projects will all be
assigned the same program ID (in order to consolidate them). For example:

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project B 6790 3456 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project C 7000 9990 main
project
Project A3 8001 1234 sub
project
of Project A
Project B1 8002 3456 sub
project
of Project B

I need to report by project name and have all associated sub-projects
(could be a lot) display directly under its main project. (Note: There
are
other fields included in reporting such as department, mgr in order to
separate the reports for distribution.)

The common denominator is Program ID which allows the projects to be
grouped
by program in order to consolidate financials. Grouping would look
something like this...

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project A3 8001 1234 sub
project
of Project A
Project B 6790 3456 main project
Project B1 8002 3456 sub
project
of Project B
Project C 7000 9990 main
project

However, I have been trying to use pivot reports (with report 6 format)
but
I am not able to achieve the proper layout.

Is there any way to get around this? I think I may need the common
denominator to be the project name – but not sure how I can do this
either.


Any help would be greatly appreciated. Thanks.

Thanks Arvil, but I need some more assistance from you. I'm familiar with
vlookups, but not sure how you are getting there yet.

a) Not sure what you mean by the RepID or named formula?
b) When you refer to Report sheet, do you mean the pivot reports or a new
report sheet altogether?
c) You also mention to use a validation list when selecting a project, but
reporting will be done on all records.

Thanks

"Arvi Laanemets" wrote:

Hi

On Report sheet, use p.e. data validation list to select a project;
Create a named formula, which returns ProgramID for selected project on
Report sheet;
On your data sheet, add a column to left of your table (the new column will
be probably A);
Into this column, enter some formula, which returns nothing, when ProgramID
doesn't equal with one returned by named formula, and 1, 2, 3, etc for 1st,
2nd, 3rd etc. row with ProgramID equal to value returned by named formula.
Something like:
A2=IF(OR($B2="",$D2<RepID),"",COUNTIF($D$2:$D2,Re pID))
(You can hide this column, to avoid any confusion for user)

On your Report sheet, use VLOOKUP to return data into 1st, 2nd, 3rd, etc row
from data sheet, something like
A5=IF(ISERROR(VLOOKUP(ROW()-4,Data!$A$2:$E$1000,3,0)),"",VLOOKUP(ROW()-4,Data!$A$2:$E$1000,3,0))
B5=IF(A5="","",VLOOKUP(ROW()-4,Data!$A$2:$E$1000,4,0))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MLK" wrote in message
...
I have a file that contains project info. Each project (row) has a unique
name, a unique system ID and an assigned program ID.

Some projects have sub-projects aligned under them and in order to be able
to group these together, the main project and the sub-projects will all be
assigned the same program ID (in order to consolidate them). For example:

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project B 6790 3456 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project C 7000 9990 main
project
Project A3 8001 1234 sub
project
of Project A
Project B1 8002 3456 sub
project
of Project B

I need to report by project name and have all associated sub-projects
(could be a lot) display directly under its main project. (Note: There
are
other fields included in reporting such as department, mgr in order to
separate the reports for distribution.)

The common denominator is Program ID which allows the projects to be
grouped
by program in order to consolidate financials. Grouping would look
something like this...

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project A3 8001 1234 sub
project
of Project A
Project B 6790 3456 main project
Project B1 8002 3456 sub
project
of Project B
Project C 7000 9990 main
project

However, I have been trying to use pivot reports (with report 6 format)
but
I am not able to achieve the proper layout.

Is there any way to get around this? I think I may need the common
denominator to be the project name €“ but not sure how I can do this
either.


Any help would be greatly appreciated. Thanks.

Hi


"MLK" wrote in message
...
Thanks Arvil, but I need some more assistance from you. I'm familiar with
vlookups, but not sure how you are getting there yet.

a) Not sure what you mean by the RepID or named formula?

You can create named ranges/formulas from menu InsertNameDefine
There you give a name, determine return value/range by some expression, and
click on Add button to save the name. You can use such names as parameters
in your formulas. (And referring to named range is the only way to use a
list from another sheet as source for data validation list.)

b) When you refer to Report sheet, do you mean the pivot reports or a new
report sheet altogether?

I did mean a new report sheet, where user selects a project, and all data
for this project is displayed in printable form.


c) You also mention to use a validation list when selecting a project, but
reporting will be done on all records.

I did miss it. When this is the case, then maybe ODBC query will be a best
choice.

Define your table as a named range:
Select the whole table (together with header row - you must have one for
this), and when you may add new rows to your table later, then some
reasonable amount of empty rows too;
With selection active, select from menu InsertNameDefine, enter some name
(p.e. MyData) into name field, (selected range reference is displayed in
formula field by default - let it be so), and press add.

Save your workbook!!!!

On some new/empty sheet, select cell A1;
Select from menu DataGetExternalDataNewDatabaseQuery;
On Databases tab, select Excel Files. OK;
In Workbook Selection Wizard, locate and select your workbook OK;
Query Wizard opens. You must see the previously defined named range (MyData
in my example here) there available;
Transfer all fields to right (click on between 2 windows). Next;
Filter Data: set filter to ProjectName Is Not Null (it elliminates empty
rows). Next;
Sort Order: order by ProgramID and then by ProjectName. Next;
Finish. You are asked where to put returned data - press OK.

You get same data as source table, but ordered differently - all projects
with same ProgramID are grouped together.
When you edit your data, you can refresh the query, selecting any cell from
query table (returned data), and clicking on button with "!" on it, or
selecting DataRefreshData from menu. You can set the query to be refreshed
on open, or after some interval, too.


Arvi Laanemets

Wow! This is amazing stuff. Thanks, I will give it a try.

"Arvi Laanemets" wrote:

Hi


"MLK" wrote in message
...
Thanks Arvil, but I need some more assistance from you. I'm familiar with
vlookups, but not sure how you are getting there yet.

a) Not sure what you mean by the RepID or named formula?

You can create named ranges/formulas from menu InsertNameDefine
There you give a name, determine return value/range by some expression, and
click on Add button to save the name. You can use such names as parameters
in your formulas. (And referring to named range is the only way to use a
list from another sheet as source for data validation list.)

b) When you refer to Report sheet, do you mean the pivot reports or a new
report sheet altogether?

I did mean a new report sheet, where user selects a project, and all data
for this project is displayed in printable form.


c) You also mention to use a validation list when selecting a project, but
reporting will be done on all records.

I did miss it. When this is the case, then maybe ODBC query will be a best
choice.

Define your table as a named range:
Select the whole table (together with header row - you must have one for
this), and when you may add new rows to your table later, then some
reasonable amount of empty rows too;
With selection active, select from menu InsertNameDefine, enter some name
(p.e. MyData) into name field, (selected range reference is displayed in
formula field by default - let it be so), and press add.

Save your workbook!!!!

On some new/empty sheet, select cell A1;
Select from menu DataGetExternalDataNewDatabaseQuery;
On Databases tab, select Excel Files. OK;
In Workbook Selection Wizard, locate and select your workbook OK;
Query Wizard opens. You must see the previously defined named range (MyData
in my example here) there available;
Transfer all fields to right (click on between 2 windows). Next;
Filter Data: set filter to ProjectName Is Not Null (it elliminates empty
rows). Next;
Sort Order: order by ProgramID and then by ProjectName. Next;
Finish. You are asked where to put returned data - press OK.

You get same data as source table, but ordered differently - all projects
with same ProgramID are grouped together.
When you edit your data, you can refresh the query, selecting any cell from
query table (returned data), and clicking on button with "!" on it, or
selecting DataRefreshData from menu. You can set the query to be refreshed
on open, or after some interval, too.


Arvi Laanemets

Why you do not consider sorting by ProgID/UniqueID please ?

Regards

"MLK" wrote in message
...
I have a file that contains project info. Each project (row) has a unique
name, a unique system ID and an assigned program ID.

Some projects have sub-projects aligned under them and in order to be able
to group these together, the main project and the sub-projects will all be
assigned the same program ID (in order to consolidate them). For example:

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project B 6790 3456 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project C 7000 9990 main
project
Project A3 8001 1234 sub
project
of Project A
Project B1 8002 3456 sub
project
of Project B

I need to report by project name and have all associated sub-projects
(could be a lot) display directly under its main project. (Note: There
are
other fields included in reporting such as department, mgr in order to
separate the reports for distribution.)

The common denominator is Program ID which allows the projects to be
grouped
by program in order to consolidate financials. Grouping would look
something like this...

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project A3 8001 1234 sub
project
of Project A
Project B 6790 3456 main project
Project B1 8002 3456 sub
project
of Project B
Project C 7000 9990 main
project

However, I have been trying to use pivot reports (with report 6 format)
but
I am not able to achieve the proper layout.

Is there any way to get around this? I think I may need the common
denominator to be the project name - but not sure how I can do this
either.


Any help would be greatly appreciated. Thanks.

Hello, sorting the file is ok for me personallny, BUT managers do not want to
see file data as reports. Reports for managers are generated using pivot
tables.

In this file, the common denominator is program ID (to group projects and
sub-projects together) however the reports group/sort the data by program ID,
with the projects/sub-projects underneath - not good because then the main
projects are not sorting alphabetically (except under each program ID).

However, what I really want is a report to group/sort by main project name
and have its associated projects/sub-projects grouped underneath.


"PY & Associates" wrote:

Why you do not consider sorting by ProgID/UniqueID please ?

Regards

"MLK" wrote in message
...
I have a file that contains project info. Each project (row) has a unique
name, a unique system ID and an assigned program ID.

Some projects have sub-projects aligned under them and in order to be able
to group these together, the main project and the sub-projects will all be
assigned the same program ID (in order to consolidate them). For example:

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project B 6790 3456 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project C 7000 9990 main
project
Project A3 8001 1234 sub
project
of Project A
Project B1 8002 3456 sub
project
of Project B

I need to report by project name and have all associated sub-projects
(could be a lot) display directly under its main project. (Note: There
are
other fields included in reporting such as department, mgr in order to
separate the reports for distribution.)

The common denominator is Program ID which allows the projects to be
grouped
by program in order to consolidate financials. Grouping would look
something like this...

PROJECT NAME UNIQUE ID PROGRAM ID DESCRIPTION
Project A 6789 1234 main project
Project A1 7002 1234 sub project of Project A
Project A2 8000 1234 sub project of Project A
Project A3 8001 1234 sub
project
of Project A
Project B 6790 3456 main project
Project B1 8002 3456 sub
project
of Project B
Project C 7000 9990 main
project

However, I have been trying to use pivot reports (with report 6 format)
but
I am not able to achieve the proper layout.

Is there any way to get around this? I think I may need the common
denominator to be the project name - but not sure how I can do this
either.


Any help would be greatly appreciated. Thanks.