Sep 16 2020 10:10 AM
I'm using the following formula in a spreadsheet:
=IFERROR(INDEX('Data Q2 2020'!$A:$A,SMALL(IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000)),ROW('Data Q2 2020'!$38:$38))-2,1),"")
This formula is working very well as intended, but then Excel replaces the above formula with:
=#VALUE! |
after a certain number of rows are filled with the intended formula (the first formula). The VALUE change does not take place immediately, but after I've saved the spreadsheet. I'm using the first formula for a large (28mg) spreadsheet to report values for over 100 clients in the past five quarters. Again, all formulas are working well and are stable when I save the file. It's only upon opening the file that my original formulas are changed to
=#VALUE! |
Any suggestions?
Thank you,
Wayne
Sep 18 2020 12:44 AM
What do you achive with this formula?
1. This part of the formula is comparing column with a cell value which will return an array of Trues and Falses. And if it is true it will return ROW(...) but if it is false it will return FALSE which is converted to 0 for calculations. These array comparing won't work as expected in an IF formula.
=IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000))
2. This will always return 38
ROW('Data Q2 2020'!$38:$38)
Your SMALL(...)-2 will be converted in to SMALL(0,38)-2 which will then evaluated to -2.
Then INDEX(...,-2) will be evaluated as #VALUE
Sep 18 2020 03:31 AM
Here you can find information
https://exceljet.net/formula/how-to-fix-the-value-error
The third-party products that this article discusses are manufactured oe Informed by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Sep 23 2020 10:49 PM
Sep 24 2020 02:35 AM - edited Sep 24 2020 02:50 AM
You are getting #VALUE error because, the formula has to finish with Ctrl+Shift+Enter.
Since you are trying to extract multiple rows, needs as array ( CSE ) formula.
It will be enclosed with curly brackets or braces { }
{=IFERROR(INDEX('Data Q2 2020'!$A:$A,SMALL(IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000)),ROW('Data Q2 2020'!$38:$38))-2,1),"")}