Recalculate sheets

[Mizso]

Hi, 

I have the attached excel file. The input sheet contains input data. I would like to separate data to different sheets. I use the following function in every cell on every sheet from A2:
=IFERROR(IF(ISBLANK(INDEX(Input!E$1:E$1000;SMALL(IF(MID(CELL("filename");(FIND("]";CELL("filename"))+1);50)=Input!$A$1:$A$1000;ROW(Input!E$1:E$1000);"");ROW()-1)));"";INDEX(Input!E$1:E$1000;SMALL(IF(MID(CELL("filename");(FIND("]";CELL("filename"))+1);50)=Input!$A$1:$A$1000;ROW(Input!E$1:E$1000);"");ROW()-1))); "")

There is an interesting behaviour, when I change the sheet. The actual sheet do not recalculated. The automatic recalculation is switch on. How can I force the recalculation between sheet switches?

Best Regards,

Miklós

[#2]

@Mizso 

The issue seems that the arrays are all running at once across all tabs instead of independently and the reference to the tab name in real time does not support a dynamic pull since it is part of an array.

The workaround is the create a reference for the INDEX reference. Assuming we have the reference date in cell G1 - this would be the adjusted formula:

=IFERROR(
IF(ISBLANK(
INDEX(Input!A$1:A$1000,SMALL(
IF($G$1=Input!$A$1:$A$1000,ROW(Input!A$1:A$1000),""),ROW()-1))),"",
INDEX(Input!A$1:A$1000,SMALL(
IF($G$1=Input!$A$1:$A$1000,ROW(Input!A$1:A$1000),""),ROW()-1))),"")

You will have to make sure you are selecting ROW 2:50 for each column separately to update the array.