0

I want to do some data validation in my excel workbook. I want to use as many centralized values as possible for the data validation as the same values are used for multiple sheets. Now I have added some validation for Currency codes (USD...). the validation is based on a list in another sheet and its working kinda good. my problem is that I can still enter lower case values and not just exact values. I would prefer a solution which does not use VBA is possible. If its only possible with VBA, then please add some information on how to add it to the workbook and such.

The values i want to allow are:

CHF
EUR
GBP
USD
DKK
SEK
NOK

and the data validation looks like this: =Sheet1!$S$2:$S$8 but also allows the values above as lower case which is not what i want (i see that i can list the values inside the data validation and then it only accepts exact matches, but for the sake of not having much work if the values change, i want to have them centralized).

I think it would be also good enough to convert the values into upper case, but it has to happen in the same cell eg. I enter eur in cell J6 then it should convert the value to upper case and write it also in J6

  • If you type a list in the combo box instead of using a range, then it should match the case. – mcalex Sep 15 '17 at 10:31
  • Is your input area a coherent range? – IQV Sep 15 '17 at 10:34
  • @mcalex thats true, but if i want to add/delete a value, its much work. ideally i would have like 100 cells as validation range, but only the non blank cells should show up (no blank values in list even if just 5 of 100 cells contain a value). also it should match case sensitive with cell reference in use – XtremeBaumer Sep 15 '17 at 10:40
  • @IQV its a named range, but i thought about using a named table, but i can't reference the tables from the data validation wizard – XtremeBaumer Sep 15 '17 at 10:44
1

I found a solution with VBA:
Do a right-click on the tab of your worksheet and chose "Show code...". In the VBA-Editor enter these macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:C100")) Is Nothing Then
         On Error GoTo ErrorHandler
         Application.EnableEvents = False
         Target.Value = UCase(Target)
    ErrorHandler:
        Application.EnableEvents = True
    End If
End Sub

Change the range corresponding to your needs. You have to save your file as *.xlsm. Then every text in the range is converted to uppercase.

  • the range is basically the column right? the range starts at J6 and goes as long as needed. so i would change the range to J6:J? – XtremeBaumer Sep 15 '17 at 10:56
  • You can use J:J. – IQV Sep 15 '17 at 10:57
  • well, the column has a header in line 3 which does not match any values and therefore i would lik to just start at row 6 because there the data starts – XtremeBaumer Sep 15 '17 at 10:58
  • Then you have to use J6:J1048576 or a line number for the end which fits your needs. – IQV Sep 15 '17 at 10:59
  • to enable the functionality for several worksheets i have to add the code for each sheet right? – XtremeBaumer Sep 15 '17 at 11:04
1

If the cell you want to validate is in cell A1, set your Data Validation type to Custom instead of List and paste this formula in:

=AND(SUMPRODUCT(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)),--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))=LEN(A1),--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

Walkthrough

This formula uses two conditions, combined with an AND() - firstly a check of whether the value is all upper case, secondly a check that the value matches your list of permitted options.

 =AND(
    SUMPRODUCT(
       --(
           (CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)
         )
       ,--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91)
     )=LEN(A1)
   ,--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

The bits that look like CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64 are checking the ASCII code for each individual character. If they're between ASCII codes 64 and 91 we know they are upper case. If the number of upper cases characters matches the length of the value string, we know they are all upper case.

The MATCH() part checks the value appears in your list of currencies.

Note: Shoutout to this post for the upper case check.

  • i get an error when i try to use the forumla as data validation in custom: there is a problem with this formula.=AND(PRODUCT(--((CODE(MID(W6,ROW(INDIRECT("1:"&LEN(W6))),1))>64)),--(CODE(MID(W6,ROW(INDIRECT("1:"&LEN(W6))),1))<91))=LEN(W6),--(IFERROR(IF(MATCH(W6,Sheet1!$S$2:$S$8,0),1),0))) should be right for an example – XtremeBaumer Sep 15 '17 at 11:01
  • @XtremeBaumer you seem to have a typo early in the formula. It should be SUMPRODUCT(..., not PRODUCT(.... Everything else looks ok. – Andi Mohr Sep 15 '17 at 12:32
  • Also if you wanted to replace Sheet1!$S$2:$S$8 with a named range so you don't have to edit the range as you add new values, you can do that. – Andi Mohr Sep 15 '17 at 12:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.