Populate field based on existence of values in an iterative column/row

[jwk1230]

The description probably does not explain what I am trying to do.


I am trying to create a dynamic roadmap based on data in two other worksheets.



In the attached document I want Roadmap:B to display the appropriate RefCode value from Quantitative:A OR Qualitative:A IF the Objective field on that same line is populated AND Status field on the same line is NOT Complete.


Ex: If Quantitative:B8 is not empty and Quantitative:P8 is NOT Complete, display QL1 in the Roadmap:B5 field.
If Quantitative:B9 is not empty and Quantitative:P9 is NOT Complete, display QL2 in the Roadmap:B6 field, etc.


Once there are no more Quantitative:B entries, move on to Qualitative:B and follow the same process:


If Qualitative:B8 is not empty and Qualitative:P8 is NOT Complete, display QL1 in the Roadmap:B7 field.
If Qualitative:B9 is not empty and Qualitative:P9 is NOT Complete, display QL2 in the Roadmap:B8 field, etc.


At any time if there are no more populated fields in the Quantitative:B column, then automatically move to Qualitative:B.


I hope this makes sense.