# Need help on INDIRECT

#### Appleboy

##### Board Regular
Hey there,

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

Code:
``=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!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### repairman615

##### Well-known Member
=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

##### Board Regular
Thanks for the reply!

After editting to suit mine this is the formula,

Code:
``=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

Last edited:

#### Appleboy

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

Rich (BB code):
``=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

##### Board Regular
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

##### Well-known Member
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.

Replies
0
Views
425
Replies
5
Views
154
Replies
11
Views
332
Replies
0
Views
224
Replies
1
Views
476

1,195,923
Messages
6,012,314
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back