Hi there,
I wondered if you could assist with the following:
Value
In cell G4, I have a formula which automatically determines & returns a monetary amount, eg £10.00, based upon hours calculated in another cell
Type
In cell H4, I wish to manually enter either “cash” or “bacs”
Attended
In cell I4, I wish to manually enter either “yes” or “no”
Due BACS
In cell J4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “bacs” AND I4 being “yes”. Else, the cell would remain blank
Due Cash
In cell K4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “cash” AND I4 being “yes”. Else, the cell would remain blank
Paid BACS
In cell L4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank
Paid Cash
In cell M4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank
All of the above works well.
However, in cell O4, I require a formula that allows O4 to remain blank unless either J4 or K4 comprise an amount. If J4 or K4 comprise an amount, I require O4 to show a negative amount, eg -£10.00, to identify that an amount is due, but remains unpaid.
Then, once an amount is manually entered into either L4 or M4 (assuming £10.00 is entered), cell O4 should then show £0.00 (or calculate how much remains underpaid/overpaid if a different amount is entered in L4 or M4).
Any help would be appreciated.
Kind regards,
Ketan
I wondered if you could assist with the following:
Value
In cell G4, I have a formula which automatically determines & returns a monetary amount, eg £10.00, based upon hours calculated in another cell
Type
In cell H4, I wish to manually enter either “cash” or “bacs”
Attended
In cell I4, I wish to manually enter either “yes” or “no”
Due BACS
In cell J4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “bacs” AND I4 being “yes”. Else, the cell would remain blank
Due Cash
In cell K4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “cash” AND I4 being “yes”. Else, the cell would remain blank
Paid BACS
In cell L4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank
Paid Cash
In cell M4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank
All of the above works well.
However, in cell O4, I require a formula that allows O4 to remain blank unless either J4 or K4 comprise an amount. If J4 or K4 comprise an amount, I require O4 to show a negative amount, eg -£10.00, to identify that an amount is due, but remains unpaid.
Then, once an amount is manually entered into either L4 or M4 (assuming £10.00 is entered), cell O4 should then show £0.00 (or calculate how much remains underpaid/overpaid if a different amount is entered in L4 or M4).
Any help would be appreciated.
Kind regards,
Ketan
