Assistance Vlookup to search entry

[mso3]

Hi friends,
I want to search by 'Reg. no.' and 'Last name'.
For by reg. no. problem:
If The user enter a reg. no. in cell 'B4' then his/her last name and first name should be retrieve in cell 'C4, D4' respectively.
I used 'vlookup' formula but I'm not getting output after entering a reg. no. in cell 'B4'. I have to go in edit mode pressing 'F2' in cell 'C4, D4' respectively to get the output.
If the user deletes the reg. no. from cell 'B4' then the output is not clearing. Again I have to follow the same process to clear it.

For last name problem:
If the user enter a last name in cell 'F4' then I require a dropdown list in cell 'G4' to select the first name match to last name cell 'F4'; a reg. no. in cell 'H4' which match the last name and first name. There may be several persons in list of same last name but their first name may be different or same; so I require a dropdown list for first name in cell 'G4'.

I require a formula or vba to achieve this target. My first preference is to have a sheet code to achieve it after entering a reg. no. or last name respectively in these two search tables. Both are different tables. It's because sometimes the person may don't know his reg. no. then in this case his entry will be search by his last name; if the person knows his reg. no. then his entry will be search by his reg. no. So I have created these two different tables to search the person's entry.

Any help will be highly appreciated.

Thanking you in anticipation.

[alansidman]

Use this formula in H4 and confirm it by pressing the Control, Shift and Enter Keys at the same time

{=IF(F4="","",INDEX(Register!$A$6:$A$11,MATCH(Search!F4&Search!D4,Register!$G$6:$G$11&Register!$H$6:$H$11,0)))}

Do not enter the curly brackets, they will appear when you enter the formula as listed above.

[Winon]

Hello mso3,

An alternative way would be to employ helper Columns and search by full Names instead, as illustrated in the attached sample Workbook.


Regards.

[Winon]

@ alansidman,

Hi Alan,

Impressive formula you have there. Kudus to you!

Regards.

[mso3]

Hi Alan,
Thank you.

The formula is giving error.
How to get the dropdown list in cell G4 for first name?
The first table is not updating without editing it each time. If I delete the reg. no. in cell B4 then the data in cells C4 and D4 should be clear.
If I enter a reg. no. inn cell B4 then the last name and first name should be retrieve automatically without editing it.

Hi Winon,
Thank you.
The sheet is protected so I didn't understand it.

These two tables are different and no any relation in between both. If possible suggest me a sheet code to achieve the a target automatically after enter a reg. no. in cell B4 or after entering a last name in cell F4, selecting first name in G4 dropdown list.

Thank you.

[bakerman2]

This one is for reg.no problem.

Please Login or Register  to view this content.

The rest will follow later (still working so no more time)

[mso3]

Hi Bakerman2,
Fine! Thank you.
Problem:
If the user enters a reg. no. which is not there in register then getting error 13 time mismatch.
If the user deletes the reg. no. in Cell B4 then the 'Update' macro has to run to bring the screen to its normal view.
If the user enters a value which is not in the register then I require a warning message that the reg. no. is not available.

Everything else is fine.

Excellent creation.
Waiting to receive a code for second table.

[bakerman2]

Okay, let's start with this.

[mso3]

Hi Bakerman,
Fine! Thank you.
The first table search by reg. no. is perfect.
If the reg. no. is not found then I require a message as:
Sorry! The reg. no. 'here the entered reg. no. should be' is not available.
For ex. If the user enter 233 then the message should be
Sorry! The reg. no. 233 is not available.

The second table is also fine but I require a little amendment in it as follows:
If the user deletes the last name from cell F4 then both the cells G4, H4 should be blank.
There are about more than 1000 entries in register. So it will be very difficult to scroll a huge drop down list of first names. Requirement: the drop down list should be match last name only using 'Indirect' function as '=Indirect(F4)'.

Everything else is fine.

Thanking you,

[Winon]

Hello mso3,

Thank you for the feedback.

With a little VBA we could do it this way;

Please Login or Register  to view this content.

[mso3]

Hi Winon,
Thank you. It's very laborious to scroll for thousands of names. For last name table 2 I don't want drop down list. It will be enter manually by the user to find the last name entry.

The code suggested by Bakerman2 is fine as per my requirement. Only I need a little amendment in it as mentioned inn my last post.
Please don't mind. I'm waiting to receive the amended code.

Thank you and have a nice time.

[bakerman2]

Second draft.

[mso3]

Hi Bakerman2,
Fine! The first table is working fine. If the reg. no. is not found then the message is perfect but I want to clear the cells C4 and D4. Everything else is perfect for table 1.

For second table:
After entering last name in cell F4 I'm getting error 1004.


Thanking you,

[bakerman2]

This is because you entered a LastName that isn't present in your DB.
Amended code to deal with this issue.

[mso3]

Hi Bakerman2,
Thank you.

The table 1 issue is solve.

For table2:
If I enter a last name in cell F4 then I'm getting run-time error 1004 application-defined or object-defined error on the following line

Please Login or Register  to view this content.

Thanking you,

[bakerman2]

Did you check the file I posted in my previous post.

[mso3]

Hi Bakerman2,
Yes, Thank you.
It's working fine but only the problem is if the user enter a last name in small letters then the error occurs. It should be not case sensitive and if the last name is not available then a message shouuld be as reg. no. table1. Everything else is perfect.

Thanking you,

[bakerman2]

This covers it all.

[mso3]

Hi Bakerman,
Excellent creation. If the user entered a last name in cell F4 and if it's not found then I want clear contents of the cells. G4, H4and select F4. I inserted a code for it but it's not working.
After selecting the first name in cell G4 the cursor should move to H4.

For first table:
If the user entered a wrong register no. in B4 then after message the cursor should stay in B4.

Please Login or Register  to view this content.

After these amendments the problem will be solve. Sorry for trouble.

Thanking you.

[Winon]

Hello mso3,

Hi Winon,
Thank you.
The sheet is protected so I didn't understand it.

The Workbook in Post #3 was protected without a password. Have you tried to Click on Unprotect Sheet?

The code suggested by Bakerman2 is fine as per my requirement. Only I need a little amendment in it as mentioned inn my last post.
Please don't mind. I'm waiting to receive the amended code.

I accept the fact that you don't want me to try and help, and that you want to do it your way. Nothing wrong with that.

But for the benefit of other Forum Members, I would like the publish this VBA free Workbook as an alternative for their consideration.

Thank you so much.


Kind Regards.

[bakerman2]

This is the last one.

[mso3]

Hi Winon,

Please don't mind. I always appreciate you for the help to solve my problems. I don't mean to say don't try or help me. Now the solution you provided is also fine and working fine macro free solution. I appreciate you for your continuous kind cooperation to solve the problems.

Thank you and have a nice day.

Hi Bakerman2,

Excellent! Now it's working fine as per requirement. I appreciate to you for taking trouble to solve the problem in a positive manner.

Thank you and have a nice day.

[bakerman2]

You are more then welcome. Thanks for adding rep points. Much appriciated.
See you on your next project.