200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have attached the workbook. For some reason the cust id and mobile in sheet 2 are not retrieving the value?
I also want that, if a new name is typed then it should add the cust id, name and mobile to sheet3. if it is not possible then I can add a button to it.
Waqar
There are two problems with your formulas in Sheet 2, the refer to the name " SRC" (but the defined name (twice) is SR) and VLOOKUP won't really work here- better to use INDEX/MATCH.
In the revised file attached, I've added an extra column to your table (for IMEI). Having selected that, I went to the Name Manager section of the ribbon and picked "Create from Selection"- that create Names for each of the columns (I picked the top row as name sources). The Named ranges then become Cust_ID, Customer, Mobile and IMEI. They have the same sizes and are expandable.
That means the formulas can all very similar and easy to understand. The MATCH bit picks to correct row (for the selected Custmer from the dropdown) and the INDEX reurns the value from the named range so they are like this (differences in bold):
=INDEX(Cust_ID,MATCH(C3,Customer,0))
=INDEX(Mobile,MATCH(C3,Customer,0))
=INDEX(IMEI,MATCH(C3,Customer,0))If you're not sure how INDEX/MATCH works, look in the Tutorials section.
I replaced the dropdown with an ActiveX ComboBox (which allowed me to set the ListFillRange as Customer - much neater than a column like Sheet1!B:B which has the disadvantage of potentially thousands of blank entries).
Please try the file and confirm it works. Note however that I deleted the other defined names (some linked and not accessible to me) so you might need to restore them if you have the need.
I think you'll need to add a button to transfer new names to a sheet (or add them manually to the table).
Hope this works for you.