Sheet Won't Auto Calculate

[masouder]

I am working with a multi-sheet workbook. Each sheet includes formulas that reference named ranges from other sheets. Calculation is set to Automatic, but the formulas on one sheet (4 formulas total) do not automatically calculate when I change a value on another sheet. On this particular sheet I have to click Calculate Sheet for the formulas to calculate. All other sheets work properly: when I update any value on any sheet all of the formulas automatically calculate.

Any ideas why this is happening is greatly appreciated.

 

[Anthony47]

A possible situation is that the two workbooks are opened in two separate instances of Excel.
In this case the folrmula that refer to workbook2 named range would include the path & the file name of Workboo2; for example:

=SUM('D:\DDownloads\FileName.xlsm'!NAME1)"

If both "live" in the same session you should read:

=SUM(FileName.xlsm'!NAME1)"

Bye

 

[masouder]

Thanks for the post, Anthony, but that's not the issue. There are no references to other instances or other files.

Anyone have any other ideas?

 

[masouder]

I tested a couple of changes and discovered that when I replaced named ranges to cells on other sheets with standard cell references then the calculations work fine. This is equally as odd as I have used named ranges in formulas hundreds of times with no issues.

Not sure if this helps hsed any light on the issue.

 

[Anthony47]

Do you mean that, for example on Workbook A the Name SampleName is assigned to range Sheet1!A1:A10; now if on Workbook2 you insert a formula in the notation =SUM(WorkbookA.xlsx!SampleName then it doesn't update, whereas if you insert =SUM([WorkbookA.xlsx]Sheet1!A1:A10) then it does update?

Could you share which is the content of the "Refers to" box of a failing name? (use the Name manager in the Formulas tab) And a failing (non updating) formula?

Bye

 

[masouder]

No, this is not between workbooks: it is between sheets in same workbook. Consider this formula from the Calculations sheet:
=MATCH(StartAsOfDate,LookupWeekEnding,1)

The named ranges StartAsOfDate and LookupWeekEnding are located on the Start and Lookups sheets.

 

[Anthony47]

No, this is not between workbooks: it is between sheets in same workbook

OMG, I was tuned on a different scenario...

And the two Names, what do they "Refers to"? (from the Name manager)

Bye

 

[masouder]

StartAsOfDate refers to a single cell; LookupWeekEnding refers to a range in of data in a column. Again, nothing I haven't done hundreds of times before with no issues.

 

[Anthony47]

StartAsOfDate refers to a single cell; LookupWeekEnding refers to a range in of data in a column.

Could you share the "Refer to" of these Names? I should like checking if is a valid "Precedent" of the formula

Again, nothing I haven't done hundreds of times before with no issues.

Just to be tuned: has this current workbook ever in the past worked correctly?

Bye