Hi.

All you need to do is to delete your dollar signs. $

'If filling horizontally
='01-07 Sep 2014'!S$8

'If filling vertically
='01-07 Sep 2014'!$S8