Is it possible to get 2 Column DropDownList for selection but populate in seperate columns

**e4excel** · 03-12-2012, 03:45 PM

Is it possible to get 2 Column Drop-Down List for selection but populate in seperate columns

Dear Forum,

I dont know but I think I had seen something like that longtime back but dont know the website or whether it was through a Combo-Box...
What I mean is that we have a Drop-down with data which is comprised of two columns for selection however when you do select you get one part in one column and the other half in the remaining column..

Lets say, I need to have a drop-down of all the Town Names combined with their pincodes..I know I can always get this done by concatenating the Main List but I need the second part where for selection of for viewing I have the combination of TOwn Names and Pincodes such as below:
Town Name - Pin Code
Andheri ( W ) - 400 058

So, I get to see the combination of the two which can have a hyphen in between the two seperate things, so when I select them I get the Town in the Column H and the attached Pincode in the adjacent column I..

Is this possible?

The purpose of having this is to avoid waiting for the Drop-Down list to enter in one column and then the VLOOKUP for the pIncode from the main-list which is taking hell lot of a time due to the size of the File..

Sometimes the person might enter something existing in the list but he would know it while selection itself as the list will be combined...

Application: I have made a list of all the possible town names for the Mumbai City however when the customer fills the form they put the area name and not the postal Town Name which falters the system so by dsiplaying a Dual List we can ascertain the same with the PinCOde as the Pincode remains the same as the Town or the Locality might be reffered differently..

Warm Regards
e4excel

**teylyn** · 03-12-2012, 05:49 PM

Hello,

check out dependent data validation. http://www.contextures.com/xlDataVal02.html

cheers,

**e4excel** · 03-13-2012, 02:38 PM

Thanks again but can we not have a simple MATCH function to get the Column no if we were to jsut put the Lookup as "Town"?

But I dont know how to get the Letter H for the Column H as well as the No of Last Filled Row No for the Column C instead of the Target.Row = 11..

PHP Code:


And Target.Row <= 11)

I know I may sound weird but I am fairly good in formulas so more or less prefer that approach..

Though I would be tryinng your method too..

I ususally use the SUBSTITUTE FUNCTION to get the COlumn Alphabet in formulas but I dont know how to get the same in VBA to be used in the folllowing lines of your code..

PHP Code:


            Range("I" & Target.Row).Value = Split(Range("H" & Target.Row).Value, " - ")(1)

            Range("H" & Target.Row).Value = Split(Range("H" & Target.Row).Value, " - ")(0)

Warm REgards
e4excel

**e4excel** · 03-13-2012, 01:36 AM

Hi Teylyn,

Which one from that List, can you specify the one, I am not looking for Dependent Validation but actually displaying a Two Column Data List in the Drop-Down but populate only one List from the LEFT in one COlumn and the remaining RIGHT in the adjacent column.

e4excel

**DGagnon** · 03-13-2012, 01:41 AM

could you attach a sample sheet? i think it would help me to better understand what you are trying to do.

**e4excel** · 03-13-2012, 02:06 AM

Originally Posted by DGagnon

could you attach a sample sheet? i think it would help me to better understand what you are trying to do.

Yes DGagnon,

PFA, this explains the requirement without any confusion..
The list would always be expanding...

Warm Regards
e4excel

**e4excel** · 03-13-2012, 04:18 PM

Oh thanks again, so the i would be calculated using the Loop?

or can i use the Match function to get the number which is slightly easier for my kind..

Thanks once again

**DGagnon** · 03-13-2012, 02:11 AM

it looks like the best option here would be using a data validation in col H, and a VLookup in I, but you stated you did not want to do that. I'm just cerious why that is?

**tomjjlcu** · 03-13-2012, 04:00 AM

Originally Posted by DGagnon

it looks like the best option here would be using a data validation in col H, and a VLookup in I, but you stated you did not want to do that. I'm just cerious why that is?

I'm not searching for Dependent Validation but really exhibiting a 2 Column Data List within the Drop-Lower but populate just one List in the LEFT in a single COlumn and also the remaining In the actual adjacent column.

**DGagnon** · 03-13-2012, 02:25 AM

i think i understand what you are trying to do, you want the look up list to contain the full string with both parts, but once selected to only display one part per side. if this is the case, try adding this macro to your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And (Target.Row >= 2 And Target.Row <= 11) Then
        If InStr(Range("H" & Target.Row).Value, "-") Then
            Range("I" & Target.Row).Value = Split(Range("H" & Target.Row).Value, " - ")(1)
            Range("H" & Target.Row).Value = Split(Range("H" & Target.Row).Value, " - ")(0)
        End If
    End If
End Sub

**e4excel** · 03-13-2012, 04:32 PM

Thnks I will come back on that tommorow after trial and error so that I can combine the codes

**e4excel** · 03-13-2012, 02:27 AM

Originally Posted by DGagnon

it looks like the best option here would be using a data validation in col H, and a VLookup in I, but you stated you did not want to do that. I'm just cerious why that is?

Dear DGagnon,

I am currently using it but the problem as I explained in the first post.
Will reiterate with a simpler scenario, this data is accumulated from the Net and therefore very TRUE in anture however when we need to enter the data that would be from a Filled Booking Form from a Client which will be either filled with the same Town Name or the most popular one, the only consolation being the PinCOde which remains the same in every case..
Now I want to maintain a Clean and the most accurate data and therefore we would like to use the same Town Names as we have in our Main List but then we get different names it can be only compared with the Pincodes so everytime you select something we need to wait that long till the selection is done in the Town Column and then the VLOOKUP follows but as there's a lot of Data and array formulas in the actual Sheet it has allready rendered the Sheet slow, so in the name of saving time it will be a boon to the person entering the data as he will look at the Town and the pincodes at one go and then select the appropriate Town based on the PinCode incase he does not get the Town with the same name as in the Form..

Ex:-
Mahim East is also called as Dharavi so now if the CLient has filled the Town/Area as Dharavi and if we have it as Mahim East Dharavi in our lIst he will need to go through the entire List but if he looks at Mahim he sees Dharavi as well as the same PinCOde then we have the exact information..

Hope this clarifies...

Actually if this works, I am also planning to build on this example and make the List capable of allowing Data Entry incase we dont have a particular Town in our Database to be entered in the cell and which can get added to the list, reverse feeding..
But I will put another query for that..

Warm Regards
e4excel

**jaslake** · 03-13-2012, 07:36 PM

Hi e4excel
Is this what you're looking for? (Row 1 is the header row...change as required)

Option Explicit
Sub Find_Col_Num()
'returns the Column #  of myMg
    Dim myMG As Long
    Dim Mg As String
    Mg = "Town"
    myMG = WorksheetFunction.Match(Mg, Rows("1:1"), 0)
End Sub

**DGagnon** · 03-13-2012, 02:32 AM

ok i think i get it now, i hope that the macro i posted above will work for you, it will let the user select from the concatinated list, then it splits that into 2 different cells.

**e4excel** · 03-14-2012, 01:49 AM

Thanks Jaslake,

Thats exactly waht I was lookking for, I think the 2-3 days have been really luck for me as i have not only quick respnses but also solutions which are Multi-useful..

@ DGanon- Thanks, I will be improvisng the code with Jaslakes code..and adding it to your code..

PHP Code:


Option Explicit

Sub Find_Col_Num()

'returns the Column #  of myMg

    Dim myMG As Long '--------------------- This can be "i" in DGagnon's Code

    Dim Mg As String

    Mg = "Town"

    myMG = WorksheetFunction.Match(Mg, Rows("1:1"), 0)

End Sub

PHP Code:


            Range(Cells(i, Target.Row).Address).Value = Split(Range(Cells(i, Target.Row).Address).Value, " - ")(1)

            Range(Cells(i + 1, Target.Row).Address).Value = Split(Range(Cells(i + 1, Target.Row).Address).Value, " - ")(0)

But How DO i get the Last Filled Row No? the way we do in FOrmula approach like COunta(C:C) as I want this Data-Validation feature to be available till the Row No of the cells filled in the COlumn C..

PHP Code:


Target.Column = 8 And (Target.Row >= 2 And Target.Row <= 11) Then

Thanks both of you...

Warm Regards
e4excel

**e4excel** · 03-13-2012, 02:36 AM

Wow Dganon, that was very quick, Impressive must say that..

Thanks a lot but can you please make the code dynamic as I woul need this for expanding data and not just till the 11th Row as in the Sample Sheet.
One thing I need to mention is that I would be treating the data differently like I am planning to have a city Name criteria before this selection so if I select the City Mumbai then I get only the List for all Towns from Mumbai but going further I decided to display only those Town with a particular Alphabet so if I manage to do that with formulas, then that would not make any difference to the VBA Code right as that gets executed only on the change event.

I am asking as I am not good in VBA..

e4excel

**DGagnon** · 03-13-2012, 12:07 PM

Originally Posted by e4excel

Thanks a lot but can you please make the code dynamic as I woul need this for expanding data and not just till the 11th Row as in the Sample Sheet.

all you need to do is modify this line to fit where you actualy want the formula to exicute

Target.Column = 8 And (Target.Row >= 2 And Target.Row <= 11) Then

Originally Posted by e4excel

One thing I need to mention is that I would be treating the data differently like I am planning to have a city Name criteria before this selection so if I select the City Mumbai then I get only the List for all Towns from Mumbai but going further I decided to display only those Town with a particular Alphabet so if I manage to do that with formulas, then that would not make any difference to the VBA Code right as that gets executed only on the change event.

For this, you can use the cascading dropdown method, i would recomend putting your named ranges in a second hidden sheet, and it shoudl work exactly how you want, see the attached

**e4excel** · 03-14-2012, 02:02 PM

No No what I means is that using VBA I need to get the Data Validation Drop-Down till the Rows of the Column C..

Example from your code, the 11 in the Last part of the below code I need to have it as a variable in such a way that the value is always the Last Filled Cell in the Column C like Counta($C:$C)

PHP Code:


Target.Column = 8 And (Target.Row >= 2 And Target.Row <= 11) Then

Changed to

PHP Code:


Target.Column = 8 And (Target.Row >= 2 And Target.Row <= LastRow) Then

where LastRow is variable Defined to store the COunta($C:$C) , however I dont know how to do that..?

I think I will manage this now..

Thanks DGagnon, I will require some more help in my new thread..

Warm REgards
e4excel

**teylyn** · 03-13-2012, 05:25 AM

tomjjlcu, Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Also, please don't plant links to commercial sites in your posts. If you do again, you'll be facing a time-out.

**e4excel** · 03-13-2012, 12:43 PM

Good Morning DGagnon..!

I am really finding this very useful thanks a lot, I will be starting a new thread using the same file with your code but one more requirement..
I am closing this thread as I am really very happy of this utility as I see lot of usefulness in this approach..

Anyways before you go, I jsut wanted to clarify

[code]Target.Column = 8 And (Target.Row >= 2 And Target.Row <= 11) Then[code]

No, what I meant was I dont want to hard-code it to Column No = 8 rather get the column no using the fixed column heading like the Match Function where we do in Formula-Style approach.

Instead of writing 8 directly, I want to use the MATCH("UNIQUE COLUMN_NAME, $1:$1 , 0) but I dont know how to write the same in VBA and especially in your code..

The starting Target.Row is 2 is fine...
Also I want the Target.Row =11 to be depending on the data filled in a Column C ( Please dont go by this file, this was just an example/sample file) so the moment I enter data in the Column C I want the Drop-Down and the code to expand automatically..

Please i you can help me with this, thanks once agaon..

Warm Regards
e4excel

**e4excel** · 03-13-2012, 12:45 PM

@ Tevelyn - Please also mention which exactly file or heading to refer from the link

**e4excel** · 03-14-2012, 02:51 PM

Okie Dokie thanks, I am set now, I have started a new thread in the same lines of the Drop-Down..

**DGagnon** · 03-13-2012, 12:49 PM

you can use a small loop to find it in this case I will be the column where it found the header. and it is only searching the first 50 columns, you can set that to what ever number works for you.

Dim found As Boolean
found = False
i = 1
Do Until i > 50 Or found = True
    If Range(Cells(1, i).Address).Value = "SERACH FOR THIS" Then
        found = True
    Else
        i = i + 1
    End If
Loop

**e4excel** · 03-13-2012, 02:51 PM

I have amrked the THread as SOLVED but would appreciate a small solution for the last remaining portion..

Thanks in advance

**DGagnon** · 03-13-2012, 03:47 PM

if you wanted to keep with the column numbers, you can do it like this

            Range(Cells(i, Target.Row).Address).Value = Split(Range(Cells(i, Target.Row).Address).Value, " - ")(1)
            Range(Cells(i + 1, Target.Row).Address).Value = Split(Range(Cells(i + 1, Target.Row).Address).Value, " - ")(0)

**DGagnon** · 03-13-2012, 04:29 PM

the loop would calculate the I, im sure it could be done with MATCH in VBA, but i dont have much experience doing that.

**DGagnon** · 03-14-2012, 12:06 PM

this code will give you the last used row, in which ever collumn you start at, it is basicaly the equivelent of clicking cell H1048576 and pressing Ctrl+Up

Range("H1048576").End(xlUp).Row

**e4excel** · 03-14-2012, 01:42 PM

Originally Posted by DGagnon

this code will give you the last used row, in which ever collumn you start at, it is basicaly the equivelent of clicking cell H1048576 and pressing Ctrl+Up

Range("H1048576").End(xlUp).Row

Thanks again, but just to reiterate what I am thinking is that the Drop-Down feature should move alongwith the Last Filled Row in the Column C..

Like If I have like 21 Cells Filled in the Column C then I want the Data-Validation to be present till the 21st Row in the Column H or whichever where I need this Drop-Down correct..!

So this would work right?

Warm Regards
e4excel

**DGagnon** · 03-14-2012, 01:47 PM

you shoudl be able to copy the cell and paste, the data validation should go with it.

**DGagnon** · 03-14-2012, 02:07 PM

ok, sorry i misunderstood, try this

Target.Column = 8 And (Target.Row >= 2 And Target.Row <= Range("C1048576").End(xlUp).Row) Then

Is it possible to get 2 Column DropDownList for selection but populate in seperate columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Is it possible to get 2 Column DropDownList for selection but populate in seperate columns

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Re: Is it possible to get 2 Column DropDownList for selection but populate in seperate col

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions