How do I change "#VALUE!' or at least stop it from showing?

Question

I've set up an Invoice template in XL. In the template I can a select a "product" in a cell (e.g C1), and the price of the product per unit is automatically shown in another cell (e.g F1). Finally in another cell (e.g cell I1), I can add the amount of product used.

It will then total up to the correct amount in (eg Cell J1): So far so good: all works!

The issue I need to address is if I don't use a product and leave C1 blank, J1 displays "#VALUE".

How can I format J1 to display either the currency symbol, or a blank cell, if there is no input in C1?

(If I change the formula in J1 to use cell B1 instead of C1, J1 displays the currency symbol only (not "#VALUE"!)

(Didn't have this issue with XL from Office 10: I merely copied the template over!)

Many Thanks

jitinm · Answer

I think you should check the below error definition site because as per microsoft #value happens when : "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."

https://support.microsoft.com/en-us/office/how-to-correct-a-value-error-15e1b616-fbf2-4147-9c0b-0a11a20e409e

sergeibaklan · Answer

Icemankool&nbsp;
You may hide it with IFERROR(), but since everything works on Excel 2010 and doesn't work on Excel 365 I'd check formulae carefully if they are correct for the modern Excel.

icemankool · Answer

jitinm&nbsp;Yes but No! It always used to be (with 2010), if C1 was left Blank, then the currency symbol only would display (no value) in J1. Not anymore, and this is what I am trying to achieve.Just to reiterate, the issue doesn't exist in&nbsp; there is "input" at C1.I would think there would be&nbsp; a conditional formatting resolution for this?&nbsp;At the moment I am on a "work around" by using B1 as a cell rather than C1 (B1 has text!).&nbsp; I don't obtain any "#VALUE!" messages in J1 when I do this, although one would think there would be!.

icemankool · Answer

Thanks Sergei: The spreadsheet actually uses different cells to those I mentioned (though t it would be easier to explain). The Actual is this: Formulae in Cell J34 is =F34*I34.
Where I34 is the amount of product used and F34 is the price per unit of product.
F34 uses an "IF" based on C34 (=IFC34= "SAND", "£10", IF C34 ="CEMENT", "£5.00" etc) so if I put sand in C34 and 0.2 in I34, J34 will correctly show £20.
The issue is only occurs when I leave C34 blank (F34 is "automatic because of the IF function).
When I input a product into C34, but omit a quantity in I34, I just get the currency symbol in J34. This is what I am trying to achieve when C34 is left blank.

jitinm · Answer

I think the error is because of the symbol typed in the formula. Maybe you should use it under the format instead of the formula.

Forum Discussion

How do I change "#VALUE!' or at least stop it from showing?

Share

Resources