Help with cell programming!

Solved/Closed
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
- Jul 19, 2013 at 03:02 AM
 ozfox999 - Aug 1, 2013 at 07:47 PM
Hi everyone. this is my first time here, and in desperate need of help from some Excel Genie to make my wish come true!

Ok, here is my "urgent" problem:

The Operative Cells are C, H and I (I as in India).

Here are the conditions I need for Cells in column I, starting in I4.

IF C3=X AND H4>4 Then I4=X

OR

IF C3=Y AND H4<-4 Then I4=Y'

Otherwise, "No Action".

Then, I need some Conditional Formatting to go with that, to show both X (blue) and Y (red) ONLY IF

H4>4 AND H4<-4

Outside these conditions, X and Y can be hidden, possibly by making them the same colour as the background in column I.

Thank you in advance for your kind help.

Joseph

10 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Jul 20, 2013 at 07:13 AM
=IF(AND(C3="X",H4>4),"y",IF(AND(C3="Y",H4<-4),"Y",""))

do coditioal formatting manually
0
Hi venkat1926,

It is heartening to see that people can help each other for abolutely no personal gain. You have helped me immensely, for which I am grateful.

Thank you very much.

Ozfox999
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Jul 21, 2013 at 01:27 AM
you are welcome
0
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013

Jul 22, 2013 at 12:16 AM
Hi venkat1926.

I am hoping that this message will get to you personally and you can be kind enough to help me out once more. The last question, although answered perfectly, still leaves me with a problem, probably due to my inability to describe it clearly. Here is my second attempt:

In answer to my initial question, you provided me with:

=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))

which worked perfectly, thank you.

However, now, I require to broaden the scope a little more, to include the following:

Problem 1

IF C3 > 4, X appears
AND IF C3 > 4, Y also appears (or is it OR?)

IF C3 <-4 Y appears
AND IF C3 <-4 X also appears (or is it OR?)

Otherwise "No Action"

Can we do that?

Although I have tried

=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))

It appears that the X condition takes precedence over the Y condition and Y does Not appear.

Problem 2

Out of interest, what about if I wanted the following:

IF C3 > 4, X appears
AND IF C3 > 8, Y also appears (or is it OR?)

IF C3 <-4 Y appears
AND IF C3 <-8 X also appears (or is it OR?)

Otherwise "No Action"

Maybe in this case, instead of having 8 or -8 we can use, say, $P$1 for 8 and $Q$1 for -8? This would allow to change this threshold more easily...

I would be most grateful if you could help me on this one, with many thanks in advance.

Kind regards

Joseph Tefaye
0

Didn't find the answer you are looking for?

Ask a question
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013

Jul 22, 2013 at 12:29 AM
Hi Vencat1926,

I just realized that I made a mistake in my last. Please find the correction below, with apologies, although I do believe that you would have worked it out :).


In answer to my initial question, you provided me with:

=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))

which worked perfectly, thank you.

However, now, I require to broaden the scope a little more, to include the following:

In all cases, the operative cell is I4

Problem 1

IF H4 > 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4 > 4, AND C3= Y, Y also appears in I4 (or is it OR?)

IF H4 < -4, AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4 < -4, AND C3= X, X also appears in I4 (or is it OR?)

Otherwise "No Action"

Can we do that?

Although I have tried

=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))

It appears that the X condition takes precedence over the Y condition and Y does Not appear.

Problem 2

Out of interest, what about if I wanted to do the following:

IF H4> 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4> 8, AND C3= Y, Y also appears in I4 (or is it OR?)

IF H4<-4 AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4<-8 AND C3= X, X also appears in I4

Otherwise "No Action"

Thanks again

Joseph
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Jul 22, 2013 at 01:08 AM
open this file

http://speedy.sh/fMeYk/oxfox999-130722.xlsx

now C3 and H4 (red)have validation list. when you select one of these cells you will see an arrow. click and choose any of those values. then see the formula in F1(yellow). change theentries in C3 and H4 and see F1. you can copy this formula anyhere (I4 also) excelpt c3 and H4

some of your conditions are repeat of previous. carefully look at those conditons.

I leave it as an exercise for the second problem
0
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013

Jul 22, 2013 at 02:24 AM
Unbelievable. Your work is pure magic. Thank you heaps venkat!
0
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013

Jul 31, 2013 at 06:19 PM
Hi Venkat,

Once again, I have a small problem and I hope you are not too busy to offer me some assistance. This one is fairly simple and I think I am having a problem with operator/statement precedence.

Looking at the cells below, I have the following condition:

A B
1 A
2 B C
3 A C
4 A
5

IF A1 = "A", then A2 = "B", IF A2 = "B", then A3 = "A", etc, repeating all the way down column A, to get an alternate As and Bs.

Now, I also need to introduce the following condition, ON TOP of the one above:

IF B2 = "C" AND B3 = "C", Then A4 = A3 (or, in this case, "A").

Thanking you in advance for your continued support.

Kind regards

Joseph
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 1, 2013 at 04:20 AM
download thils and see colored cell whlich have formulas

http://speedy.sh/U2m8V/ozfox-130801.xlsx
0
As usual, the ever reliable venkat has done again. Problem solved perfectly. Thank you for your help.
0