How to validate a formulae

[jim-jams3]

I have a database on one sheet and a 2 count if formulae recording information on the next to be exact one formulae counts the number of monthly values and the other count yearly values. I want the sum of these formulaes to be equal or less than 25. and to show an error if the sum of these is mor than 25.

thanks jim

 

[jasonb75]

deleted, misread part of your post, the one above will work better.

 

[hkaplan2]

Something like:

=if(sum(formula1+formula2)>25,"My Error Text",sum(formula1+formula2))

 

[jim-jams3]

the formulae i have used are =COUNTIF('Members Database'!K:K,"monthly") and =COUNTIF('Members Database'!K:K,"yearly") a message comes up saying can not validate values outside workbook

 

[mikerickson]

To use addresses on other sheets in Validation or Conditional Formatting, those ranges need to be named ranges.

For example, if your first formula is in a cell, create a named range "firstResult". Similarly, create "secondResult" refering to the cell holding the second formula.

Then validating cells in MembersDatabase!K with the formula
=(firstResult+secondResult)<25
should prevent you from entring bad values.

 

[jim-jams3]

The attention bar then say the formulae curently evaluates to an error. Do you wish to continue

ps.

this is my members database sheet

<TABLE style="WIDTH: 64pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=85 x:str><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; WIDTH: 64pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl24 height=17 width=85>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Monthly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=17>Yearly</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26 height=18>Yearly</TD></TR></TBODY></TABLE>

and this is the formulea on the other sheet (statistics)

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=248 x:str><COLGROUP><COL style="WIDTH: 186pt; mso-width-source: userset; mso-width-alt: 4534" width=248><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 186pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=248 x:num="12">=COUNTIF('Members Database'!K:K,"yearly")</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=18 x:num="8">=COUNTIF('Members Database'!K:K,"monthly")</TD></TR></TBODY></TABLE>

 

[jasonb75]

Think I have spotted the problem in your formula, some of your comments suggest you are trying to reference between 2 workbooks, but your original post says 2 sheets in the same book, assuming this is correct I believe the ' ' enclosing the members database name is causing excel to think you're looking for another workbook.

=COUNTIF('Members Database'!K:K,"monthly")

Should be

=COUNTIF(Members Database!K:K,"monthly")

Your complete formula

=IF(COUNTIF(Members Database!K:K,"yearly")+COUNTIF(Members Database!K:K,"monthly")>25,"error",COUNTIF(Members Database!K:K,"yearly")+COUNTIF(Members Database!K:K,"monthly"))

 

[mikerickson]

You need to create a named range
Name: myRange
RefersTo: = 'Members Database'!K:K

and then use the formula

=(COUNTIF(myRange,"yearly") = COUNTIF(myRange,"monthly"))

If that errors, it would help to know what errror message is given.

 

[jim-jams3]

You need to create a named range
Name: myRange
RefersTo: = 'Members Database'!K:K

and then use the formula

=(COUNTIF(myRange,"yearly") = COUNTIF(myRange,"monthly"))

If that errors, it would help to know what errror message is given.

this is the fomulae i am using =(one+two)<25

one count if formulae is one the other is two

 

[jasonb75]

Not wanting to question your judgement Mike, you know far more about excel than I do, but I've had this working fine without using named ranges.

I'm still thinking that the referencing in the original formula =COUNTIF('Members Database'!K:K,"monthly") is the cause of the problem by looking for a workbook called 'Members Database', if you read the OP it would appear that it should be referring to a worksheet in the existing book.

You need to create a named range
Name: myRange
RefersTo: = 'Members Database'!K:K

and then use the formula

=(COUNTIF(myRange,"yearly") = COUNTIF(myRange,"monthly"))

If that errors, it would help to know what errror message is given.