Here's an example:
Several people are working on a PSD mockup of, say, a website.
Inside this mockup are icons, already appropriately placed & spaced on the page.
Then, we get feedback that one of the icons needs to be different. So, we update the icon itself, possibly as a shared asset, the new file sharing the same name as the old one. From there, is it possible that, when opening the PSD mockup, the icon inside is automatically replaced with the new one because the asset is connected to the cloud?
No. the user still needs to download the asset locally and replace is linked smart object.