|May I suggest that you go in the "opposite direction"?|
Instead of pushing the data from Sheet2 into Sheet1 (which will require a macro) why not pull the data using Data Validation Drop Down lists?
This will save the user from having to go back and forth from Sheet1 to Sheet2, asking themselves "Do I already have that material?", etc.
In addition, if something needs to be changed, the user could simply choose something else from the Drop Down in any given cell and replace one item with another with no need to manually delete the original item. If you used code to add items to the bottom of the list, then anything above it that was deleted would leave a blank row and the sheets could get very messy.
Once the "material" had been pulled into Sheet1 via the drop downs, VLOOKUP could be used to pull all of the associated data into other columns in Sheet1.
You could protect and hide Sheet2 and the users would never have to access it.
Taking it one step farther, I have a workbook that I use to put together the lineup for the Softball teams I coach. It allows you to choose a player for each position in each inning via drop downs. The nice thing about it is that once you select a player in an inning, that player's name is removed from the other drop downs for that inning so that you can not accidently use the same player at 2 different positions in the same inning. If you change your mind and delete a player from a position in an inning, that name will become available in all drop downs so you can place her elsewhere.
I don't know how extensive your materials list is, but this method would prevent to same material from being chosen more than once.
If you are interested is seeing this workbook, send me an email address via Private Message and I'll send it to you. Do not post your email address in this forum.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.