Nov 16 2020 06:16 AM
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
Nov 16 2020 08:43 AM - edited Nov 16 2020 08:51 AM
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.