Posts by Paddyd

Paddyd · May 22nd 2003

"The only issue with your solution is that it needs error checking: e.g., a space in B54 would generate an answer of 1 instead of the desired 0 if B54 is "blank" "

Your previous suggestion re data validation is a good one My formula would also return an error for text entries in the cell. I guess Blue_Hornet's formula is the most comprehensive in this regard.

Paddyd · May 22nd 2003

"You can use the Advanced Filter to do it but that requires a number of steps."

However, the steps are simple & could easily be automated by recording the actions with the macro recorder & assigning the results to a button or whatever.

"Is there a formula (array or normal) that can compile a unique list of entries from a list? "

Yes, but the simple versions will leave blanks. More complicated (& multi-step) array formulas can do this, but probably defeat the purpose if it's simplicity your after.

"Is this a job for a UDF? "

No. It's a job for a macroed autofilter.

Paddyd · May 22nd 2003

"LEN (length) is not what I'm after. "

Did you test the formula? The len() check is to determine whether there's anything in the cell. If not, the len() check evaluates to zero, which is interpreted as false in the if() formula, hence the formula returns zero. Otherwise, the true portion is evaluate, this being the calculation you requested.

Paddyd · May 21st 2003

=IF(LEN(B54),100%-B54,0)

format b51 for percentages

Paddyd · May 19th 2003

Hi - welcome to the board!

"Can you advise what i need to do to able to read Russian from the macro?"

My first suggestion would be to post up the macro code so we can take a look at it.

paddy

Paddyd · May 19th 2003

Hi - welcome to the board!

with data set uyp as below, proceed as foillows:

1) select c2
2) go to format | conditional formatting
3) select formula is. enter:

=$A2="No"
& set format to green
4) click add to add another condition
5) select formula is. enter:

=$A2="Yes"
& set format to red.
6) hit OK.
7) copy c2, select rest of range, paste formats...

<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=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book5</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : 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=5><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=formCb598623><INPUT onclick='window.clipboardData.setData("Text",document.formFb334101.sltNb270852.value);' type=button value="Copy Formula" name=btCb709334></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=5><TABLE border=0><TBODY><TR><FORM name=formFb334101><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value" name=sltNb270852><OPTION value='=IF(LEN(B2),"Yes","No")' selected>A2<OPTION value='=IF(LEN(B3),"Yes","No")'>A3<OPTION value='=IF(LEN(B4),"Yes","No")'>A4<OPTION value='=IF(LEN(B5),"Yes","No")'>A5<OPTION value='=IF(LEN(B6),"Yes","No")'>A6<OPTION value='=IF(LEN(B7),"Yes","No")'>A7<OPTION value='=IF(LEN(B8),"Yes","No")'>A8<OPTION value='=IF(LEN(B9),"Yes","No")'>A9<OPTION value='=IF(LEN(B10),"Yes","No")'>A10</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value='=IF(LEN(B2),"Yes","No")' name=txbFb262357></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></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">Complete</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Date Completed</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Description</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[0].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">No</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">texta</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[1].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">Yes</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1/01/2003</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">textb</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[2].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">Yes</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1/01/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">textc</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[3].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">No</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">textd</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[4].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">Yes</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1/01/2003</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">texte</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[5].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">No</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">textf</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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"><A onclick="document.formFb334101.sltNb270852.options[6].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">Yes</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">12/12/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">textg</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"><A onclick="document.formFb334101.sltNb270852.options[7].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">No</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">texth</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"><A onclick="document.formFb334101.sltNb270852.options[8].selected=true; document.formFb334101.txbFb262357.value = document.formFb334101.sltNb270852.value;" href="#javascript:void(0);">No</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">texti</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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=5><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>

paddy

Paddyd · May 19th 2003

People in a couple of other threads also sems to be having problems posting attachments. Maybe send the details to a site admin to check for faults?? See if you can load it up in test forum??

Paddyd · May 19th 2003

what size is the workbook? maybe you need to zip it up?

Paddyd · May 18th 2003

Done, although I find the admin's inability to sort the list a little disconcerting!

Paddyd · May 18th 2003

see the faq's for:

Can I insert an attachment?

paddy

Paddyd · May 18th 2003

Good call re the setv etc.

re:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LookupTable,0,1),0)),VLOOKUP(lookup-value,LookupTable,ColIdx,0),"")

isn't the index element redundant? why not refer directly to the range in question?

Paddyd · May 18th 2003

"I just cannot get it to work on my budget spreadsheet. "

Possible to post up the offending sheet?

Paddyd · May 18th 2003

2 things, just as an aside:

1) inbuzi's got a point re the isna - in general, you should make error checks as specific as possible. iserror() will mask all errors, including syntactic ones in the formula construction. fwiw, the error.type function has more flexibility than the is...() functions for this sort of thing.

2) Given the 1,024 character limit on formulas, you could argue that Roy's code needs a length check to guard against the admittedly small possibility of it truncationg original formulas that were about 500 characters long.

paddy

Paddyd · May 18th 2003

They're defined names - see insert | name | define.

Paddyd · May 17th 2003

FWIW:

The following formula can be used to do a conditional sum on a filtered ange:

=SUMPRODUCT((B2:B9="b")*(C2:C9)*SUBTOTAL(3,OFFSET(A2:A9,ROW(A2:A9)-MIN(ROW(A2:A9)),,1)))

The formula was built for the following data:

<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=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : 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=5><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=formCb653653><INPUT onclick='window.clipboardData.setData("Text",document.formFb684429.sltNb127443.value);' type=button value="Copy Formula" name=btCb491404></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=5><TABLE border=0><TBODY><TR><FORM name=formFb684429><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb684429.txbFb660490.value = document.formFb684429.sltNb127443.value" name=sltNb127443><OPTION value="" selected>A1</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value=ID1 name=txbFb660490></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></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">ID1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">ID2</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Num</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">5</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">6</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">a</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">7</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">b</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">8</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </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=5><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 returns 14 if you filter on col a to only show b's.

The:

SUMPRODUCT((B2:B9="b")*(C2:C9)...

compontent is straightforward. The

SUBTOTAL(3,OFFSET(A2:A9,ROW(A2:A9)-MIN(ROW(A2:A9)),,1))

is effectively used to return an array that indicates which rows are visable. Unsurprisingly, the formula needs to be array-entered with control + shift + enter, not just enter.

Paddyd · May 16th 2003

Hi - welcome to the board!

see:

http://www.ozgrid.com/forum/viewthread.php?tid=2288

for a couple of techniques.

paddy

Paddyd · May 16th 2003

"assuming you've tried the format cells and it hasn't worked "

This will never work At least, once a value has been entered into a cell, changes to its format wont influence the underlying data type. ('cos a value & it's representation are 2 different things).

An alternative to manipulating the data with a formula is to use text to columns:

1) highlight the column,
2) go to data | text to columns
3) hit next twice
4) select the appropriate date option from the column data format list
5) hit finish.

Takes a lot less time to do than to write

Paddyd · May 16th 2003

or, if it's just the occasional formula, physically copy the formula from the formula bar & paste it into the destination cell.

Paddyd · May 15th 2003

Just as an addition:

Given the length of the formula, you might be better off putting the data in a table rather than a formula. That way, changes to your coding scheme can be effected by changing a single table entry, rather than every formula.

Assuming that the relationship between the letters & numbers was set up on a sheet namesd "admin" in cels a1:b27, the formula would be:

=IF(isnumber(B7),B7,VLOOKUP(B7,Admin!$A1:B27,2,FALSE) )

paddy

Paddyd · May 15th 2003

"I can get the formula to return numerical values but only based on a search using a non-numerical value"
=HLOOKUP(D5,Reference!B3:L4,2,FALSE)

Might you "numbers" actually be held as text? I assume that there is a number inb d5. What do the values in the lookup range return when you apply:

=isnumber(b3)

...etc? If "false", then you've got text entries, not numbers. One way to remedy this is to copy a blank cell, select the range of 'text' numbers, got edit | paste special | add.

HTH

paddy

PS. If this doens't fix it, I suggest you post up the offending workbook.

Posts by Paddyd

simple formula

Unique entries

simple formula

simple formula

Reading Russian from a worksheet

Conditional Formatting

Need to modify formula [SOLVED]

Need to modify formula [SOLVED]

Need to modify formula [SOLVED]

Need to modify formula [SOLVED]

#N/A = 0 [SOLVED]

Need to modify formula [SOLVED]

#N/A = 0 [SOLVED]

filtering [SOLVED]

Resolved!

Variable Drop Down

Change date format

Pasting Formulas...

Need help with formulas

HLOOKUP