This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

How do I change this rule formula for conditional formatting in Excel 2007 to include an OR statement?

This is my current formula (that works well):

=AND(min_rankning_1="bergsäker vinnare";Välj_rankningstabell="Min rankning")

min_rankning_1 is a named cell, referring to tab "LOPP 1" cell CD2567.

Välj_rankningstabell is a named cell, referring to tab "Reducerade system" cell H2.

This rule formula is entered in cell H5 on tab "Reducerade system".

What I need is to include OR, like this (although this syntax probably wouldn't work) so that more named cells are taken into account:

=AND(min_rankning_1 OR fackpressens_rankning_1 OR ATG:s_rankning_1="bergsäker vinnare";Välj_rankningstabell="Min rankning" OR "Fackpressens rankning" OR "ATG:s rankning")

By the way, for above conditional formatting rule, how long can it be? I need it to be much longer than above because I will include many more named cells.

This is my current formula (that works well):

=AND(min_rankning_1="bergs

min_rankning_1 is a named cell, referring to tab "LOPP 1" cell CD2567.

Välj_rankningstabell is a named cell, referring to tab "Reducerade system" cell H2.

This rule formula is entered in cell H5 on tab "Reducerade system".

What I need is to include OR, like this (although this syntax probably wouldn't work) so that more named cells are taken into account:

=AND(min_rankning_1 OR fackpressens_rankning_1 OR ATG:s_rankning_1="bergsäke

By the way, for above conditional formatting rule, how long can it be? I need it to be much longer than above because I will include many more named cells.

```
=AND(OR(min_rankning_1="bergsäker vinnare"; fackpressens_rankning_1="bergsäker vinnare"; ATG:s_rankning_1="bergsäker vinnare");OR(Välj_rankningstabell="Min rankning"; Välj_rankningstabell="Fackpressens rankning"; Välj_rankningstabell="ATG:s rankning"))
```

There may be syntax errors due to the installed language.

=OR(AND(a_1="bergsäker vinnare";Välj_rankningstab

In your original post, you had provided named cell names, but the above formula is using different names. Is that correct?

You also have changed the values in the second part of the AND to just letters, while in your original post had words/phrases. Is that correct?

It does appear that you have a working understanding of using AND and OR together.

Yes, the formula uses different names, but they are still cell names like in the original formula.

Yes, the values in the second part of the AND have been changed to just letters (were words/phrases in my original formula).

The criteria is that only one of these letters can be selected in the drop-down menu I have in cell H5, and for that selection, if the named cell contains "bergsäker vinnare" (same for all) then format according to conditional formatting.

=OR(AND(a_1="bergsäker vinnare";H5="A");

AND(b_1="bergsäker vinnare";H5="B");

AND(c_1="bergsäker vinnare";H5="C");

AND(d_1="bergsäker vinnare";H5="D");

AND(e_1="bergsäker vinnare";H5="E");

AND(f_1="bergsäker vinnare";H5="F");

AND(g_1="bergsäker vinnare";H5="G");

AND(h_1="bergsäker vinnare";H5="H");

AND(i_1="bergsäker vinnare";H5="I");

AND(j_1="bergsäker vinnare";H5="J");

AND(k_1="bergsäker vinnare";H5="K");

AND(l_1="bergsäker vinnare";H5="L");

AND(m_1="bergsäker vinnare";H5="M");

AND(n_1="bergsäker vinnare";H5="N");

AND(o_1="bergsäker vinnare";H5="O");

AND(p_1="bergsäker vinnare";H5="P");

AND(q_1="bergsäker vinnare";H5="Q");

AND(r_1="bergsäker vinnare";H5="R");

AND(s_1="bergsäker vinnare";H5="S");

AND(t_1="bergsäker vinnare";H5="T");

AND(u_1="bergsäker vinnare";H5="U"))

And if instead a different colour was applicable to each value of H5, then these would each be individual conditional formulas ...

(Colour 1)

=AND(a_1="bergsäker vinnare";H5="A")

(Colour 2)

=AND(a_1="bergsäker vinnare";H5="B")

etc.

Hope this gets you closer to the desired result.

Your first assumption is correct, that is the formula I use now: Same colour is applied to cell H5 when "bergsäker vinnare" has been selected for any of the selected options in cell H2 (not H5; the conditional formatting begins from H5 to H19, for the up to 15 horses taking part in the race).

So the selection of the ranking table on another sheet (sheet "LOPP 1") is made in the drop-down menu in H2 on this sheet "Reducerade system". The conditional formatting is applied on cells H5 to H19 on this sheet "Reducerade system".

Then I will have another colour if "stark vinnare" is selected, a third colour if "normal vinnare" (etc.) is selected in H2.

But the problem now is that your proposed solution does not work, only this works for "stark vinnare":

=AND(min_rankning_1="stark

Also the "Applies to" range should be appropriate (just something to confirm).

I'm not sure what you mean with "Applies to" range should be appropriate.

Conditional_Rules_EG.png

In the drop-down menu in H2 on tab A, there are 23 table names for tables on tab B to choose between. The named cells on tab B that are referred to in the conditional formatting rules are in total 11. So what I'm hoping is that, by help with the operators OR and AND, I will be able to use only 11 rules per cell (instead of 143 rules per cell without the operators OR and AND).

So in H5 there can be only one true value (the named cell on tab B has one of the 11 options selected/ and in the drop-down menu in H2 on tab A there is only one table name selected).

=OR(

AND(a_1="bergsäker vinnare";Välj_rankningstab

AND(b_1="bergsäker vinnare";Välj_rankningstab

AND(c_1="bergsäker vinnare";Välj_rankningstab

AND(d_1="bergsäker vinnare";Välj_rankningstab

AND(e_1="bergsäker vinnare";Välj_rankningstab

AND(f_1="bergsäker vinnare";Välj_rankningstab

AND(g_1="bergsäker vinnare";Välj_rankningstab

AND(h_1="bergsäker vinnare";Välj_rankningstab

AND(i_1="bergsäker vinnare";Välj_rankningstab

AND(j_1="bergsäker vinnare";Välj_rankningstab

AND(k_1="bergsäker vinnare";Välj_rankningstab

AND(l_1="bergsäker vinnare";Välj_rankningstab

AND(m_1="bergsäker vinnare";Välj_rankningstab

AND(n_1="bergsäker vinnare";Välj_rankningstab

AND(o_1="bergsäker vinnare";Välj_rankningstab

AND(p_1="bergsäker vinnare";Välj_rankningstab

AND(q_1="bergsäker vinnare";Välj_rankningstab

AND(r_1="bergsäker vinnare";Välj_rankningstab

AND(s_1="bergsäker vinnare";Välj_rankningstab

AND(t_1="bergsäker vinnare";Välj_rankningstab

AND(u_1="bergsäker vinnare";Välj_rankningstab

It should look like the attached image Rule.

Apply a second Rule, with a different colour, and value such as the following:

=OR(

AND(a_1="stark vinnare";Välj_rankningstab

AND(b_1="stark vinnare";Välj_rankningstab

AND(c_1="stark vinnare";Välj_rankningstab

AND(d_1="stark vinnare";Välj_rankningstab

AND(e_1="stark vinnare";Välj_rankningstab

AND(f_1="stark vinnare";Välj_rankningstab

AND(g_1="stark vinnare";Välj_rankningstab

AND(h_1="stark vinnare";Välj_rankningstab

AND(i_1="stark vinnare";Välj_rankningstab

AND(j_1="stark vinnare";Välj_rankningstab

AND(k_1="stark vinnare";Välj_rankningstab

AND(l_1="stark vinnare";Välj_rankningstab

AND(m_1="stark vinnare";Välj_rankningstab

AND(n_1="stark vinnare";Välj_rankningstab

AND(o_1="stark vinnare";Välj_rankningstab

AND(p_1="stark vinnare";Välj_rankningstab

AND(q_1="stark vinnare";Välj_rankningstab

AND(r_1="stark vinnare";Välj_rankningstab

AND(s_1="stark vinnare";Välj_rankningstab

AND(t_1="stark vinnare";Välj_rankningstab

AND(u_1="stark vinnare";Välj_rankningstab

Confirm it works for those two Rules, then repeat for the 11 "values" you mentioned. Remember to select Stop if True and confirm the range.

Conditional formatting, particularly this complex, is tricky, so a debug method is to use a bunch of adjacent cells with parts of the formula to see what is TRUE and FALSE, and therefore whether the cells should light up. For instance if K5 through AE5 (or further) are free they could contain the following parts:

K5: AND(a_1="bergsäker vinnare",Välj_rankningstab

L5: =AND(b_1="bergsäker vinnare",Välj_rankningstab

M5: =AND(c_1="bergsäker vinnare",Välj_rankningstab

N5: =AND(d_1="bergsäker vinnare",Välj_rankningstab

O5: =AND(e_1="bergsäker vinnare",Välj_rankningstab

P5: =AND(f_1="bergsäker vinnare",Välj_rankningstab

Q5: =AND(g_1="bergsäker vinnare",Välj_rankningstab

R5: =AND(h_1="bergsäker vinnare",Välj_rankningstab

S5: =AND(i_1="bergsäker vinnare",Välj_rankningstab

T5: =AND(j_1="bergsäker vinnare",Välj_rankningstab

U5: =AND(k_1="bergsäker vinnare",Välj_rankningstab

V5: =AND(l_1="bergsäker vinnare",Välj_rankningstab

W5: =AND(m_1="bergsäker vinnare",Välj_rankningstab

X5: =AND(n_1="bergsäker vinnare",Välj_rankningstab

Y5: =AND(o_1="bergsäker vinnare",Välj_rankningstab

Z5: =AND(p_1="bergsäker vinnare",Välj_rankningstab

AA5: =AND(q_1="bergsäker vinnare",Välj_rankningstab

AB5: =AND(r_1="bergsäker vinnare",Välj_rankningstab

AC5: =AND(s_1="bergsäker vinnare",Välj_rankningstab

AD5: =AND(t_1="bergsäker vinnare",Välj_rankningstab

AE5: =AND(u_1="bergsäker vinnare",Välj_rankningstab

Confirm the TRUE/FALSE values as expected. And do the same for the next rule set (perhaps with "stark vinnare" in row 6). This will let you see what the conditional rules are reacting to.

If I still haven't quite met your intentions, this could be a useful method to figure it out through experimenting.

Best wishes

Sam

Conditional_Rules_2.png

For range, I can't have H5:H19, only the cell for respective conditional formatting rule (H5, H6 etc.). Otherwise I get all the other cells in this range also coloured when the conditional formatting rule in one of the cells is validated.

Here is a screencast:

http://screencast.com/t/eAxBGDHBP42K

In H2 there is a drop-down list with 23 table names; in H5 through H19 there can be one of 11 different conditions fulfilled depending on what has been selected in the named cell on tab B.

Is it possible that the AND OR somehow should change positions?

(J5 and Conditional on cell H5)

=OR(AND(a_1="bergsäker vinnare",Välj_rankningstab

AND(b_1="bergsäker vinnare",Välj_rankningstab

(J6 and Conditional on cell H6)

=OR(AND(a_2="stark vinnare",Välj_rankningstab

AND(b_2="stark vinnare",Välj_rankningstab

I may still not be understanding correctly, so you could try to correct the formulas in column "J" then copy that into the Conditional Rule.

Best wishes. /Sam

Conditional_Rules_3.png

EE-07-22.xlsx

What I mean is that the condition for TRUE is that only one of the different options in the formula is true (can't be more than one, can be zero also, that is, nothing is true).

So shouldn't it be AND between each of the 23 different options? Although I tried that now, and it didn't work.

It's not possible to use IF in conditional formatting in the beginning, is it?

This is how I think of the conditional formatting formula, which is logic to me: =AND(horsenamenamedcella="

That is, if both the conditions in the first parenthesis pair after AND are true, then background colour the cell with dark blue, OR if both conditions in the second parenthesis pair after AND are true, then background colour the cell with dark blue.

Ok, I understand the logic in your current conditional formatting formula now. But why doesn't it work? It seems completely correct. OR in the beginning, then AND for each of the 23 different options. Is there anything wrong with the parenthesises? So the OR in the beginning doesn't compare the 23 different options?

=OR(AND(B8="A*",C8="A*"),A

I found it here:

https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/

So the formula should be correct. But why doesn't it work?

This is the formula:

=OR(AND(min_rankning_1="be

AND(fackpressens_rankning_

Explanations:

min_rankning_1: A named cell on tab B which contains a drop-down list with 11 options (one option is "bergsäker vinnare")

Välj_rankningstabell: A named cell on tab A which contains a drop-down list with 20 options (among them, for example, "Min rankning", "Fackpressens rankning", "ATG:s rankning", etc.).

fackpressens_rankning_1 and ATG:s_rankning_1: same as for min_rankning_1 (see above).

Only this formula works (but it's not sufficient, I need the combined OR AND):

=AND(min_rankning_1="bergs

On tab A, there is a drop-down list in H2 with 20 options. Then there are cells H5 to H19 where I need this conditional formatting formula so that if a certain option (e.g. "bergsäker vinnare") has been selected for a certain named cell in one of the 20 tables on tab B and at the same time this table name (e.g. "Min rankning") has been selected in H2 on the current tab A, then the background colour should change to dark blue.

Here is a screencast of tab A ("Hästnamn" means "Horse name" and "Lopp 1" means "Race 1"; the context is an Excel for handicapping harness races):

http://screencast.com/t/lsLAztjj

And a screencast of tab B (where the 20 tables are with drop-down lists with 11 options such as "bergsäker vinnare" etc.):

http://screencast.com/t/OwynpYGG1YLL

1) Either the Tab A (Reducerade system) would represent the ranknings across all 23 tables, so if the corresponding "Hästnamn" is flagged ("rankningstyp" value is) "bergsäker vinnare" in any of them, it would turn dark blue,

2) Or for only the selected "Välj_rankningstabell" (H2), such as "Min rankning", any corresponding "Hästnamn" in the "Min rankning" table which is flagged (rankningstyp value is) "bergsäker vinnare", it would turn dark blue,

If you are indeed trying to match against "Hästnamn" then it would require a VLOOKUP(), which is complex enough, and also need to handle when the "Hästnamn" doesn't match (the VLOOKLUP fails).

On Tab A (Reducerade system), there would be 11 Conditional Rules (one for each colour) applied to the range $H$5:$H$19. For the one example of "bergsäker vinnare":

In Case 1, you would lookup across all tables for "bergsäker vinnare", and if any are TRUE, then Dark Blue. This example is for two tables, to test.

=OR(IFERROR(VLOOKUP(H5;Tab

IFERROR(VLOOKUP(H5;Table2;

In Case 2, you would test for the selected table, using a deep nested IF. This example is for two tables, to test.

=IFERROR(VLOOKUP(H5;

IF(Välj_rankningstabell="T

IF(Välj_rankningstabell="T

;2;FALSE)="bergsäker vinnare";FALSE)

The VLOOKUP( value, table, column, exact) looks for a "value" in a "table" and returns the corresponding "column" and must match "exactly" or returns an error if there is no match.

You may have started on the better path considering the need for IF() conditions. Hope I haven't gone too far astray.

Best wishes. /Sam

Tab A, H5: if tab B CD 2565 (=named cell min_rankning_1) = "bergsäker vinnare", then background colour cell H5 (tab A) in dark blue. So there is just 1-1 correspondence/match.

The same for H6:H19 as above (H6: CD 2566 named cell min_rankning_2) etc.

Some clarification:

Välj_rankningstabell (in cell H2 on tab "Reducerade system") could for example be "Min rankning" or "Fackpressens rankning".

If, for example, "Min rankning" has been selected for Välj_rankningstabell (cell H2), then no need to VLOOKUP a whole range of cells but only need to check the value in one specific cell (in this case, cell CD2567 on tab "LOPP 1").

If, for example, "Fackpressens rankning" has been selected for Välj_rankningstabell (cell H2), then no need to VLOOKUP a whole range of cells, but only need to check the value in one specific cell (in this case, cell BO2567 on tab "LOPP 1").

=iferror(h5;if(Välj_rankni

But I got this error message:

"You've entered too many arguments for this function. To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

But there is no "Insert Function" button there (I am in "Edit formatting rule" in Conditional formatting).

Ok, it cleared the error when I deleted the equal sign (equal sign should not be used in the conditional formatting dialogue).

"iferror(h5;if(Välj_rankni

But Excel automatically distorts the formula so it looks like this when I open the formula for editing afterwards:

="iferror(h5;if(Välj_rankn

So Excel adds the = sign plus duplicates the quotation marks.

If I add the = sign from the beginning I receive error message that the formula contains an error. If I don't add the = sign from the beginning Excel distorts the formula.

="iferror(VLOOKUP(h5;if(Vä

=AND(OR(min_rankning_1="st

So there must be a 1 to 1 correspondence: if table "Min rankning" has been selected in H2, then use dark blue as background colour in H5 if "bergsäker vinnare" has been selected in 'LOPP 1'!CD2567, or use light blue as background colour in H5 if "stark vinnare" has been selected in 'LOPP 1"!CD2567 (etc.).

Then the same for H6:H19 (H6 for "Min rankning" selected in H2 corresponds to 'LOPP 1'!CD2568).

The value that is selected in the drop-down menu in cell H2 on tab "Reducerade system" is the name of the table on tab "LOPP 1".

=IF(OR(AND(min_rankning_1=

I found the formula pattern here:

http://stackoverflow.com/questions/13219177/is-it-possible-to-combine-and-and-or-in-excel-formulae

The formula doesn't need an IF() - it just needs to return the TRUE/FALSE of the OR() function. Note you can add new lines in a text file this way and paste directly in the Excel cell - makes it a bit more readable.

=OR(

AND(min_rankning_1="bergsä

AND(fackpressens_rankning_

)

Put this in an adjacent cell to H5 (I5 for example). This serves two purposes - showing you exactly what the formula result is and you can then apply the conditional to this adjacent cell value and avoid any errors about off-sheet references. This secondary column (e.g. "I") can be hidden later for tidiness.

The Conditional on cell H5 is simply:

=I5

For every colour, a new Conditional is required.

I know the "range" business is a bit confusing, but once the Conditional Rules are in place for each colour (15 for example), the "range" will make it apply to all the cells. You won't have to do 11 x 15 Conditionals for instance

Keep working through the logic for the two choice case. When that works, you will have the pattern to extend to 11 or so.

Best wishes. /Sam

But another issue has arised now, and I don't know exactly what the issue is because I get no error message but the result is not the same.

When I enter my conditional formatting formula with 447 characters in total, there is no problems at all, I get exactly the formatting I want.

Then, when I add the full conditional formatting formula on 1,832 characters the background changes from my preset grey to ordinary white and the conditional formatting is not applied. Why? I enter the formula exactly as the pattern I've posted here, only make it much longer. But when making it so long as now, it doesn't work (although no error message is displayed).

I've double-checked several times now, and the long version follows exactly the same version as the short version of the conditional formatting formula, but only the short version works. If there would have been a length restriction, shouldn't I have received an error message? What could be causing this?

Problem solved! I found out I had forgotten a small "1" in the name of a cell.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window