+ Reply to Thread
Results 1 to 6 of 6

Validate that entry in one cell matches an entry in another range of cells

  1. #1
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    239

    Validate that entry in one cell matches an entry in another range of cells

    Hi -

    See attached workbook sample. I need two separate procedures:

    Procedure #1:

    I have a workbook designed to capture product information and which contains two worksheets. Sheet 1 will capture high-level information about the products while Sheet 2 will capture price information about the same products.

    Users will begin by entering high-level data on Sheet1 including entering the Part Numbers in column A. Once Sheet 1 is complete users will then begin entering data on Sheet 2. Once again, they need to enter Part numbers on Sheet2 column A. I want to validate the part number(s) they enter on Sheet2 Column A match a Part number previously entered on Sheet1 Column A. If no match is found, I merely want to highlight the cell on Sheet 2 in yellow. For this procedure, there no need for any drop down list on Sheet 2 that forces the user to enter a valid part number.


    Procedure #2:

    This procedure is something I plan to use in an Add-In I'm developing. Here I do want to create a drop down list that forces user to enter a valid Part number on Sheet 2/ column A.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: Validate that entry in one cell matches an entry in another range of cells

    Hello Grilleman,

    Consider employing a helper column on Sheet2 say,Column AD and enter this Formula in AD2:

    =IF(A2="","",IFERROR(INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!A:A,0)),1))

    Select AD2 and drag it down as far as required.

    Now select Cell A 2>Conditional Formatting and use this Formula:

    =$AD2=1

    Choose your Formats>O.K./Apply your way out.

    Select A2 and drag it down as far as required.


    Hope that helps.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: Validate that entry in one cell matches an entry in another range of cells

    Hello again Grilleman,

    Maybe it would be easier for you if I could get some sample Workbooks to upload.

    Regards.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: Validate that entry in one cell matches an entry in another range of cells

    I have a solution to both your problems, not requiring a helper column.

    Procedure 1 involves a conditional format with an expanding range to include any new data entered with a match function.

    Procedure 2 involves a named range that is dynamic to include your part numbers on sheet 1 (as long as there aren't any gaps), and turns that range into a source for the data validation on sheet 2.

    Procedures.xlsx

  5. #5
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    239

    Re: Validate that entry in one cell matches an entry in another range of cells

    Although both solutions contained in this thread work, I need a solution that can be deployed via an add-in (i.e., a module containing code that performs these tasks). Because users that install the add-in may be working with workbook templates that already contain data, I need to run procedure #1... highlight part numbers on sheet 2 that do not match part numbers entered on sheet 1. Secondly and because other users will be starting with blank workbook templates, I would also like to include a drop down list on sheet2 so it is easy for the user to select part numbers that match what is contained on sheet 1. Hopefully this makes sense.

    Thanks,

    Grilleman

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: Validate that entry in one cell matches an entry in another range of cells

    Hello Grilleman,

    Please see if the attached sample Workbook is closer to what you want.

    Regards.
    Attached Files Attached Files

+ 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. Replies: 4
    Last Post: 03-21-2014, 12:01 AM
  2. [SOLVED] VBA to validate entry?
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-25-2014, 11:46 AM
  3. A macro to validate a text entry or a specific number of digits in a cell
    By Johnny Quest in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2012, 03:28 PM
  4. Single data entry cell for a range of cells from most to least recent entries?
    By orange108 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2011, 01:10 PM
  5. Validate MsgBox Entry to Data in Cells
    By David in forum Excel General
    Replies: 14
    Last Post: 12-21-2005, 06:35 PM

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