Cell color as condition openoffice
Solved
chocosbar
-
Errius -
Errius -
Good evening,
please forgive me in advance if this question has already been asked, but I couldn't find an answer.
Unfortunately, I don't know anything about OpenOffice spreadsheets, but:
I would like to know if the fill color of a cell can be a condition in a formula.
Let me explain:
I have made a relatively simple spreadsheet, like a calendar, in which I have noted, next to each time slot, the amount to be paid by the patient.
I fill it in red if the patient has not paid (they will pay at the next consultation), and in green if they have paid.
To the right of my table, I have a standard sum formula for the end of the day, and another for the cumulative daily receipts. Fictitious receipts, because, obviously, some patients have not yet paid.
I would like to make a formula that tells me, next to my theoretical results, something like:
the unpaid total (i.e., the total of the red cells) is XX euros.
the paid total (this part is easier: theoretical total minus the total of red cells).
If such a formula doesn’t exist, it’s unfortunate, but I don’t see how to do it.
Would you have any alternatives?
Even though I must admit that I really have a preference for my simple color-coded system... I am a visual person, so if the formula exists, I would like it.
Thank you in advance!
Configuration: Mac OS X / Safari 533.20.27
please forgive me in advance if this question has already been asked, but I couldn't find an answer.
Unfortunately, I don't know anything about OpenOffice spreadsheets, but:
I would like to know if the fill color of a cell can be a condition in a formula.
Let me explain:
I have made a relatively simple spreadsheet, like a calendar, in which I have noted, next to each time slot, the amount to be paid by the patient.
I fill it in red if the patient has not paid (they will pay at the next consultation), and in green if they have paid.
To the right of my table, I have a standard sum formula for the end of the day, and another for the cumulative daily receipts. Fictitious receipts, because, obviously, some patients have not yet paid.
I would like to make a formula that tells me, next to my theoretical results, something like:
the unpaid total (i.e., the total of the red cells) is XX euros.
the paid total (this part is easier: theoretical total minus the total of red cells).
If such a formula doesn’t exist, it’s unfortunate, but I don’t see how to do it.
Would you have any alternatives?
Even though I must admit that I really have a preference for my simple color-coded system... I am a visual person, so if the formula exists, I would like it.
Thank you in advance!
Configuration: Mac OS X / Safari 533.20.27
5 answers
Hello
Simply put, use a Paid column with a tracking code, for example OK, which will easily allow you to count the paid (OK) and unpaid amounts!
=SUMIF(code field; "OK"; amount field)
with validation in the code cells, it's even more convenient than changing colors!
If you're attached to your red and green, you can use conditional formatting that will turn the amount and tracking cells red as soon as an amount is entered in the amount cell, and green when the amount is marked as paid by OK. With an OK in the same green as the fill, it won't even be noticeable!
Cheers
Let's ask ourselves if we're not the only ones who understand what we're explaining?
Simply put, use a Paid column with a tracking code, for example OK, which will easily allow you to count the paid (OK) and unpaid amounts!
=SUMIF(code field; "OK"; amount field)
with validation in the code cells, it's even more convenient than changing colors!
If you're attached to your red and green, you can use conditional formatting that will turn the amount and tracking cells red as soon as an amount is entered in the amount cell, and green when the amount is marked as paid by OK. With an OK in the same green as the fill, it won't even be noticeable!
Cheers
Let's ask ourselves if we're not the only ones who understand what we're explaining?
