• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Similar to Data Validaton Rule

vijay.vizzu

Member
Dear All,


Recently i got a problem that, i have an workbook with around 14 sheets, now i want to make a rule like in any one of the sheet (assume sheet5 current) if i putting a number in a cell, it should check the entire workbook, that entered number exist or not. If exist, then it should display error message like "Number already exists in sheetname", if not it should accept that number. I can do this through data validation but it can only that current sheet only not on all worksheets. So please help me to resolve this problem


I hope you all understand my problem


Thanks & regards

Vijay
 
Hi, vijay.vizzu!


Despite being later or immediately contradict and refuted as it happens more frequently each time when I say "no, that's not possible", I'm afraid that I have to tell you that you can't do it Excel, neither with data validation nor with conditional formatting, for quoting the two places where you can set or define rules.


Regards!
 
Hi Vijay,


Are you talking about any specific Column, or whole sheet..

Do you only want to check in 'Column B' of all Worksheets.. if the Text Exists or Not..


Regards,

Deb
 
Dear All Excel Ninjas,


I think it can be possible by a VBA Code. I am not familar with VBA Coding, can you all please try to prepare code for me. Please .....
 
Hi Vijay ,


Can you go through this link ?


http://www.pcreview.co.uk/forums/data-validation-across-many-worksheets-t3258501.html


What you have to do is :


1. Make a list of all the sheet names ; suppose I have 5 sheets named Sheet1 , Sheet2 , Sheet3 , Sheet4 and Sheet5. I put in these names in the range A10:A14 on Sheet1 , and create a named range Sheet_Names , referring to =Sheet1!$A$10:$A$14


2. Create a named range , and use the given formula in the Refers To box , specifying the cell address where you will be having the Data Validation drop-down e.g. suppose my DV drop-down is in cell A1 on Sheet1. I create a named range called Counter , and in the Refers To box , put in the following formula :


=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheet_Names&"'!$E:$E"),Sheet1!$A$1))


3. In any unused cell on the sheet where you will be having your DV drop-down , put in the formula =Counter ; suppose this cell is A3 on Sheet1.


4. In A1 ( on Sheet1 ) , put in the DV formula , by selecting Custom , and putting in the following formula : =($A$3=0)


Now , if you type in any number in A1 on Sheet1 , if the same number exists in column E on any of the sheets listed in Sheet_Names , you will get an error message.


Narayan
 
Back
Top