+ Reply to Thread
Results 1 to 8 of 8

Excel validation of cell as per other sheet

  1. #1
    Registered User
    Join Date
    05-04-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    37

    Excel validation of cell as per other sheet

    How can i validate excel cell from other sheet below is my sheet example want to validate Sheet1 from Sheet2 as reference.

    ATTACHING EXCEL FOR REFERENCE
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,814

    Re: Excel validation of cell as per other sheet

    How many variations are there of "IN_INXX": I assume that we need to test this and the corresponding format to be "nnn_nnn".

    Please add an explanation of the validation.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Excel validation of cell as per other sheet

    Data Validation of cell B6:
    Allow: Custom
    Formula:
    =ISNUMBER(SEARCH(VLOOKUP(A6,Sheet2!$A$2:$B$6,2,0),B6))
    Copy/paste special: Validation >>> to B7

  4. #4
    Registered User
    Join Date
    05-04-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    37

    Re: Excel validation of cell as per other sheet

    There can be many variations IN_IN013,IN_IN023 like this

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,814

    Re: Excel validation of cell as per other sheet

    So explain the validation you want performed: we are not mind-readers.

    Add more examples to the file.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,461

    Re: Excel validation of cell as per other sheet

    Select B6:B10
    Data--> Data Validation
    Custom--> Formula
    Enter formula

    =ISNUMBER(SEARCH(INDEX(Sheet2!$B$2:$B$10,MATCH($A6,Sheet2!$A$2:$A$10,0)),$B6,1))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,814

    Re: Excel validation of cell as per other sheet

    Do you really want DV where the value in Column B is fixed against the value in Column A? If A is changed to 123-457 formulae fail.

    or is 123-456 meant to represent a "generic" number i.e nnn-nnn

  8. #8
    Registered User
    Join Date
    05-04-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    37

    Re: Excel validation of cell as per other sheet

    Its resolved thank you all for your time to look into this issue!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro copy from validation list cell to specific cell other sheet
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2020, 02:26 PM
  2. [SOLVED] Sheet Info From Cell For Data Validation
    By Kozinski in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-12-2016, 07:47 AM
  3. Replies: 1
    Last Post: 10-23-2015, 04:18 PM
  4. [SOLVED] Sheet tab Renaming from Cell Value (cell using Data Validation Dropdown)
    By wedzmer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 01:30 PM
  5. [SOLVED] Input message into cell from a cell in another sheet using data validation
    By keineko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2015, 12:31 PM
  6. Excel validation Sheet
    By venkateshsql in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2011, 07:54 PM
  7. Validation in Excel Sheet
    By harshaputhraya in forum Excel General
    Replies: 1
    Last Post: 04-06-2006, 08:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1