I have this formula in cell C5 which I am trying to copy down to C150
But in diong so it only places the same value as Cell C5 in all the
cells.
I have to individually do a CTRL SHIFT & ENTER for each cell to update
the value.

=IF(ROWS($B$5:B5)$IQ$2,"",INDEX('T&C'!$K$3:$BT$3, MOD(SMALL(IF('T&C'!$K
$5:$BT$138<$IQ$1,(ROW('T&C'!$K$5:$BT$138)-ROW('T&C'!$K$5)+1)*10^5+COLUMN
('T&C'!$K$5:$BT$138)-COLUMN('T&C'!$K$5)+1),ROWS($B$5:B5)),10^5)))

On Fri, 6 Apr 2012 11:32:15 +0100, Yunus wrote:


I have this formula in cell C5 which I am trying to copy down to C150
But in diong so it only places the same value as Cell C5 in all the
cells.
I have to individually do a CTRL SHIFT & ENTER for each cell to update
the value.

=IF(ROWS($B$5:B5)$IQ$2,"",INDEX('T&C'!$K$3:$BT$3 ,MOD(SMALL(IF('T&C'!$K
$5:$BT$138<$IQ$1,(ROW('T&C'!$K$5:$BT$138)-ROW('T&C'!$K$5)+1)*10^5+COLUMN
('T&C'!$K$5:$BT$138)-COLUMN('T&C'!$K$5)+1),ROWS($B$5:B5)),10^5)))

FIRST Enter it in just C5 with <ctrl<shift<enter.
THEN fill down to C150. You can do this in a variety of ways:
Select C5:C150. Then <ctrl<d
With C5 selected, float the cursor over the lower right corner until it changes to a cross hair. Then left click and drag down to C150.

In article ,
says...

On Fri, 6 Apr 2012 11:32:15 +0100, Yunus wrote:


I have this formula in cell C5 which I am trying to copy down to C150
But in diong so it only places the same value as Cell C5 in all the
cells.
I have to individually do a CTRL SHIFT & ENTER for each cell to update
the value.

=IF(ROWS($B$5:B5)$IQ$2,"",INDEX('T&C'!$K$3:$BT$3 ,MOD(SMALL(IF('T&C'!$K
$5:$BT$138<$IQ$1,(ROW('T&C'!$K$5:$BT$138)-ROW('T&C'!$K$5)+1)*10^5+COLUMN
('T&C'!$K$5:$BT$138)-COLUMN('T&C'!$K$5)+1),ROWS($B$5:B5)),10^5)))

FIRST Enter it in just C5 with <ctrl<shift<enter.
THEN fill down to C150. You can do this in a variety of ways:
Select C5:C150. Then <ctrl<d
With C5 selected, float the cursor over the lower right corner until it changes to a cross hair. Then left click and drag down to C150.

Subject: CTRL SHIFT ENTER
From: Yunus

Thanks Ron,
But I just realised the cause. Somehow the Automatic Calculation Update
was turned OFF.

I can only assume it must have happened when I re-opened the File and
the Macros got Disabled and also turning off the Automatic Update
Calculation Option.

So infuriating I spent all morning on it.