Welcome to the forum.

It won't work the way you want, and it isn't to do with it being on a different sheet. You are going to need to employ the INDIRECT function.

https://www.contextures.com/xlFunctions05.html