Referencing

Is there a way to automate filling in a cell with the tabname where the information was referenced? I have attached an example file. I’ve been working on trying to figure this out for weeks, so any help or ideas would be more than appreciated!!
AutoReference.xlsx (1.7 MB)

If anyone could answer to the feasibility of this it would be appreciated.

Anyone out there that could provide some insight?