Dear Excel experts,
Please find the error in my access table and correct it for me. I want to search the details of an individual by either surname or office in my table. I have enter the formula for it but it is not giving me the result.
Waiting to find the solution at the earliest.
Please see the attached file.
Thanking you in anticipation.
Mukesh
You indicate that you have an Access table but have provided an Excel spreadsheet. Where are you trying to get this data? Access? Excel? If Access, what is the SQL statement for your query? If in Excel, then suggest you contact an Admin and move this to Excel forum and show formula that you are using to try and extract data.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hello Alan,
First of all I appologize for giving confusing file name. It's a excel file and not access file. It's a contact directory of my contacts.
I have created a search table to find my contact details quickly by typing only surname in 'c4' and if there are 2 or more persons in my contact list with a same surname then the first name of him will come in cell 'c3' as a dropdown list. I will choose the desire first name and the details of him will displlay in row 6.
I have used the naming function for it. I have created the names of surname, table but unable to create a name for first name in name manager. As a result I am unable to create the dropdown list of first name in cell 'c3'.
Now I have changed the file name as 'contact directory' to avoide confusion in future.
I think this will be helpful to you to suggest me the solution quickly.
I have attached the new file here for your kind perusal.
Waiting to get the solution from you at the earliest eagerly.
Thanking you in anticipation.
Mukesh
Hi,
I'm a bit confused. For a start, your formulas in C6:W6 have a named range "Surnames", though this does not exist (in Name Manager there is a "SURNAME").
I amended this Named Range accordingly and, taking aside your issue of selecting from more than one First Name, I tried manually inputting a Surname appropriate to "Baviskar" into cell C3 ("alan") and your formulas in C6:W6 still produce errors, not to mention Circular References...
I think you need to resolve all these issues first and then think about a workaround for cases involving more than one First Name.
Regards
Hello XOR LX,
Thank you for quick response. In row 6 cell 'c6' the result will display if you type surname in cell 'c4'. I have taken this idea from the phone list on excel forum. I'm attaching this reference file for your reference so that you can understand the logic behind it. I have change the format as per my requirement. So there may be error in my formulas. I want to solve it with the help of you.
Please see the attached reference file to solve my problem. I hope this will clear your doubts and avoide confusion.
Thanking you in anticipation.
Mukesh
Mukesh,
You need to be careful when simply taking formulae from other sources, not really understanding them and expecting them to work in your particular situation.
Anyway, your formula in C6 should be:
=IFERROR(IF(AND($C$3="",COUNTIF(SURNAME,$C$4)<2),INDEX(Access!D$6:D$2000,MATCH($C$4,Access!$C$6:$C$2000,0)),INDEX(Access!D$6:D$2000,MATCH($C$3&$C$4,Access!$D$6:$D$2000&Access!$C$6:$C$2000,0))),"Name??")
This is an ARRAY formula as well, which I don't think you appreciated. Needs to be entered with CTRL+SHIFT+ENTER, not just ENTER. Copy across as required.
Regards
Dear XOR LX,
Thank you for your response. I pasted the formula in 'c6' but still it's not giving me the desire result. I appreciate the array formula which gives the desire results.
I'm totally agree with you to not do anything without understanding the logic behind it but I don't know excel well and unable to create such a excellent contact directory. I like the idea of it so I decided to copy the idea in my contact directory by simply changing the format as per my requirement. I'm extremely sorry for that.
I humbly request you to adjust the formulas and names in name manager to get the desire result in my contact directory. Please help me to fulfil my dream to create a excellent contact directory.
Waiting to here positive response soon. Sorry once again for trouble. Sorry!
Thanking you in anticipation.
Mukesh
Don't know what you're doing wrong. Just tried it again. Typed in "alan" in C3 and bingo.
Just realised that when you attempted to 'copy' this phonelist example, you forgot several important parts, in particular a key element of the First Name Data Validation set-up.
I've added it in now and adapted it to work for your version, but Mukesh I really must emphasize the danger in taking something like this and not knowing what you're doing. Having said that, best of luck.
Regards
Dear XOR LX
Thank you for solving my problem. I really feel guilty to copy anything without knowing the machanism behind it. Still now I don't realize the logic behind it which you have done for me. You have created a separate sheet for multiple names but I want this multiple names in row 7 just below the result in row 6. I have inserted the formula of it in row 7 which is wrong. Can we insert the multiple names formula in row 7 from cell 'b7' to 'w7' respectively? This will remove the third sheet namely multiple names and get the result in 2 sheets only.If you don't mind can you do it for me? I feel myself very guilty to ask it to you but I know my limitation and poor knowledge of excel. It's working very fine and fulfil my dream. Credit goes to you.
Please also give me a brief idea how you created this excellent contact directory so that in future if my contacts goes beyond 2000 I will extend the range of my contact directory upto 5000 contacts.
Finally, Please take some trouble for me to insert multiple name formula in row 7 and send me the final adjusted excellent contact directory to me.
Extremely sorry for trouble. Sorry! Sorry! Sorry!
Thank you.
Mukesh
Mukesh,
But this is not at all consistent with your original request, nor with the phonelist spreadsheet example you borrowed.
What you asked for is for a drop-down menu for First Names to be presented if the Surname entered has more than one match. Quote: “if there are 2 or more persons in my contact list with a same surname then the first name of him will come in cell 'c3' as a dropdown list. I will choose the desired first name and the details of him will display in row 6.” This is precisely what I have given you. You made no mention of row 7, nor of any other row for that matter. Are you now saying that in cases of multiple surnames you in fact do not want to choose which SINGLE person's details to display but rather to show details for ALL people with matching surnames on separate rows???
This is a completely different thing and you could have saved me a lot of time had you been more specific with your request earlier. Your sheet now works precisely as does the phonelist spreadsheet in which direction you pointed me - if this sheet was not precisely how you wanted yours to be, then why did you borrow from it all its formulas?
Regrettably I will have to let someone else take over on this issue now as I feel the amount of work I have put in in this case has not been reciprocated by a level of explanation that I would have hoped for.
Best of luck
Dear XOR LX,
Sorry! But a little confusion. I don't want the details of all the persons having same surname but only I want to display the first names on the same sheet so that I could I understand that there are more than 1 person in my contact list who are having same surname. So that I could select the appropriate first name in the dropdown list in cell 'c3'. I wish to remove third sheet and get the data in 2 sheets only.
Now My problem is solved and I marked this threat as solved.
Please don't be upset because I know the time you have spend for my problem. Really I appreciate you and your knowledge of excel. I personally feel very sorry to read your mail.
Now I have copied the formula from multiple names sheet on search sheet from 'y2:y7' which gives me the desire result. Now I want to change the name in name manager to remove third sheet only. Therefore I humbly request you to mail me the process of first name naming function on my personal email id given below to avoide traffic on forum.
My email id is as follows:
[email protected]
Sorry once again. Please excuse me and try to understand me positively.
Waiting to hear from you on my personal email id positively.
Thanking you in anticipation.
Mukesh
Attached.
Regards
Very good morning XOR LX,
Thank you for taking trouble and solve my problem.
Now I don't have words to appreciate you. I can simply say excellent! excellent! excellent!
Thank you and sorry for trouble once again.
Mukesh
Dear XOR LX,
Now I modify the contact directory as per my requirement but I'm unable to create a dropeown list in search sheet. Please tell me how to create it. One more problem is that I'm not getting all the results of same surname in search. First 2 contacts are missing every time. See the file attached. For ex. there are 3 contacts of 'Baviskar' but only third contact, fourth and fifth first name is in result which should be first, second and third.
Please correct my mistake and tell me where I am wrong so that in future this mistake will not occur. Do give me the step by step instructions to be perfect.
Thanking you in anticipation. Sorry for trouble.
Mukesh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks