Although the solution may very well be simple, this is quite a complicated problem to explain. It would be best to see the attached file, and read the following description along side.

The attached worksheet contains an index formula in range C27:L43. The formula removes blank cells and shifts data derived from a separate range to the left. The data input in the original cells contains somewhat of a priority, whereas a "PA" would constitute a completion of the entire row, unless it is followed by a "U". In that case, The row is no longer complete, and therefore the "U" needs to populate next to the first "PA" in the range C27:C43.

As you will see in cells C34:E34,the formula drawing correctly from the related cells. What I think needs to be adjusted is not the INDEX formula itself, it is the range of data (AO6:BH22) that the index formula draws from.

I sincerely apologize if my question is difficult to understand. If you need clarification, please ask and I will do my best to explain in more detail.

Thanks in advance



Tracker Test 1.xlsx