Data Validation Formulas

[PERE]

Hi,

I am pasting two balance sheets into 1 excel sheet and would like help with:

- Setting a formula that checks two cells containing text within a row and returns the word 'Matched'. Plan is to drag down the formula to see which descriptions do link and do not link i.e. Taxes and Taxes = 'Matched', Taxes and Dividends = 'Check Description'

- The other is to check the dollar balance (or not) between two cells within a row. For example $100 and $100 = 'Balanced' and $100 and $80 = '($20) Difference' (aiming to have it return a positive or negative number).

Any help would be greatly apprecaited!

PERE

[benishiryo]

hi there again, PERE. assuming 1st problem in A1 & B1, try:
=IF(A1=B1,"Matched","Check Description")
if you are particular about upper & lower cases, maybe:
=IF(EXACT(A1,B1),"Matched","Check Description")

2nd problem assuming in E1 & F1, then:
=IF(F1-E1,F1-E1,"Balanced")
Format cells to show brackets when negative

[PERE]

Thanks Benishiryo, they both worked.
In relation to the first formula, how do I add in "No Ref" to basically state that one or both of the cells (A1 and B1 in your example) the formula is referencing has no text in it so it thus returns "No Ref" ??

[PERE]

Also do you know how to make the text or cell red if "Check Description" or "No Ref" is returned??

[benishiryo]

you're welcome. maybe:
=IF(OR(A1="",B1=""),"No Ref",IF(A1=B1,"Matched","Check Description"))

to do conditional formatting, select the range you want to apply to (i.e. A1:B10 or if you highlighting the results say in column C, then C1:C10)
go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=OR($C1="Check Description",$C1="No Ref")
or maybe just this:
=$C1<>"Matched"

format to fill or change font colour.

[PERE]

yes that worked well, thankyou.

Sorry, another question. What about this scenario in a new cell.

Y4 contains a current value
AB4 contains a proposed value
Z4 contains the difference between AB4 - Y4.
In AF4 I would like the formula to run this calculation then return "Balanced" if the difference is exactly that in Z4 or the +ve or -ve difference.