Conditional Formatting

[Fiona Bellinger]

Hi Rob,

I’m trying to add a conditional format to my spreadsheet (attached)

(https://docs.google.com/spreadsheets/d/1m0_G3XGpMqqNExmivONiJvk3IvX3cBxD7qD5lcJbkHc/edit#gid=1986571470)

As you can see (hopefully), I’m summarising budget data for my boss…

• Tab VADEC has all my information
• Tab Summary is what I need help with…

I’ve used a IF formula of =IF(‘VADEC ‘!K7>0,’VADEC ‘!K7,’VADEC ‘!J7) to fill in the data required, what I would like to do is format the resulting data depending on what cell it can from.

• If my query ‘VADEC ‘!K7>0 is true, the resulting entry of ‘VADEC ‘!K7 should be standard format.
• If the resulting entry is ‘VADEC ‘!J7, then this needs to be highlighted in red and in italics. Basically I want to easily be able to identify if the summary data is a result of an actual invoice which we have paid, or if we are still waiting for the school to invoice us (and thus highlighted).

I’ve managed to (messily) get it to format the way I want for one cell, but i’m sure there is a quicker simpler and more automatic process out there.

Hopefully this is clear. I’ve shown in the black box what I want, but as the original spreadsheet has rows upon rows upon columns of columns of data, I’m not keen on manually doing this!

Thanks,

Fiona

[Rob Clarke]

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.

• This reply was modified 7 years, 5 months ago by Rob Clarke.

[Rob Clarke]

Hey Fiona,

You have protected the VADEC tab in your sheet also – would you give me access to that please?

thanks,

Rob

• This reply was modified 7 years, 5 months ago by Rob Clarke. Reason: screenshot

[Fiona Bellinger]

Oops – now unprotected…

Thanks,

Fiona

[Rob Clarke]

Thanks Fiona,

Did you have a go at creating a custom formula inside the Conditional Formatting rules?

[Emma Riddell]

Hi Fiona (and Rob)

I may have a solution for this…

https://docs.google.com/spreadsheets/d/1pQ8yfMgdK9MitbRvx7b5Kt4Dt_ApaYvq9HKWyfIX2l8/edit#gid=1986571470

…Fiona, this is a copy of your original file. Hope this helps. If it does let me know and I will delete my version…

All the best,

Em Riddell

[Author]

Posts