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

[Icemankool]

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

[#2]

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-0a11...

[#3]

@Icemankool 

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.

[#4]

@jitinm 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  there is "input" at C1.

I would think there would be  a conditional formatting resolution for this?

 At the moment I am on a "work around" by using B1 as a cell rather than C1 (B1 has text!).  I don't obtain any "#VALUE!" messages in J1 when I do this, although one would think there would be!.

[#5]

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.

[#6]

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.

[#7]

Which symbol? (Please see my Reply to Sergei)

[#8]

Pound "£" :- (=IFC34= "SAND", "£10", IF C34 ="CEMENT", "£5.00" etc) . I tried to replicate it in the excel and it is showing #value error. When I removed it worked just fine. Instead you should use currency symbol in the format cell

[#9]

@jitinm 

[#10]

The issue seems to be with the "if" list function.
If I merely type figures into f34 and i34, then it doesn't matter which cell is left blank, there is no "#value!" notification in J34, and J34 will only display the currency symbol. For this test, F34 has not been "IF'd"!

[#11]

@Icemankool 

Price in formula shall be the number, not text. Result is formatted as pounds applying Accounting format.

Please check attached file.