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.