Help with worksheet function

[Andries]

Hi All

I need a code or function that must look at table 1 and return the SUBJECT CODE to table 2 but when there is a "O/B" status in column C then it should carry on in the green block for argument sake.

In the first table at cell B9 you find a subject with an O/B status...the code or function should then skip cells B9-12 because it is still part of the "81"row range and continue in cells B13 as shown in table 2.

This is the function that is currently used but it is not doing what I want

"=IF(AND(ROWS(F$3:F3)<11,C3="O/B"),"",B3)"

These tables are only snippets of a big file

Thank you in advance

Dries

data2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 163px"><COL style="WIDTH: 140px"><COL style="WIDTH: 78px"><COL style="WIDTH: 187px"><COL style="WIDTH: 163px"><COL style="WIDTH: 140px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">BLOCK/ROW/SEAT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUBJECT CODE</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">STATUS</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">BLOCK/ROW/SEAT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUBJECT CODE</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">STATUS</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">A</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">B</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">C</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">C</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">D</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">E</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">E</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">F</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">F</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-WEIGHT: bold">G</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-WEIGHT: bold">H</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-WEIGHT: bold">I</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-9</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-WEIGHT: bold">J</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 12pt">w2-81-10</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">K</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">K</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">L</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">L</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">M</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">M</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">N</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">N</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">O</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">O</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">P</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">P</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">Q</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Q</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">R</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">R</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">S</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">S</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">T</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt">O/B</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt">w2-82-10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">T</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">U</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">U</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">V</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">V</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">W</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">W</TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">X</TD><TD style="FONT-SIZE: 12pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-SIZE: 12pt">w2-83-4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">X</TD><TD style="FONT-SIZE: 12pt"> </TD></TR></TBODY></TABLE>

 

[Joe4]

Duplicate:
http://www.mrexcel.com/forum/showthread.php?t=541398

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).