Complex validation

Question

I need to return a blank cell (in MERP) if A1 has a name, and if it has a number, to use the name in A1 until a new name is encountered.&nbsp; Currently, the formula in the MERP column is =IF((ISTEXT(A1))=TRUE," ",$A$1), but the absolute reference only returns the value in A1.&nbsp; I need to go down A1 until a new name is encountered and use that name.&nbsp; &nbsp; I'm not very good with complex formulas, so any assistance is appreciated.&nbsp;NAMESum of DebitSum of Credit&nbsp;Test Debit&nbsp;&nbsp;Test Credit&nbsp;MERP NameFord, Henry1925.77293.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,925.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 293.45&nbsp;22100229.44229.44&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 229.44&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 229.44Ford, Henry2210299&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00Ford, Henry2210346.0146.01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 46.01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 46.01Ford, Henry2210499&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.00Ford, Henry510411499.6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,499.60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Ford, Henry51042132.72&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 132.72&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Ford, HenryPresley, Elvis3349.53686.23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,349.53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 686.23&nbsp;21160&nbsp;27.53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27.53Ford, Henry

subodh_tiwari_sktneer · Answer

TKIng1917&nbsp;&nbsp;You may try this...&nbsp;In F2...=IF(NOT(ISNUMBER(A2)),"",INDEX(A$2:A2,,MATCH("ZZZ",A$2:A2)))and then copy it down.

Forum Discussion

Complex validation

Share

Resources

NAME	Sum of Debit	Sum of Credit	Test Debit	Test Credit	MERP Name
Ford, Henry	1925.77	293.45	1,925.77	293.45
22100	229.44	229.44	229.44	229.44	Ford, Henry
22102	9	9	9.00	9.00	Ford, Henry
22103	46.01	46.01	46.01	46.01	Ford, Henry
22104	9	9	9.00	9.00	Ford, Henry
51041	1499.6		1,499.60	-	Ford, Henry
51042	132.72		132.72	-	Ford, Henry
Presley, Elvis	3349.53	686.23	3,349.53	686.23
21160		27.53	-	27.53	Ford, Henry