Hey,
I have array formulas that were working fine (with a macro that I was using to update the file they are in) and now they return #VALUE! errors.
Before this started happening I was experiencing the file crashing when opening, so I moved it to a local folder from a share drive, added a new sheet to file and then moved it back into the share drive. Now, when I run the macro and update the file, all results from the array formulas are #VALUE!.
I've checked the auto/manual calculation option and tried with and without the iterative calcs enabled. Also checked to make sure the formats are not an issue (they appear to be the same as before the file was moved). When going through the "Evaluate Formula" function I get a message in the box saying it's a circular reference.
The formula is:
{=INDEX('Sheet 1'!$B$1:$C$30000,SMALL(IF(Sheet 1'!$B$1:$B$30000=B$105,ROW('Sheet 1'!$B$1:$B$30000)),ROW(1:1)),2)}
When the formula was working it would run down a list of data and find the smallest value based on array constant B105. The formula in the cell underneath this would find the second smallest value based on the same array constant.
Thanks for the help!
I have array formulas that were working fine (with a macro that I was using to update the file they are in) and now they return #VALUE! errors.
Before this started happening I was experiencing the file crashing when opening, so I moved it to a local folder from a share drive, added a new sheet to file and then moved it back into the share drive. Now, when I run the macro and update the file, all results from the array formulas are #VALUE!.
I've checked the auto/manual calculation option and tried with and without the iterative calcs enabled. Also checked to make sure the formats are not an issue (they appear to be the same as before the file was moved). When going through the "Evaluate Formula" function I get a message in the box saying it's a circular reference.
The formula is:
{=INDEX('Sheet 1'!$B$1:$C$30000,SMALL(IF(Sheet 1'!$B$1:$B$30000=B$105,ROW('Sheet 1'!$B$1:$B$30000)),ROW(1:1)),2)}
When the formula was working it would run down a list of data and find the smallest value based on array constant B105. The formula in the cell underneath this would find the second smallest value based on the same array constant.
Thanks for the help!
