Results 1 to 2 of 2
Thread: Match Name and update ID
2016-01-12, 09:24 #1
- Join Date
- Jan 2016
- Thanked 0 Times in 0 Posts
Match Name and update ID
This is my first post in this forum and requesting to help.
On daily basis new data is imported in Main sheet and details are...
Column A refers to Worksheet name.
Column B has Tag Number, with that number to search Column A in referenced worksheet (as mentioned sheet name in Col A of Main sheet) for particular record and once found then
Column C has first name,Middle name & last name to match with particular referenced worksheet Column B, for exact match and if matched than copy Column C ID to Column D in Main worksheet,
if partial or first name matched then Display to user for confirmation to update column D in main worksheet with ID for referenced sheet.
In this sample data i have created a output as result sheet.
I have tried to explain but i know this is not sufficient, i have attached the sample data of what i am trying to achieve.
Please some one help creating macro which will ease my task.
2016-01-20, 16:42 #2
- Join Date
- Nov 2002
- New York, New York, USA
- Thanked 19 Times in 19 Posts
Welcome to the Lounge.
I have attached a possible solution that does not use a macro. This method should be more flexible than a macro approach especially if you keep adding new Worksheets. Just remember to create a new Range Name for any new Worksheets
First, I created a Range Name for each and every Worksheet with Data. by Example for worksheet 101 I created the Range Name "MySheet101" which is the Range $A$2 to $C$54. Of course you can expand the range so it will always be large enough to hold the informaiton you will need
Then on the main sheet I created two formulas.
In Col "L" Using information in Col A it creates the Range Name of the desired worksheet..
In Col D I placed a Vlookup formula that includes and Indirect Function. It will go out and find your information.
Lastly, at the top of Main, I put the formula as a label. That label can be copied and pasted anywhere you want and then later copied to D2 and L2 of Main. which can be turned back to real formulas by removing ' at beginning of the formula. Then copy those formulas as many times as needed.
Hope this helps.