-
Aug 5th, 2019, 06:01 PM
#1
Thread Starter
Member
Formula for Vlookup to get two same value
Hello Everyone,
I've a dashboard...in that first column has projects and second column has name of the employee. Some of the projects has done by same employee by various date.
For ex. In sheet2
Column1 Column2. Column3
Project 1 Name1 1/8/2019
Project 1 Name1 2/8/2019
Project 1 Name1 4/8/2019
Project 2 Name 2 2/8/2019
Project 2 Name 2 5/8/2019
I have a drop down in sheet1...if i select project1 on that...all the employee name's should be displayed on next column..like this
A2
Project 1
D5
Name1
Name2
Name3
Can anyone help me to make this.
Thanks in advance.
-
Aug 6th, 2019, 03:46 AM
#2
Re: Formula for Vlookup to get two same value
i really do not understand what you want
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 6th, 2019, 05:53 PM
#3
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Hi Westconn
Actually In my Dasboard, some project has done by the Same person but different Task (Task is not in Default Letters). I want to fetch the list of person who all are working in the Specific Project.
Please see the sample.
Sample.zip
Thanks
-
Aug 8th, 2019, 03:23 AM
#4
Re: Formula for Vlookup to get two same value
you can test if this works for you
Code:
Set mn = Sheets("main")
fname = ThisWorkbook.FullName
Set cn = CreateObject("ADODB.connection")
Set rs = CreateObject("adodb.recordset")
With cn
.Provider = "microsoft.ace.oledb.12.0;data source=" & fname & ";" & _
"Extended properties='Excel 12.0; header = yes;imex=1'"
.Open
End With
Sql = "select task,responsible from [dashboard$] where project = '" & mn.Range("c5") & "';"
cn.Execute
rs.Open Sql, cn ', adOpenStatic, adLockReadOnly
rs.movefirst
mn.Range("d13").CopyFromRecordset (rs)
rs.Close
cn.Close
currently this is set to read the project from c5 and populate the range below D12:E12
if the code is in someother workbook, the workbook objects will need to be modified to suit
appears to work correctly without error
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 8th, 2019, 06:56 PM
#5
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Hi Westconn,
Thanks for your code.. it's working fine for first name (first cell)...but i want to get all the names and task for the specific Project...is it possible.?
Thanks
-
Aug 8th, 2019, 06:57 PM
#6
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Hi Westconn,
Thanks for your code.. it's working fine for first name (first cell)...but i want to get all the names and task for the specific Project...is it possible.?
Thanks
-
Aug 9th, 2019, 03:07 AM
#7
Re: Formula for Vlookup to get two same value
i changed cell C5 to project 3 and got the result as you displayed above
to get the name in first column, change the select to
Code:
Sql = "select responsible, task from [dashboard$] where project = '" & mn.Range("c5") & "';"
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 9th, 2019, 09:07 PM
#8
Thread Starter
Member
Re: Formula for Vlookup to get two same value
I got an Error on below mentioned line..Do i need to Check anything in Tools-->References option?
Thanks
-
Aug 9th, 2019, 10:53 PM
#9
Re: Formula for Vlookup to get two same value
oops that line was something i was testing, should have been removed before posting
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 11th, 2019, 06:47 AM
#10
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Thank you so much Westconn...
It's working perfectly...
One more thing...if i want to update end date column from dashboard sheet along with name and task...for that where/how i need to change in VBA
Thanks again.
-
Aug 11th, 2019, 07:14 AM
#11
Re: Formula for Vlookup to get two same value
you can try
Code:
Sql = "select responsible, task, end date from [dashboard$] where project = '" & mn.Range("c5") & "';"
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 11th, 2019, 07:44 AM
#12
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Yes... I've tried the same thing already...but i got an error like..." Method Open' of object'_Recordset' failed.
On this line
Code:
rs.Open Sql, cn ', adOpenStatic, adLockReadOnly
-
Aug 11th, 2019, 04:33 PM
#13
Re: Formula for Vlookup to get two same value
Code:
Sql = "select responsible, task, [end date] from [dashboard$] where project = '" & mn.Range("c5") & "';"
tested OK, but you may need to format the date cells
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 11th, 2019, 10:43 PM
#14
Thread Starter
Member
Re: Formula for Vlookup to get two same value
Thank you so much westconn..
It's working fine now...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|