Forum Discussion
Recalculate sheets
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: =I...
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.