Indirect function for worksheet names in Excel?

Question

I am wondering if there is an equivalent to the indirect function for worksheets or workbooks instead of cells. What I mean by that is I am updating a rather large and unwieldy workbook for payroll for a company that has all sorts of people at different hourly rates working on different projects. Each employee submits their payroll info for the month as an excel worksheet. What I want to do is automate the aggregation of each employees hours for each particular project as much as possible. If I could call a particular cell in a particular workbook without changing the formula every time that'd be great.

To put it another way, every month each employee submits their worksheet titled "Name Month". I want to compile these in a workbook and automatically pull the data for a summary page that will list the bills for different projects. I think I could do this if the indirect function could also be used for worksheets instead of just cells, but it can't. Is their an equivalent function?

I do not know VBA.

Thanks

Answer 1

For:

A1 = Workbook

A2 = Worksheet

A3 = Cell Reference

Use =INDIRECT("[" & A1 & ".xls" & "]" & A2 & "!" & A3)

To reference a worksheet with a space you need to surround it in single quotes ('). For:

A1 = Workbook

A2 = Worksheet Name 1

A3 = Worksheet Name 2

A4 = Cell Reference

Use =INDIRECT("[" & A1 & ".xls" & "]" & "'" & A2 & " " & A3 & "'" & "!" & A4)

To reference a particular range in the sheet that you do not have recorded in a cell, simply place it alongside the !:

Use =INDIRECT("[" & A1 & ".xls" & "]" & "'" & A2 & " " & A3 & "'" & "!E34")

Note: With INDIRECT, you must reference only open workbooks