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)

Note: With INDIRECT, you must reference only open workbooks

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