Hello! I’m trying to set a formula where only the next empty cell is filled with a number, but I haven’t been able to figure it out.
This is the context. The project manager checks the “Pay retainage” column when it’s time to pay the retainage to a subcontractor. I would like that when this happens, the next “Pay#xx” column that is empty fills out with the retainage amount (which is 10% of the total contract). So far, I’ve managed to make all the empty columns show the retainage amount, but the next empty (to the right) needs to be filled out. The others should remain empty. See picture (it should only show $200 in column Pay3, anything after that should be empty.
Please help!
This is what I’ve tried:
IF(AND({About Retainage}, {% Done1}=""), 0.10 * {Total Contract}, {% Done1}*{Total after retainage})