+ Reply to Thread
Results 1 to 21 of 21

Validation based on the value of another cell

  1. #1
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Validation based on the value of another cell

    Hi
    I would appreciate help with this preferably as a formulae solution
    Cells in Column I can be one of 4 letters - M,H,A or P
    Cells in column M can be one of 12 words including the word "Past"
    If a cell in column I is "P" then the corresponding cell in Column M must be "Past"
    There are no restrictions on the values in Column I or Column M i.e. any of the remaining letters in column I (M,H and A) can be used with the remaining 11 words in Column M

    Many thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    Welcome to the forum.

    Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    Attached as requested
    Last edited by AliGW; 08-10-2019 at 06:54 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    Is that a desensitised workbook? Or are the details real? If the latter, please replace with dummy daya (e.g. Mickey Mouse, Donald Duck, etc.) - think GDPR.

  5. #5
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    apologies just realised I uploaded the original.
    Will upload correct one now
    Thanks for your patience!
    How do I delete the incorrect one from the thread?
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    I've done it for you, but for future reference, edit the post, Go Advanced, Manage Attachments, delete.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    I really don't understand your query. What are you trying to do? The columns in the new workbook don't appear to match, but what does this mean?

    There are no restrictions on the values in Column I or Column M i.e. any of the remaining letters in column I (M,H and A) can be used with the remaining 11 words in Column M

  8. #8
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    OOPs! on the original spreadsheet the appertaining columns were I and M
    In preparing a desensitised example I have copied data into the wrong columns so the columns are now H and L. Teach me to check and check again!
    To start again
    1. All cells are entered from an input form and they are manually entered i.e. they are not derived from a formulae or linked from another cell
    2. Cells in column H can contain one of four single letter codes to represent a Type
    3. Cells on the same row in Column L can contain up to twelve codes representing Committee Reps
    4. Any combination of Types and Committee Reps can be used with the exception of Membership Type "P" which must have a Committee Rep code of "Past" and vise versa
    5. I wish to validate these cells so that any entries that do not match the P and Past criteria notified and the error message activated.
    Thanks again for your patience

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    Can you please add some expected results to your sample workbook (manually calculated) so that we can see what we are aiming at?

  10. #10
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    Maybe it's because its a Saturday and I am being more that usually thick! sorry but I do not understand what you wish to see
    There are no calculations as all data is input manually except of course the formulae that will drive the data validation and this is what I am seeking the advise on.
    In the example attached L2 shows "SH" whereas it should be "Past" as in L5, L10, L15 as the column H entries are "P" (Highlighted Red and Green)
    I wish to data validate on entry to avoid the L2 situation
    Hopefully clearer
    Thanks
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    No, I think it's me - the penny has dropped!

    I don't think you can do this without VBA.

  12. #12
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    I was hoping that wouldn't be the case but you cannot win them all!
    Thanks for your help AliGW

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    Shall I move this to the VBA section for you?

    The only other thing you can do is add a helper column with a conditional message - would that work for you?

  14. #14
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    Yes please. I'm not comfortable with VBA but if there's an answer I will try and get my head round it.
    Your idea of a helper column has sparked an idea of an error box that I am using on the Dashboard to highlight errors that uses conditional formatting. The error box normally not visible could "appear" near the input form driven by a hidden helper column.
    Thanks

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    You could try this in M2 drag copied down:

    =IF(AND(H2="P",L2<>"Past"),"Committee Rep MUST be PAST","")

    You can format the cell to make the text stand out.

    Then add data validation to L2:

    =M2=""
    Attached Files Attached Files
    Last edited by AliGW; 08-10-2019 at 11:39 AM.

  16. #16
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    That's great thanks

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    I added detail - please look at the attachment.

  18. #18
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    Works a treat!

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  20. #20
    Registered User
    Join Date
    08-10-2019
    Location
    stourbridge uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Validation based on the value of another cell

    Done! Thanks again for your help

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Validation based on the value of another cell

    You're very welcome.

+ 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: 1
    Last Post: 10-23-2015, 04:18 PM
  2. Data Validation Cell based on previous Data Validation Cell?
    By Dessesbo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-14-2015, 11:42 PM
  3. lock cell based on data validation list in another cell
    By jamied2000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2014, 12:43 PM
  4. [SOLVED] Data validation: cell must match another cell based on multiple cells
    By dadrivr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 07:24 PM
  5. [SOLVED] Cell validation based on values
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2012, 06:01 PM
  6. Cell fill colour based on chosen cell data validation value
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2010, 05:33 AM
  7. [SOLVED] data validation to restrict input in cell based on value of cell above that cell
    By NC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2005, 04:06 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