Thanks protonLeah and Marcol.

That does solve the second part of the problem. I should've been more specific on the first part. I want P4 to automatically look inside O4 and determine "C4" as the cell reference. And then I can use the offset function.
So instead of using the formula offset(C4,1,-1), in which case I have to manually type in C4, I want it to be more like offset(find the location O4 is looking at, 1,-1).

Any way to do this?

Thanks again .