anyone good at excell?

[diymirage]

i have a spreadsheet in excel im working on, and there is a column which is populated by a formula
the idea is all my monthly expenses are in column A , there is a formula in column E that tells me the remaining expenses in A this month (after that date) and it is based on a figured in column D so i can look at my bank account, punch the balance in on column D (where the number equals the date, so today, i would use D20, and then E20 would tell me how much money i can spend at summit

the formula for E20 is =(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)

NOW, this leaves me with a long list of negative numbers, all down column E, because column D is generally empty

is there a way to hide the cells in column E unless the corresponding cell in column E is populated?

in other words, i only want to see E20 if i put something in D20

 

[GoodysGotaCuda]

Try

=if(D20>0,(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),””)

=if D20 isn’t blank then show me the calculation, else show me nothing “”.

 

[diymirage]

Try

=if(D20>0,(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),””)

=if D20 isn’t blank then show me the calculation, else show me nothing “”.

if i do that, the formula still works (i still get the right amount if i put something in D20, but when D20 is empty, then E20 reads #NAME?

if i change it from =if(D20>0,(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),””) to =if(D20>0,(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),)) (which is replacing the "") at the end with )) it becomes #FALSE but it will still work

so it still puts something in there, rather then a - sign or nothing

 

[diymirage]

ok, i figured it out
i have to do this for each cell seperately but i select each cell, then go to home, and select "conditonal formatting"
enter a new rule, only cells with a certain value and then i say value eqaul to
at this point i drop in the formula (for E20, it was (D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31) )
and i replace the cell name with a 0 (so now it becomes =(0-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)
then under format i hit font color white, and boom, bob's yer uncle

while i was at it, i also dropped in a conditional format that all numbers below 0 are red, and above are green

 

[Cal Tonsley]

ok, i figured it out
i have to do this for each cell seperately but i select each cell, then go to home, and select "conditonal formatting"
enter a new rule, only cells with a certain value and then i say value eqaul to
at this point i drop in the formula (for E20, it was (D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31) )
and i replace the cell name with a 0 (so now it becomes =(0-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)
then under format i hit font color white, and boom, bob's yer uncle

while i was at it, i also dropped in a conditional format that all numbers below 0 are red, and above are green

Glad to hear you worked around it, the way to have fixed it in your formula was to use IFERROR in your formula

IFERROR((D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),””)

 

[Dusterdenovo]

IIRC you can use the Format Painter to copy the Conditional Formatting to other cells after you've done it once.

 

[diymirage]

Glad to hear you worked around it, the way to have fixed it in your formula was to use IFERROR in your formula

IFERROR((D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31),””)

ill try that, just for fun

IIRC you can use the Format Painter to copy the Conditional Formatting to other cells after you've done it once.

that would have been nice to know, but each cell has a unique formula anyway

 

[Dusterdenovo]

Formatting doesnt affect cell formulas if thats what you mean.

 

[diymirage]

Formatting doesnt affect cell formulas if thats what you mean.

no, what i mean is, the formula for E20 is =(D20-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)
the code to make it "disappear" with the white font is "cell value is equal to =(0-A21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)"

the formula for E21 is =(D21-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)
and the code to hide that one is "cell value is equal to =(0-A22-A23-A24-A25-A26-A27-A28-A29-A30-A31)"

so each cell requires its own formula to hide it
at least, i think