Variable cell referencing a worksheet

I have several payroll worksheets that are named for each specific employee's name. For John Doe the sheet is named Doe.J. When I have a new employee, I add a new sheet, copy/paste a template to the new sheet, I type in the guy's name, change the name of the new worksheet to fit the new employee's name, then I put the new employee on a sheet that is a collection of all employees. The new employee goes to the bottom of the list on the collection sheet. In adjacent cells I have a formula that references the cell on each specific employee's own sheet.

John Doe is at the bottom of the list in cell A25
In B25 is his hire date.
In C25 I have the reference =Doe.J!B$17

Is there a way to do the formula in C25 automatically?

Originally Posted by 6StringJazzer

Rioran has done the hard part but I think you need one more step:

Formula:

Please Login or Register  to view this content.

I get a #REF! error

Maybe this will help clarify what I am after.

I've attached a sample file. In cell C4, how do I get it to pick up the information from B17 of Smith.J and do it automatically?

Try

Formula:

Please Login or Register  to view this content.

This will only work if all your worksheets are named using the format [Last Name].[First Name Initial] though

Originally Posted by DMTD

Try

Formula:

Please Login or Register  to view this content.

This will only work if all your worksheets are named using the format [Last Name].[First Name Initial] though

That works perfectly. Thanks!

Excellent

The other formulas submitted above should work fine too - there was just an extra "." being added to the end of the string (indirect was referring to Doe.J. not Doe.J) and the cell at the end was pointing to A1 not B17 in your timesheet.

Does this help?

Please Login or Register  to view this content.