Posts by VBAwant2b

Perfect! Does help.

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

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.

Thank You! Thank You! Thank You!

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!

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.

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

Sorry, I meant to say, Column B may be correct and what I need.

After spot reviewing it. It is correct, although there are no formulas in any of the cells in Column B.

Let me review Test SBLO V2.

Just looking at it, I don't need Columns C-G.

I think Column A may be correct and what I need.

The need for a subroutine was to get results without filling a sheet with formulas in each cell but I can always Copy/Past Value. This is just one part of many different calculations I'll be doing.

I appreciate your efforts. Let me review.

Sheet1(2) is just an example of code that counts the number of skips between hits for winning numbers. I only included it to show the subrountine used to find that.

What I was hoping to find was a modification of that subroutine that counted the number of skips for ALL 43 numbers with the results displayed in Sheet2 Column B.

1. B7 (5), 5 drawings since the #1 (A7) was hit/picked/selected. See Sheet1 C872.

If you entered 1, in C878 of Sheet1 and counted the number of drawings between the last time 1 hit (Sheet1 C872), it would be 5 drawings.

2. B8 (0), the number 2 (A8) was picked/hit/selected in the previous drawing on (Sheet1 C877), so, there would be no skips/drawings between it IF #2 was picked/hit/selected for the next drawing.

3. B9 (12), 12 is the number of drawings between the next drawing that would be entered in one of the cells (C878:G878) IF 3 were picked/hit/selected in the next drawing.

Five numbers out of 43 will be entered as the next drawing in Sheet1 C878:G878. I don't know which five out of the 43 it will be but I'd like to know how many skips it would be between drawings for each of the 43 numbers that could be drawn with the count placed in Column B.

If I were to enter 1, 2, 3, 4, 5 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 5, 0 , 12, 47, 9 in H878:L878.

The result of 5 in H878 is number of drawings since the #1 occurred in a previous drawing. The result of 0 would be the number of skips for the #2 in the previous drawing. The #3 had 12 skips since it last occurred in a previous drawing. Etc. for the #4, 47 skips since it last occurred in a previous drawing. Nine skips for the #5 since it last occurred in a previous drawing.

If I entered 6, 7, 8, 9, 10 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 0, 1, 4, 3, 12 in H878:L878.

Instead of entering 1, 2, 3, 4, 5 and clicking the command button, get the results, then entering 6, 7, 8, 9, 10, get the results, etc. etc. through 39, 40, 41, 42, 43, I'd like to modify the Sub attached to the "Test SBLO" command button to count the number of skips between the last occurrence for all 43 numbers of which 5 will be picked/hit/selected. Place the count of skips in Column B of Sheet2 for each number in Column A.

Let me add this.

Imagine on the next drawing, instead of selecting 5 numbers out of 43 numbers, they drew all 43 numbers. Just for giggles.

I want to know how many skips between the last occurrence for each number (all 43 numbers).

I'd like to see that result in Column B on Sheet2.

Hope that helps make it more clear.

If you look on Sheet2, Column B, it shows the results I'm looking for. These were manually counted and entered.

Moving forward, as I enter the latest drawing on Sheet1, the results will change on Sheet2.

Carim,

See attached.

I'm grateful for your assistance.

SBLO.xlsm

Thanks Carim! I'll attach a .xlsm that gives a sample of what I'm seeking Monday. Everything is on my work PC. Also, I have horrendous rural internet service (500kbps) at home.

Sheet1

Sheet2

Sheet1 contains a list of drawings. The numbers are in Columns C through G. One row for each drawing.

Sheet2 contains numbers 1-43 of which 5 will be selected for the next drawing.

In Sheet2 Column B (SBLO) is where I want to place the number of drawings Skipped Between the Last Occurance for each number in Column A (#).

The Sheet2 example has the results I'm seeking in Column B (SBLO) according to the latest drawings in Sheet1.

[IMPORTANT] I didn't template the full history of drawings in my Sheet1 example. The #4 on Sheet2 shows 47 skips between last occurance but to save space I didn't list the drawings that far back.

Ex. 2-6-11-13-31 was the last drawing on Sheet1 so they will have 0 skips between last occurance on Sheet2. The drawing before that on Sheet1 was 6-7-22-23-32. Numbers# 7, 22, 23, 32 on Sheet2 will have 1 skip between last occurance, 6 already has 0, etc. etc.

Lame attempt below. This is stuff I mashed together from my little subroutine library but it definitely has big holes in it. I'm far from being VBA proficient. I only know enough to be dangerous and always frustrated.

Thanks in advance to anyone willing to help.

Sub Count_SBLO()
Dim myRange As Range
Dim c As Range
Set drwgRange = Sheet1.Range("C3", Range("G1048576").End(xlUp))
For Each c In myRange
For i = c.Row - 2 To 1 Step -1
If Not IsError(Application.Match(c, drwgRange.Rows(i), 0)) Then
c.Offset(, 1) = c.Row - 1 - i
Exit For
Else
c.Offset(, 1) = 0
End If
Next i
Next c
End Sub