= Trend (Week Numbers compared to Month Numbers

[PCRIDE]

Doing a simple Trend as explained here
TREND function - Excel - Office.com

I was able to do this using 3 month trending and using Month Numbers.

Now I have expanded my sheet for 17 weeks, using WeekNumbers and everything is the same just more columns

Got any ideas? Yes I did Shift CTRL Enter for the array but it worked without doing that as well when I was trending 3 months.

 

[PCRIDE]

=TREND(D4:T4,D1:T1,AB1)

D4:T4 is a % Number

D1:T1 is a week number but extracted from the words "Week 50, 2013" using =IF(MID(T3,7,1)=",","0"&MID(T3,6,1),MID(T3,6,2))
I needed to put the If(Mid check because the format of the data for the new year had a comma that I needed to get rid of.
HOWEVER, just testing this with normal numbers, no formula, I still get #Value for the result so its not a formula issue.
Even testing it using Week 1 - 3 vs Month 1-3, still get the error

 

[PCRIDE]

Ok so further investigation it something with the formatting of the Week Numbers.

I copied and pasted values and tried it in another section of the sheet. Still got the same error. When I typed the same number (or different number) over the top of the paste values, the formula started to work, which is strange because it works on the other sheet with Months and the same formula format. I actually copied the monthly report to make the weekly report.

What format is happening that is causing this? I need it to work with the Week Number formulas so as the weeks change, the report updates.