There are 4 possible jobs, the following cells will contain either a “Yes or a “No” that they were completed:

Job cells are:

job 1 = X105, job 2 = AW105, job 3 = X133, job 4 = AW133

The first one of these job cells, in numerical order, to have a “yes”, needs to have the corresponding TD cell populate in cell AK178.

TD Cells are:

TD 1 = Q108, TD 2 = AP108, TD 3 = Q136, TD 4 = AP136

My problem is that there can be more than 1 job completed.  Cell AK178 is for the 1st job that has a “yes”.

Cell AK179 needs to be populated with the next job that has a “yes” in numerical order. 

So, if Job 1 is a “No”, Job 2 is a “Yes”, Job 3 is a “Yes”, and Job 4 is a “No”…….

then AK178 should equal AP108

and AK179 should equal Q136

or………….

If Job 1 is a “Yes”, Job 2 is a “No”, Job 3 is a “Yes”, and Job 4 is a “No”…….

then AK178 should equal Q108

and AK179 should equal Q136

I can write the formula for one but not if there are 2 completed jobs.  Help please!!

​There are 4 possible jobs, the following cells will contain either a “Yes or a “No” that they were completed:Job cells are:job 1 = X105, job 2 = AW105, job 3 = X133, job 4 = AW133 The first one of these job cells, in numerical order, to have a “yes”, needs to have the corresponding TD cell populate in cell AK178.TD Cells are:TD 1 = Q108, TD 2 = AP108, TD 3 = Q136, TD 4 = AP136 My problem is that there can be more than 1 job completed.  Cell AK178 is for the 1st job that has a “yes”. Cell AK179 needs to be populated with the next job that has a “yes” in numerical order.   So, if Job 1 is a “No”, Job 2 is a “Yes”, Job 3 is a “Yes”, and Job 4 is a “No”…….then AK178 should equal AP108and AK179 should equal Q136or………….If Job 1 is a “Yes”, Job 2 is a “No”, Job 3 is a “Yes”, and Job 4 is a “No”…….then AK178 should equal Q108and AK179 should equal Q136 I can write the formula for one but not if there are 2 completed jobs.  Help please!!   Read More

​