Incrementing a cell contents by 1 based on a condition and previous value

The sheet "Award Code" contains the code which are constant numbers.

The sheet "Jan" contains some data

The sheet "Feb" is the main sheet where we want to get some work done.

Basically if the ELIGIBLE column (B) is YES, then we increment the value of the corresponding AWARD column (C) by 1 then what was for it in the previous ("Jan") Sheet.

So for example, in the "Jan" sheet the cell C4 had a value of "2". So in "Feb" sheet, since B4 is "Yes", C4 has gets a value of "3" (2 + 1).

One boundary condition is that if the value of AWARD col reaches "19" then it gets reset to "1" again.

Attach is the excel sheet.

Thanks

Do you mean something like:

Feb!C2: =(MOD(VLOOKUP(A2,Jan!A:C,3,0),19)+(B2="Yes"))*(B2="Yes")

Originally Posted by DonkeyOte

Do you mean something like:

Feb!C2: =(MOD(VLOOKUP(A2,Jan!A:C,3,0),19)+(B2="Yes"))*(B2="Yes")

Yesssss that works! Awesome!

The only thing is that when I create a new sheet for the next month, "Mar" (copy from Feb), the formula in the "Mar" sheet still references "Jan" instead of "Feb".

Is there any way so that the month in the formula that you provided automatically gets changed. If not automatically then by just a minimalistic effort? The thing is that the intended user of this are not very tech savvy so they might have hard time figuring out.

Thank you once again.

Depends on the volume of data... you could possible use INDIRECT but this is a Volatile function so is generally best avoided... to illustrate:

Feb!E1: =CELL("filename",E1)
Feb!F1: =TEXT(DATE(1901,MONTH(1&RIGHT(E1,LEN(E1)-FIND("]",E1)))-1,1),"mmm")

Feb!C2: =(MOD(VLOOKUP(A2,INDIRECT($F$1&"!A:C"),3,0),19)+(B2="Yes"))*(B2="Yes")
copied down for all rows

Now if you copy Feb sheet, rename to Mar ... you should find Mar sheet now links through to Feb automatically.

The (better though less intuitive) alternative is to simply repoint the formula manually thereby avoiding Volatile functions but if this isn't viable the above should work.

Thanks for the detailed explanation. I think I might resort to the manual update since the volatile function looks quite scary.