Forum Discussion

Wayne2016's avatar
Wayne2016
Copper Contributor
Sep 16, 2020

Excel replacing formula with "=#VALUE!"

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

excel

4 Replies

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Sep 24, 2020

     

    You are getting #VALUE error because, the formula has to finish with Ctrl+Shift+Enter.

     

    • Select the formula cell, press F2, and press Ctrl+Shift+Enter simultaneously.

     

    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),"")}

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 18, 2020

    Wayne2016 

    Here you can find information

     

    How to fix the #VALUE! error

    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.

    • Wayne2016's avatar
      Wayne2016
      Copper Contributor
      Sep 24, 2020
      Thank you, Nikolino, for your reply and information.

      Regards,
      Wayne
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    Sep 18, 2020

    Wayne2016 

    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

Resources