conditional supression of data in Excel

[fieldtechuk]

I have an Excel spreadsheet, which is generating invoices. It has three columns, Qty, Rate and Total. Total is Qty * Rate, and the Total column has the following formatting: "_(* #,##0.00_);_(* (#,##0.00);_(* ""??_);_(@_)" which means I do not get a number on blank lines. However, and here's my problem: Sometimes I need to show a zero value in the total column - only when the Rate is also zero i.e showing a number of items not charged for. The Total field is protected, and I do not wish to unprotect it. I can't seem to find a way to do this. Anyone have any ideas (willing to use VB script if that helps).

 

[MarcLodge]

Hi,
Just a thought, but could you use conditional formatting to change the font colour to white when you do not wish to see the data?

Marc

 

[fieldtechuk]

I did think of that, but couldn't find a condition which gave me the required results

 

[AnotherHiggins]

why not just account for this in the Total formula?

You currently have something like [COLOR=blue white]=A2*B2[/color]

Just change that to [COLOR=blue white]=if(B2=0,0,if(A2*B2=0,"",A2*B2))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MarcLodge]

How about removing the cell formatting and leaving it as a number, then if the quantity is is column A, the Rate in B and the Total in C, adding the following Conditional Formatting:

Formula Is =AND($A$1=0,$C$1=0)

Format font to White.

Think I've got this right, but you might need to play with it!

Marc

 

[AnotherHiggins]

Oh, and a word of caution about having conditional formatting set the font to white -

If a user has their background set to a color other than white, they will be able to see the font.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ThomasLafferty]

Just a quick note about formulaicly or otherwise suppressing data - if your sheet is ever audited, these are the kinds of things that will make an auditor suspicious, so I would only do this if it was ABSOLUTELY NECESSARY.

Tom

Live once die twice; live twice die once.