Need help on INDIRECT

[Appleboy]

Hey there,

I am wondering if I can use more INDIRECT into this formula?

=INDEX(INDIRECT("'["&CA80&".xlsm]Total'!$B$100:$J$100"),MATCH(B41,IF('[File.xlsm]Total'!$B$98:$J$98=CA1,'[File.xlsm]Total'!$B$99:$J$99),0))

At the moment, the first INDIRECT enable the formula to work perfectly. However, I want to change the remaining two File.xlsm to the same INDIRECT cell too.

Unfortunately, I can't get this to work! Please help thanks!

 

[repairman615]

=INDEX(INDIRECT("'["&CA80&".xlsm]Total'!$B$100:$J$100"),MATCH(B41,IF(INDIRECT("[File.xlsm]Total!$B$98:$J$98")=CA1,INDIRECT("[File.xlsm]Total!$B$99:$J$99")),0))

Does this work?

 

[Appleboy]

Thanks for the reply!

After editting to suit mine this is the formula,

=INDEX(INDIRECT("'["&CA80&".xlsm]Cumulative'!$B$100:$J$100"),MATCH(B41,IF(INDIRECT("["&CA80&".xlsm]Cumulative!$B$98:$J$98")=CA1,INDIRECT("["&CA80&".xlsm]Cumulative!$B$99:$J$99")),0))

However, it return #REF! as the value in the cell. Any idea why?

Edit: I have referred to http://support.microsoft.com/kb/213933 for the INDIRECT. Tried to use example 2 but I do not know how I should approach it for my formula.

P.S: Using Excel 2007

 

[Appleboy]

Problem fixed after carefully checking the formula! You forgotten about the ' sign.

=INDEX(INDIRECT("'["&CA80&".xlsm]Cumulative'!$B$100:$J$100"),MATCH(B41,IF(INDIRECT("'["&CA80&".xlsm]Cumulative'!$B$98:$J$98")=CA1,INDIRECT("'["&CA80&".xlsm]Cumulative'!$B$99:$J$99")),0))

Thanks for all the help!

 

[Appleboy]

Hey there, so sorry about the triple post but I have a new problem now.

When I archive the old data and open up the new one, the old values will become #REF! unless I open the old data file.

Is there a way to store the values so that it will not require me to open the old data again?

Note: Once the data is archive, it will never be use again.

 

[repairman615]

There is no function for formula that will do this.

Using VBA to copy the cells and paste the values seems like it would do.

The formula would be replaced with the result of the formula.

Are these always set in same cell(s)?
List?

I would suggest recording a macro and if that doesn't do exactly as needed, then post the code, along with any details, and we may be able to help.