Posts by Blue_Hornet

Blue_Hornet · Aug 29th 2003

This may not work for an existing column of hyperlinks. I don't know how to extract the "target" information from the cells to find what you want, and if that's your requirement then I hope that someone else can help you.

But if you can build the links yourself using the =HYPERLINK() function (very simple), then you can create a UDF to extract the filename from the relevant cell. Here's how I did it:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book2</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl97 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb821957><INPUT onclick='window.clipboardData.setData("Text",document.formFb767434.sltNb503295.value);' type=button value="Copy Formula" name=btCb033667></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb767434><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb767434.txbFb605691.value = document.formFb767434.sltNb503295.value" name=sltNb503295><OPTION value="=HYPERLINK(A1, A2)" selected>A4<OPTION value='=findlast( "", A1)'>A7</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value="=HYPERLINK(A1, A2)" name=txbFb605691></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">O|:PB|6Sigma|GreenBelt|Nelson|doc012.pdf</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Some Stuff</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb767434.sltNb503295.options[0].selected=true; document.formFb767434.txbFb605691.value = document.formFb767434.sltNb503295.value;" href="#javascript:void(0);"><U>Some Stuff</U></FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><< This is my hyperlink</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb767434.sltNb503295.options[1].selected=true; document.formFb767434.txbFb605691.value = document.formFb767434.sltNb503295.value;" href="#javascript:void(0);">doc012.pdf</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><< This is the target file name.</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

And here is my UDF "FindLast()":

Option Explicit
Dim StartAt As Integer

Function FindLast(myValue As Variant, myTarget As Variant)

StartAt = 0

StartHere:

If Not IsError(Application.Find(myValue, myTarget, StartAt + 1)) Then
StartAt = Application.Find(myValue, myTarget, StartAt + 1)
GoTo StartHere:
End If

FindLast = Right(myTarget, Len(myTarget) - StartAt)

End Function

HTH,
Chris

PS: For some reason the 'backslash' character has been stripped from all of my input text, including my cut-and-paste uploads (HTML Lite and the UDF itself), as well as manual re-entries. I've replaced them manually with the pipe symbol "|", but if you think there's one or more missing, you may be right. The function at A7 should read:

=FindLast( "backslash character goes here", A1)

or it won't make any sense at all.

Blue_Hornet · Aug 26th 2003

Hi, Damien, and welcome.

I also had some questions about where you're really heading with this, but I also have some suggestions to simplify some of your formulas.

First, I don't use the LOOKUP function, because I'm much more familiar with VLOOKUP and HLOOKUP, when I understand the data tables that I'm looking up. So that's a modification that I made out of prejudice, I guess.:)

(But that also allows me to use the COLUMNS function to count my columns, rather than enter those as hard-coded numbers. So when my data gets moved around, or the table is modified, it's easier for me to adapt. See columns D - F.)

Second, you had a deeply nested IF function in column K:K, but most of the multipliers seemed to be the same, for all of the "P" pay classes, so I simplified that formula to an IF with an OR function.

But mostly I agree with mknov and thomach -- "Huh?"

Here's where I got, anyway:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=13><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Damien.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl97 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=13><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb329569><INPUT onclick='window.clipboardData.setData("Text",document.formFb569857.sltNb171505.value);' type=button value="Copy Formula" name=btCb227917></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=13><TABLE border=0><TBODY><TR><FORM name=formFb569857><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value" name=sltNb171505><OPTION value="=VLOOKUP($A2, 'Employee Master List'!$A$2:$D$241, 2, FALSE)" selected>B2<OPTION value="=VLOOKUP($A2, 'Employee Master List'!$A$2:$D$241, 4, FALSE)">C2<OPTION value="=VLOOKUP($A2, '2'!$A$2:$E$241, COLUMNS($A1:C1), FALSE)">D2<OPTION value="=VLOOKUP($A2, '2'!$A$2:$E$241, COLUMNS($A1:D1), FALSE)">E2<OPTION value="=VLOOKUP($A2, '2'!$A$2:$E$241, COLUMNS($A1:E1), FALSE)">F2<OPTION value="=VLOOKUP('1'!A2, '2'!A:B, 2, FALSE)">H2<OPTION value="=VLOOKUP(H2, '3'!A:B, 2, FALSE)">I2<OPTION value==SUM(I2*D2)>J2<OPTION value='=IF(OR((H2="PR1"), (H2="PR2"), (H2="PR3"), (H2="PR4"), (H2="PR5")), E2*2.25*I2, E2*I2*1.75)'>K2<OPTION value==F2*I2>L2<OPTION value="=VLOOKUP($A3, 'Employee Master List'!$A$2:$D$241, 2, FALSE)">B3<OPTION value="=VLOOKUP($A3, 'Employee Master List'!$A$2:$D$241, 4, FALSE)">C3<OPTION value="=VLOOKUP($A3, '2'!$A$2:$E$241, 3, FALSE)">D3<OPTION value="=VLOOKUP($A3, '2'!$A$2:$E$241, 4, FALSE)">E3<OPTION value="=VLOOKUP($A3, '2'!$A$2:$E$241, 5, FALSE)">F3<OPTION value="=VLOOKUP('1'!A3,'2'!A:B,2,FALSE)">H3<OPTION value="=VLOOKUP(H3,'3'!A:B,2,FALSE)">I3<OPTION value==SUM(I3*D3)>J3<OPTION value='=IF(OR((H3="PR1"), (H3="PR2"), (H3="PR3"), (H3="PR4"), (H3="PR5")), E3*2.25*I3, E3*I3*1.75)'>K3<OPTION value==F3*I3>L3<OPTION value="=VLOOKUP($A4, 'Employee Master List'!$A$2:$D$241, 2, FALSE)">B4<OPTION value="=VLOOKUP($A4, 'Employee Master List'!$A$2:$D$241, 4, FALSE)">C4<OPTION value="=VLOOKUP($A4, '2'!$A$2:$E$241, 3, FALSE)">D4<OPTION value="=VLOOKUP($A4, '2'!$A$2:$E$241, 4, FALSE)">E4<OPTION value="=VLOOKUP($A4, '2'!$A$2:$E$241, 5, FALSE)">F4<OPTION value="=VLOOKUP('1'!A4,'2'!A:B,2,FALSE)">H4<OPTION value="=VLOOKUP(H4,'3'!A:B,2,FALSE)">I4<OPTION value==SUM(I4*D4)>J4<OPTION value='=IF(OR((H4="PR1"), (H4="PR2"), (H4="PR3"), (H4="PR4"), (H4="PR5")), E4*2.25*I4, E4*I4*1.75)'>K4<OPTION value==F4*I4>L4<OPTION value="=VLOOKUP($A5, 'Employee Master List'!$A$2:$D$241, 2, FALSE)">B5<OPTION value="=VLOOKUP($A5, 'Employee Master List'!$A$2:$D$241, 4, FALSE)">C5<OPTION value="=VLOOKUP($A5, '2'!$A$2:$E$241, 3, FALSE)">D5<OPTION value="=VLOOKUP($A5, '2'!$A$2:$E$241, 4, FALSE)">E5<OPTION value="=VLOOKUP($A5, '2'!$A$2:$E$241, 5, FALSE)">F5<OPTION value="=VLOOKUP('1'!A5,'2'!A:B,2,FALSE)">H5<OPTION value="=VLOOKUP(H5,'3'!A:B,2,FALSE)">I5<OPTION value==SUM(I5*D5)>J5<OPTION value='=IF(OR((H5="PR1"), (H5="PR2"), (H5="PR3"), (H5="PR4"), (H5="PR5")), E5*2.25*I5, E5*I5*1.75)'>K5<OPTION value==F5*I5>L5</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value="=VLOOKUP($A2, 'Employee Master List'!$A$2:$D$241, 2, FALSE)" name=txbFb769607></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>L</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Staff No.</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Last Name</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Department</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Ord Hours</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">OT Hours</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Other Hours</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Pay Class</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Hourly Rate </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Ordinary Earnings</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">OT Earnings</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Other Earnings</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">8300</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[0].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Baker</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[1].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Admin</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[2].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">37.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[3].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[4].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[5].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">A3</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[6].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">25.6</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[7].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">947.57</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[8].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[9].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">8319</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[10].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Lentz</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[11].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Admin</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[12].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">37.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[13].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">2.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[14].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[15].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">A1</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[16].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">19.4</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[17].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">717.8</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[18].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">67.9</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[19].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">8328</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[20].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Chamberlain</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[21].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Sales</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[22].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">37.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[23].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[24].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[25].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">S1</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[26].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">8.5</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[27].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">315.61</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[28].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[29].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">8331</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[30].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Balogh</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[31].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">Sales</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[32].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">15.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[33].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[34].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0.0</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[35].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">S1</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[36].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">8.5</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[37].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">127.95</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[38].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb569857.sltNb171505.options[39].selected=true; document.formFb569857.txbFb769607.value = document.formFb569857.sltNb171505.value;" href="#javascript:void(0);">0</FONT></A></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=13><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

Blue_Hornet · Aug 22nd 2003

I have an Excel sheet set up to display a list of hundreds to thousands of individual component items to a master bill of materials. The items are grouped by category, and I have a macro that subtotals of weight and manhours for each category, and then displays only that second level of information.

The problem is that when I run the macro to turn subtotalling 'off', and display the list of items normally, formerly hidden rows at the bottom of my sheet start to reappear. There is no data in those rows; I only had them hidden to prevent data being written outside of predefined ranges.

Currently I'm working with a list containing 360 Items in about 30 groups. The subtotalling function adds about 30 lines (seems to correspond to the number of groups that are subtotaled), and this number of rows is 'unhidden' whenever that macro is reversed. (Or are they new appended lines? I'm not sure.)

How can I prevent these extra lines from being appended (? or just unhidden?) to the sheet each time the macro is run and reversed, yet maintain my subtotals below each group?

So far, I've managed to work around this behavior by finding the original LastRow, then comparing that to the new LastRow when my subtotalling / subtotal removal is done, and hiding any rows that have appeared, but I'd prefer to avoid this altogether if there's anything simpler.

Thanks,
Chris

Blue_Hornet · Aug 21st 2003

Thanks, Ralph. I knew it had to be something simple. Who'd'a thought it was the OTHER fields that also needed to be calculated?

Chris

Blue_Hornet · Aug 21st 2003

Using Word 97 on Windows 2000.

I'm trying to make a simple invoice form that does its own calculation of a 15% overhead adder, and then a grand total box. So the user enters the description of a charge, plus the charge itself, in separate form fields, then the 15% is calculated, then the grand total summed (both of these should be automatic).

I can enter the form fields and the calculation formulas just fine, but the calculation is only performed when I unprotect the form, click 'properties' on the field, and exit. ('Calculate on exit' is checked.)

Is there something that I'm missing, so that I can leave the form protected all the time to allow the user to only enter the fields and have the calculation performed automatically? Right now it doesn't calculate on Save, on Print or any other event that I can see, other than the user unprotecting the form and entering (and then exiting) the form's properties. That's no good.

It also doesn't seem to matter if I do this with a calculated form field or Insert / Field and the formula. It doesn't calculate on screen as I want when the document is protected.

Thanks,
Chris

Blue_Hornet · Aug 4th 2003

I've been using Excel 97 and noticing the same thing. In fact, I noticed another interesting quirk the other day: I got a column border that existed on only one cell in the entire column, but was duplicated for all cells in the column. And it didn't matter what I did to get rid of it: I could delete the columns and re-create them, change the print range, hide columns, etc. -- I always got that extra border. (This was after I had resolved the problem of missing borders on columns, similar to what Ex had noticed on his rows. I did that by adding a visible column of width 0.1 to the print_range.)

Weird, is right.

Chris

Blue_Hornet · Aug 4th 2003

Hi, bporter, and welcome.

I'd like to offer what is perhaps a more complete and correct solution. (I hesitate to try to correct Jack and Thomach, but maybe just this once I can get away with it...it sure isn't often.)

=IF(MOD(E30,1)<=0.5, FLOOR(E30,1), CEILING(E30,1))

The reason for this is, what if your summing at E30 ends in a value such as 806.491 ... should that be rounded to 807? Or does it get rounded, more properly, back down to 806?

The MOD(), CEILING() and FLOOR() functions are just alternate (cleaner?) ways of doing the same thing that Jack's formula does. And I changed the value to round on: values ending in .50 or more are rounded up, anything ending in less than .50 (.499999.....) is rounded down.

And the observation to thomach's comment is that the "comma" icon in Excel (97, anyway) doesn't revalue the number, but it only changes the display of the unchanged value. I can't imagine that Excel 2002 would work differently. So 806.499 shows as 806.50 (or whatever rounding value it should display), but the underlying value doesn't actually change. As far as I know, a formula or other manipulation of the value is required for that.

Chris

Blue_Hornet · Jul 21st 2003

Leonardo:

John Walkenbach has the best troubleshoooting guide that I've seen to resolve the problem of Phantom Links. Find it at:
http://www.j-walk.com/ss/excel/usertips/tip054.htm

Chris

Blue_Hornet · Jul 16th 2003

Hi, Jo

Welcome to the board. I hope I have something here that will help you, but it may be kind of kludgy. It is definitely ugly:

=IF(INDIRECT("Conditions!bx" & ROWS($BX$1:BX22) + ROWS($BX$1:BX1)) = 0, "", INDIRECT("Conditions!bx" & ROWS($BX$1:BX22) + ROWS($BX$1:BX1)))

The 'rows' function skips rows 2 by 2, and the 'indirect' function returns the referenced cell values. If I understood your question rightly, this worked on a sample sheet I set up. (But if this was 'normal', I'd look to modify the sheet!)

Just copy this down your column, and the 'rows' function takes care of the counting for you.

Chris

Blue_Hornet · Jul 10th 2003

Dave,

Nice try, but still no cigar. I'm running Excel '97, so the first option was void for me. I tried the second, and have now rebooted since then ... and still no change.

Chris

Blue_Hornet · Jul 9th 2003

Richie:

No, I only noticed it as I was attempting to use (and then resolve problems with) HTML Maker, which has been only in the past week or so. I don't know when the last time I would have checked Tools / References otherwise (or why). It seems to have only become an issue since installing HTML Maker.

Chris

Blue_Hornet · Jul 9th 2003

Excel 97; Win 2000; No Outlook

As far as I know the rest of my ability (limited as it is ... lol) to record and run macros is unaffected.

Just now I created a new worksheet, ran an existing Personal.xls macro on it--which worked fine--and then recorded a new macro that also worked. But when I went to Tools | References I got the same error.

Chris

Blue_Hornet · Jul 8th 2003

Will,

See, that's the funny thing ... when I go to Tools | References the only thing I get is the error message: "Object library not registered." No other options, and nothing whatever about checked and unchecked references. Just the error message.

Chris

Blue_Hornet · Jul 8th 2003

Richie:

Thanks for the link, which got me to some more places to post the question. My question is similar to one of the FAQs, but the response there doesn't help me.

When I try to follow the advice in the FAQ answer (start by going to Tools / References from the VBA window) I get the error message: "Object library not registered." Colo's FAQ doesn't address that, and for someone who doesn't already know what that means, Microsoft's Help on the topic is completely useless.

What is an object library and how do I register it? I've registered at a few libraries in my life, but ...

Thanks,
Chris

Blue_Hornet · Jul 7th 2003

I've been trying to use the HTML Maker lately, but I get an error message (Compile Error: Can't find project or library.) at 'Left' in:

Set rngSelection = Application.InputBox(strMsg1, Left(strMsg2, 46), Selection.Address, Type:=8)

in module Sub HTMLMaker()

Is it something I did?

Thanks,
Chris

Blue_Hornet · Jul 7th 2003

James:

I'm not sure that I fully understood what it was that you were looking for. See the attached example and see if this answers.

HTH,
Chris

Blue_Hornet · Jul 1st 2003

Welcome to the board, lwall.

Maybe it's being persnickety to point out that Kieran's formula probably does what you WANT it to do (COUNTS the number of times that list values end with '-1'), but it doesn't actually SUM them--though from the formula it may appear that way.

That's because your list values 'look like' numbers, but probably aren't. Unless you have some strange number formatting you neglected to mention, those are text values, not numbers, so they can't be 'summed'.

Persnickitively--and if it's not a word, it should be--yours,
Chris

Blue_Hornet · Jun 30th 2003

Hi, Barn

Welcome to the board. A simple answer to your question (maybe simpler than you want) is:

Enter the following in B1:
=sum(A:A)
which would sum everything in column A and show the (always current) total in B1.

Another simple solution is to start your input in A2, and set B1 = 0. Then B2 is:
=B1+A2
and this gets copied down as far as you want in column B.

With that, you could have some neat counting formula (I'm not getting into it here), that could count how many entries are in column A and find the corresponding B value (the current balance) and place it in a single, never-changing spot on your sheet.

Have fun with it!

Chris

Blue_Hornet · Jun 29th 2003

Glenn,

It seems that what you really want is VLOOKUP. But you'll have to rearrange your data somewhat, I think.

The general form is:
=VLOOKUP( value to be looked up,
range to look up in--which starts with the column of values to be searched, and includes the column of values to be returned,
offset columns from the value found in the search range--counting the first column,
--optional--TRUE or FALSE, depending on whether you must have exact matches--false--or you can accept the nearest return to the lookup value--true)

I've rearranged your data and posted. That should be a bit more clear.

Chris

Blue_Hornet · Jun 24th 2003

Well, here are some things I learned from trying (and finally managing) to resolve my own issue:

1. If you are going to have a macro open your 'Master File' of hyperlinks and sample sheets, you'd better check to make sure that the last line in the calling macro isn't:
Sheets(1).Activate
if the new code on (new) Sheet1 forces the looping back to the LAST (visible and workable) worksheet, and you'd prefer to open the FIRST one. Duh!

2. For some reason, and I still don't quite get this, when a macro is opening the Master File (the workbook in question), the Workbook_Open event isn't triggered properly, apparently since the calling macro hasn't ended yet. I'll be looking into this some more sometime, maybe.

Richie, thanks for the suggestion to post a copy of the file. As I started to prepare the copy for posting, I realized that EVERYTHING was working perfectly. So the problem had to be in the file I was working with, and this forced me to take another look (okay, 3 or 4 more looks) at it.

Chris

Posts by Blue_Hornet

VBA: Extract text from a hyperlink

Formulas: need help with lookup

VBA: Hidden Rows Reappearing

Controls (Forms Toolbar): Calculation in Word

Controls (Forms Toolbar): Calculation in Word

Printing: Missing bottom border

Formulas: Rounding Results Up/Down

VBA: Remove a unused Link

Formulas: Getting autofill to recognise pattern in formulas

VBA: OzGrid HTML Maker

VBA: OzGrid HTML Maker

VBA: OzGrid HTML Maker

VBA: OzGrid HTML Maker

VBA: OzGrid HTML Maker

VBA: OzGrid HTML Maker

Formulas: Need help regarding classification of data

[Solved] Counting with *

Formulas: Additon / Running Totals

Formulas: IF Function trouble

Activating Sheets by CodeName