Excel Find Formula?

I have a workbook (which I think is attached to this post) that needs a Master Total Sheet added (Sheet1 in the example workbook). As you can see Bob, Joe, Jack each have there own worksheet and will have the words "Grand Total" in column A, and a value in column B (it is the grand total from a pivot table).

What I am needing to happen is on Sheet1, Bob is in cell A2, I want to go to the worksheet called Bob, scan down to find the text "Grand Total" offset 1 column, and return the total back to Sheet1!B2, and do that for each person listed.

What would be the formula to achieve that?