INDIRECT - prevent recalculation

[timdk]

Dear mrexcel users,

I am building an annual report for a company in excel. Using VLOOKUP and INDIRECT it pulls financial data from other open workbooks. (each assignment is a separate workbook, VLOOKUP pulls data from each workbook) I am using the following formula:

=VLOOKUP($C6;INDIRECT("'[" & $F6 & $F$1 &"]" & $G$1 & "'!" & $E$1);2;FALSE)

The formula works perfectly. However, if I make any adjustments in the annual report after I closed the other workbooks, excel recalculates and the cel gives a REFF error in all the cells containing the VLOOKUP+INDIRECT functions.

I am aware INDIRECT doesn't work with closed workbooks and I have no problem with opening a workbook when entering data. However, I would like the data to remain there after I pulled it out, without excel recalculating.

Is there any way to turn the data automatically into static values without PasteValues every cell? or perhaps prevent recalculation for just a couple of cells?

Thank you in advance!

 

[James006]

Hello,

May be you could achieve your objective with an event macro ... to set The Worksheet.EnableCalculation property to False ...

HTH

 

[timdk]

Hello,

May be you could achieve your objective with an event macro ... to set The Worksheet.EnableCalculation property to False ...

HTH

Thanks for your reply James006. That would work. However, I don't wish to disable calculations entirely. I just want to stop the cells with INDIRECT functions to get REFF errors when making adjustments in the workbook after I closed the other external workbooks.

 

[James006]

I can understand your point of view ... but as far as I know you cannot be as precise as you would like to ...

Another way to approach your problem would be to do it the other way round ... i.e.

1. disable calculation entirely

2. force calculation on everything ... but your "indirect" cells ...

 

[timdk]

I can understand your point of view ... but as far as I know you cannot be as precise as you would like to ...

Another way to approach your problem would be to do it the other way round ... i.e.

1. disable calculation entirely

2. force calculation on everything ... but your "indirect" cells ...

I didn't know that was possible. How do I isolate the indirect cells from being calculated?
Thanks again James

 

[James006]

You can create a range which would include all your cells without your indirect cells, and give this range a name ...

Then you can force the calculation for this named range ...

Hope this will help

 

[timdk]

You can create a range which would include all your cells without your indirect cells, and give this range a name ...

Then you can force the calculation for this named range ...

Hope this will help

I tried it and it works Thanks a lot James best of luck to you!

 

[James006]

Glad your could fix your problem ...

Cheers