Indirect function alternatives.

[Eamonn100]

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
1																																																														Latest Figure	Current Date	Latest Date
2		1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000	13.000	14.000	15.000	16.000	17.000	18.000	19.000	20.000	21.000	22.000	23.000	24.000	25.000	26.000	27.000	28.000	29.000	30.000	31.000	32.000	33.000	34.000	35.000	36.000	37.000	38.000	39.000	40.000	41.000	42.000	43.000	44.000	45.000	46.000	47.000	48.000	49.000	50.000	51.000	52.000	53.000	54.000	55.000	56.000	57.000	58.000	59.000	60.000	61.000	Jan-17	Jan-18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2013/2016 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI 1 2 Sheet1! 2.000 3.000 4.000 5.000 6.000 7.000 8.000 9.000 10.000 11.000 12.000 13.000 14.000 15.000 16.000 17.000 18.000 19.000 20.000 21.000 22.000 23.000 24.000 25.000 26.000 27.000 28.000 29.000 30.000 31.000 32.000 33.000 34.000 35.000 36.000 37.000 38.000 39.000 40.000 41.000 42.000 43.000 44.000 45.000 46.000 47.000 48.000 49.000 50.000 51.000 52.000 53.000 54.000 55.000 56.000 57.000 58.000 59.000 60.000 61.000 <colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead> </thead><tbody> </tbody> Sheet2​

<tbody>
</tbody>

 

[Eamonn100]

Above is a 60 cell example of what I'm doing. Sheet1 is my main sheet and I use Sheet2 as a updating sheet. I'm using the INDIRECT function so that I can switch between different main sheets. Basically I'm trying to incert a new figure and move all the others one space to the left when a condition is met. Starting in B2 Sheet2 is this formula. It is dragged over and down.

=IFERROR(IF(DATEDIF(INDIRECT($A$2&CELL("address",$BK2)),INDIRECT($A$2&CELL("address",$BL2)),"M")>=12,INDIRECT($A$2&CELL("address",C2)),INDIRECT($A$2&CELL("address",B2))),INDIRECT($A$2&CELL("address",B2)))

The formula calculates the difference between 2 dates on Sheet1, (cells BK2 and BL2). If the difference between the two dates is 12 months or greater it returns the next cell to the right. If an error or less than 12 months it returns the current figure, (stays the same). Because of the large number of INDIRECT function (20 million) it calculates extremely slow. Can anyone think of a way of updating and moving a group of cells to the left when certain conditions are met?