INDIRECT problem

[smokenack]

I have been trying to integrate the INDIRECT function into a link between two workbooks but have made a total hash of it.

Cell D1 will contain the worksheet name that I am trying to link to.

this is the sort of thing that I've tried.

=\home\super\working time\WTD\[WTDRoster.xls]INDIRECT($D$1"'!E4)

It doesn't work nor do any permutation that I've tried. a simple syntax error hopefully.

thanks for looking

Nick

 

[Aladin Akyurek]

I have been trying to integrate the INDIRECT function into a link between two workbooks but have made a total hash of it.

Cell D1 will contain the worksheet name that I am trying to link to.

this is the sort of thing that I've tried.

=\home\super\working time\WTD\[WTDRoster.xls]INDIRECT($D$1"'!E4)

It doesn't work nor do any permutation that I've tried. a simple syntax error hopefully.

thanks for looking

Nick

=INDIRECT("'\home\super\working time\WTD\[WTDRoster.xls]"&$D1&"'!E4")

 

[smokenack]

Thanks Aladin.I have managed to enter this into the cell but it has produced an invalid cell reference error. Formula evaluation shows $D1 underlined and step in comments 'the cell being evaluated contains a constant' Cell D1 contains 30.01.05

Any more thoughts would be appreciated

Nick

 

[Oaktree]

Indirect will return #REF! unless the referenced is open.

 

[Aladin Akyurek]

Thanks Aladin.I have managed to enter this into the cell but it has produced an invalid cell reference error. Formula evaluation shows $D1 underlined and step in comments 'the cell being evaluated contains a constant' Cell D1 contains 30.01.05

Any more thoughts would be appreciated

Nick

Here WTDRoster.xls. The file path is:

C:\ExcelMisc\smokenack\[WTDRoster.xls]

Note that you don't have a drive specification. Maybe that is the issue if I didn't make a mistake with the INDIRECT formula.

WTDRoster.xls
	A	B	C	D	E	F	G
1	C:\ExcelMisc\smokenack\[WTDRoster.xls]30.01.05
2	C:\ExcelMisc\smokenack\[WTDRoster.xls]
3
4					Grotegenade
5
6
7
30.01.05

The following reads E4 from the sheet named 30.01.05 from within smokenack.xls...

For all this work, the WTDRoster.xls must be open, a requirement when using INDIRECT.

smokenack.xls
	C	D	E	F
1		30.01.05	Grotegenade
2
3
4
Sheet1

E1:

=INDIRECT("'C:\ExcelMisc\smokenack\[WTDRoster.xls]"&$D1&"'!E4")

If WTDRoster.xls should stay closed, try to track down the PULL function by Harlan Grove here on this site or using Google.

Another option is INDIRECT.EXT that comes with the morefunc.xll add-in...

=INDIRECT.EXT("'C:\ExcelMisc\smokenack\[WTDRoster.xls]"&$D1&"'!E4")

 

[smokenack]

thanks I've got it to work by shortening the file path to [WTDRoster.xls] As both workbooks have to be open and reside in the same folder I was unnecessarily complicating matters.

Thanks for your help.

Nick