asked on
Adding Record and Expenses tab for Keno Workbook
Requirements : File attached RecordNExp tabs Keno.xlsx
In Record tab, I will be inputting data manually.
Column A = Date
Column B = Day
Column C:L/ Row 2 = Numbers drawn 1 to 10
Column N = I will manually input spend
Column O = I will manually input win
Now Column N and O should automatically update (sync) spend and win column of record tab to the expenses tab.
Update button needed to update input of spend and win tabs for sync to work between record and expenses tab.
Other buttons needed are Add Month and Clear all
In Record tab , currently there are only two rows of draws for each date, but if I play more than two draws then would need an option to type the third row in that date.
ASKER
latest updated lotto workbook attached : 29240392c (1)lotto workbooktill27July.xlsm
Also:
- Why is column D in the Expenses tab blank?
- Is "Income" column E minus column F?
- Is "Total Win" the sum of column G?
- Why are the question marks in Total Spend($?) And Total Spend($?)
Regardless of that outcome, when you reopen the workbook please test the Record and Expenses processing. A lot of code was added so I'm sure there are things wrong; let me know what they are.
29243858.xlsb
ASKER
Why is column D in the Expenses tab blank
Keno spend column would come under a single column. I was trying to merge two cells, but don't know what happened there . So column D can be deleted
ASKER
- Is "Income" column E minus column F?
yes
ASKER
- Is "Total Win" the sum of column G?
yes
ASKER
- Why are the question marks in Total Spend($?) And Total Spend($?)
Because there is no value at the moment, since there is no data coming from the Record tab
ASKER
ASKER
ASKER
ASKER
A lot of code was added so I'm sure there are things wrong; let me know what they are.
I entered data manually in the Record tab in below picture
And a run time error popped up when update results button was pressed
So I could see not see the outcome in the expenses tab, because of the runtime error
ASKER
I will but I'm hoping you can test the way it is and ignore the fact that there's a blank column.
ok . run time error info is in the previous post
ASKER
Yes you can add time column after day
Yes 4 rows is fine. But what if I play 3 draws for 10 am instead of 1 draw.What did we do in the lotto workbook?
ASKER
What did we do in the lotto workbook?
It was similar, where I could add more lines. I had to manually create a blank row below the last typed row.
And the update results picked up the extra row and updated the expenses tab correctly
ASKER
ASKER
The continuity question was the one in below picture
A request to make was to title the M column to " Draws Played"
For example , if I spent $2 for Row 5 , then Spend Column N5 would automatically display 2,
and if I spent $1 in Row 6, then Spend Column N5 should now display "3"
because Row 5 + Row 6 = $3 and hence N5 displays 3 ($ sign not required as it is understood it is cash spend)
In below picture, I would wish to set a Spend Limit in Cell N65 = 124.
So $124 is my spend limit. But in the future or next if I want to change the spend limit to 150, that should be possible.
In above picture total spend was Row 5 = 3 and Row 9 = 4, so 3+4 = 7.
So now limit spend N65 which was $124, now should reduce to 117
Calculation is $124 - $ 7 = $117 and 117 should display in N65.
provide an option to add a date buttonHow would that be used?
2) In the lotto workbook you were "charged" 70 cents for each draw played without a powerball and $1.50 for each powerball line. What is the cost per draws played in the keno workbook?
3) Also you are showing Spend and Win on the Record tab. Why is it there when the same information is on the Expenses tab? And if you really do want it on the Record tab, why don't we put the total line on the Record tab and get rid of the Expenses tab.
4) When is it a win?
5) Are you expecting to see colors in the Record tab? If so should the values be compared against the Monitored Numbers?
ASKER
provide an option to add a date button
How would that be used?
Suppose I start playing in the middle of the month, then would there be an option to add half a month. ?
Or let's say if I started playing ken on 29th July instead of 1st Aug 22, that is when I thought I might to add from and to date period.
The current way that you have done is Add Month.
But say if I started to play keno on 29th July 22, would I be able to delete the dates from1st July to 28th July without upsetting the coding.
But if the coding does get affected , then I thought to also request a from and to date period where I can the date period that I want.
ASKER
What is the cost per draws played in the keno workbook?
Each draw played cost $1 per draw.
Minimum spend $1 and Maximum spend is $100
So following is how it is paid out
Match your Keno numbers to the numbers drawn to see if you win. Your prize will depend on how many numbers you picked and how many numbers you matched in below picture
ASKER
3) Also you are showing Spend and Win on the Record tab. Why is it there when the same information is on the Expenses tab? And if you really do want it on the Record tab, why don't we put the total line on the Record tab and get rid of the Expenses tab.
In Record tab, there is a record of the number of lines played either 10 am , 1pm, 3pm and 6pm.
In 10 am draw , I could for example play 6 lines which needs 6 rows , then in 1 pm I might just play 1 line = 1 row.
Whereas in Expenses tab, this is how it should look in below picture
Also when I use my laptop it is just a 11inch screen, so having expenses tab separate helps to see the tab fully.
In below picture this is how it would look to reflect what is in record tab, because I played linees
ASKER
And if you really do want it on the Record tab, why don't we put the total line on the Record tab and get rid of the Expenses tab.
because laptop is 11 inch screen , would have to scroll back and forth.
ASKER
When is it a win?
For this I have to type, in spend and win column manually and that would update the expenses column automatically with the update button on the record tab.
ASKER
5) Are you expecting to see colors in the Record tab? If so should the values be compared against the Monitored Numbers?
Yes that would definitely help as I could use the same numbers in the future watching the record tab everyday.
Not sure against what it should be compared, but I can leave this to you and if I find it ok , then I will indicated that
But say if I started to play keno on 29th July 22, would I be able to delete the dates from1st July to 28th July without upsetting the coding.I believe that you don't need to do anything and only those rows with data get copied o the Expenses tab.
because laptop is 11 inch screen , would have to scroll back and forth.Would being able to press Ctrl+Shift+B to go to the bottom and Ctrl+Shift+T to go to the top help?
Yes that would definitely help as I could use the same numbers in the future watching the record tab everyday.In the lotto workbook I think we match against the WNR and if so I can do the same thing here.
ASKER
Would being able to press Ctrl+Shift+B to go to the bottom and Ctrl+Shift+T to go to the top help?
I tried the above keyboard shortcut for 1 to 80 tab, unfortunately the scrollbar did not move back or forth.
Neither did it go to the bottom or the top.
ASKER
In the lotto workbook I think we match against the WNR and if so I can do the same thing here
As long as color highlights can be displayed that should be fine.
ASKER
I believe that you don't need to do anything and only those rows with data get copied o the Expenses tab.
I am happy with that then.
ASKER
I don't think that my idea to have totals at the bottom of rhe record tab is practical so lets forget about that. I'd also like to forget about my idea of including the times.
Which of your previous comments were you referring to for the above ideas
I added the Prize Table tab as a hidden worksheet.
To add a second line for a particular date in the Record tab, double-click the date that you want to duplicate.
Other than a spending limit I know that some things are missing, but please tell me what you feel is missing.
29243858a.xlsb
ASKER
I updated the workbook successfully to 31 July 22
update file name : e4f42e38-1110-11ed-87f0-005056819165
After updating the file till 31st July, I had to manually update to 1st Aug and 2nd Aug.
I clicked By day and got option to enter 2 days (1st Aug and 2nd Aug) worth of draws.
I entered all draws for 1st Aug and only 10am for 2nd Aug and deleted 1 pm , 3pm and 6 pm as they are not drawn yet and then I hit on add draws, but I got a message, but the message will not go away till it is corrected. So not sure what I did not do correctly. ?
So the manual draws are not updating in below picture, is there any step I may have done differently for not being able to add draws.
But you should not be adding anything less than a full day when you choose By Day. Instead you should use By Draw.
ASKER
But you should not be adding anything less than a full day when you choose By Day. Instead you should use By Draw.
I clicked on Add by day, because it displayed 8 draws in one go for 1st and 2nd Aug.
ASKER
then clicked again by draw and selected 1 draw and that was ok.
so this way it is all good . thanks.
Now I am still trying to understand the Record and Expenses workings, so will take some time to figure out .
ASKER
ASKER
After this I typed 6 numbers in row 65 in below picture
and then tried to update the results, but when trying to update got a run time error 91
and everything got deleted in below picture while trying to update and looks this in below after trying to update
Also there are row spaces between spaces in expenses tab.
Blank rows in Record tab is fine for purpose of entering data , but I was hoping to have no blank rows in the expenses tab in above picture. Can the Expenses tab be made to look like below picture without blank rows
Also if you look at the Expenses tab you'll see it's a lot better.
29243858b.xlsb
ASKER
so I typed 6 numbers in row 64 and clicked on update results, but the expenses tab did not update.
The expenses tab looks like the one in below picture without the 31st July update.
Not sure where did the 31st July update go ? I see only upto 7th July
ASKER
29243858c.xlsb
ASKER
ASKER
The 3 pm draw of 31st July in above picture shows 5 of the 6 balls matched which is 2 6 9 11 15 22, except for 22.
so 2 6 9 11 15 matched the 3 pm draw, hence as per the prize table , the win is $40 in 064
Therefore the expenses tab should update $40 win.
ASKER
Okay, one of my suggestions was that I add the time to the Record and Expenses tab,
Adding the time to the Record tab is fine, but if you are able to merge the update in expenses tab date would be good if that was possible
ASKER
Why do you need columns N and O on the Record tab? The Expenses does it's own calculation.
Why do you need column M?
ASKER
Why do you need columns N and O on the Record tab?
ok I agree with you on this one . I would have to view the spend and win in Expenses tab then.
But in saying that I was going to ask you add a spend limit at the bottom of each month.
So that was the reason for spend and win tab. Unless you are able to add a spend limit directly in expenses tab
ASKER
It's already doing that (the two 3-Jul lines).
I can see it is doing the merge in expenses tab that is great
ASKER
ASKER
The Expenses does it's own calculation.
So what is the reason the $40 is not getting updated in the expenses tab for 31st July.
That is when you suggested to add the time to the record tab to fix it.
ASKER
ASKER
29243858d.xlsb
ASKER
But after entering data on the 10th of July, all the manually entered draws in Record tab got deleted, while the one that you entered has remained, so not sure what happened there
All the data in the expenses tab got deleted as well in below picture
So I wish to have the manually entered draws in Record tab to remain and not get deleted, but at the moment whatever I entered in the Record tab is getting deleted.
Also the new data in the expenses is getting overwritten, meaning the previous information is deleted to replace the new information, that has happened in below picture
Above I can see data only from10 July 22 only , all the previous ones are wiped out. Not sure why.
ASKER
But after entering data on the 10th of July, all the manually entered draws in Record tab got deleted, while the one that you entered has remained, so not sure what happened thereI will look into that.
Also the new data in the expenses is getting overwritten, meaning the previous information is deleted to replace the new information,That's by design. Each time the Update Results is clicked everything in the Expenses tab is replaced by the information in the Record tab.
29243858e.xlsb
ASKER
Yes the record tab is now able to retain data,
However I typed 5 numbers for 10am draw 2, 6, 16 , 18 , 21
and 2, 6, 16 matched and the payout for 3 out of 5 matched is $2.
However Update button did not transfer $2 in Profit column Cell E3, so E3 stayed blank instead of populating it with 2
ASKER
Run time error 91
I clicked on CLEAR ALL and start with a clean slate and chose Add Month and Added Aug 22,
After that I typed number 3 in 6 pm draw for 1st Aug and 3 is a match and so would have shown a profit of $2 , so I clicked update results button to update expenses tab , but instead I got a run time error 91 in below picture
Do you see anything wrong with me making a change that does not allow days to be added to the Record tab if they don't exist in the WNR?
ASKER
But I typed manually the draw results for 1st Aug 22 and updated the Main tab as in below picture till 1st Aug 22 i
But I still get the error 91 , is it because the different tabs are not syncing to the manual update.?
However WNR TAB still shows 31st July 22 in below picture, which could be the reason why error 91 shows up.
While Monitor Groups in below picture shows till 18th July, but I know that this tab gets only updated to the latest draws only if it is a downloaded update. Not sure why this tab does not pick up Manual draws update.
So am I missing some steps here ?
So it seems the different tabs are not syncing to the same date of 1 Aug when doing a manual draw update.
29243858f.xlsb
ASKER
Main tab and 1 to 80 got updated to 1st Aug 22, but WNR tab did not get updated.
So is WNR tab solely dependent on the downloaded update ?
Is it possible to make the Manual draws updater to also update the WNR TAB ?
I do mistakes sometimes by typing one or two wrong numbers in the Manual Draws tab and unfortunately the WNR TAB might pick up those wrong numbers too.
So is that why you have not linked the Manual draws updater to update the WNR TAB. ?
In a future question I could have the manually added draws show up in the WNR and if you make a mistake you can edit the WNR.
So is that why you have not linked the Manual draws updater to update the WNR TAB. ?No, I just didn't know it was a requirement.
ASKER
In a future question I could have the manually added draws show up in the WNR and if you make a mistake you can edit the WNR
The typing mistakes that I do while entering in the manual tab are , typing the same number twice or typing a different number that was not in the declared results or missing a cell.
Would your code be able to pick that up in the Manual tab itself and alert me about that.? as a pop up
As I am worried that updating the WNR Tab if I made a mistake here as well would affect the update globally meaning all the tabs that are dependent on the update
ASKER
ASKER
Would your code be able to pick that up in the Manual tab itself and alert me about that.? as a pop up
My question was focussed on the previous comment as above , can you code pick up my typing error in the Manual itself and alert me as a pop up so I can correct it in the Manual tab itself ?
ASKER
ASKER
I can't do anything about a wrong number because the code doesn't know the right ones. \
I thought so.
I can however validate for duplicate and missing numbers.That would be good. But if you think it is problematic when you actually have to try it do let me know
But if you think it is problematic when you actually have to try it do let me knowIn your next question add that as a requirement.
ASKER
The final question I have on the current question is , since I have not been able to test the 1st Aug draw because the the lotto website update for latest draw is not available. Would I get to see the this row under each month in below picture , which is what I wanted .
Would I get to see the this row under each month, which is what I wanted .Im not sure what that means.
ASKER
Would I get to see the this row under each month, which is what I wanted .
Im not sure what that means.
Totals row as below should be visible under every month like below picture is for July 22
Row 10 showing keno spend, profit, loss, income
ASKER
Are you saying the when you eventually Add Month of August that you still want to see the July figures in the Expenses tab?
No that row is for July 22 only and do not wish to mix up.
But for Aug 22 , it should have that row for Aug only below the month
ASKER
I have been struggling to test the Aug Month as there is no new download update.
So I used the following method to find out if the Row(spend, profit, loss and income) under each month in Expenses tab gets displayed or not.
So I used clear all button and then added June month and July month and then tested it and realised that the spend, profit , loss and income are already getting merged in one row. At this point, I wish to see the merged row separately, maybe in the J K L and M columns.
But I wish to see the spend , profit, loss and income row at the bottom of each month which is not happening at the moment. The reason I need this row under each month is because I when request you to add a spend limit , then that would be visible for each month along with the ROW of spend, profit, loss and income
ASKER