Hi everyone. I've run into a bit of a problem with one of my
worksheets. I have a column whose cells are either blank, "Buy" or
"Sell" I want the column next to it to put "***" in cells that are one

row up from a "Buy" and "xxx" in cells that are one up from a "Sell"
but I only want these to occur if below the "Buy" there is a "Sell"
ignoring the blank cells, and visa-versa for "xxx" when "Sell" is above

a "Buy" ignoring blank cells.


Thanks in advance for your help. I can elaborate if needed.


--
ph2da00
------------------------------------------------------------------------
ph2da00's Profile: http://www.excelforum.com/member.php...o&userid=28406
View this thread: http://www.excelforum.com/showthread...hreadid=479985

Say your column is A, next column is B. I used three helper columns C,D,E,
otherwise the formula would be unconveniantly long (you can hide them or you
can build a huge formula):

in B1: =IF(AND(A2="Buy";C2<D2);"***";IF(AND(A2="Sell";D2< C2);"xxx";""))
in C1:
=IF(ISERROR(MATCH("Sell";INDIRECT(E1);0));65536;MA TCH("Sell";INDIRECT(E1);0))
in D1: =IF(ISERROR(MATCH("Buy";INDIRECT(E1);0));0;MATCH(" Buy";INDIRECT(E1);0))
in E1: ="A" & CELL("row";A1)+1 & ":A65536"

and fill down as necessary!

Regards,
Stefi

€žph2da00€� ezt Ã*rta:


Hi everyone. I've run into a bit of a problem with one of my
worksheets. I have a column whose cells are either blank, "Buy" or
"Sell" I want the column next to it to put "***" in cells that are one

row up from a "Buy" and "xxx" in cells that are one up from a "Sell"
but I only want these to occur if below the "Buy" there is a "Sell"
ignoring the blank cells, and visa-versa for "xxx" when "Sell" is above

a "Buy" ignoring blank cells.


Thanks in advance for your help. I can elaborate if needed.


--
ph2da00
------------------------------------------------------------------------
ph2da00's Profile: http://www.excelforum.com/member.php...o&userid=28406
View this thread: http://www.excelforum.com/showthread...hreadid=479985

Sorry, I forgot to replace my semicolon list separators to commas:

in B1: =IF(AND(A2="Buy",C2<D2),"***",IF(AND(A2="Sell",D2< C2),"xxx",""))
in C1:
=IF(ISERROR(MATCH("Sell",INDIRECT(E1),0)),65536,MA TCH("Sell",INDIRECT(E1),0))
in D1: =IF(ISERROR(MATCH("Buy",INDIRECT(E1),0)),0,MATCH(" Buy",INDIRECT(E1),0))
in E1: ="A" & CELL("row",A1)+1 & ":A65536"

Stefi
€žStefi€� ezt Ã*rta:

Say your column is A, next column is B. I used three helper columns C,D,E,
otherwise the formula would be unconveniantly long (you can hide them or you
can build a huge formula):

in B1: =IF(AND(A2="Buy";C2<D2);"***";IF(AND(A2="Sell";D2< C2);"xxx";""))
in C1:
=IF(ISERROR(MATCH("Sell";INDIRECT(E1);0));65536;MA TCH("Sell";INDIRECT(E1);0))
in D1: =IF(ISERROR(MATCH("Buy";INDIRECT(E1);0));0;MATCH(" Buy";INDIRECT(E1);0))
in E1: ="A" & CELL("row";A1)+1 & ":A65536"

and fill down as necessary!

Regards,
Stefi

€žph2da00€� ezt Ã*rta:


Hi everyone. I've run into a bit of a problem with one of my
worksheets. I have a column whose cells are either blank, "Buy" or
"Sell" I want the column next to it to put "***" in cells that are one

row up from a "Buy" and "xxx" in cells that are one up from a "Sell"
but I only want these to occur if below the "Buy" there is a "Sell"
ignoring the blank cells, and visa-versa for "xxx" when "Sell" is above

a "Buy" ignoring blank cells.


Thanks in advance for your help. I can elaborate if needed.


--
ph2da00
------------------------------------------------------------------------
ph2da00's Profile: http://www.excelforum.com/member.php...o&userid=28406
View this thread: http://www.excelforum.com/showthread...hreadid=479985