Hi all,
I have a very formula heavy tab on a sheet, 25,500 to be exact, that are dramatically dragging the spead of my workbook down.
I have changed the workbook to Manual calculation, but these tab still seem to runn here and there and a calculation is obviously done as I save, which at times can take 5 mins +.
Each of the cells in the sheet run the following formula :-
{=IF(ISERROR(INDEX(INDIRECT($D$2&"!$D$1:$Q$9999"),SMALL(IF(INDIRECT($D$2&"!$D$1:$Q$9999")=$D$3,ROW(INDIRECT($JD$2&"!$D$1:$Q$9999"))),ROW(INDIRECT($D$2&"!1:1"))),2)),"",INDEX(INDIRECT($D$2&"!$D$1:$Q$9999"),SMALL(IF(INDIRECT($D$2&"!$D$1:$r$9999")=$D$3,ROW(INDIRECT($D$2&"!$D$1:$Q$9999"))),ROW(INDIRECT($D$2&"!1:1"))),2))}
and as you can see, it is an array.
Is there as way of either :-
1. Change the formula so it only runs if there is data in D2 & D3
2. A macro or VBA to stop/start all calculations on the entire sheet
3. A different formula entirely.
The sheet in question is using the INDIRECT to collect a part # & a Sheet title to search & return informtion on the first 15 instances of the part # (see attached image)
Thanks in advance for your help...
Martin
I have a very formula heavy tab on a sheet, 25,500 to be exact, that are dramatically dragging the spead of my workbook down.
I have changed the workbook to Manual calculation, but these tab still seem to runn here and there and a calculation is obviously done as I save, which at times can take 5 mins +.
Each of the cells in the sheet run the following formula :-
{=IF(ISERROR(INDEX(INDIRECT($D$2&"!$D$1:$Q$9999"),SMALL(IF(INDIRECT($D$2&"!$D$1:$Q$9999")=$D$3,ROW(INDIRECT($JD$2&"!$D$1:$Q$9999"))),ROW(INDIRECT($D$2&"!1:1"))),2)),"",INDEX(INDIRECT($D$2&"!$D$1:$Q$9999"),SMALL(IF(INDIRECT($D$2&"!$D$1:$r$9999")=$D$3,ROW(INDIRECT($D$2&"!$D$1:$Q$9999"))),ROW(INDIRECT($D$2&"!1:1"))),2))}
and as you can see, it is an array.
Is there as way of either :-
1. Change the formula so it only runs if there is data in D2 & D3
2. A macro or VBA to stop/start all calculations on the entire sheet
3. A different formula entirely.
The sheet in question is using the INDIRECT to collect a part # & a Sheet title to search & return informtion on the first 15 instances of the part # (see attached image)
Thanks in advance for your help...
Martin