VLOOKUP refuses to automatically update

[NickJ]

I have a rather difficult problem.

I have a VBA program that creates a new workbook from two existing workbooks. The sheets are filled by copying named blocks from one of the workbooks.

In one of the blocks I would like a value to be automatically updated after the new file has been created. To do this I entered the formula into one of the cells in the block:

=VLOOKUP(--$D$2,'project information'!$J$7:$O$43,6,FALSE)

the sheet 'project information' is in the other workbook, so before the new file is created this returns #N/A, which is fine. Once the new workbook has been created, the user must enter values into the 'project information' sheet, which should then be updated in the sheets throughout the rest of the workbook. However, it still says #N/A. When I click to edit the cell (but change nothing), however, it updates just fine, so I know the formula is working correctly.

- I have checked that the cells are all formatted as numbers.
- I have checked for spaces.
- The calculations tab is set to automatically update.
- I have tried manually updating by pushing ALT+CTRL+SHIFT+F9.
- I have tried saving and reopening after the workbook was created.
- I have scoured internet forums.

Please help.

Nick.

 

[NickJ]

Just for further information - I have also tried the following formula:


=IF(ISNA(MATCH($D$2,'project information'!$J$7:$J$43,0)),"",INDEX('project information'!$O$7:$O$43,MATCH($D$2,'project information'!$J$7:$J$43,0)))

This gives exactly the same problem. It will update correctly but only once I click in the cell to edit it.

I believe that for some reason, Excel is not realising the existence of the 'project information' sheet, as the sheet did not exist when the formula was originally entered.

I am considering writing a VBA macro to get around this, but it would be nice if I could avoid it...