Excel formulas with other sheet references not auto-calculating

I am using MATLAB to create an Excel spreadsheet. Within the data output to Excel, I have Excel formulas. For formulas that do no reference external sheets, they are calculated automatically when the spreadsheet is created (for example, column 'N' in the screenshot is a formula).

However, when I try to reference a different Excel sheet within the formula, the value in the Excel spreadsheet is '#N/A'. In Excel, if I click in the formula and then press enter, the formula works correctly (demonstrating that the format of the formula is correct).

In the screenshot, I clicked in the formula, and then pressed enter for cell P5, so the value is calculating correctly. However, I have not yet done that process for cell P6, so the value is '#N/A'.

How do I make Excel auto-calculate formulas that reference other sheets?

ML code to create the formula for Excel

c(5:end,2*numHex+7+i) = cellstr(strcat("VLOOKUP(D",rowNums,",CRETAinfo!A$2:",...

cdECol,"$",string(height(cretaData)+1),",",string(cdDColNum),",FALSE"));

ML code to write the cell array created to Excel

xlswrite(outFile,c);

Answer 1

[Cole Pratt]

I ended up solving this by using the below code instead of xlswrite().

writecell(c,outFile,'UseExcel',true,'WriteMode','overwritesheet','Sheet','Data');

My ML code to create the formula for Excel did not need to change, nor did I need to use "CalculateFull()" as Sreeram recommended.

Also note that I needed to have this writecell after I had already created the sheets that I needed to reference in the Excel formulas (i.e. I needed to create the "CRETAinfo" sheet before I used writecell() for the sheet with the Excel formulas.

Answer 2

[Sreeram]

Hi Cole,

While I couldn't reproduce this behaviour in MATLAB R2022b, here's a potential workaround to help unblock the workflow.

To force Excel to recalculate all formulas, consider performing a ‘CalculateFull’ via MATLAB’s COM interface. Here is how it can be done:

Excel = actxserver('Excel.Application');
Excel.Workbooks.Open(fullfile(pwd, outFile));
Excel.CalculateFull();
Excel.ActiveWorkbook.Save();
Excel.Quit();
delete(Excel);

The ‘CalculateFull’ method recalculates all formulas in the workbook. More information about this function can be found in the following Microsoft documentation:

https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculatefull

Additional details on using “actxserver” in MATLAB are available here:

https://www.mathworks.com/help/releases/R2022b/matlab/ref/actxserver.html

If this solution does not help resolve the issue, sharing a MATLAB script with a minimal example might help the community to investigate further.

[Cole Pratt]

Hey Sreeram,

Thank you for the response. Unfortunately, though, that didn't work.

It looks like the main issue is that Excel thinks that the calls to other sheets are broken links.

I'm not sure how to make it automatically recognize that these are calls to sheets within this workbook instead of external files.