Lookup text in array & return cell reference

[Matt]

I need to create a simple spreadsheet to forecast when an employee is forced
to have a rostered day off (RDO). Employees can only work a maximum of 13
consecutive days and are then forced to have an RDO.

To do this, I have an array (A1:A14). Cell A1 has the text "RDO" in it.
Cells A2:A14 have a value of "10" in each cell. I would like to insert a
formula into cells A15:A28. The formula needs to look back 14 cells and
return "RDO" in the respective cell within the array A15:A28 that is 14
columns from the last occurence of "RDO".

Hope this makes sense. Thanks in advance.

 

[Matt]

Correction to previous question:

1st array is A1:N1 and 2nd array is O1:AB1 (not A1:A14 and A15:A28).

Sorry

 

[Jacob Skaria]

Dear Matt

Copy the below formula in O1. and copy that to the other columns from P1 to
AB1

=IF(OFFSET(O1,0,-14)="RDO","RDO","")

If this post helps click Yes

 

[Matt]

Thanks Jacob

Your solution does/doesn't work. It does work by forecasting an RDO based
on when an RDO was taken in the previous 2 weeks however if an employee took
an RDO on 1st day (cell A1) and 8th day (cell H1), then your formula
forecasts an RDO on 15th day and also on 22nd day. Correct answer should be
22nd day only (thereby only having a maximum of 13 days duration for any
period worked).

I have worked out a formula that does work (paste into cell O1 and copy
across to cell AB1):

=IF(A1="RDO",IF(ISNUMBER(B1*C1*D1*E1*F1*G1*H1*I1*J1*K1*L1*M1*N1),"RDO",0),0)

There is probably a simpler method. This works for me though. Thanks again
for your help. Can your formula be adopted to suit (yours looks a lot
simpler).

 

[Jacob Skaria]

Hi Matt

Sorry I have misunderstood...Try the below formula in O1, and copy that to
the subsequent columns

=IF(COUNTIF(B1:N1,"RDO")=0,"RDO","")


If this post helps click Yes

 

[Matt]

Much better. Looks like a much better formula too.

Thanks again for your help Jacob.