Sheet name? or Header?

If sheet name:

http://www.mcgimpsey.com/excel/formu..._function.html

In article .com,
michaelberrier wrote:

I've seen 1000 posts on how to base a header on a cell value. I need
to do the opposite. That is to say, if the sheet name is "Dogs", I
need to put that value in a particular cell on a separate sheet. This
can be done with vba or indwelling formula.