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]

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.

They are normal formulas, so don't enter them as arrays. As for the #N/A result, that would be because of a MATCH failing, at a guess. Try to find the cause of that as a first step.

 

[asad]

ok I will try and get back to you.↲and let you know.

 

[asad]

Wow, it actually worked on my home computer. Tomorrow I will try it at my work. I have Excel 2007 at home and 2003 at work. If I have any problem, I will let you know.
One more thing, do I have to install that addin in all the computers at the other location for it to work, or will the addon stay with the workbook?

Thanks a lot for your patience and help.

Asad

 

[GlennUK]

If I remember rightly, if you are using Excel 2003, you can choose to embed the add-in functionality into actual individual workbooks. It should be a menu option. Then you don't have to instal the add-in all over the place.

 

[asad]

Thanks a lot Glenn.

I will get back tomorrow with the answers or questions .

Asad

 

[asad]

Hello Glenn,

I still got the problem at my work. It was not working. So I thought the morefunc installation didn't get done very well. I uninstalled it and reinstalled it but when I try to activate it, this time it gave me an error

Run-time error '1004':
Method 'Select of object 'Sheets' failed.

I then deleted the Morefunc folder, downloaded it again using your link and tried the whole procedure all over again. I got same result and same error message.
What did I do wrong?
Help me please.

Asad

 

[asad]

Hello Glen,
I got the Morefunc addin installed properly in my fifth attempt. And it is working on my work computer.
Thanks for your help with everything.

But, I am still struggling to embedd in the workbook because as soon as I try to do that I get the same error message as before and it stops working altogether. Anything I should be doing before I embedd it in my workbook?

Asad

 

[GlennUK]

Sorry, as far as I know it should work. If all else fails you may have to write your own INDIRECT.EXT function. If you need that, start another thread.

 

[asad]

OK. I will try to resolve this by installing that addin on the computers that will be used to access this file.
Thanks for your patience and help Glenn.

Asad

 

[GlennUK]

My pleasure to help. Good luck!