Using IF function for data evaluation

T

Tomas23

New Member
Joined
Oct 19, 2008
Messages
11
Hi all,

I am struggling to think of a good way to pose this question as I am fairly useless with Excel, but here goes...

I have a fairly large spreadsheet that allows the selection of multiple possiblities each with their own options costed against a finite resource that the user inputs. A basic version of some of the code I am using is here, if that helps. I would like to run logic tests on the data to find whether it abides by a number of preset rules or not. I have listed the rules here, hopefully this will help to show what I am babbling about.


<script langage="" javascript="">
<!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}
</script>

<center>[HtmlMaker 2.42]

</center><hr>


<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="7" style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - WB.xls</td><td style="font-size: 9pt; font-family: caption; color: rgb(255, 255, 255);" align="right">___Running: 11.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td colspan="7" style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="middle"><form name="formCb755237"><input name="btCb873980" value="Copy Formula" onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type="button"></form></td></tr></tbody></table></td></tr><tr><td colspan="7" style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" bgcolor="WHITE"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="center" bgcolor="White"><select name="sltNb935705" onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value"><option value="">A3</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="White"><input name="txbFb426622" size="80" value="Total Cost" type="text"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>A</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>B</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>C</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>D</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>F</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>3</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Total Cost</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Maximum A and B</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Maximum A</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Maximum B</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Required Total</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"><2000</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2250</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">4</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">4</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">6</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">6</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">4000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">8</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">8</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Each +1000</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>10</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Total Cost</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Minimum C</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">D</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">E</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>11</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"><2000</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>12</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">2000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-4</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>13</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">3000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">4</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-5</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>14</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">4000+</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">5</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-6</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-4</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>15</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: left; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">Each +1000</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);">0-1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); text-align: right; vertical-align: bottom; background-color: rgb(255, 255, 255); font-size: 10pt; font-family: Arial; color: rgb(0, 0, 0);"> </td></tr><tr><td colspan="7" style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(255, 255, 255); width: 120pt;" align="left">Sheet3</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>


<center><hr>
<form name="form1"><input value="Please click this button to send the source to clipbord" onclick="CopyToClipBoard();" type="button">
<input value="View Source" onclick="ViewSource();" type="button"></form>
This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003

Code mods by Ivan F Moala - 15th May 2003


If I give an example; with the required total being 2250, I would like to be able to check that the totals for each section A-E abide by the rules that are given. So the user would be allowed to select up to 4 of A and B, and has to have at least 3 of C, and so on. If possible I would also like to make it check that there is at least one of A or B.

I tried building a function with IF but didn't get very far as I haven't worked out how to make the function compare the 'required total' with the range of values that would then inform which rules to apply. If possible I would like it to highlight whether or not the choices made are legal, perhaps by highlighting the box within the table above.

Is this possible, if so any pointers would be appreciated! Thanks in advance for looking :)​
</center>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Tomas23,

Please explain in more detail, and:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356


Or, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan
 
Upvote 0
Hi, thank you for replying.

Unfortunately the spreadsheet contains a lot of information that I am not allowed to publish on the net, and if I remove it then the sheet itself will probably not make any sense. That said I think I can expalin my query without using any of that data so hopefully this will make more sense. Here is a generic version of the topsheet of my workbook:
WB.xls
ABCDEFG
2RequiredTotalUserChoicesReferenceTable
32250PTotalCostMaximumAandBMaximumAMaximumB
4Spent<2000303
52250P2000+414
6Unspent3000+626
70P4000+838
8Each+1000112
9A+B
10Numberofchoices4TotalCostMinimumCDE
11Cost650<200020-30-1
12A2000+30-40-2
13Numberofchoices13000+40-50-3
14Cost2004000+50-60-4
15BEach+100010-10-1
16Numberofchoices3
17Cost450
18C
19Numberofchoices6
20Cost800
21D
22Numberofchoices2
23Cost350
24E
25Numberofchoices2
26Cost450
27
28Legal?
29#NAME?
Sheet3


The lefthand table is a readout of the other pages so it would show the totals from the other pages where the user has input their choices. I have added some examples to hopefully help with my explanation. The righthand table is a reference table for the user so that they know what restrictions are put on their choices. So for example if they have a project totalling 2250 points then they must have at least 3 choices of C, and cannot have more than 4 choices of D (but don't have to take any).

I would like to add code that would automatically detect whether the users selections complied with these restrictions, and if possible flagged it up if they have made an illegal choice. My problem is that I don't know how to make the code compare the total value (A5) with the values given in the 'user choices reference table' in row D, to allow the code to apply the correct restrictions.

So for example if the user had selected a total cost of 2250 then:

I would like the spreadsheet to compare this total to the find what bracket it fitted in to (in this case 2000-3000) then apply the restrictions associated with that value to the user choices. So check if they had made 3 choices of C (or more), 4 or less choices of D, and so on. I would also like to flag up illegal choices, preferably highlighting which restriction caused the choice to be invalid, so it might look something like the next example (it was too big it said...)
 
Upvote 0
WB.xls
ABCDEFG
1RequiredTotalUserChoicesReferenceTable
22250PTotalCostMaximumAandBMaximumAMaximumB
3Spent<2000303
42250P2000+414
5Unspent3000+626
60P4000+838
7Each+1000112
8A+B
9Numberofchoices5TotalCostMinimumCDE
10Cost650<200020-30-1
11A2000+30-40-2
12Numberofchoices13000+40-50-3
13Cost2004000+50-60-4
14BEach+100010-10-1
15Numberofchoices3
16Cost450
17C
18Numberofchoices2
19Cost800
20D
21Numberofchoices2
22Cost350
23E
24Numberofchoices3
25Cost450
26
27Legal?
28NO
Sheet4
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
864
gvsumich
G
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
405
Alex Blakenburg
A
E
  • Question
Adding a custom message.
Replies
1
Views
462
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
341
VBAEXCELNew
V
R
  • Question
Macro not working after adding two new items
Replies
4
Views
165
myall_blues
myall_blues

Forum statistics

Threads
1,216,077
Messages
6,128,685
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top