With regaurds to changing the cell reference..
I've sort of got a hashed together solution

In A1 i write =PrevSheet()
In B1..B100 etc i write H4 , H5 ... H100

In the C colm i put
="'"&$A$1&"'"&"!"&B1
="'"&$A$1&"'"&"!"&B100 etc...

In the D colm i put =INDIRECT(C1), =INDIRECT(C2) ... =INDIRECT(C100)

This gives me the values i want and works but its a bit of messing
about!

Is there any easier ways to do this??