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

<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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Latest Figure [/TD]
[TD="align: center"]Current Date[/TD]
[TD="align: center"]Latest Date[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.000[/TD]
[TD="align: center"]2.000[/TD]
[TD="align: center"]3.000[/TD]
[TD="align: center"]4.000[/TD]
[TD="align: center"]5.000[/TD]
[TD="align: center"]6.000[/TD]
[TD="align: center"]7.000[/TD]
[TD="align: center"]8.000[/TD]
[TD="align: center"]9.000[/TD]
[TD="align: center"]10.000[/TD]
[TD="align: center"]11.000[/TD]
[TD="align: center"]12.000[/TD]
[TD="align: center"]13.000[/TD]
[TD="align: center"]14.000[/TD]
[TD="align: center"]15.000[/TD]
[TD="align: center"]16.000[/TD]
[TD="align: center"]17.000[/TD]
[TD="align: center"]18.000[/TD]
[TD="align: center"]19.000[/TD]
[TD="align: center"]20.000[/TD]
[TD="align: center"]21.000[/TD]
[TD="align: center"]22.000[/TD]
[TD="align: center"]23.000[/TD]
[TD="align: center"]24.000[/TD]
[TD="align: center"]25.000[/TD]
[TD="align: center"]26.000[/TD]
[TD="align: center"]27.000[/TD]
[TD="align: center"]28.000[/TD]
[TD="align: center"]29.000[/TD]
[TD="align: center"]30.000[/TD]
[TD="align: center"]31.000[/TD]
[TD="align: center"]32.000[/TD]
[TD="align: center"]33.000[/TD]
[TD="align: center"]34.000[/TD]
[TD="align: center"]35.000[/TD]
[TD="align: center"]36.000[/TD]
[TD="align: center"]37.000[/TD]
[TD="align: center"]38.000[/TD]
[TD="align: center"]39.000[/TD]
[TD="align: center"]40.000[/TD]
[TD="align: center"]41.000[/TD]
[TD="align: center"]42.000[/TD]
[TD="align: center"]43.000[/TD]
[TD="align: center"]44.000[/TD]
[TD="align: center"]45.000[/TD]
[TD="align: center"]46.000[/TD]
[TD="align: center"]47.000[/TD]
[TD="align: center"]48.000[/TD]
[TD="align: center"]49.000[/TD]
[TD="align: center"]50.000[/TD]
[TD="align: center"]51.000[/TD]
[TD="align: center"]52.000[/TD]
[TD="align: center"]53.000[/TD]
[TD="align: center"]54.000[/TD]
[TD="align: center"]55.000[/TD]
[TD="align: center"]56.000[/TD]
[TD="align: center"]57.000[/TD]
[TD="align: center"]58.000[/TD]
[TD="align: center"]59.000[/TD]
[TD="align: center"]60.000[/TD]
[TD="align: center"]61.000[/TD]
[TD="align: center"]Jan-17[/TD]
[TD="align: center"]Jan-18[/TD]

</tbody>

Sheet1​

[TABLE="width: 4043"]
<tbody>[TR]
[TD] 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

<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>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Sheet1![/TD]
[TD="align: center"]2.000[/TD]
[TD="align: center"]3.000[/TD]
[TD="align: center"]4.000[/TD]
[TD="align: center"]5.000[/TD]
[TD="align: center"]6.000[/TD]
[TD="align: center"]7.000[/TD]
[TD="align: center"]8.000[/TD]
[TD="align: center"]9.000[/TD]
[TD="align: center"]10.000[/TD]
[TD="align: center"]11.000[/TD]
[TD="align: center"]12.000[/TD]
[TD="align: center"]13.000[/TD]
[TD="align: center"]14.000[/TD]
[TD="align: center"]15.000[/TD]
[TD="align: center"]16.000[/TD]
[TD="align: center"]17.000[/TD]
[TD="align: center"]18.000[/TD]
[TD="align: center"]19.000[/TD]
[TD="align: center"]20.000[/TD]
[TD="align: center"]21.000[/TD]
[TD="align: center"]22.000[/TD]
[TD="align: center"]23.000[/TD]
[TD="align: center"]24.000[/TD]
[TD="align: center"]25.000[/TD]
[TD="align: center"]26.000[/TD]
[TD="align: center"]27.000[/TD]
[TD="align: center"]28.000[/TD]
[TD="align: center"]29.000[/TD]
[TD="align: center"]30.000[/TD]
[TD="align: center"]31.000[/TD]
[TD="align: center"]32.000[/TD]
[TD="align: center"]33.000[/TD]
[TD="align: center"]34.000[/TD]
[TD="align: center"]35.000[/TD]
[TD="align: center"]36.000[/TD]
[TD="align: center"]37.000[/TD]
[TD="align: center"]38.000[/TD]
[TD="align: center"]39.000[/TD]
[TD="align: center"]40.000[/TD]
[TD="align: center"]41.000[/TD]
[TD="align: center"]42.000[/TD]
[TD="align: center"]43.000[/TD]
[TD="align: center"]44.000[/TD]
[TD="align: center"]45.000[/TD]
[TD="align: center"]46.000[/TD]
[TD="align: center"]47.000[/TD]
[TD="align: center"]48.000[/TD]
[TD="align: center"]49.000[/TD]
[TD="align: center"]50.000[/TD]
[TD="align: center"]51.000[/TD]
[TD="align: center"]52.000[/TD]
[TD="align: center"]53.000[/TD]
[TD="align: center"]54.000[/TD]
[TD="align: center"]55.000[/TD]
[TD="align: center"]56.000[/TD]
[TD="align: center"]57.000[/TD]
[TD="align: center"]58.000[/TD]
[TD="align: center"]59.000[/TD]
[TD="align: center"]60.000[/TD]
[TD="align: center"]61.000[/TD]

</tbody>

Sheet2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?