Help with indirect function

[asad]

I got the following formula

=IF(INDEX('Leave Approved'!$I$15:$IA$220,MATCH($A16,'Leave Approved'!$I$15:$I$220,0),MATCH(O$2,'Leave Approved'!$I$4:$IA$4,0))=1,-1,IF(OR((TODAY()+7-WEEKDAY(TODAY(),2)) < O$2,ISERROR(VLOOKUP($A16,'[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$BF$8:$BG$202,2,FALSE))),"",VLOOKUP($A16,'[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$BF$8:$BG$202,2,FALSE)))

The part in red need to be replaced by indirect function or something similar so that it convers the date in cell O2 in a way that formula still works.
Is it possible?
Asad

 

[GlennUK]

Are you going to be having several workbooks like that referenced at the same time?

Or is it that you want to point all your formulas at a new source file in one go?

 

[asad]

I will have lot of workbooks in fact one workbook for every week. I need my formula to look at the respective files to pick up the required information.
The row 2 is filled with dates in each cell. The dates are all week ending dates and it will be same in the workbook name. All workbooks will have weekending date as part of their name.
That's why I want to reference like this.
Asad

 

[GlennUK]

INDIRECT will work if you are willing to have all referenced workbooks open at the same time. Otherwise you will need a third-party add-in, like MOREFUNC add-in by Laurent Longre. MOREFUNC had a function named INDIRECT.EXT that does exactly what you want ... are you allowed to load add-ins?

http://xcell05.free.fr/downloads/Morefunc.zip

 

[asad]

I am allowed to download addins but the problem is that this file will be used at different location and they may not have that addin in all the computers.
Is there any other way like any other function or VBA or something that will get the data from those files?

 

[asad]

Hello Glenn,

I am trying to work out how to use that addin you suggested. Here is a post of the worksheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1				RDO As On	RDO As On
2				30/03/2011	29/05/2011	24/04/2011	1/05/2011	8/05/2011	15/05/2011
3	DF064	ADDERLY	ADDERLY, David Allan	40:00:00	52:00:00			0
4	DF002	ADDICOTT	ADDICOTT, Steven Charles	20:00:00	32:00:00			0
5	DF078	AGGETT	AGGETT, Brian Charles	30:00:00	42:00:00			0
6	DF129	AKHTAR	AKHTAR, Tahir Mamhood	15:00:00	27:00:00			0
7	DF032	ALBOUSWEILEM	AL BOUSWEILEM, Mostafa	40:00:00	52:00:00			0
8	DF012	ALATSAS	ALATSAS, Dimitrios	20:00:00	32:00:00			0
9	DF136	AMARASINGHE	AMARASINGHE, Lakshman	15:00:00	27:00:00
10	DF019	AMATYA	AMATYA, Raj Bahadur	40:00:00	52:00:00			0
11	DF125	ANTONOVICH	ANTONOVICH, Glen Anthony	20:00:00	32:00:00			0
12	DF021	ARDON	ARDON, Moises	15:00:00	27:00:00			0
13	DF116	AWAD	AWAD, George	40:00:00	50:00:00			-1
14	DS001	AWAD	AWAD, Michil	20:00:00	32:00:00
15	DF134	BACH. D	BACH, Dominique Thanh Tan	30:00:00	42:00:00			0
16	DF004	BACH. R	BACH, Hung Tan	15:00:00	#REF!		#REF!		1
RDO

Can you please tell me what is wrong with the formula? The answer should be 1 for both H column and I column. But it comes blank with indirect.ext function. I have tried and tried but it comes back as blank.
Any help will be appreciated.

Asad

 

[asad]

Any suggestions please?

 

[asad]

I just found out the formula is working but only when the source workbook is open.
But, indirect.ext function is supposed to work with the closed workbooks as well.
How can I fix this problem?
Any ideas anybody??????????

 

[GlennUK]

Why are you entering these formulas as arrays?

 

[asad]

I thought that that's the way to work with it. As you can see the whole formula includes working with index function as well as with vlookup function. So entred it as an array formula.
If I enter it as a normal formula, it gives me #N/A.