Data Validation - select AcctName and AcctCodes shows

  • Thread starter Thread starter SJAdams4444
  • Start date Start date
S

SJAdams4444

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:
 
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.
 
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
 
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!
 
That code looks plenty good to me considering that it worked and definitely
does exactly what I wanted it to!

Thanks!!!!
 
OK last part....

After using this I have found that if you go to clear the box with the data
validation, it gives you an error

RunTime Error1004
Unable to get the VLOOKUP property of the Worksheet Function Class.


It highlights
If Application.WorksheetFunction.CountIf(Look3, Look1) = 0 Then
--> Look2 = Application.WorksheetFunction.VLookup(Look1, TblLkUp, 2,
False)
If Target.Address = "$F$3" Then Target = Look2


When you wrote to select View Code you had "Option Explicit" underneath it...
I am unfamiliar with this connotation, what does it to and where does it go?
And does it have something to do with this error???
 
As I stated, I am relatively new to VBA, need a check in the VBA to check and
see if the cell is null.
I used the following code for my example originally stated, modify your cell
references as needed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look1 As Range
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
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"))
NullCheck = "N"
If Look1 = "" Then NullCheck = "Y"
If NullCheck = "N" Then
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 If
End Sub
 
I too and relatively new to VBA, I knew VB before, but VBA is a whole new
monster.

This code seems to work well, so may I pick your brain one more time and ask
if it is possible to expand the code to work on multiple cells.
ie, I have a column of 8 cells that I want this to work in, right now it
only works for the first cell.
 
Assuming your request is to have 7 other drop down lists based off different
tables, you would have to set up each one separately, as far as i could tell,
and you would just add the If sections one on top of the other.

If, however, you are looking for more data based on the now account number,
you could use VLOOKUP based on the account number referencing the specific
table(s).
 
Im sorry I am not being clear enogh today...

I actually want this same thing to run in the cells below the one I have
now...
Actually duplicating your example:
You have cell A2 running this code selecting an account name and inputting
the accont code.
Can I then go choose cell A3, select another account name from a drop down
list...(same drop down list) and have it put the correect code.
Essentially, how do you expand "Active Sheet Range A2" down to A8, but not
having it think you mean A2:A8

I am very sorry I dont know how to explain myself better.
 
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
 
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.
 
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?
 
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.
 
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!
 
Back
Top