Named range self-referencing offset

I'd modify your last formula (for cell C1) to be:

=offset(indirect(cell("address",C1)),0,-1)

to get "Bob" or -2 to get "25".