HI, I've been using this formula for some time and I can just about fudge the way it works.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Courses&"'!b:b"),$B4,INDIRECT("'"&Courses&"'!"&SUBSTITUTE(SUBSTITUTE(REPT(ADDRESS(1,COLUMNS($A:G),4),2),1,":",1),1,""))))
I understand its looking at the named range...