VBA or IFErrorVlookup?

Copper Contributor


Hi everyone, happy Wednesdays!


I'm wondering if anyone has any advice for my current Spreadsheet. I'm trying to input contact details into sheet1, and have them carry over to sheet2. In sheet2, I am adding additional information about the individuals (what groups they're part of). 


Contact DataContact DataData copied over to sheet2Data copied over to sheet2


I wanted to add an auto-sort onto the names in sheet1 to make sure that when it is populated with actual details, it is easy to find who I'm looking for. I added a VBA code for this.


VBA for auto-sortVBA for auto-sort


When a new name is added, it autosorts the order on sheet1, which then carries over to sheet2. This is fine and as I wanted, but as it adds the new information in sheet2, it adds as a single cell, and not a new row, which means it does not move the rest of the corresponding data. This can be seen here as I added the name Angie. Due to the auto-sort, it came in before Anna and Anne which is great. However, the ticks assigned to Anna and Anne group 1 have remained in the same row now assigned to Angie and Anna.


When adding a new name 'Angie', the data for the other individuals does not adjust for the new infomation addedWhen adding a new name 'Angie', the data for the other individuals does not adjust for the new infomation added


Is there a better way for me to do this and copy the data from sheet1 to sheet2? Or a way I can keep th data in sheet2 together? I thought about an IFErrorVLookup to carry the names over, similar to this, but I can't seem to get the formula right, and have no idea if this is going in the right direction.


=IFERROR(VLOOKUP('Participant Information'!$A$2:$A$19, 1, FALSE), "")


Thanks in advance for any help!! This is quite new for me so just doing my best!



1 Reply



Assuming you have a very current version of Excel, there are a couple of "dynamic array" functions now available--notably SORT and FILTER-- that should do away with the need to write VBA routines to do a lot of what you're apparently doing. Microsoft created a YouTube video that introduces these, and it's a good one (it's where I learned of, and how to use, them.)


Beyond that, I would encourage you, since you're not using full names here, to post a copy of the actual workbook on OneDrive or GoogleDrive, with a link here that grants access to it. Working with the actual workbook is a LOT more helpful than with images.