Public Function.

[How_Do_I]

Hi…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The Jeanie is working using the following Public Function:
<o></o>

Public Function V(Optional vrnt As Variant) As Variant<o:p></o:p>

'<o:p></o:p>
' Stephen Dunn<o:p></o:p>
' 2002-09-12<o:p></o:p>
'<o:p></o:p>
Static vrntV As Variant<o:p></o:p>
If Not IsMissing(vrnt) Then vrntV = vrnt<o:p></o:p>
V = vrntV<o:p></o:p>
End Function

<o></o>
It is giving the red highlighted results.
<o></o>
However, when I remove the data in Row A3:E3 everything disappears from the red highlighted area… It should return the “1”. Obviously this is a much reduced Jeanie to show my issue but strangely I get a #NUM error in my full workbook where as in this Jeanie example I get nothing.
<o></o>
Can anyone see what is going wrong please?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 32px"><COL style="WIDTH: 32px"><COL style="WIDTH: 133px"><COL style="WIDTH: 133px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 84px"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Ref</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Home</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Away</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Date</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Matches</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD>Hull City</TD><TD>Blackpool</TD><TD style="TEXT-ALIGN: right">11/11/2011</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD></TD><TD>Coventry City</TD><TD>Leicester City</TD><TD style="TEXT-ALIGN: right">11/11/2011</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">Blackpool</TD><TD style="BACKGROUND-COLOR: #ffff00">Leicester City</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Hull City</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000">1</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Coventry City</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=COUNT(Date)</TD></TR><TR><TD>H6</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G6,IF(Away=H$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>I6</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G6,IF(Away=I$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>H7</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G7,IF(Away=H$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>I7</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G7,IF(Away=I$5,1,"")),0))),V(),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD>G2</TD><TD>Date</TD><TD>=Sheet1!$E$2:$E$18</TD></TR><TR><TD>H6</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>H6</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>H6</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>I6</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>I6</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>I6</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>H7</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>H7</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>H7</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>I7</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>I7</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>I7</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[How_Do_I]

Jeanie with second row of data removed...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Ref	Week	Home	Away	Date		Matches
2	1		Hull City	Blackpool	11/11/2011		1
3
4
5								Blackpool	Leicester City
6							Hull City
7							Coventry City
8
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Aladin Akyurek]

It's not V(). Rather the MATCH bit. Such an outcome obtains only when there is just one record in the data area and the IF bit returns just a 1. MATCH(1,1,0) will invariably fail. Hence the empty result area.

Modify the formula in H6 to:

=IF(ISNUMBER(V(MATCH(1,{0}+IF(Home=$G6,IF(Away=H$5,1,"")),0))),V(),"")

which still needs to be confirmed with control+shift+enter.

Just with a single record to go, the MATCH bit will become:

MATCH(1,{1},0)

That is, MATCH gets, as needed, an array for comparison/matching, not a scalar.

 

[How_Do_I]

Hi Aladin…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Your solution is great, it works so thanks for that… I'm a little confused though... How come this works, it’s the original formula with different formulas for the named ranges. Any ideas about that please?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Ref	Week	Home	Away	Date		Matches
2	1		Hull City	Blackpool	11/11/2011		1
3
4
5								Blackpool	Leicester City
6							Hull City	1
7							Coventry City
8
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Aladin Akyurek]

Hi Aladin…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Your solution is great, it works so thanks for that… I'm a little confused though... How come this works, it’s the original formula with different formulas for the named ranges. Any ideas about that please?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 32px"><COL style="WIDTH: 32px"><COL style="WIDTH: 133px"><COL style="WIDTH: 133px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 84px"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Ref</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Home</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Away</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Date</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Matches</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD>Hull City</TD><TD>Blackpool</TD><TD style="TEXT-ALIGN: right">11/11/2011</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">Blackpool</TD><TD style="BACKGROUND-COLOR: #ffff00">Leicester City</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Hull City</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000">1</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Coventry City</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=COUNT(Date)</TD></TR><TR><TD>H6</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G6,IF(Away=H$5,1,"")),0))),V(),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD>G2</TD><TD>Date</TD><TD>=Sheet1!$E$2:$E$3</TD></TR><TR><TD>H6</TD><TD>Away</TD><TD>=Sheet1!$D$2:$D$3</TD></TR><TR><TD>H6</TD><TD>Home</TD><TD>=Sheet1!$C$2:$C$3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

You have 2 records here, not 1. That means the MATCH bit gets an array to look at, not a single value.

 

[How_Do_I]

I think I get it now Aladin... My last post is one record but the named ranges are looking at a range that covers 2 records.

This Jeanie has failed, original formula looking at a range that covers a single record...

Thanks for your help

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Ref	Week	Home	Away	Date		Matches
2	1		Hull City	Blackpool	11/11/2011		1
3
4
5								Blackpool	Leicester City
6							Hull City
7							Coventry City
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Aladin Akyurek]

I think I get it now Aladin... My last post is one record but the named ranges are looking at a range that covers 2 records.

A 2 record range delivers a 2 item-array to MATCH.
In the original situation, where you deleted 1 record from the 2 record area, MATCH gets a single value (a scalar value), not an array. The extension with {0}+ forces the single value to go into an array structure with a single item/value in it.

That is: 1 vs {1}

The first is a scalar value, the last an array. MATCH needs an array.

This Jeanie has failed, original formula looking at a range that covers a single record...

You seem to be in love with this Jeanie thing...

 

[How_Do_I]

You seem to be in love with this Jeanie thing...

I've no idea how I'd explain the majority of my problems without it!

 

[jasonb75]

Just a little observation, you don't need a custom function to do that.

=SUMPRODUCT((Home=$G6)*(Away=H$5)*Ref)

Format the red cells using [=0]"";#

 

[How_Do_I]

Just a little observation, you don't need a custom function to do that.

=SUMPRODUCT((Home=$G6)*(Away=H$5)*Ref)

Format the red cells using [=0]"";#

I will look at that Jason... The custom function was introduced to stop errors returns and/or double calculations when a game has not been played.

Your solution returns a "0" if the game hasn't been played so your solution has already become a double calculation:

=IF(SUMPRODUCT((Home=$BM12)*(Away=BN$1)*Ref)=0,"",SUMPRODUCT((Home=$BM12)*(Away=BN$1)*Ref))