Multiple Column Data Validation

Hi,

This could be better achieved wit VBA/Combobox Not Data Validation.
However,with a bit of trickery,I managed a workaround.

Proceed as Follows:

Let's suppose that the Ranges with ProjectID and ProjectNamesare respectively A1:A20 And B1:B20 .

In a new Column, let's say Column C , Select the Range C1:C20 AND Array Enter the following Formula: {=A1:A20} Press Ctrl+Shift+Enter.

Now,each cell in this new Column(C) will be given a custom Format.
So, Cell C1 for example will be Formatted as follows:

Click Format>Cell>Number Tab>Custom and Type @ "JonesProject" in the Type Area.

After formatting Cell C1, it will display A65110 JonesProject But the Actual underlying value is in fact A65110.
Carry on formatting the rest of the Cells in Column C in the same way with their respective cells in Columns A and B .

Select the Cell with the Data Validation and assign to it the List refering to the range C1:C20.

Column C can now be safely Hidden.

After performing the steps above,you will find that on clicking the Data Validation Arrow,the Combobox shows the ProjectID AND ProjectNames But the Cell is Populated with just the ProjectID


Obviously,if the List of Projects is very long,Custom-formatting each and every cell can be very tedious but I couldn't think of a better solution.

Let me know if any Probs.

Clever work-around but I don't think it will work in my situation as my list is long and changes frequently. Thanks anyway.
BuzzG

Hi,

If the list is long and changes all the time then you can use VBA to carry out all the steps for you.

If you want to experiment with this technic ,please back up your data before

In the code below,I assume that ProjectIDs and ProjectNames are respectively in Columns A and B Starting at Row 2. Row 1 is assumed to be housing the Column Headings.

I also assume that the Cell with the Data Validation is Cell G2

The Code also assumes that the Extra Column which eventually will be Hidden is ColumnC.

Adapt all the above to suit your data arrangement.

Note that the code will execute everytime you add a new ProjectId/ProjectName Or Edit an existing one !

Therefore,you can just add a mock ProjectId at the bottom of your list for the Code to fire and then remove it.

RightClick the Worksheet Tab, Choose View Code and Paste the code below:


As I mentioned in my previous post, Using a ComboBox would have been much easier to program .Having said that,Getting similar results using Data Validation is much more challenging. :wink:

Good Luck.

The drawback to that approach is an ever growing list of custom formats. Is there a limit to the number of custom formats per workbook? I'm interested in a more direct solution. Your thoughts on setting up a combo box?
Thanks for taking the time to help.
BuzzG.

You can ignore some of your coding, thus it will be better....

Private Sub Worksheet_Change(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Application.ScreenUpdating = False<o></o>
If Target.Column = 1 Or Target.Column = 2 Then<o></o>
UpdateValidationCell<o></o>
Target.Select<o></o>
End If<o></o>
End Sub<o></o>
<o> </o>
Sub UpdateValidationCell()<o></o>
Set ProjectIdRange = Range(Range("A2"), Range("A2").End(xlDown))<o></o>
Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))<o></o>
For Each CL In ProjectIdRange.Cells<o></o>
CL.Offset(0, 2).NumberFormat = "General"<o></o>
CL.Offset(0, 2).Formula = "=" & (CL.Address)<o></o>
CL.Offset(0, 2).NumberFormat = "@ " & """" & CL.Offset(0, 1).Value & """"<o></o>
Next<o></o>
Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))<o></o>
Columns("C").Hidden = True<o></o>
End Sub

See if my suggestion in this thread is any use. Note that you would need to remove all the **** from the code. (The **** were introduced when the board software was changed some time ago.)