Jun 04 2022 04:55 AM
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
Jun 04 2022 05:06 AM
Jun 04 2022 06:28 AM
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.
Jun 04 2022 06:28 AM
@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!.
Jun 04 2022 06:44 AM
Jun 04 2022 06:51 AM
Jun 04 2022 06:54 AM
Jun 04 2022 06:56 AM
Jun 04 2022 06:59 AM
Jun 04 2022 07:00 AM
Jun 04 2022 07:05 AM
Price in formula shall be the number, not text. Result is formatted as pounds applying Accounting format.
Please check attached file.