OR - formula is giving #value error

[irfananeeza]

Hi Experts, I need help with the following #value error. I need a formula to automatically determine existence of "Court Fees". If "Court Fees" found on either Bank Account sheet or Credit Card sheet, then populate it on Cell A7 of the income sheet. The formula must be dynamic. Any help would be appreciated.

Book1
	A	B
1	Categories	Amount
2	Sales
3	Advertising
4	Training Exp
5	Rent
6	Utilities
7	#VALUE!	formula should auto decide what should be the cell value "Court Fees" or "Other Expenses"
Income

Cell Formulas
Range	Formula
A7	A7	=IF(OR('Bank Account'!$E$1:$E$1000,'Credit Card'!$E$1:$E$1000)="Court Fees","Court Fees","Other Expenses")

 

[etaf]

how about

=if(
or(
countif( 'Bank Account'!$E$1:$E$1000, "Court Fees")>0 ,countif('Credit Card'!$E$1:$E$1000 , "Court Fees")>0),

"Court Fees","Other Expenses")


=IF(OR(COUNTIF( 'Bank Account'!$E$1:$E$1000, "Court Fees")>0,COUNTIF('Credit Card'!$E$1:$E$1000, "Court Fees")>0),"Court Fees","Other Expenses")

 

[Joe4]

You cannot apply the OR function to a whole range of cells like that.
If you are trying to see if ANY cells in that range ="Court Fees", use COUNTIF.

EDIT: Just like etaf shows you above!

 

[irfananeeza]

Amazing ... works like a charm !!! Thanks @etaf and @Joe4.

 

[etaf]

you are welcome

 

[irfananeeza]

@etaf, I did post my data using XL2BB addin. Was it not posted right? Or was there any issue there?. Just want to make sure next time follow the right way. Thanks.

 

[etaf]

no issue, always good to see an XL2BB
BUT
as it did not have the other sheets - could have also been posted separately with XL2BB

'Bank Account'!
or
'Credit Card'!

so without recreating those sheets and data. i thought it easier and quicker, at least for me , just to post the formula