Hello,

I am a basic Excel user.

I have an Excel sheet which displays a hierarchy of our institution's org units. The org unit names are listed in order: one per row. Its placement by column designates its level in the hierarchy. In each row, the hierarchical parent org units are not displayed, you have to view the previous row/s to find the hierarchically 'superior' org units. The number codes of each org units are simply listed in one column (column O).

I want to build the hierachy of 'superior' org units for each org unit across the row (so it is standalone), but I want to use the number code (column O) rather than the name.

For example, looking at row 497:
The org unit specified in that row has its name displayed in the H column (it is therefore 8 levels in from the top hierarchical level). Getting the first level number code into cell A is easy as all org units on this sheet share the same first level org unit. So, to fill in the second level of the org unit on row 497, I want to find the first row above it in which column B (org unit name) has a value. Once I find that, I want to copy its number code (cell O) into row 497, cell Q. Once I get that done for all rows and have the number code for the second level org unit filled in their cell Q, I will go about getting the third level org unit number code for each row's cell R.

Just for the Following is the logic I have come up with so far, and I don't know how to enter a formula to achive it. Any help would be appreciated!:

I am thinking, for each row:

if cell B>0 then cell Q= cell O
if cell B is not > 0, then is cell B of the previous row >0?, if not, previous row, previous row, previous row, until a value is found in cell B. Once it is found, then cell Q of the initiating row = cell O of the row in which we found a value in cell B.

BUT, depending on which cell the org unit of any given row is, I should not look for any values past that level to fill in.

Argh. Sorry.

Thank you!

Configuration: Windows XP
Internet Explorer 6.0