Excel 'What if' Data table cannot simulate using data from a different sheet in Windows 11. Behavior works in Windows 10, same version of Excel.

Question

We are operating a large excel sheet, running Monte Carlo simulations (10.000's) using data tables, drawing on data from another sheet where multiple values are dependent on the Trial ID. We have recently experienced an issue where behavior for users have changed when upgrading their Windows version from Windows 10 (business) to Windows 11.

Data Tables cannot perform simulation using data from a different sheet (in same workbook) when using "Calculate Sheet" ("Automatic, except data tables" enabled). Previously, this have not been a problem.

The issue is reproducible in a clean workbook!

Sheet 1 has data, which changes depending on a trial ID. Sheet 2 contains trial ID, and a column of numbers [1..200]. The goal is to calculate a sum across three rows in Sheet 1 - Subtotal(9;C2:C6), and display this sum for each of the 200 Trial IDs using a data table.

Set-up:

1. Create workbook with two sheets.
2. In Sheet 1, in row 1, fill in A1( =Sheet2!$B$2 )
3. In Sheet 1, in row 3, fill in A3("Column 1") B3("Column 2") C3("Column3").
4. In Sheet 1, in row 4, add these formulas: A4( =$A$1+3 ) B4( =A4*2) C4( =SUM(A4:B4)
5. Fill formulas from row 4 to rows 5 and 6.
6. In Sheet 2, in row 2, fill in A2("Trial ID:") B2( "1" )
7. In Sheet 2, in row 4, fill in C4( =SUBTOTAL(9,Sheet1!C4:C6)
8. In Sheet 2, in column B, Fill in numbers 1..200 starting in cell B5.

Intended behavior in Windows 10, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), Each trial is run, and produces different values, depending on the data in Sheet 1.
• If you change data in Sheet 1, return to Sheet 2, and click "Calculate Sheet", each trial is re-run with updated values.

Wrong behavior in Windows 11, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), the first run may produce all 0's for each trial. Following attempts will provide the same value for all trials.
• If you now run "Calculate Now" (to calculate entire workbook), you get correct results as per Windows 10. However, if you now change a value in Sheet 1, go back to Sheet 2, and run "Calculate Sheet", it will again provide the same value for all trials.

We have reproduced good behavior on several Win 10 devices.

We have reproduced bad behavior on several Win 11 devices, leading us to believe the change is related to OS, and not excel version.

Can anyone else reproduce this behavior in Windows 11? Is there a setting we need to change?

This is a major issue for our workflows, as using "Calculate Now" in stead of "Calculate Sheet" means we have to run all simulations (24 data tables á 10.000 trials), rather than selected data tables, leading to each simulation run taking 15-30 minutes rather than seconds.

Theory: My theory is, that some behavior has changed from Windows 10 to Windows 11, where "Calculate Sheet" in excel is no longer allowed to access/calculate dependent fields in other sheets, as part of its calculation, leading to no values changing with trial ID. Why this change happens, I do not know.

Answer 1

Hello MrSpence,

Unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight on your issue.

Thanks, Sneha