I have place a string infront of E because thats the column i require.
I want to unlock the 6 so the number changes 7,8,9 as i drag the formula
across..

Because you're dragging horizontally across columns, Excel is coded so
the 6 will not change. If you drag downward it will change (also by
design). So the non-clever, inelegant response is "you can't do that".

However, the clever, elegant workaround is:
1. Copy the =SUM('Wkly stats'!$E6) formula and paste downwards for an
appropriate number of cells so you get the 7, 8, and 9 references.
2. Highlight the formulas with the 7, 8, and 9 references. Perform a
search and replace: search for the = sign and replace with a random
text string such as your initials. This converts the dynamic formulas
to static text strings.
3. Copy the static text strings, highlight the to the right of the
=SUM('Wkly stats'!$E6) formula, then from the menu click Edit Paste
Special, select the Transpose box, and click OK.
4. Within the pasted cells (which are highlighted from your paste)
perform another search and replace, this time replacing your initials
with the = sign. This converts the static text strings back into
dynamic formulas.
5. Make sure your boss knows your doing things in Excel that Excel
can't do, and give yourself the rest of the day off.