INDIRECT - prevent recalculation

timdk

New Member
Joined
Jul 15, 2015
Messages
4
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello,

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

HTH
 
Upvote 0
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.
 
Upvote 0
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 ...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Glad your could fix your problem ...

Cheers

:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top