BRTC Database
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 80px"><COL style="WIDTH: 112px"><COL style="WIDTH: 89px"><COL style="WIDTH: 113px"><COL style="WIDTH: 114px"><COL style="WIDTH: 124px"><COL style="WIDTH: 114px"><COL style="WIDTH: 85px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>F</TD><TD>G</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>S</TD><TD>U</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STATUS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STAFF</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">MEMBERSHIP TYPE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EXPIRATION DATE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">DOOR ACCESS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">SAFETY ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EQUIPMENT ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PAYROLL FORM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PENDING</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">CANCELED</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">11/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/13/12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">INACTIVE</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">JH</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">BRB</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">N</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">F&HCIC</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=IF((U2="Y"),"INACTIVE",IF((AND(ISBLANK(K2),S2="PENDING")),"PENDING",IF((AND(ISBLANK(K2),L2="Y",M2="Y",N2="Y",O2="Y",U2="N")),"ACTIVE",(IF((AND(K2>0,K2<TODAY(),U2="N")),"EXPIRED",IF((AND(G2="BRB",L2="N",M2="N",N2="N",O2="N")),"WARNING",IF((AND(K2>TODAY(),U2="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A3</TD><TD>=IF((U3="Y"),"INACTIVE",IF((AND(ISBLANK(K3),S3="PENDING")),"PENDING",IF((AND(ISBLANK(K3),L3="Y",M3="Y",N3="Y",O3="Y",U3="N")),"ACTIVE",(IF((AND(K3>0,K3<TODAY(),U3="N")),"EXPIRED",IF((AND(G3="BRB",L3="N",M3="N",N3="N",O3="N")),"WARNING",IF((AND(K3>TODAY(),U3="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A4</TD><TD>=IF((U4="Y"),"INACTIVE",IF((AND(ISBLANK(K4),S4="PENDING")),"PENDING",IF((AND(ISBLANK(K4),L4="Y",M4="Y",N4="Y",O4="Y",U4="N")),"ACTIVE",(IF((AND(K4>0,K4<TODAY(),U4="N")),"EXPIRED",IF((AND(G4="BRB",L4="N",M4="N",N4="N",O4="N")),"WARNING",IF((AND(K4>TODAY(),U4="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A5</TD><TD>=IF((U5="Y"),"INACTIVE",IF((AND(ISBLANK(K5),S5="PENDING")),"PENDING",IF((AND(ISBLANK(K5),L5="Y",M5="Y",N5="Y",O5="Y",U5="N")),"ACTIVE",(IF((AND(K5>0,K5<TODAY(),U5="N")),"EXPIRED",IF((AND(G5="BRB",L5="N",M5="N",N5="N",O5="N")),"WARNING",IF((AND(K5>TODAY(),U5="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A6</TD><TD>=IF((U6="Y"),"INACTIVE",IF((AND(ISBLANK(K6),S6="PENDING")),"PENDING",IF((AND(ISBLANK(K6),L6="Y",M6="Y",N6="Y",O6="Y",U6="N")),"ACTIVE",(IF((AND(K6>0,K6<TODAY(),U6="N")),"EXPIRED",IF((AND(G6="BRB",L6="N",M6="N",N6="N",O6="N")),"WARNING",IF((AND(K6>TODAY(),U6="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A7</TD><TD>=IF((U7="Y"),"INACTIVE",IF((AND(ISBLANK(K7),S7="PENDING")),"PENDING",IF((AND(ISBLANK(K7),L7="Y",M7="Y",N7="Y",O7="Y",U7="N")),"ACTIVE",(IF((AND(K7>0,K7<TODAY(),U7="N")),"EXPIRED",IF((AND(G7="BRB",L7="N",M7="N",N7="N",O7="N")),"WARNING",IF((AND(K7>TODAY(),U7="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A8</TD><TD>=IF((U8="Y"),"INACTIVE",IF((AND(ISBLANK(K8),S8="PENDING")),"PENDING",IF((AND(ISBLANK(K8),L8="Y",M8="Y",N8="Y",O8="Y",U8="N")),"ACTIVE",(IF((AND(K8>0,K8<TODAY(),U8="N")),"EXPIRED",IF((AND(G8="BRB",L8="N",M8="N",N8="N",O8="N")),"WARNING",IF((AND(K8>TODAY(),U8="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A9</TD><TD>=IF((U9="Y"),"INACTIVE",IF((AND(ISBLANK(K9),S9="PENDING")),"PENDING",IF((AND(ISBLANK(K9),L9="Y",M9="Y",N9="Y",O9="Y",U9="N")),"ACTIVE",(IF((AND(K9>0,K9<TODAY(),U9="N")),"EXPIRED",IF((AND(G9="BRB",L9="N",M9="N",N9="N",O9="N")),"WARNING",IF((AND(K9>TODAY(),U9="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A10</TD><TD>=IF((U10="Y"),"INACTIVE",IF((AND(ISBLANK(K10),S10="PENDING")),"PENDING",IF((AND(ISBLANK(K10),L10="Y",M10="Y",N10="Y",O10="Y",U10="N")),"ACTIVE",(IF((AND(K10>0,K10<TODAY(),U10="N")),"EXPIRED",IF((AND(G10="BRB",L10="N",M10="N",N10="N",O10="N")),"WARNING",IF((AND(K10>TODAY(),U10="N")),"ACTIVE","")))))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
My spreadhseet is A1:Y190 setup as a table and will grow in rows.
I included some of the columns along with the formulas.
I am trying to make the column A rows change based upon how the data is entered and was trying formulas; however, have ran into limitations and was thinking VBA could help; however, I'm not sure how to create code in VBA and would appreciate some help:
If Membership Type(G) = "BRB":
Looking at columns L,M,N,O as all = "Y", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".
If LMNO have "N" in any of the columns with the above criteria, then display "WARNING".
Regardless of the above:
If Expired(K), then display "EXPIRED"
If Pending(S), then display "PENDING"
If Cancelled(U), then display "INACTIVE"
If Membership Type(G) <> "BRB", but equal to "F&HCIC", "GO", "MBC", "MVIC" or "WHBC":
Looking at columns L,M,N as all = "Y", O="N", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".
If L,M,N have "N" in any of the columns with the above criteria, then display "WARNING".
Regardless of the above:
If Expired(K), then display "EXPIRED"
If Pending(S), then display "PENDING"
If Cancelled(U), then display "INACTIVE"
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 80px"><COL style="WIDTH: 112px"><COL style="WIDTH: 89px"><COL style="WIDTH: 113px"><COL style="WIDTH: 114px"><COL style="WIDTH: 124px"><COL style="WIDTH: 114px"><COL style="WIDTH: 85px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>F</TD><TD>G</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>S</TD><TD>U</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STATUS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STAFF</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">MEMBERSHIP TYPE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EXPIRATION DATE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">DOOR ACCESS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">SAFETY ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EQUIPMENT ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PAYROLL FORM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PENDING</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">CANCELED</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">11/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/13/12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">INACTIVE</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">JH</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">BRB</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">N</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">F&HCIC</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=IF((U2="Y"),"INACTIVE",IF((AND(ISBLANK(K2),S2="PENDING")),"PENDING",IF((AND(ISBLANK(K2),L2="Y",M2="Y",N2="Y",O2="Y",U2="N")),"ACTIVE",(IF((AND(K2>0,K2<TODAY(),U2="N")),"EXPIRED",IF((AND(G2="BRB",L2="N",M2="N",N2="N",O2="N")),"WARNING",IF((AND(K2>TODAY(),U2="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A3</TD><TD>=IF((U3="Y"),"INACTIVE",IF((AND(ISBLANK(K3),S3="PENDING")),"PENDING",IF((AND(ISBLANK(K3),L3="Y",M3="Y",N3="Y",O3="Y",U3="N")),"ACTIVE",(IF((AND(K3>0,K3<TODAY(),U3="N")),"EXPIRED",IF((AND(G3="BRB",L3="N",M3="N",N3="N",O3="N")),"WARNING",IF((AND(K3>TODAY(),U3="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A4</TD><TD>=IF((U4="Y"),"INACTIVE",IF((AND(ISBLANK(K4),S4="PENDING")),"PENDING",IF((AND(ISBLANK(K4),L4="Y",M4="Y",N4="Y",O4="Y",U4="N")),"ACTIVE",(IF((AND(K4>0,K4<TODAY(),U4="N")),"EXPIRED",IF((AND(G4="BRB",L4="N",M4="N",N4="N",O4="N")),"WARNING",IF((AND(K4>TODAY(),U4="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A5</TD><TD>=IF((U5="Y"),"INACTIVE",IF((AND(ISBLANK(K5),S5="PENDING")),"PENDING",IF((AND(ISBLANK(K5),L5="Y",M5="Y",N5="Y",O5="Y",U5="N")),"ACTIVE",(IF((AND(K5>0,K5<TODAY(),U5="N")),"EXPIRED",IF((AND(G5="BRB",L5="N",M5="N",N5="N",O5="N")),"WARNING",IF((AND(K5>TODAY(),U5="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A6</TD><TD>=IF((U6="Y"),"INACTIVE",IF((AND(ISBLANK(K6),S6="PENDING")),"PENDING",IF((AND(ISBLANK(K6),L6="Y",M6="Y",N6="Y",O6="Y",U6="N")),"ACTIVE",(IF((AND(K6>0,K6<TODAY(),U6="N")),"EXPIRED",IF((AND(G6="BRB",L6="N",M6="N",N6="N",O6="N")),"WARNING",IF((AND(K6>TODAY(),U6="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A7</TD><TD>=IF((U7="Y"),"INACTIVE",IF((AND(ISBLANK(K7),S7="PENDING")),"PENDING",IF((AND(ISBLANK(K7),L7="Y",M7="Y",N7="Y",O7="Y",U7="N")),"ACTIVE",(IF((AND(K7>0,K7<TODAY(),U7="N")),"EXPIRED",IF((AND(G7="BRB",L7="N",M7="N",N7="N",O7="N")),"WARNING",IF((AND(K7>TODAY(),U7="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A8</TD><TD>=IF((U8="Y"),"INACTIVE",IF((AND(ISBLANK(K8),S8="PENDING")),"PENDING",IF((AND(ISBLANK(K8),L8="Y",M8="Y",N8="Y",O8="Y",U8="N")),"ACTIVE",(IF((AND(K8>0,K8<TODAY(),U8="N")),"EXPIRED",IF((AND(G8="BRB",L8="N",M8="N",N8="N",O8="N")),"WARNING",IF((AND(K8>TODAY(),U8="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A9</TD><TD>=IF((U9="Y"),"INACTIVE",IF((AND(ISBLANK(K9),S9="PENDING")),"PENDING",IF((AND(ISBLANK(K9),L9="Y",M9="Y",N9="Y",O9="Y",U9="N")),"ACTIVE",(IF((AND(K9>0,K9<TODAY(),U9="N")),"EXPIRED",IF((AND(G9="BRB",L9="N",M9="N",N9="N",O9="N")),"WARNING",IF((AND(K9>TODAY(),U9="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A10</TD><TD>=IF((U10="Y"),"INACTIVE",IF((AND(ISBLANK(K10),S10="PENDING")),"PENDING",IF((AND(ISBLANK(K10),L10="Y",M10="Y",N10="Y",O10="Y",U10="N")),"ACTIVE",(IF((AND(K10>0,K10<TODAY(),U10="N")),"EXPIRED",IF((AND(G10="BRB",L10="N",M10="N",N10="N",O10="N")),"WARNING",IF((AND(K10>TODAY(),U10="N")),"ACTIVE","")))))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
My spreadhseet is A1:Y190 setup as a table and will grow in rows.
I included some of the columns along with the formulas.
I am trying to make the column A rows change based upon how the data is entered and was trying formulas; however, have ran into limitations and was thinking VBA could help; however, I'm not sure how to create code in VBA and would appreciate some help:
If Membership Type(G) = "BRB":
Looking at columns L,M,N,O as all = "Y", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".
If LMNO have "N" in any of the columns with the above criteria, then display "WARNING".
Regardless of the above:
If Expired(K), then display "EXPIRED"
If Pending(S), then display "PENDING"
If Cancelled(U), then display "INACTIVE"
If Membership Type(G) <> "BRB", but equal to "F&HCIC", "GO", "MBC", "MVIC" or "WHBC":
Looking at columns L,M,N as all = "Y", O="N", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".
If L,M,N have "N" in any of the columns with the above criteria, then display "WARNING".
Regardless of the above:
If Expired(K), then display "EXPIRED"
If Pending(S), then display "PENDING"
If Cancelled(U), then display "INACTIVE"
