Solved

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

Posted on 2016-07-22
41
62 Views
Last Modified: 2016-10-30
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.
0
Comment
Question by:hermesalpha
  • 27
  • 8
  • 5
41 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41725174
The OR operator works in the same manner as the AND operator. This would make your formula something like:
=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"))

Open in new window


There may be syntax errors due to the installed language.
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 250 total points
ID: 41725184
Though that may not be what you are after. Guessing that the matching pairs are important, the formula might be this:

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

Open in new window

0
 

Author Comment

by:hermesalpha
ID: 41735937
I tried now with your second solution, and it displays correctly for min_rankning_1, but not for the other ones (fackpressens_rankning_1 etc.). So I get correct conditional formatting for min_rankning_1 only.
0
 

Author Comment

by:hermesalpha
ID: 41736187
This is how I have entered the formula now:

=OR(AND(a_1="bergsäker vinnare";Välj_rankningstabell="A");AND(b_1="bergsäker vinnare";Välj_rankningstabell="B");AND(c_1="bergsäker vinnare";Välj_rankningstabell="C");AND(d_1="bergsäker vinnare";Välj_rankningstabell="D");AND(e_1="bergsäker vinnare";Välj_rankningstabell="E");AND(f_1="bergsäker vinnare";Välj_rankningstabell="F");AND(g_1="bergsäker vinnare";Välj_rankningstabell="G");AND(h_1="bergsäker vinnare";Välj_rankningstabell="H");AND(i_1="bergsäker vinnare";Välj_rankningstabell="I");AND(j_1="bergsäker vinnare";Välj_rankningstabell="J");AND(k_1="bergsäker vinnare";Välj_rankningstabell="K");AND(l_1="bergsäker vinnare";Välj_rankningstabell="L");AND(m_1="bergsäker vinnare";Välj_rankningstabell="M");AND(n_1="bergsäker vinnare";Välj_rankningstabell="N");AND(o_1="bergsäker vinnare";Välj_rankningstabell="O");AND(p_1="bergsäker vinnare";Välj_rankningstabell="P");AND(q_1="bergsäker vinnare";Välj_rankningstabell="Q");AND(r_1="bergsäker vinnare";Välj_rankningstabell="R");AND(s_1="bergsäker vinnare";Välj_rankningstabell="S");AND(t_1="bergsäker vinnare";Välj_rankningstabell="T");AND(u_1="bergsäker vinnare";Välj_rankningstabell="U"))
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41737408
The formula above is or isn't working?

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.
0
 

Author Comment

by:hermesalpha
ID: 41737834
The long formula I entered doesn't work for some reason.

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.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41738016
Are the named cells using absolute references, like $H$5 or are they using relative references, like H5?
0
 

Author Comment

by:hermesalpha
ID: 41746245
In the conditional formatting rules, only references are made to named cells. These named cells themselves are controlled by a VBA code that determines which colour each should be assigned based on what has been selected in the drop-down menu. So on neither sheet is any absolute references nor relative references made.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41747773
Is the conditional formatting not being applied at all or is it not being applied consistently across the range of cells?
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41748994
Just to clarify, the conditional formula is applied on cell H5 which is a value restricted to a drop-down list and from your last comment it is cell H5 that is part of the determination not Välj_rankningstabell (or H2 from the original comment).  If so then the conditional format formula would likely be as follows:
=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.
0
 

Author Comment

by:hermesalpha
ID: 41773509
SamIDRC,

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 vinnare";Välj_rankningstabell="Min rankning")
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41774705
With multiple rules the "Stop if True" checkbox should be selected, yes?

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

Author Comment

by:hermesalpha
ID: 41774941
Yes, there can only be one true value: one selection made in the drop-down menu in H2 and one named cell on that other sheet that has only one value selected of the several other possible.

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

Expert Comment

by:SamIDRC
ID: 41775060
See attached example of 3 Conditional Rules (menu Home | Conditional formatting | Manage Rules) which determine what colour the cell becomes.  These apply to the same "range: =$O$7:$O$75" (which could be one cell).  (This is from one of my spreadsheets and not related to your coordinates.)
Conditional_Rules_EG.png
0
 

Author Comment

by:hermesalpha
ID: 41776604
I understand what you mean now with "Applies to" range. The conditional formatting should only be applied to that cell (H5), no other cells. So there is no range, only one cell to apply the conditional formatting for.

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).
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41776880
OK, a possible solution, followed by a debugging suggestion.  Set the cursor on cell H5 and apply a conditional rule such as the following, using range $H$5:$H$19.

=OR(
AND(a_1="bergsäker vinnare";Välj_rankningstabell="A");
AND(b_1="bergsäker vinnare";Välj_rankningstabell="B");
AND(c_1="bergsäker vinnare";Välj_rankningstabell="C");
AND(d_1="bergsäker vinnare";Välj_rankningstabell="D");
AND(e_1="bergsäker vinnare";Välj_rankningstabell="E");
AND(f_1="bergsäker vinnare";Välj_rankningstabell="F");
AND(g_1="bergsäker vinnare";Välj_rankningstabell="G");
AND(h_1="bergsäker vinnare";Välj_rankningstabell="H");
AND(i_1="bergsäker vinnare";Välj_rankningstabell="I");
AND(j_1="bergsäker vinnare";Välj_rankningstabell="J");
AND(k_1="bergsäker vinnare";Välj_rankningstabell="K");
AND(l_1="bergsäker vinnare";Välj_rankningstabell="L");
AND(m_1="bergsäker vinnare";Välj_rankningstabell="M");
AND(n_1="bergsäker vinnare";Välj_rankningstabell="N");
AND(o_1="bergsäker vinnare";Välj_rankningstabell="O");
AND(p_1="bergsäker vinnare";Välj_rankningstabell="P");
AND(q_1="bergsäker vinnare";Välj_rankningstabell="Q");
AND(r_1="bergsäker vinnare";Välj_rankningstabell="R");
AND(s_1="bergsäker vinnare";Välj_rankningstabell="S");
AND(t_1="bergsäker vinnare";Välj_rankningstabell="T");
AND(u_1="bergsäker vinnare";Välj_rankningstabell="U"))

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_rankningstabell="A");
AND(b_1="stark vinnare";Välj_rankningstabell="B");
AND(c_1="stark vinnare";Välj_rankningstabell="C");
AND(d_1="stark vinnare";Välj_rankningstabell="D");
AND(e_1="stark vinnare";Välj_rankningstabell="E");
AND(f_1="stark vinnare";Välj_rankningstabell="F");
AND(g_1="stark vinnare";Välj_rankningstabell="G");
AND(h_1="stark vinnare";Välj_rankningstabell="H");
AND(i_1="stark vinnare";Välj_rankningstabell="I");
AND(j_1="stark vinnare";Välj_rankningstabell="J");
AND(k_1="stark vinnare";Välj_rankningstabell="K");
AND(l_1="stark vinnare";Välj_rankningstabell="L");
AND(m_1="stark vinnare";Välj_rankningstabell="M");
AND(n_1="stark vinnare";Välj_rankningstabell="N");
AND(o_1="stark vinnare";Välj_rankningstabell="O");
AND(p_1="stark vinnare";Välj_rankningstabell="P");
AND(q_1="stark vinnare";Välj_rankningstabell="Q");
AND(r_1="stark vinnare";Välj_rankningstabell="R");
AND(s_1="stark vinnare";Välj_rankningstabell="S");
AND(t_1="stark vinnare";Välj_rankningstabell="T");
AND(u_1="stark vinnare";Välj_rankningstabell="U"))

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_rankningstabell="A")
L5:      =AND(b_1="bergsäker vinnare",Välj_rankningstabell="B")
M5:      =AND(c_1="bergsäker vinnare",Välj_rankningstabell="C")
N5:      =AND(d_1="bergsäker vinnare",Välj_rankningstabell="D")
O5:      =AND(e_1="bergsäker vinnare",Välj_rankningstabell="E")
P5:      =AND(f_1="bergsäker vinnare",Välj_rankningstabell="F")
Q5:      =AND(g_1="bergsäker vinnare",Välj_rankningstabell="G")
R5:      =AND(h_1="bergsäker vinnare",Välj_rankningstabell="H")
S5:      =AND(i_1="bergsäker vinnare",Välj_rankningstabell="I")
T5:      =AND(j_1="bergsäker vinnare",Välj_rankningstabell="J")
U5:      =AND(k_1="bergsäker vinnare",Välj_rankningstabell="K")
V5:      =AND(l_1="bergsäker vinnare",Välj_rankningstabell="L")
W5:      =AND(m_1="bergsäker vinnare",Välj_rankningstabell="M")
X5:      =AND(n_1="bergsäker vinnare",Välj_rankningstabell="N")
Y5:      =AND(o_1="bergsäker vinnare",Välj_rankningstabell="O")
Z5:      =AND(p_1="bergsäker vinnare",Välj_rankningstabell="P")
AA5:      =AND(q_1="bergsäker vinnare",Välj_rankningstabell="Q")
AB5:      =AND(r_1="bergsäker vinnare",Välj_rankningstabell="R")
AC5:      =AND(s_1="bergsäker vinnare",Välj_rankningstabell="S")
AD5:      =AND(t_1="bergsäker vinnare",Välj_rankningstabell="T")
AE5:      =AND(u_1="bergsäker vinnare",Välj_rankningstabell="U")

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
0
 

Author Comment

by:hermesalpha
ID: 41777711
Thanks, but I still can't get it to work with that conditional formatting formula. It's exactly the same rule I have now, and it doesn't work. I tried also to tick the checkbox for "Stop if true".

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?
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41778484
It may be helpful to address the two-case condition.  Attached is an image and the corresponding spreadsheet for the following two formulas using your named cells (I think).  The formula is in column "J" to see the TRUE/FALSE result and is exactly copied unto the Conditional Rule for column "H".

(J5 and Conditional on cell H5)
=OR(AND(a_1="bergsäker vinnare",Välj_rankningstabell="A"),
AND(b_1="bergsäker vinnare",Välj_rankningstabell="B"))

(J6 and Conditional on cell H6)
=OR(AND(a_2="stark vinnare",Välj_rankningstabell="A"),
AND(b_2="stark vinnare",Välj_rankningstabell="B"))

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
0
 

Author Comment

by:hermesalpha
ID: 41779212
I don't understand the constellation of OR(AND(

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.
0
 

Author Comment

by:hermesalpha
ID: 41779216
I think maybe because it is not working could be because the different options are lined up together with each other. Perhaps it would work better to use OR and enter different OR conditions lined up after each other, one long line up, then after that long line up add AND (for the H2 table name) and also here make a line up for all the 23 different options in the H2 cell.

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="bergsäker vinnare";namedcellforh2="Min rankning");OR(horsenamenamedcellb="bergsäker vinnare";namedcellforh2="Fackpressens rankning)

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?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:hermesalpha
ID: 41779232
I found this exactly the same conditional formatting formula as mine (disregard the wildcards):

=OR(AND(B8="A*",C8="A*"),AND(C8="A*",D8="A*"),AND(D8="A*",E8="A*"),AND(E8="A*",F8="A*"),AND(F8="A*",G8="A*"),AND(G8="A*",H8="A*"),AND(H8="A*",I8="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?
0
 

Author Comment

by:hermesalpha
ID: 41779424
I have reformulated everything here:

This is the formula:

=OR(AND(min_rankning_1="bergsäker vinnare"; Välj_rankningstabell="Min rankning");
AND(fackpressens_rankning_1="bergsäker vinnare";Välj_rankningstabell="Fackpressens rankning"); AND(ATG:s_rankning_1="bergsäker vinnare"; Välj_rankningstabell="ATG:s 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äker vinnare";Välj_rankningstabell="Min rankning")

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
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41780115
Yes, it would seem the logic is incomplete and may also be trying to address two distinct cases.  If I have not completely misunderstood ...

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;Table_1;2;FALSE)="bergsäker vinnare";FALSE);
IFERROR(VLOOKUP(H5;Table2;2;FALSE)="bergsäker vinnare";FALSE))

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="Table 1";Table1;
IF(Välj_rankningstabell="Table 2";Table2;""))
;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
0
 

Author Comment

by:hermesalpha
ID: 41782814
Perhaps you are right, but I still can't see anything else than there is a 1-1 match between two cells, and in that case no need for VLOOKUP or deep nested IF.

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.
0
 

Author Comment

by:hermesalpha
ID: 41819506
Sam, I think I follow you now. It's definitely your second suggestion that is valid here. I will try your suggestion now.

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").
0
 

Author Comment

by:hermesalpha
ID: 41819570
I tried this formula (that I modified somewhat from yours):

=iferror(h5;if(Välj_rankningstabell="Min rankning";'LOPP 1'!CD2567;if(Välj_rankningstabell="Fackpressens rankning";'LOPP 1'!BO2567;""));2;FALSE)="bergsäker vinnare";FALSE)

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).
0
 

Author Comment

by:hermesalpha
ID: 41819582
I get no error message when I enter this formula without the initial =:

"iferror(h5;if(Välj_rankningstabell="Min rankning";'LOPP 1'!CD2567;if(Välj_rankningstabell="Fackpressens rankning";'LOPP 1'!BO2567;""));2;FALSE)="bergsäker vinnare";FALSE)

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

="iferror(h5;if(Välj_rankningstabell=""Min rankning"";'LOPP 1'!CD2567;if(Välj_rankningstabell=""Fackpressens rankning"";'LOPP 1'!BO2567;""""));2;FALSE)=""bergsäker vinnare"";FALSE)"

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.
0
 

Author Comment

by:hermesalpha
ID: 41819595
I tried this now, but received error message again that the formula I have entered contains an error:

="iferror(VLOOKUP(h5;if(Välj_rankningstabell="Min rankning";'LOPP 1'!CD;if(Välj_rankningstabell="Fackpressens rankning";'LOPP 1'!BO;""));2;FALSE)="bergsäker vinnare";FALSE)
0
 

Author Comment

by:hermesalpha
ID: 41819604
I tried this formula now, and it works when selecting "Fackpressens rankning" but not when selecting "Min rankning":

=AND(OR(min_rankning_1="stark vinnare";Välj_rankningstabell="Min rankning");fackpressens_rankning_1="stark vinnare";Välj_rankningstabell="Fackpressens rankning")
0
 

Author Comment

by:hermesalpha
ID: 41819695
I got it almost to work now, but it says I can not make references to other worksheets or workbooks in a conditional formatting formula. So I wonder how to reference to a range of cells that are all named? It doesn't sound like it's possible.
0
 

Author Comment

by:hermesalpha
ID: 41819698
Or can I name a whole table perhaps? And than use that name in the conditional formatting formula?
0
 

Author Comment

by:hermesalpha
ID: 41819699
Or can I name a whole table perhaps? And than use that name in the conditional formatting formula?
0
 

Author Comment

by:hermesalpha
ID: 41819745
There is a complication that will not render it possible to use your second suggestion, and that is that besides "bergsäker vinnare" and other options in the drop-down menu there are other options that there can be several of (exactly the same) selected in the range in the table.

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).
0
 

Author Comment

by:hermesalpha
ID: 41819783
What if I use your formula and then just label each separate cell as a named range (although in reality it is not a range but a single cell), shouldn't I be able to use your formula then?
0
 

Author Comment

by:hermesalpha
ID: 41819921
I could see now that maybe I haven't been clear enough: the value in H5 and the value in CD2567 are always completely different. The value in H5 on tab "Reducerade system" is the horsename; the value in CD2567 on tab "LOPP 1" is what the user selects in the drop-down menu (for example "bergsäker vinnare").

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".
0
 

Author Comment

by:hermesalpha
ID: 41819974
I think this conditional formatting formula could work, but there must be some small mistake in it because it doesn't work now:

=IF(OR(AND(min_rankning_1="bergsäker vinnare";Välj_rankningstabell="Min rankning");AND(fackpressens_rankning_1="bergsäker vinnare";Välj_rankningstabell="Fackpressens rankning"));"")

I found the formula pattern here:

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

Assisted Solution

by:SamIDRC
SamIDRC earned 250 total points
ID: 41823769
Hi.  (I have been away on course for a few days). Glad to see you are making some progress.

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äker vinnare";Välj_rankningstabell="Minrankning");
 AND(fackpressens_rankning_1="bergsäker vinnare";Välj_rankningstabell="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
0
 

Author Comment

by:hermesalpha
ID: 41830260
Thanks, it worked perfect when I changed to only OR AND and deleted the IF.

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.
0
 
LVL 7

Expert Comment

by:SamIDRC
ID: 41830731
Hi.  Glad its working.  Could you close off the question if it is mostly resolved.

TIA
0
 

Author Closing Comment

by:hermesalpha
ID: 41865998
Thanks Shaun and Sam, your two solutions actually ended up being exactly the same, and that was the formula to use. It works perfect now.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now