Data Validation = vlookup,left,right

[Vallzeon]

Hi Everyone,

Ive been searching everywhere just to solve this one. i hope vba is not the only solution.

here it goes, what i have is a 1-100 entries of name (a2-a101). i have encoded a data validation customized formula on cell a102. here is the formula:

=ISNA(VLOOKUP(LEFT(B1190,4),LEFT(B$1:B1189,4),1,FALSE))

it always make a warning if i encode a name on the cell a102 with the same first 4 characters. eg:

cell a19
Clement Ivanov

cell a102
Clementinator

after i hit "enter" on cell 102, a message i encoded on the data validation appears and it works nicely.

here is the catch. what i cannot figure out is how can i make the data validation gives a warning ONLY if same FIRST AND LAST 4 characters were encoded.

Sample:
(wont show any warning)
cell a19:
Clement Ivanov

cell a102:
Clementinator

(will show data validation warning)
cell a19:
Clement Ivanov

cell a102:
Clem anov

in a nutshell, i want to combine left and right in the data validation +vlookup +isna. again, i hope vba is not the only answer.

Thanks in advance!

[p24leclerc]

If you could attach a workbook with some dummy data in it, it would help us.

[FDibbins]

Hi

Your vlookup is an array formula, Im not sure if CD accepts array formulas?

Perhaps you could create a helper column that uses =LEFT(B1,4) copied down, and then use that in your vlookup?

[Vallzeon]

http://www.mediafire.com/view/?3eh57hk5970nobl

here is a dummy file. please help me.

to further explain, what i really want to happen is for the MS Excel notify me if there are names which MIGHT be a duplicate so I will check it first before considering the entry.

Thanks in advance again.

[FDibbins]

Not everyone can access sites like that to download your file (company firewalls etc). Please upload your file here instead. Also note that excel is not big on fuzzy logic (names that MIGHT be a duplicate)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Vallzeon]

Data validation dummy.xlsx

Thanks for the suggestions. this is the file. please see trial file.

[:) Sixthsense :)]

Place the cursor in A2 cell and extend the selection below and in Validation Formula copy and paste the below one

Please Login or Register  to view this content.

Ensure that the Activecell should be A2 in your selection since the above formula refers to A2 cell.

[Vallzeon]

So Sumproduct was the key again. am i right that multiplication (*) would also mean the usage of the 2 different arrays? anyways, thank you so much man!

[:) Sixthsense :)]

* acts as an AND operator in sumproduct formula.

If you use a helper column then you can avoid the usage of sumproduct too....