Hi Fiona,
Thanks for your question. Would you give me full edit access to this sheet please, so I can have a fiddle?
I suspect there may be a couple of options for solving this, depending on what your boss requires/etc.
eg.
- Could you use a ‘Remaining’ column to show how much is left to invoice rather than your current set up? I wonder if this might be simpler, but am not sure if it would give you exactly what you want.
- It might be a case of nesting this query inside an IF statement, so that if the value inside the ACTUAL column equals or is greater/lesser than the BUDGET it shows accordingly.
- What about showing a percentage of budget spent or remaining?
Another option, which might be useful to you, is to apply conditional formatting using a custom formula. This might be exactly what you want.
Let me know what you think – plus give me edit access if you can 😉
Rob
PS here is the Google help article about conditional formatting with a couple of basic examples of formulas to apply conditional formatting.
