vlookup and subtotal formulas not updating

[Guest]

I have a problem with an Excel file that until recently has worked well for
years. This file is a personnel master file that has been carried over from
year to year and which computes payroll and benefits for each employee.

The file contains a large number of vlookup formulas that pull data from a
variety of sources. When I carried over the file into 2007, the vlookup
formualas and subtotal functions stopped updating. I am able to force the
update on a cell by cell basis by double clicking in the cell. After I do
this the cell in question provides the correct answer.

I have no idea why these formulas are not automatically updating for new
data. the automatic recalculation box is checked in the Tools -
Option-Calculation dialog box.

I would appreciate any insights into this problem

 

[Guest]

I don't have an answer regarding the underlying cause, but I can offer a
workaround. Put the following code in the worksheet's code section. Select
the sheet, right-click on the sheet's name tab and choose [View Code] from
the list - in 2007 it's about the middle of the popup list. Then copy this
code and paste it into the module and see if that does not help some.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Now that is only going to recalculate things when you initially choose that
sheet, and if it happens to be the sheet that comes up for display when you
open the workbook, the event won't fire.

This code, placed into the same module (the worksheet's code module) would
cause a recalculate of the sheet each time you choose a new cell/group of
cells on the sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

with them both in the module, you should be covered. But again, this is
only a workaround and doesn't actually address the issue of WHY the
conversion from pre-2007 to 2007 caused this situation, especially since
you've confirmed that automatic calculation for the workbook is turned on.

You may want to read the Excel 2007 Help topic titled "Change formula
recalculation, iteration, or precision" and see if it doesn't shed some light
on things also. You say you've been double-clicking cells to get them to
update, and I'm wondering if the keyboard shortcut(s) to force recalculation
are working for you or not:
[F9] = Recalculate all formulas that have changed since last calculation.
[Shift]+[F9] = Recalculate all formulas that have changed since last
calculation in the active worksheet.
[Ctrl]+[Alt]+[F9] = Recalculate all formulas even if nothing has changed.
[Ctrl]+[Shift]+[Alt]+[F9] = recheck dependent formulas and recalculate all
formulas in all open workbooks even if nothing has changed.

 

[Guest]

thanks for the input. To anwer your question, the [F9] shortcut didn't work
but the [Ctrl][Alt][F9] did work. We hadn't tried the other two. I'll let
you know if we sort out the cause of this problem

I don't have an answer regarding the underlying cause, but I can offer a
workaround. Put the following code in the worksheet's code section. Select
the sheet, right-click on the sheet's name tab and choose [View Code] from
the list - in 2007 it's about the middle of the popup list. Then copy this
code and paste it into the module and see if that does not help some.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Now that is only going to recalculate things when you initially choose that
sheet, and if it happens to be the sheet that comes up for display when you
open the workbook, the event won't fire.

This code, placed into the same module (the worksheet's code module) would
cause a recalculate of the sheet each time you choose a new cell/group of
cells on the sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

with them both in the module, you should be covered. But again, this is
only a workaround and doesn't actually address the issue of WHY the
conversion from pre-2007 to 2007 caused this situation, especially since
you've confirmed that automatic calculation for the workbook is turned on.

You may want to read the Excel 2007 Help topic titled "Change formula
recalculation, iteration, or precision" and see if it doesn't shed some light
on things also. You say you've been double-clicking cells to get them to
update, and I'm wondering if the keyboard shortcut(s) to force recalculation
are working for you or not:
[F9] = Recalculate all formulas that have changed since last calculation.
[Shift]+[F9] = Recalculate all formulas that have changed since last
calculation in the active worksheet.
[Ctrl]+[Alt]+[F9] = Recalculate all formulas even if nothing has changed.
[Ctrl]+[Shift]+[Alt]+[F9] = recheck dependent formulas and recalculate all
formulas in all open workbooks even if nothing has changed.

I have a problem with an Excel file that until recently has worked well for
years. This file is a personnel master file that has been carried over from
year to year and which computes payroll and benefits for each employee.

The file contains a large number of vlookup formulas that pull data from a
variety of sources. When I carried over the file into 2007, the vlookup
formualas and subtotal functions stopped updating. I am able to force the
update on a cell by cell basis by double clicking in the cell. After I do
this the cell in question provides the correct answer.

I have no idea why these formulas are not automatically updating for new
data. the automatic recalculation box is checked in the Tools -
Option-Calculation dialog box.

I would appreciate any insights into this problem