How to nest references?

What I'm wanting to do is essentially use a reference to update yet another reference. In more detail, I want to retrieve a value on a worksheet and use that value to alter yet another reference to another worksheet.

For example, I want to retrieve a value (let say its 16 from either a local cell or a cell on another worksheet) and then retrieve yet another value from another worksheet based upon that value (lets say the worksheet I would want is then "Subject 16").


I've attempted various nesting formats of the typical reference formula but obviously have had no luck. Would I need to use VBA or is there a simple solution that I am missing? If I do have to use VBA how should I go about doing this? Thanks in advance. I'm sure there is a simple solution for this but I only have marginal experience with Excel.