Auto populating Excel Table from keywords, error checking for defined names, etc

Question

I am attempting to find a solution with either excel or visual basics.

I have an expanding column of remarks on multiple sheets that I need to search through for a keyword (I’m using the word “change” or “chang” to capture “changing” when needed) When this keyword is found, I need it to give back the cell address, along with a defined name I’ve set up (one for each sheet).

I was able to make a table on a “summary sheet” to include the “new comment”, address, and defined name, and it works well enough. My only concern now is if I add a new row and therefore add a new comment in the column on one sheet, I will have to go back and add a row to my table, and make sure the address and defined name are also corresponding to that “new comment”. This will get very messy and errors can occur, especially since I could have 20+ sheets.

So far all I have been using is excel formulas. But I have also been playing around with excel macros to try and accomplish this (with no luck).

Any help would be greatly appreciated! And I of course can explain this better if need be.

Edit: I added a picture of the general basis of what I’m trying to accomplish. I can’t add the code I’ve been trying as it’s on my work laptop- so I made a simple mock-up of my sheet to include here. I took pictures of the highlighted cell in sheet 1 to show the formulas, and then a picture of sheet 2.1