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.

[Glenn Kennedy]

Firstly... a bit of an apology. I don't know where you are in terms of your Excel skills. However, you have a pile of merged cells in you Table that are not necessary,. If you need more space, widen the column: don't merge cells. They'll get you in the end!! I have removed all of the merged cells in your data sections. I left the ones in the headers... but even they make it a bit of a pain when you want to select columns. Rant over.

Try this array formula in Roadmap, B5, copied down. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

Please Login or Register  to view this content.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...