|For "fun" (yes I am bored at work) I'm seeing if this can be done with a formula.|
I'm putting the results in Sheet2 and assuming all your data is in sheet1. As you say it's a bit tricky to get all the letters filling in dynamically, in fact I doubt it can be done with a formula unless you're willing to accept a lot of wastage:
Sheet2!A2 could be:
note I've shoved in an extra return there to aid formatting
Which would make sheet2!B2
(another couple of returns in the above).
These two rows can then be copied down 256 rows (or some similarly outrageously high number, to accomodate data values all the way up to "IW") and it will be dynamic.
Note though that countifs over large ranges are notoriously slow, and if you have shedloads of data the VBA approach normally wins as it only iterates the required number of times.
Also a couple of caveats with the above approach:
1) I'm assuming the ONLY data in Sheet1 column A is the following: "Start" or similar text in cell A1, the entered value in A2, and the actual data.
2) I'm assuming the ONLY data in sheet1 row 4 is the data.
3) I'm assuming valid week numbers are entered in cells A2 and B2. The formula will not work for blanks, or entering 0 (weeks that don't exist, etc)