Programme Spreadsheet - Version 1.3 Out Now (June 2011)

[craig]

KavJack wrote:

Sure, I don't mind. It's quite a big task though. My Access design view is as follows: GIDX: Index value Home Team: Text Away Team: Text Venue Competition Date Played Season Cover Price Volume Number Quantity Pages Condition Style Size Date Acquired Price Paid Current Price Source (of acquisition) E*Bay (yes or no) E*Bay_Name Score My Collection Volume Neutral Venue Spare_4 - often used a s a pointer to image of programme Spare_5 Spare_6 - often contains actual E*Bay number for auction when won item Note I have written many queries - too many to list. For example ..Leeds in Inter-City Fairs.. enquiry SELECT [Programme Collection].[Home Team], [Programme Collection].[Away Team], [Programme Collection].Venue, [Programme Collection].[Competition], [Programme Collection].[Date Played], [Programme Collection].Season, [Programme Collection].Score, [Programme Collection].[Cover Price], [Programme Collection].[Date Acquired], [Programme Collection].Source FROM [Programme Collection] WHERE ( ((InStr([Programme Collection].[Competition],"Inter-City"))>0 OR (InStr([Programme Collection].[Competition],"U.E.F.A"))>0) AND (((InStr([Programme Collection].[Home Team],"Leeds"))>0) OR ((InStr([Programme Collection].[Away Team],"Leeds"))>0)) ) ORDER BY [Programme Collection].[Date Played]; Contact me at [email protected] if you want to go into this in greater detail.

[KavJack]

My Access databases containing the data for results usually have the following design, expanded from Tony Brown's soccerdata format:

ID
SEASON
DIV
DIVNAME
HTFULL
HOME_SCORE
ATFULL
AWAY_SCORE
GDATE (Date game was played)
VENUE (location game was played at)
Have_Prog
Attended_Game

I don't always have all fields for all my databases. I only have the Venue field for Football League games. Tony Brown does not have this field. Many Football League clubs have played at quite a few different venues, i.e. Both Bradford City and Huddersfield Town have played games at Elland Road when their stadia caught on fire. I doubt if I could collect this data for non-league teams. My Cup databases have a slightly different format since that involves round data as well. I recently added and Attendance field for the FA Cup database. Access is not the problem - the problem is being able to accurately collect the data.

[craig]

Hi Matt,

Id just like to make a possible suggestion for the next release...

Is it possible to add a new column to the spreadsheets, so that people can mark if they wish to replace that programme if a mint condition programme comes along.

I have programmes in my collection which have tcs, crs, phs etc on but i want to mark them down as "Got" on the spreadsheet, however id also like to replace these if a better condition programme comes along...

Also, the "Friendlies" tab only counts the first 48 fixtures but obvioulsy teams have played more than that.

Craig

P.S. I e-mailed you a while ago regarding the friendlies.

[Barrett]

Without reading through the whole thread again, is the spreadseet in Excel 2003 or 2007?

I am getting Office 2007 tomorrow to update my pc, will this cause any problems with the spreadsheet?

[craig]

Geddon has produced version for both, but its better with 2007.

[Geddon]

I've already spoken to you about the Friendlies, just put them in the season they were played and they'll be counted in the new version.

Your second suggestion - that's pretty much what the notes column is for, other than noting postponements, or do you want that to show up on the Overview page etc? You could use the # where you usually put NP, I use it to denote programmes I've bought but haven't got yet.

[craig]

The good thing about it at the moment though is that you can see at a glance what you have and dont have.

What i think would improve it though is if there was a way of noting programmes that you HAVE but would like to replace due to poor condition etc.

Possibly by using a # in the "got" column. This could then turn the cell a dark green so the key would be;

Orange - Need
Light Green - Got
Dark Green - Got but wish to replace

All the other counters within the spreadsheet though would need to count .s and #s as having got the programme.

Also the PP - P / NP doesnt seem to work on the away fixtures? I think this may have already been pointed out to you though.

[Geddon]

Hmm, that's a good idea actually, it's something I've just started doing with my collection. Shouldn't be too hard to implement either.

As for NP & PP not working on the away fixtures, what do you mean not working? Does it not count the programme in the columns, or not do the conditional formatting?

[craig]

The formatting doesnt work when you put PP - P or PP - NP in.

And also when you do put PP - P or PP - NP in a home game, it seems to also colour the away game on the same line in the same colour

[Senior82]

Using Excel 2003

When I put a dot or a comma in the homes section the cell changes colour, but when I do it in the away section the cell doesn't change - it just displays a dot or comma?

It doesn't seem to like the clicky 'drop down' menus either!

[Geddon]

Senior,

I've only just seen your post sorry.

I'm not sure why it's not displaying correctly, it should work OK in Excel 2003 as cells can still take three condition formatting conditions.

The drop down menus are not part of version 1.1, only on the 2009-2010 tab, they work fine there on my 2003 version of Excel?

The new version for Excel 2007 is coming along nicely and should be released for download in the next week. Craig has been testing it for me as well and we're confident that this one should be bug free, although there will still be a beta release before the full version

[Geddon]

Version 1.2 Released
It's taken a little while longer than I expected, but finally I've managed to get the latest version working properly.

New features:
- Home and away by team by season tabs. An easy way to keep track of which programmes you need for each team. Only for league games.
- A "got, but need to buy a replacement because my current copy has rusty staples/team changes etc." option. When entering your data, you can now choose this option for programmes by entering a semicolon. Programme will show up in all counters as "got" but on the individual season page it'll turn dark green, indicating that you need a replacement.
- Fixed some bugs that I missed last time around.
- Dates are now stored only in one column instead of three like last time.
- A few other things tidied up.

At the moment there is no Excel 2003 support for this version but again it's something I might do in future. Unfortunately due to the amount of conditional formatting used it's not just a simple matter of converting it straight away.

DOWNLOAD THE NEW VERSION

Thanks for reading and using the spreadsheet. As always any feedback is greatly appreciated, as I'm sure there are probably a few things that myself and Craig have missed. I will relase another version in a few weeks just correcting the errors (if there are any!).

[craig]

Thanks a lot mate!!

That link is different to the address you game me though

Also as ive said before if there are any problems with bandwidth limitations your end i can sort out storage on here for you if you require?

Ill add it to the downloads section of the site aswell tomorrow!

Thanks again!

[Geddon]

Yeah the link is different but it's the same thing, you pointed out that I'd called it V1.12 when it is actually V1.2, so I just changed it. Exactly the same file, don't worry!

There shouldn't be any bandwidth problems as there's very little else on my site, but please add it to the download section yes, and you're welcome to host it as well if you want.

[speedykev]

The latest version is fantastic. For some reason when entering information i get a message stating the file is protected and therefore read-only. When i follow the instructions given to remove the protection it requires a password?

[Geddon]

Hi mate,

You can't unlock the sheets without a password, but you can run the UnlockAllSheets macro and then you should be able to edit whatever. The sheets are locked by default though to stop people from being able to change/delete cells that will effect how the spreadsheet works.

Out of interest, which sheets were you editing when you had the problem? Craig mentioned a similar thing as well, so hope it's the same thing.

[craig]

Ive not had any problem like kev described. Where were you trying to enter data to Kev?

The only problem ive had is when trying to check/uncheck the boxes in the HT/AT tabs which was sorted by unlocking the sheets via the macro as you suggested.

[Geddon]

It's a similar problem in the sense that it's being caused by the spreadsheet being locked.

Like I said, the UnlockAllSheets macro will let you edit things, but I would advise you to lock them again after incase you accidently delete something that'll change the behaviour of the spreadsheet.

If you can tell me where you're having the problem I'll have a look at it and be able to issue a fix.

[CTFC]

Downloaded this ages ago and have inputted pretty much all of my programmes but was wondering how I can now add the comming season to the database?

[craig]

Geddon said the other day hes releasing a new spreadsheet very shortly. Theres a couple of bugs fixed and the new season added. All youll need to do is download it, open the file and click "Import from version 1.2" and then select your original file and all your date will be copied across