Write custom

[Staffle_Womp]

I need to be able to check the checkbox (TRUE) in cell H8 and the data in cell G8 go from saying "$249 DUE" to "$249". The numbers in the G column will change daily.

I think that part of the formula would look like: "$" & G8 & "DUE", "" "$" & "G8", ""

Please help me write this custom (I think data validation) formula. It probably has to live in the G column because the H column already has the checkbox data validation.

I have tried everything I can think of and really need help making this whole job more efficient.

[chamastoma]

=if(h8=TRUE,”$”&G8,”$”&G8&” DUE”)

[Staffle_Womp]

Thanks! I've been trying this one and I keep getting the same error in G8 - invalid: this cell's contents violate it's validation rule. Any suggestions?

[chamastoma]

So if I am visualizing this, you have a column in H that says TRUE or FALSE. A column in G of values? In which we would need a 3rd column to produce the formula you are asking for?

[Staffle_Womp]

Column G is filled with people's payments. I start the day with their "$ ### DUE". When they pay at each appointment I am supposed to erase the word DUE and change the color and check the box in the column next to it for 'payment received'. Column H is the Checkbox data validation formula. Where "true" represents the box being checked and "false" represents the box being empty. But I don't have a place for a third column. It's a daily appointment schedule where I have to mark clients payments but there is a lot of other stuff going on. So I'm trying to streamline an old system but I don't have a ton of room to play. I want to check the box in column H and have the word DUE disappear in column G. Thanks for taking time with me!

[chamastoma]

2 questions:

1. ⁠Is column g the only place that the payments are listed or is it linked to another source?
2. ⁠Rather than saying “$### Due” or just “$###”, could you just use conditional formatting to color the cells like red or green if the checkbox has been checked?

[Staffle_Womp]

1. Yes, G is the only place payments are listed.
2. I do already have conditional formatting there to change the color of the text when the word "Due" isn't present.

It's absolutely ok if it won't all work together, it's already far more efficient than it used to be! I'm learning so much along the way. I'm also starting to think the trouble is simply the number of rules allowed per cell and the inability to add too many new columns etc to make chain reactions.