L
Legacy 512884
Guest
I am a beginner so I'm not sure what I'm trying to do is even possible. I'm setting up a simple accounting table.
Here are my column headings:
Amount | Status | Date Sent | Date Received | Amount Received
The Status cells each have a dropdown list to select either "open", "sent", or "paid". My need is that if "sent" is selected from the dropdown, then the neighboring Date Sent cell displays the date in red font (or red fill with white font).
If "paid" is selected, the paid date needs to appear in the Date Received column in green font (or green fill with white font).
If "open" is selected, it kind of "resets" everything. in the row
What I'm running into is that if "paid" is selected, then the dropdown is no longer set to "sent" so, logically, the Sent Date goes away.
So with "paid" selected, I need the sent date to remain, and stay red AND have the Date Received date appear and be green.
Along with the 'paid" status, I need the Amount Received cell to be populated with the Amount number from the far left Amount Received column, and be in green.
If you try the different dropdown list items in the Status column, you'll see where I am to date.
I've gotten each of these needs accomplished individually but not collectively in an overarching solution. Any help would be greatly appreciated.
I hope what is shown here below is the "mini-sheet" ?!?!
Here are my column headings:
Amount | Status | Date Sent | Date Received | Amount Received
The Status cells each have a dropdown list to select either "open", "sent", or "paid". My need is that if "sent" is selected from the dropdown, then the neighboring Date Sent cell displays the date in red font (or red fill with white font).
If "paid" is selected, the paid date needs to appear in the Date Received column in green font (or green fill with white font).
If "open" is selected, it kind of "resets" everything. in the row
What I'm running into is that if "paid" is selected, then the dropdown is no longer set to "sent" so, logically, the Sent Date goes away.
So with "paid" selected, I need the sent date to remain, and stay red AND have the Date Received date appear and be green.
Along with the 'paid" status, I need the Amount Received cell to be populated with the Amount number from the far left Amount Received column, and be in green.
If you try the different dropdown list items in the Status column, you'll see where I am to date.
I've gotten each of these needs accomplished individually but not collectively in an overarching solution. Any help would be greatly appreciated.
I hope what is shown here below is the "mini-sheet" ?!?!
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4 | D4 | =IF(C4="sent",TODAY(),"") |
| E4 | E4 | =IF(C4="paid",TODAY(),"") |
| F4 | F4 | =IF(C4="paid",B4,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| D4 | Expression | =IF(C4="sent",) | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| C4 | List | open,sent,paid |
