I am looking for a formula....
I have one worksheet which contains the following columns:
CHARTER HIERARCHY
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Owner[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Parent Title[/TD]
[TD="align: center"]Strategy[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]001[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]002[/TD]
[TD]Program[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Delta[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]003[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Bravo[/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]004[/TD]
[TD]Charter[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]005[/TD]
[TD]Project[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Foxtrot[/TD]
[TD]006[/TD]
[TD]Charter[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 3[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]007[/TD]
[TD]Program[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Foxtrot[/TD]
[TD]Strat 2[/TD]
[/TR]
</tbody>[/TABLE]
This worksheet pulls data from another system. It is essentially a flat file version of a relational database. As you can see, each project's parent is a program and each program's parent is a charter. Charters do not have parents.
I have a second worksheet which contains the following columns:
PROJECT DETAILS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]Charter Lookup[/TD]
[TD="align: center"]Capitalizable[/TD]
[TD="align: center"]In Service Date[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]formula goes here[/TD]
[TD]No[/TD]
[TD]Feb 1[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 15[/TD]
[/TR]
</tbody>[/TABLE]
The "Title" column refers to the same data in both worksheets.
There is a legitimate reason that this data is in two separate worksheets, as opposed to just added "Capitalizable" and "In Service Date" to the CHARTER HIERARCHY worksheet.
In the Project Details worksheet there is a column called "Charter Lookup" (Col B). What I am trying to do is a VLOOKUP from the Charter Hierarchy worksheet to find the parent of the project. But I want to go all the way to the charter level, skipping projects and programs.
For example:
=VLookup("Alpha",CHARTER HIERARCHY, 6)
This should find "Golf" in the Charter Hierarchy worksheet. The problem is that Golf is not a charter. It's a program. So now I need to do another VLookup on the same table, like this:
=VLookup("Golf",CHARTER HIERARCHY, 6)
This should return "Foxtrot," which is a charter. So now I want the cell value in the Charter Lookup field to equate to "Foxtrot."
Basically, I need to keep polling the table until I find a result of type Charter. Then I need to return that result. If there is not result of type charter, then I can return "No Charter Found."
I hope this makes sense.
Thanks,
Shawn
I have one worksheet which contains the following columns:
CHARTER HIERARCHY
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Owner[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Parent Title[/TD]
[TD="align: center"]Strategy[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]001[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]002[/TD]
[TD]Program[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Delta[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]003[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Bravo[/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]004[/TD]
[TD]Charter[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]005[/TD]
[TD]Project[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Foxtrot[/TD]
[TD]006[/TD]
[TD]Charter[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 3[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]007[/TD]
[TD]Program[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Foxtrot[/TD]
[TD]Strat 2[/TD]
[/TR]
</tbody>[/TABLE]
This worksheet pulls data from another system. It is essentially a flat file version of a relational database. As you can see, each project's parent is a program and each program's parent is a charter. Charters do not have parents.
- Alpha (project) rolls up into Golf (program) which rolls up into Foxtrot (charter).
- Charlie (project) rolls up into Bravo (program) which rolls up into Delta (charter).
I have a second worksheet which contains the following columns:
PROJECT DETAILS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]Charter Lookup[/TD]
[TD="align: center"]Capitalizable[/TD]
[TD="align: center"]In Service Date[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]formula goes here[/TD]
[TD]No[/TD]
[TD]Feb 1[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 15[/TD]
[/TR]
</tbody>[/TABLE]
The "Title" column refers to the same data in both worksheets.
There is a legitimate reason that this data is in two separate worksheets, as opposed to just added "Capitalizable" and "In Service Date" to the CHARTER HIERARCHY worksheet.
In the Project Details worksheet there is a column called "Charter Lookup" (Col B). What I am trying to do is a VLOOKUP from the Charter Hierarchy worksheet to find the parent of the project. But I want to go all the way to the charter level, skipping projects and programs.
For example:
=VLookup("Alpha",CHARTER HIERARCHY, 6)
This should find "Golf" in the Charter Hierarchy worksheet. The problem is that Golf is not a charter. It's a program. So now I need to do another VLookup on the same table, like this:
=VLookup("Golf",CHARTER HIERARCHY, 6)
This should return "Foxtrot," which is a charter. So now I want the cell value in the Charter Lookup field to equate to "Foxtrot."
Basically, I need to keep polling the table until I find a result of type Charter. Then I need to return that result. If there is not result of type charter, then I can return "No Charter Found."
I hope this makes sense.
Thanks,
Shawn