I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

use an auxiliar cell with vlookup and concatenate funcions combined

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SJAdams4444" escreveu:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

To my understanding, concatenate function combines two strings together??
I want to be able to select the words for the account names in the drop down
list, and then have the numbers corresponding to that Account Name show up,
but for some formatting reasons, I want this done in one same cell. Or if it
has to be more, I want only 1 cell to show, the others can be hidden, but I
want to be able to only interact with the one cell on the worksheet.

"Marcelo" wrote:

use an auxiliar cell with vlookup and concatenate funcions combined

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SJAdams4444" escreveu:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

And to clarify my own mistake, I ONLY want the numbers to show up, I do not
want to see the words in the cell after selecting from the dropdown list

"SJAdams4444" wrote:

To my understanding, concatenate function combines two strings together??
I want to be able to select the words for the account names in the drop down
list, and then have the numbers corresponding to that Account Name show up,
but for some formatting reasons, I want this done in one same cell. Or if it
has to be more, I want only 1 cell to show, the others can be hidden, but I
want to be able to only interact with the one cell on the worksheet.

"Marcelo" wrote:

use an auxiliar cell with vlookup and concatenate funcions combined

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SJAdams4444" escreveu:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

This will work for you, but you will have to excuse my VBA programming, still
getting used to it.
My setup:
Account list is on tab called Data, starting in row 2, with column A being
Account NAME, and column B being Account NUMBER.
Cell D1 on Data tab: =COUNTA($A$2:$A$1000) then defined as NuAcct
Cell E1 on Data tab: ="$A$2:$B$"&NuAcct+1
Cell F1 on Data tab: ="$B$2:$B$"&NuAcct+1
Need to define a list, Insert--Name--Define...
Name in Workbook: AccountList
Refers to: =OFFSET(Data!$A$2,0,0,NuAcct,1)

Next, on your sheet that will have the drop down menu.
Assuming cell A2 has the drop down list
Click in A2, go to Data--Validation.
Allow: List
Source data: =AccountList

Right click on your tab name, and choose View Code.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look1 As Range
Dim Look2 As Variant
Dim Look3 As Range
Dim TblLkUp As Range
Set Look1 = ActiveSheet.Range("$A$2")
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
If Application.WorksheetFunction.CountIf(Look3, Look1) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Look1, TblLkUp, 2, False)
If Target.Address = "$A$2" Then Target = Look2
End If
End Sub

I am quite sure another VBA user could clean up the code, but it does work.

Hope this helps!

--
John C


"SJAdams4444" wrote:

And to clarify my own mistake, I ONLY want the numbers to show up, I do not
want to see the words in the cell after selecting from the dropdown list

"SJAdams4444" wrote:

To my understanding, concatenate function combines two strings together??
I want to be able to select the words for the account names in the drop down
list, and then have the numbers corresponding to that Account Name show up,
but for some formatting reasons, I want this done in one same cell. Or if it
has to be more, I want only 1 cell to show, the others can be hidden, but I
want to be able to only interact with the one cell on the worksheet.

"Marcelo" wrote:

use an auxiliar cell with vlookup and concatenate funcions combined

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SJAdams4444" escreveu:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

That code looks plenty good to me considering that it worked and definitely
does exactly what I wanted it to!

Thanks!!!!

"John C" wrote:

This will work for you, but you will have to excuse my VBA programming, still
getting used to it.
My setup:
Account list is on tab called Data, starting in row 2, with column A being
Account NAME, and column B being Account NUMBER.
Cell D1 on Data tab: =COUNTA($A$2:$A$1000) then defined as NuAcct
Cell E1 on Data tab: ="$A$2:$B$"&NuAcct+1
Cell F1 on Data tab: ="$B$2:$B$"&NuAcct+1
Need to define a list, Insert--Name--Define...
Name in Workbook: AccountList
Refers to: =OFFSET(Data!$A$2,0,0,NuAcct,1)

Next, on your sheet that will have the drop down menu.
Assuming cell A2 has the drop down list
Click in A2, go to Data--Validation.
Allow: List
Source data: =AccountList

Right click on your tab name, and choose View Code.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look1 As Range
Dim Look2 As Variant
Dim Look3 As Range
Dim TblLkUp As Range
Set Look1 = ActiveSheet.Range("$A$2")
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
If Application.WorksheetFunction.CountIf(Look3, Look1) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Look1, TblLkUp, 2, False)
If Target.Address = "$A$2" Then Target = Look2
End If
End Sub

I am quite sure another VBA user could clean up the code, but it does work.

Hope this helps!

--
John C


"SJAdams4444" wrote:

And to clarify my own mistake, I ONLY want the numbers to show up, I do not
want to see the words in the cell after selecting from the dropdown list

"SJAdams4444" wrote:

To my understanding, concatenate function combines two strings together??
I want to be able to select the words for the account names in the drop down
list, and then have the numbers corresponding to that Account Name show up,
but for some formatting reasons, I want this done in one same cell. Or if it
has to be more, I want only 1 cell to show, the others can be hidden, but I
want to be able to only interact with the one cell on the worksheet.

"Marcelo" wrote:

use an auxiliar cell with vlookup and concatenate funcions combined

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SJAdams4444" escreveu:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

Well that certainly is a lot nicer and shorter way than I accomplished things
(basically I just copy/pasted the thing with new variable names for the rest
of the cells I wanted to perform this in.)

Thank you so much for all your help.

"John C" wrote:

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

Well John C I am very much hoping you will return to answer another question.

The last code you gave me works beautifully, but it still runs into one
error...
I can clear contents of the cells on at a time without an error, but if i
try to select more than one cell and clear the contents, it runs into an
error on the
If Target = "" Then NullCheck = "Y"
line, saying that there is a type mismatch.
Is there another way to set the nullcheck that can account for the "multiple
nulls" that arise from doing a ClearContents on multiple cells?


"John C" wrote:

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

I should thank you John C, not only have you helped me with this code, but
you have also got me learning, and I greatly appreciate that.
I actually was able to figure out that problem. If you add in
If isect = 1 Then
******* If Target.Count 1 Then Exit Sub
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0
Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If

The code will work when you are working with more than one cell at a time in
the row.


"SJAdams4444" wrote:

Well John C I am very much hoping you will return to answer another question.

The last code you gave me works beautifully, but it still runs into one
error...
I can clear contents of the cells on at a time without an error, but if i
try to select more than one cell and clear the contents, it runs into an
error on the
If Target = "" Then NullCheck = "Y"
line, saying that there is a type mismatch.
Is there another way to set the nullcheck that can account for the "multiple
nulls" that arise from doing a ClearContents on multiple cells?


"John C" wrote:

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?

Cool. Yeah, my VBA is still pretty bad, and I am learning. I get it to do
what I need done, but beyond that.... :) Happy days!
--
John C


"SJAdams4444" wrote:

I should thank you John C, not only have you helped me with this code, but
you have also got me learning, and I greatly appreciate that.
I actually was able to figure out that problem. If you add in
If isect = 1 Then
******* If Target.Count 1 Then Exit Sub
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0
Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If

The code will work when you are working with more than one cell at a time in
the row.


"SJAdams4444" wrote:

Well John C I am very much hoping you will return to answer another question.

The last code you gave me works beautifully, but it still runs into one
error...
I can clear contents of the cells on at a time without an error, but if i
try to select more than one cell and clear the contents, it runs into an
error on the
If Target = "" Then NullCheck = "Y"
line, saying that there is a type mismatch.
Is there another way to set the nullcheck that can account for the "multiple
nulls" that arise from doing a ClearContents on multiple cells?


"John C" wrote:

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?