Count Skips Between Last Occurrence

Sorry, I'm at work trying to keep the bosses happy while trying to focus on this as well.

See attached. I made comments in a few of the cells to explain why they don't match.

See if it makes sense to you.

Copy of Test SBLO V2.xlsm

Almost. Very close.

Your formula result (C25), "7" for #24 is correct. My manual count, "6", was incorrect.

I tried to enter a few numbers for the next drawing to see how the formulas on Sheet SBLO update and they don't exactly.

Enter 39, 40, 41, 42, 43 as the next winning numbers on the Data Sheet and see what results you get for SBLO.

Also, try entering the last winning numbers 2, 6, 11, 13, 31 as the winning numbers for the next drawing to see what you get. The formula update should be "0" for each number. I'm getting "-1" as a result.

Lastly, whenever I enter the winning numbers for the next drawing, all of the results should update with an additional skip.

In other words, if #7 didn't hit on the next drawing and the formula results show it had "1" skip, when I enter the next winning numbers, although "7" wasn't one of the winning numbers, the formula should update to show it's now "2" skips IF it were to hit in the next drawing.

Aaaaah! My bad.

Yes, I left it empty not realizing it was part of the formula and necessary to do a correct update.

This works!

Wow! You don't know how much I appreciate your efforts!

Thank You! Thank You! Thank You!

Hi Carim,

I moved the formula you created for me yesterday to another workbook. When I tried to reopen the workbook this morning, I get a message to update links.

Why can't I use that formula in another workbook without having to link it to the workbook that it was created in?

I made a worksheet reference change in the formula from Data! to DB! which is an exact copy of the Data worksheet in Test SBLO V3.xlsm

Test SBLO V3 - {=COUNTA(Data!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}

New Workbook - {=COUNTA(DB!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}

Does it have anything to do with "ListN1"? I really don't understand that part of the formula. What is ListN1? I'll research ROW function more.

I'd really need to be able to use the formula without having to link it to another workbook.

When I try to break the links, it warns me to save a copy. I'm not sure what is going on. I'll try to research this more myself because I need to understand why I can't use the formula as written.

I wonder if a subroutine is the way to go.

Perfect! Does help.

Once again, thanks for sharing your Excel brilliance with mere mortals like myself.