|
conditional search
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 |
conditional search
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 |
conditional search
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 |
| All times are GMT +1. The time now is 01:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com