data validation on multiple cells

Hi cherry sharma,

welcome to the forum.

On sheet1, select your list of validation values, i.e A1 to B3 in your example and assign this range a name, e.g. valList

On sheet 2, enter a value in cell A1
In cell B1, enter this custom data validation formula

=B1=VLOOKUP(A1,valList,2,FALSE)

Now you can only enter a value into B1 if it can be matched in the valList table in Sheet1

hth

OK, I wrongly assumed that you would first type a value like 10 or 11 or 12 in column A of Sheet2 before you tackled column B. But this can also be done.

In Sheet1, select the values in column B and give them a range name, like valListB.

Now, in Sheet2, the data validation custom formula for A1 is

=AND(COUNT(A1:B1)=2,A1=INDEX(valList,MATCH(B1,valListB,0),1))

and the data validation custom formula for B1 is

=AND(COUNT(A1:B1)=2,B1=VLOOKUP(A1,valList,2,FALSE))

So, when you enter only one value, in either A or B, you can enter pretty much whatever you like, but as soon as you enter the second value in the same row, you'll get an error message if the two values don't match up with the values as they are defined per row on Sheet1.

Will that do you?

You have to copy the data validation down to the cells below. Set up the data validation in cells A1 and B1, then copy cells A1 and B1, select the cells A2 to B100 (or whatever) and click Edit - Paste Special - Validation.

That won't wash.

You're changing the goal posts in the middle of the game. In your first post you clearly said

10 1000
11 1001
12 1002

and

10 1000 -- is valid
10 1001 -- is invalid
11 1001 -- is valid
11 1002 -- is invalid

and now you say

10 1000 -- valid
10 1002 -- invalid
10 1001 -- valid

That's quite a contradiction, don't you think? It would really help to specify the full requirements up front, instead of sending people off on a wild goose chase.

If it can be done, I'm out of it for the moment. Maybe someone else can take over. To me that looks like a concept that requires array formulae, which will not be possible in data validation custom formula settings.

going back into my cave ....

Honestly, my intention was never to waste your time by misguiding. I take the blame for posting incomplete requirements & I apologize. If you wish, you can ignore my last post by not answering. Frankly I never thought I would reach this far .... Thanks again & sorry for wasting your time.

Hah!
back out of my cave!

Good idea with the concatenation! The result is a number, though, not text in valListC and you only have one column in valListC, so you can not use 2 as the third argument of Vlookup. Try this as your validation custom formula on Sheet2 with valListC on Sheet1:

=VALUE(CONCATENATE(A1,B1))=VLOOKUP(VALUE(CONCATENATE(A1,B1)),valListC,1,FALSE)

copy the cell with the data validation. click the target cell(s) and then Edit - Paste Special - Validation- OK

You may want to post a workbook with your original data layout,so we can assure that the cell references are anchored to the correct cells.

cheers

teylyn, when i copy contents from say Book 2 (which does not have any "data validation") into Book 1 (which has validation) the contents gets copied, over riding any validation you put.

i want to preserve the validation on the cell, when i paste values from any application.

cherry,

that's one of the drawbacks of data validation. If you copy anything into a cell with data validation, the data validation will be overwritten by the new content and format/validation. Even if you just copy the values, the data will not be validated.

Data validation is designed to reduce errors during manual data entry, cell by cell. It is not designed to verify data that gets into cells any other way. You would need VBA code to check for that.

should i create a new topic for the VBA code?

Yes please. Feel free to link to this thread if you feel it is useful.

The forum search is hopeless. Try a site search in google instead, like

"your search keywords" site:excelforum.com