Betting Sheet data entry suggestions

[admin]

A few people have asked about the use of the drop down menus to select Sport, Agency, etc. in the Bets worksheet. Some users find they have to scroll up the long list each time to make a selection.

I actually recommend you input data into those cells by typing in your selections rather than using the drop down buttons. Due to Excel auto-complete it always works out much faster. The drop down selections are primarily there to ensure data integrity (i.e. you can only input a value that is listed in the Settings sheet).

If you do wish to select from the drop down list but find the list too long, you can change a column as follows. The instructions below presume you only want to choose from 20 selections in the sport field.

1. In the top menu select Review --> Unprotect sheet. The password is sport.
2. Highlight the sport column (from row 11 onwards, depending on the version you are using)
3. In the top menu select Data --> Data Validation
4. In the Settings tab for 'Validation criteria' you will see a cell reference for "Source:". Click on this area and change it to reference fewer cells. If your copy of the spreadsheet has "=$AY$11:$AY$210", change it to something like "=$AY$11:$AY$30".

If your sheet always defaults to showing you the last value in the drop down menu, one alternative to the above is you input your list at the bottom rather than at the top of the Settings sheet.

[Stein276]

First off, thanks for a great spreadsheet. I've had a quick play with it and found it very useful.

Just a suggestion but a quick, more flexible workaround to the above question would be,

- Before doing this you must unprotect the settings & bets sheets.

1. On the "settings" sheet create a named range called SETTINGS_BET_AGENCIES from D7 : D206 and clear any cells that don't have a bet agent listed.
2. Then on the "bets" sheet click on cell C9 and choose data validation and in the source box type =OFFSET(SETTINGS_BET_AGENCIES,0,0,COUNTA(SETTINGS_ BET_AGENCIES),1) and then make sure to tick apply these changes to other cells etc

You can repeat the process for the 3 other sections on the "settings" sheet and repeat the data validation with the offset command (using the correct named range) on the "bets" sheet.

- When finished go back and protect the settings & bets sheets.

By doing it this way, the information we just typed into data validation counts the items and adjusts the data validation list length as needed.

Hope this helps, I've made the changes to mine and it works great.

Cheers
Stein276

[admin]

Many thanks for the suggestion Stein276.

[Stein276]

No problem at all.

Upon further investigation you may also want to make this change to the "agency" column (C,H & M) on the deposits sheet.

Also, with the performance summary filters (bet type, capper & sports) it now shows % in the dropdown for empty cells from the settings sheet.

There is a workaround by using data validation and a dummy cell with a formula like =MATCH(CELL_REFERENCE,SETTINGS_BET_AGENCIES,0)) which will still give us a number in FILT_B, FILT_C & FILT_S.

That's if you don't want the % listed.

Cheers
Stein276

[robotzel78]

Is it possible to insert, in next update version, the Hitrate perfomance and Average odds in "Performance Sheet", in performance by Tipster/Capper section?
I mean about colomn V and W for these type of stats.

Thanks in advance and keep up the good job with this extraordinary file.