Conditional Formatting

Home › Forums › Google Sheets & Forms › Conditional Formatting

Tagged: , ,

Viewing 5 reply threads
  • Author
    Posts
    • July 18, 2017 at 4:14 pm #2614
      Fiona Bellinger
      Participant
      Points: 51

      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

    • July 18, 2017 at 6:21 pm #2616
      Rob Clarke
      Keymaster

      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, 4 months ago by Rob Clarke.
    • July 19, 2017 at 10:49 am #2619
      Rob Clarke
      Keymaster

      Protected tab in SheetHey 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, 4 months ago by Rob Clarke. Reason: screenshot
    • July 19, 2017 at 12:47 pm #2621
      Fiona Bellinger
      Participant
      Points: 51

      Oops – now unprotected…

      Thanks,

      Fiona

    • July 20, 2017 at 10:37 am #2625
      Rob Clarke
      Keymaster

      Thanks Fiona,

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

    • October 8, 2017 at 7:52 pm #3344
      Emma Riddell
      Participant
      Points: 536

      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
Viewing 5 reply threads
  • You must be logged in to reply to this topic.
Skip to toolbar