Validate Cell Data That is Populated by Formula

[TJ_58]

Hi - I'm not sure if what I'm trying to do is possible, but I'm hoping it is. I haven't had any luck searching out an answer so far.


In Brief:

In Excel 2010 I have a sheet where the user selects values in two different validated lists and the first two characters of each selection are concatenated into a third cell. I want to validate the result of this third cell and pop up a Stop error if the concatenation creates an invalid entry.


In Detail:

I have validated lists of Departments and Regions; when the user makes a selection on both lists, their selections are trimmed and concatenated in a third cell to create a Cost Center. For example:


Formula in J12: =IF(H12="(SELECT)","",IF(I9="(SELECT)","",CONCATENATE(LEFT(H12,2),LEFT(I9,2))))


Dept List (cell H12): 10 Accounting, 20 Sales, 30 HR

Region List (cell I9): 01 NW, 02 SW, 03 NE, 04 SE


The user selects from these lists to determine their numerical cost center. For example, selecting '10 Accounting' and '03 NE' will populate the result '1003' in the target cell (J12).


However, I need to be able to validate these populated results, because not all combinations are valid. For example, if a user selected '10 Accounting' and '01 NW' instead of '03 NE' I need a Stop error to occur because there is no Accounting department operating in the NW region so '1001' is not a valid cost center (the user knows this, but selected the wrong region without realizing the mistake).


Currently J12 contains the formula above and has validation applied pointing to a list of valid cost center combinations (I un-checked 'In-cell dropdown' in the validation window because this cell will be locked and the user should not be able to change or select anything in it). I was hoping that when the cell result populated based on user selection in its parent cells, the error would pop up if the result did not match an entry on the validation list. But this is not happening.


I'm not sure this is possible, but if it is, any help will be greatly appreciated.

 

[SirJB7]

Hi, TJ!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


In this case you should use the worksheet change event of the worksheet, placing this code in the VBA editor:

-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksRng1 = "H12"
Const ksRng2 = "I9"
Const ksWS3 = "Sheet with the cost center list"
Const ksRng3 = "L10:L14, range of the cost center list (just address)"
' declarations
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim I As Long, A As String, A1 As String, A2 As String, bOk As Boolean
' start
'  ranges
Set rng1 = Range(ksRng1)
Set rng2 = Range(ksRng2)
Set rng3 = Worksheets(ksWS3).Range(ksRng3)
'  input changed
If Application.Intersect(Target, rng1) Is Nothing And _
Application.Intersect(Target, rng2) Is Nothing Then Exit Sub
A1 = Left(rng1.Cells(1, 1).Value, 2)
A2 = Left(rng2.Cells(1, 1).Value, 2)
If Len(A1) <> 2 Or Len(A2) <> 2 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
' process
A = A1 & A2
With rng3
I = 1
bOk = False
Do Until bOk Or .Cells(I, 1) = ""
If .Cells(I, 1).Value = A Then
bOk = True
Else
I = I + 1
End If
Loop
If Not bOk Then
MsgBox "Combination selected invalid!", _
vbApplicationModal + vbCritical, "Warning"
End If
End With
' end
Set rng3 = Nothing
Set rng2 = Nothing
Set rng1 = Nothing
End Sub

[/pre]
-----


Regards!

 

[TJ_58]

Thank you so much for your reply and your assistance - I spent all day searching online and here on the forum before posting, so I'm glad to know it's possible. I'm very much a novice when it comes to VBA, so I hope you don't mind me asking questions as needed.


The third constant being declared at the beginning of the code - this is my named range of valid cost centers, correct? Can I replace "L10:L14" with the name of the existing range, or does it need to be a sheet/cell reference? The named range is on a different sheet (in the same workbook) than the sheet we're working with.

 

[SirJB7]

HI, TJ!

Don't be afraid nor shy and ask any doubt, we're all here to learn, remember that.

You're right, ksRng3 is the reference to your CostCenter range, and if it's in another worksheet you should adjust the code a little. I'm going to modify it on my previous post so copy it again and replace in your workbook. Good luck.

Regards!

 

[TJ_58]

Thank you - it doesn't look like I'm going to be able to get back to the spreadsheet in question before Monday, so I'll be sure to come back if I have more questions after applying the changes.