LinkBack URL
About LinkBacks
Testing Values Between Two Sheets (Simplified and Updated)
PLEASE SEE POST #9 FOR AN UPDATED VERSION OF MY QUESTION
Hello,
I need help creating formula that test the validity of values in one sheet, using rules in another. The formula would work between two sheets in the same workbook the Testing sheet and the Rules sheet. I really need 6 different formulas but with slight changes to each I really only need 3 and I figured I can do the other 3 on my own. A breakdown of the rows and columns for both sheets is below.
Testing Sheet
Column [A] of the Testing sheet are listed number combinations.
Columns [B] through [L] have indicator rule headers and the rows below contain True or False values.
Column [M] contains values that are either Win, Loss, or Break Even.
Columns [N] through [T] is where the formula I need will reside in the rows below.
Rules Sheet
Columns [A] through [S] contain number combinations in the header and indicator rules or number combinations in the rows below.
How I want all of this to work:
1st: The formula would start by matching the number combination in the Testing sheet with the number combination header in the Rules sheet.
2nd: The formula would return to the Testing sheet and see what value is in column [M] that corresponds with the number combination it matched.
3rd: The formula would then go back to the Rules sheet and review whether the matching number combination column contained text or numbers and based on that result it would follow the rules outlined below.
FORMULAS
WIN
1.) =MATCH(‘Testing Sheet’ A3 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A3 equals 1231111/222=‘Rules Sheet’ A1 equals 1231111/222)
IF(‘Testing Sheet’ M3=Win AND ‘Rules Sheet’ A2=Text)
AND(‘Testing Sheet’ [B3 – L3] All values are True when compared to text in ‘Rules Sheet’ [A2 – A10])
THEN result in ‘Testing Sheet’ N3=TRUE
2.) =MATCH(‘Testing Sheet’ A12 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A12 equals 1311434/323=‘Rules Sheet’ J1 equals 1311434/323)
IF(‘Testing Sheet’ M12=Win AND ‘Rules Sheet’ J2=Number)
THEN result in ‘Testing Sheet’ N12=Optimize
3.) =MATCH(‘Testing Sheet’ A2 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A2 equals 1231111/222=‘Rules Sheet’ A1 equals 1231111/222)
IF(‘Testing Sheet’ M2=Win AND ‘Rules Sheet’ A2=Text)
AND(‘Testing Sheet’ [B26 – L26] 1+ values are False when compared to text in ‘Rules Sheet’ [O2 – O10])
THEN result in ‘Testing Sheet’ N2=Review
LOSS OR BREAK EVEN
1.) =MATCH(‘Testing Sheet’ A14 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A14 equals 1311441/232 =‘Rules Sheet’ K1 equals 1311441/232)
IF(‘Testing Sheet’ M14=Loss or Break Even AND ‘Rules Sheet’ K2=Text)
AND(‘Testing Sheet’ [B14 – L14] 1+ values are False when compared to text in ‘Rules Sheet’ [K2 – K10])
THEN result in ‘Testing Sheet’ N14=TRUE
2.) =MATCH(‘Testing Sheet’ A6 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A6 equals 1311421/323=‘Rules Sheet’ D1 equals 1311421/323)
IF(‘Testing Sheet’ M6=Loss or Break Even AND ‘Rules Sheet’ D2=Number)
THEN result in ‘Testing Sheet’ N6=‘Testing Sheet’ A6
3.) =MATCH(‘Testing Sheet’ A26 to ‘Rules Sheet’ [A1 – S1])
MATCH FOUND(‘Testing Sheet’ A26 equals 1311444/333=‘Rules Sheet’ O1 equals 1311444/333)
IF(‘Testing Sheet’ M26=Loss or Break Even AND ‘Rules Sheet’ O2=Text)
AND(‘Testing Sheet’ [B26 – L26] All values are True when compared to text in ‘Rules Sheet’ [O2 – O10])
THEN result in ‘Testing Sheet’ N26=Review
Simplified Breakdown of All Six
(Testing Sheet = Testing S./Result Sheet = Result S./Number Combinations = # Combo)
Testing S./Result S. # Combo Match = Testing S. Win = Result S. Text = Testing S. All TRUE = Answer = TRUE
Testing S./Result S. # Combo Match = Testing S. Win = Result S. Numbers = Answer = Optimize
Testing S./Result S. # Combo Match = Testing S. Win = Result S. Text = Testing S. 1+ FALSE = Answer = Review
Testing S./Result S. # Combo Match = Testing S. Loss or Break Even = Result S. Text = Testing S. 1+ FALSE = Answer = TRUE
Testing S./Result S. # Combo Match = Testing S. Loss or Break Even = Result S. Numbers = Answer = Numbers
Testing S./Result S. # Combo Match = Testing S. Loss or Break Even = Result S. Text = Testing S. All TRUE = Answer = Review
I hope I’ve been able to explain well enough what I am looking for. I’ve attached a workbook with examples for a better understanding.
Thank you in advance for any and all help.
Register To Reply
Originally Posted by Pete_UK
Thank you very much Pete! Thank you very much Quang! I greatly appreciate all of the hard work and effort you both put in for me to find my desired solution. I can't begin to thank you enough. It is GREATLY appreciated!
Bookmarks