Hi Sonny, and welcome to the Excelguru forums!
We could certainly do this using VBA, but I wonder if there might be an easier way.
Have you thought about setting up a table of data in your main worksheet (as you've described above), then just use PivotTables on other worksheets to summarize it? Instead of having to worry about maintaining the code, you'd then just need to go to the other worksheet, right click and refresh the PivotTable to get your info. (Actually, we could write VBA to keep the tables updated too.)
I could upload an example, but I'd need to know what version of Excel you're using.