Vlookup and data validation I think thats what i need not sure any help would be great

[justinwb]

Good afternoon everyone

I have been working on this sheet for about a month and so far its working
now I have found that there have been a lot of changes and now the simple vlookup does not do the trick I need it to do

I have drivers that come to our warehouse

and I have set it up so when I type in there rego in to Row S
=IFERROR(VLOOKUP(S23,Drivers!A:C,2,FALSE),"") drivers name
=IFERROR(VLOOKUP(S23,Drivers!A:C,3,FALSE),"") drivers phone number

S23 starts at S2 and goes to S62 only 1 needs to be fixed and I just use the same code for each other truck

fills it self out from drivers sheet in side the work book

there are now over 380 drivers names with regos

and now a few of the drivers are changing trucks regos

so now when I driver turns up with a rego already in the sheet it shows the first person on the list

every 3 days I update the sheet with new drivers where there regos are not in the sheet or if there a different name on there so I have records

I would like a way so that when i type in a rego if there is more then 1 person with a match rego

a list will pop up with the names of the drives that have the same matching rego so I can click the right name and then phone number will also change

I have to use the rego as the point cause we have over 50 trucks a day come on site the rego is how our warehouses nows which truck to load up other wise using a mobile number would be the easiest way

I tried to use the build in file uploader and did not work so upload to http://www.filedropper.com/10817


I have spent a few hours looking up youtube and nothing that I can fix will fix this problem and I'm not even sure excel would be able to do this

any help would be Great

JustinwB

[AliGW]

Will you please attach the sample Excel workbook here?

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[avk]

Try with in "R2"

Please Login or Register  to view this content.

In "T2"

Please Login or Register  to view this content.

Ensure shift+ctrl+enter

[MarvinP]

Hi Justin,

I've written some VBA behind your workbook to do the following.
If you have a Rego number next to the driver name, you Double-Click in the Driver Name cell and it will do an advanced filter to give you the dropdown you want.

You will need to Double Click in the Driver Name cell to rebuild the dropdown list.

DoubleClickDriver2AdvFilterDropdown.xlsm

[justinwb]

Thanks MarvinP

the VBA that you have written behind the workbook is Great never thought it would work like that

I was playing around with regos and names with different mix up and it looks like its all running right

The 2nd problem that I now have is on the Phone number side it does not changed to match the new drivers name that gets selected to match up with the driver and the rego
it stays with the first name it found


the excel sheet that I upload on here I had to delete a lot of data cause its a work file
How would I go about importing this to our live sheet

I tried to do the next bit on my own with http://spreadsheeto.com/index-match/

Just could not work out the last bit with matching up the drivers with there rego to give back the right phone number
any added help would be great

Thanks again MarvinP

[MarvinP]

Hi Justin,

VLookup needs the column you are looking in on the left. When you do a =IFERROR(VLOOKUP(S2,Drivers!A:C,3,FALSE),"") you are looking at the Rego instead of the driver name. You have duplicate Rego's so your VLookup function fails. Try using this formula instead:
=IFERROR(VLOOKUP(R2,Drivers!B:C,2,FALSE),"")

Also - I use some Dynamic Named Ranges in my macro example workbook above. Just copying the VBA to a new workbook isn't enough. You'll need to see and duplicate the DNR ranges also. Keep asking if this doesn't make sense.

[justinwb]

MarvinP you are the best
with changing
=IFERROR(VLOOKUP(R2,Drivers!B:C,2,FALSE),"")

I have Changed the row T with the above and it works great

I tried to use a test sheet and try and redo it with copying all the scripts over
and I gettting a debug error Run-time error "1004':
method range of object _worksheet failed



A security warning pops up and says click to enable content
after doing some tests it looks like its for each first time I open the book we have a few computers that access the files do they have to enable the content on each pc on each workbook
the problem I have is I change the names every day to that days date is there any way around that or I just have to enable editing and enable content every sheet that gets opened up

almost there ;-)

Thanks again

[kvsrinivasamurthy]

Worksheet event is used. Range S2:S62

When there is change in S3, Validation is done for R3 cell with list of all drivers names for the Rego number in S3.
After selection if driver name from list in R3, Validation is deleted.

Pl see file

Please Login or Register  to view this content.

[MarvinP]

Hi Justin,

I just got up (morning in Seattle) and see your post. Do you need another answer? If you do then it really helps if you attach your workbook with the changes you have made, so I can see what might be giving an error.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[justinwb]

Thanks everyone for your help
got to love time zones Australia Melbourne

I was trying to get it all set up before I went of on holiday and got so busy today could not fully set it up working
with out a few bugs and did not want to leave it for my work mates with a 90% finished sheet there are 9 sheets in total on the workbook with 1 sheet just set up for vlooking about 18 cells when I type in the truck ID up and matching up each line is fun ;-)

I'm heading off for a 2 week holiday and then will be back at work

When I get back from my holiday I will get the 2 sheets and we can work out where the error is coming from
I will have very limited access to my accounts and will mostly not be able to reply till I get back

I would like to also say thanks to everyone for your help and support with this excel problem that I am having

JustinwB

[justinwb]

HI MarvinP

Just come back from holidays

and had some free time to look over your sheet
It looks like the new sheet we using at work they used a bunch of tables and other stuff that is to hard to copy over to a new book so I have no choice then to copy over the sheet you did to there sheet

I went over the full book and copied all of the Name Manager and the Visual basic and saved the file as a (.xlsm)

there is a bug when I 2x left click a name but going to and it stops the code

error code run-time error 1004
method range of object _worksheet failed

yellow line on If Not Intersect(Target, Range("DriversValid")) Is Nothing Then

I have seen that in the drivers part when a new rego gets typed in cells e1,g1,h1,i1 make a list up all on there own which makes sense so it knows which cells to filter I just cant work out how to get the new book to do that

any help would be great
There must be a step I have missed by using the duplicate the DNR ranges or some thing else not sure


Thanks again

[MarvinP]

Hi Justin,

I think the error is because you have not defined a Dynamic Named Range of "DriversValid" in your workbook yet. When you use the Advanced Criteria and want to filter for multiple Rego, you need to put then in the same column and the column head needs to be the same word as the original table. Read:

https://www.vertex42.com/blog/excel-...ed-ranges.html

https://www.youtube.com/watch?v=21d9R9W-J6M

[justinwb]

Hi Marvin P

I tried to follow the video there is a added cell in E1 with the rego and I could not work out how to add it to the Advanced Criteria

the test sheet that I tried to do was I copied all the Vb and the name Managers to the new sheet

The dynamic Named Range

When I Click on advanced it is auto filled out with
filter the list
list range $A$1:$C$30
Criteria range $E$1:$E$2
copy to $G$1:$I$1

which is the same as the sheet you set up

any advise would be great

JustinwB

[MarvinP]

Hi justinwB,

I think it is time we can see the workbook that you have now, since it has changed from the one you attached above. Describe what you do and what error pops up and what result you expect. If you can't attach a workbook then you need to learn how to debug code using a breakpoint.

http://www.cpearson.com/Excel/DebuggingVBA.aspx

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[justinwb]

Good Afternoon MarvinP

I deleted my test file I must of been doing something wrong and stuffed up a line or 2

I was at work today and sent over the new template we use and copied over all the vb and the Name manager saved the vb as I went added the headers for !Drivers e1 g1,h1,h1
tested out the sheet and it was like it was looping then looked over and found there was a Data validation with list =drivers

saved the book as a macro and tried it and it worked perfectly

I going to do a test run at work on Monday with the added 390 rego lines

I will let you know how it goes

Thanks again for all your help MarvinP

[justinwb]

Good morning Marvin P

Thanks for all the info and support

I have being using the sheet live at work for a few days

=IFERROR(VLOOKUP(R2,Drivers!B:C,2,FALSE),"") from above
I was seeing that some of the phone numbers where being changed to match the drivers name with there rego and noticed it was just using the first drivers name on the list of drivers in B

after looking over the full code I changed it to =IFERROR(VLOOKUP(R2,Drivers!H:I,2,FALSE),"")
Which now points to the new Dynamic Named Range

The more I have being using the sheet the betting i'm getting at finding little changes with the Script and how it works

I think when I was doing all the copying of the scripts over I was doing the Vb first then the Vlookup and then stuffing up the the Name Manager part which was causing the error

Thanks again

JustinwB

[MarvinP]

Hey Justinwb,

I'm glad you are using what we suggested and making it better. Most of the problems we solve on this forum can be done in a few different ways. The way to show someone their answer helped is to click on the "* Add Reputation" under the post that helped. That way we can find you faster and are more motivated to help more. It is like a "thumbs up" like on Reddit or other sites.

If you answer has been solved, you should also edit your first post and change the thread title to "Solved". That lets us know you are no longer looking for help on this question.

[justinwb]

Good afternoon MarvinP

Have been testing this sheet out over the last few days
I changed this line =IFERROR(VLOOKUP(R2,Drivers!H:I,2,FALSE),"") now points to the Dynamic Named Ranges
before it was pointing to the full list and not the Dynamic one and some of the phone numbers where not matching the rego
I have also found out why I was getting problems
I was copying over the VB first now I have used a few sheets and do the Dynamic headers first then then Name manager then the VB code and it works great

Will kept you up to date over the next few weeks
everything is now running great
and saves a good hour plus is paperwork at work

Thanks again for all your help and support

JustinwB