Conditional Formatting by position

[alisonpwallis]

Dear All

I have a table showing two levels of information - a course and the components in that course (e.g. exam, coursework). I want to shade the background of the cells depending on which course it is like this:

Header 1	Header 2	Header 3
Course 1	Component 1	Score
Course 1	Component 2	Score
Course 2	Component 1	Score
Course 2	Component 2	Score
Course 2	Component 3	Score

So for example - the two rows with Course 1 in would be blue and the three rows where Course 2 is would be green (and then back to blue if there was a course 3).

I don't know how many components each course might have. The only thing I can do is note that the course has changed. I've tried using 'Above' and 'Below' but I can't get it to work. So I create a new expression which is just the course ID (called [Course]) and then I do:

if(Above([Course])=Below([Course]), RGB(235,100,30), RGB(152,87,174)

This makes everything one colour!

Does anyone have any suggestions for this?

Thanks

Alison

[Anil_Babu_Samineni]

You may try like below for sample

=If([Header 1] = 'Course 1', Red(), If([Header 1] = 'Course 2', Green(), Blue()))

[alisonpwallis]

Hi Anil

Thanks for your reply. The problem is I don't know which courses they will actually be since they will be depending on which tutor has logged in to the system so I can't colour code them all. There are around 800 in total!

I just want them to be shaded differently so that when you see a long list you can see where each new course starts.

Thanks

Alison

[trdandamudi]

See if the below helps:

Go to the dimension and expand the plus sign next to it.

Select Background color and click on Edit.

Now use the below expression:

=If([Header 1]='Course 1',Blue(),If([Header 1]='Course 2',Green(),Blue()))

Apply the same expression for all the dimensions and you will get the result. Hope this helps.,...

[MK_QSL · Accepted solution]

Create an ID field as below..

Load *, AutoNumber([Header 1]) as ID Inline

[

  Header 1, Header 2, Header 3

  Course 1, Component 1, 100

  Course 1, Component 2, 200

  Course 2, Component 1, 100

  Course 2, Component 2, 200

  Course 2, Component 3, 300

  Course 3, Component 1, 100

  Course 4, Component 2, 200

  Course 4, Component 3, 300

];

Now use below expression for background color.

=IF(Even(ID),Red(),Green())

Check enclosed file for sample.

[alisonpwallis]

Thank you very much Manish - that worked perfectly!

Thanks

Alison