Home › Forums › Google Sheets & Forms › Conditional Formatting
Tagged: Conditional Formatting, Formula, Google Sheets
- This topic has 4 replies, 2 voices, and was last updated 7 years, 1 month ago by
Rob Clarke.
-
AuthorPosts
-
-
July 18, 2017 at 4:14 pm #2614
Hi Rob,
I’m trying to add a conditional format to my spreadsheet (attached)
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
-
July 18, 2017 at 6:21 pm #2616
Rob Clarke
KeymasterHi 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, 1 month ago by
Rob Clarke.
-
July 19, 2017 at 10:49 am #2619
Rob Clarke
KeymasterHey 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, 1 month ago by
Rob Clarke. Reason: screenshot
-
This reply was modified 7 years, 1 month ago by
-
July 19, 2017 at 12:47 pm #2621
Oops – now unprotected…
Thanks,
Fiona
-
July 20, 2017 at 10:37 am #2625
Rob Clarke
KeymasterThanks Fiona,
Did you have a go at creating a custom formula inside the Conditional Formatting rules?
-
October 8, 2017 at 7:52 pm #3344
Hi Fiona (and Rob)
I may have a solution for this…
…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
-
-
AuthorPosts
- You must be logged in to reply to this topic.