I wrote a macro function that constructs an array based on 7 adjacent worksheets. The function is recalculated when I open the workbook or when I duplicate (for example) one of the sheets in the workbook. However, it is not automatically recalculated when one of the cells on which it depends is modified. In this case I must systematically trigger its recalculation manually (F9 key on the keyboard). How could I remedy this inconvenience?
If the cells or ranges the macro function depends on are expressed as references then it should be recalculated when needed, i.e. one of those cells changes value or gets recalculated itself. Just like any built-in spreadsheet function. For example =YOURFUNCTION(A1:A9)
Oh I see. Here is the formula which invokes my function : {=DATAFROMDAYSHEETS("D50";SHEET()-1;0;6)}
So, I guess that there is no way that it gets recalculated at each value change of the cells D50.
Thanks for your reply.
Correct. That "D50" is just a literal string value, not a cell reference.
It is not difficult. Just add as a parameter some function that is recalculated whenever the data in the spreadsheet is updated - NOW(), TODAY(), RAND().
For example, {=DATAFROMDAYSHEETS("D50";SHEET()-2;0;6;NOW())}.
The disadvantage of this approach is that your function will actually be calculated for any change in any cell, not just the range that requires recalculation. And this can lead to a significant slowdown in performance.
Thanks for your suggestion. Actually, the parameter “D50” refers to the cell D50 in up to 7 distinct sheets.
Only the macro knows which cells should triggers the invocation of itself! Besides that I don’t want that the macro be run each time any cell in any sheet has been modified. I will continue to use the F9 key after having selected the cell containing the macro.
If the macro is to be triggered on a value change of any cell D50 in a range of sheets, you could add that as a 3D cell range dependency like with SUM(Sheet1.D50:Sheet7.D50), so
{=DATAFROMDAYSHEETS("D50";SHEET()-2;0;6;SUM(Sheet1.D50:Sheet7.D50))}
Note that if columns/rows are inserted before column D or row 50 in those sheets, the dependency will not be adjusted if not all sheets are affected simultaneously.