Forum Discussion

Don_Pepe's avatar
Don_Pepe
Copper Contributor
May 12, 2023
Solved

#VALUE disappears when I delete a random cell or line

Hi,    Sometimes when I open my file, it's full of "#VALUE" and nothing seems wrong.  I tried to recalculate and recalculate manually but doesn't work.    I found out that if I delete a random c...
excel
Formulas and Functions
  • Don_Pepe's avatar
    Don_Pepe
    May 17, 2023
    Hi NikolinoDE,

    Thanks for your help, I found the issue.
    DATEVAL and TEMPSVAL (DATEVALUE & TIMEVALUE in en) induce regional formating error in Excel online.
    I just remove them and now everything is ok.

    Thanks again for your help,

    Don
NikolinoDE's avatar
NikolinoDE
Gold Contributor
May 12, 2023

Don_Pepe 

It's possible that there is an error in the formula or reference in your spreadsheet that is causing the #VALUE error. When you delete a cell, line, or column, Excel may automatically recalculate the remaining cells and fix the error.

You can try the following steps to troubleshoot the issue:

  1. Check for errors in the formula or reference by selecting the cell that is showing the #VALUE error and looking at the formula bar. Make sure all the references are correct and that there are no typos or missing references.

  2. Check for any cells that may be empty or contain text instead of numbers or dates. These can also cause the #VALUE error.

  3. Try using the "Evaluate Formula" feature to step through the formula and see if you can identify the error.

  4. If none of these steps work, try copying the data to a new worksheet or a new Excel file and see if the error persists.

Hopefully, one of these steps will help you identify and fix the issue with the #VALUE error.

  • Don_Pepe's avatar
    Don_Pepe
    Copper Contributor
    May 12, 2023

    NikolinoDE,

     

    Thanks for your help, I cheked these thing and everything were ok. 

    If I recalculate, I still have the error but if I copy the formula then delete the formula then past the formula in the same cell, the error desappears. 

     

    FYI, the formula is (in french sorry):

    =DATEVAL(GAUCHE(TEXTE(AX$4-1;"jj/mm/aaaa") &" "& TEXTE($B6;"hh:mm:ss");10))+TEMPSVAL(DROITE(TEXTE(AX$4-1;"jj/mm/aaaa") &" "& TEXTE($B6;"hh:mm:ss");8))

     

    Best,

     

    Don

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      May 12, 2023
      The updated formula you provided looks correct to me.
      There shouldn't be any errors in this formula, as long as the cell references (AX$4 and $B6) are valid and the data in those cells are correctly formatted as dates and times.
      One thing to note is that the results of this formula may depend on the regional settings of your computer. The date and time formats used in the formula (i.e. "jj/mm/aaaa" and "hh:mm:ss") may need to be adjusted based on the regional settings of your computer to ensure that the formula returns the correct values.
      If you continue to experience issues with this formula, please provide more information on the specific error message you are seeing.


      I hope this helps!
      • Don_Pepe's avatar
        Don_Pepe
        Copper Contributor
        May 17, 2023
        Hi NikolinoDE,

        Thanks for your help, I found the issue.
        DATEVAL and TEMPSVAL (DATEVALUE & TIMEVALUE in en) induce regional formating error in Excel online.
        I just remove them and now everything is ok.

        Thanks again for your help,

        Don

Resources