1

I have a very large Excel workbook (25+ worksheets, 15+ VBA modules) for financial analysis.

I've noticed that one of the formula doesn't display a value until I manually save the workbook 2 - 3 times. Before saving it will appear as a dash (-). After saving 2 - 3 times, the values appear as expected.

I have put in a few ActiveWorkbook.Save in my macro runs already but they don't seem to affect this case.

The formula in question is (for one cell):

=OFFSET(THIS_CELL, 0, -1)+INDEX('ExternalWorksheet'!$A$1:$CR$300,MATCH("Some Term",'ExternalWorksheet'!$A:$A,0),MATCH($B5,'ExternalWorksheet'!$1:$1,0))+INDEX('ExternalWorksheet'!$A$1:$CR$300,MATCH("Some Other Term",'ExternalWorksheet'!$A:$A,0),MATCH($B5,'ExternalWorksheet'!$1:$1,0))+INDEX('ExternalWorksheet'!$A$1:$CR$300,MATCH("Yet Another Term",'ExternalWorksheet'!$A:$A,0),MATCH($B5,'ExternalWorksheet'!$1:$1,0))-OFFSET(THIS_CELL, 0, -17)

(I know it is unruly, I didn't create it, I'm just the programmer ;) )

I am using the THIS_CELL trick as described in this other SO post because this formula appears in multiple 'tables' in this worksheet.

My client isn't pleased about having this column appear as dashes and the columns that depend on it appearing as N/A until he saves. From what I can tell, this behaviour is related to the usage of THIS_CELL (or the OFFSET) as if I remove the OFFSET the cells update as expected. However, I can't see how to get rid of it as I have to repeat this formula over multiple 'tables' in the worksheet and have them reference the correct cells. I also can't switch to RC notation as my client doesn't like it.

Community
  • 1
  • 1
BeardedCoder
  • 323
  • 4
  • 15
  • 1
    Since `OFFSET` is a *volatile* function, that should be re-calculating on *every* calculation cycle, not just ones that affect its outcome. Is 'ExternalWorksheet' the name of a local worksheet or a worksheet in an external workbook? If it is local and pulling values from a closed external workbook, look into Excel Options ► Advanced ► When calculating this workbook ► Update links to other documents and Save external link values. –  Feb 17 '15 at 01:06
  • 'ExternalWorksheet' refers to a worksheet in a different workbook. I already have both Update links and Save external link turned on. – BeardedCoder Feb 17 '15 at 01:47

2 Answers2

1

You can modify THIS_CELL trickery to remove dependency on the OFFSET function altogether by changing the initial selected cell to produce the offset necessary. The reason that the first step in those directions states select cell A1 (this is important!) is so THIS_CELL always refers to the cell you selected. You don't want the cell that you selected. You want the one either 1 or 17 columns to the left.

(stolen and modified from Create a named formula called THIS_CELL)

  1. In the current worksheet, select cell R1 (this is important!)
  2. Open Name Manager (Ctl+F3)
  3. Click New...
  4. Enter "THE_CELL_BEHIND_ME" into Name:
  5. Enter the following formula into Refers to: =!Q1

NOTE: Be sure cell R1 is selected. This formula is relative to the ActiveCell.

  1. Under Scope: select Workbook.

  2. Click OK.

  3. Repeat using the name THE_CELL_WAY_BEHIND_ME and =!A1 for refers to.

  4. Close the Name Manager.

Using =THE_CELL_BEHIND_ME is the same as =OFFSET(THIS_CELL, 0, -1) and =THE_CELL_WAY_BEHIND_ME is the same as =OFFSET(THIS_CELL, 0, -17).

Community
  • 1
  • 1
  • While that worked to remove the OFFSET it didn't solve the problem of the value not appearing until multiple manual saves have occurred. Thank you though, I understand the THIS_CELL magic better now! – BeardedCoder Feb 17 '15 at 02:11
0

I did a comparsion between CTRL+S save and a ActiveWorkbook.Save and in this case they do work differently. No matter how many times I save via VBA it wouldn't update the formulae. CTRL+S appears to be forcing a re-calculation.

From this post I added in

ActiveSheet.EnableCalculation = False  
ActiveSheet.EnableCalculation = True

prior to saving and that has solved the problem.

Community
  • 1
  • 1
BeardedCoder
  • 323
  • 4
  • 15