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.
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.
So basically ... you do need a single check ... but throughout your FIVE columns C - G ...
See attached Version 3
Hope it is in line with your expectations ...
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.
If I am not mistaken ... it is only my guess ... you have left cell A878 empty ... and Column A is THE reference column to count the total number of records ...
Am I wrong ...???
Thanks for your Like
Does it mean the formula is producing your expected results ...???
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!
Pleased to hear you could fix your problem ...
Thanks a lot for your Thanks ...AND for the Like
Thank You! Thank You! Thank You!
Thank You! Thank You! Thank You!
Thanks again
Just marked your Thread as Resolved
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.
Hello,
In any workbook where you would like to use the formula ... the only thing to do is to create a Dynamic Named Range ...
Control F3 ... > New > the Name you want > Refers to > =OFFSET(Data!$C$2,0,0,COUNTA(Data!$C:$C)-1,5) > OK
Hope this will help
Perfect! Does help.
Once again, thanks for sharing your Excel brilliance with mere mortals like myself.
Perfect! Does help.
Once again, thanks for sharing your Excel brilliance with mere mortals like myself.
You are welcome
Never forget our common destiny ... IS ... to BE MERE MORTALS .... And PAY TAXES ... as Woody Allen used to say ...!!!
Don’t have an account yet? Register yourself now and be a part of our community!