Excel 2007 : issues with vlookup not calculating

[harry1013]

Hi all,

I have a vlookups that don't seem to be refreshing as I calculate them. They are pulling data from another excel worksheet that is continuously updating with new data. When I use 'calculate now', the excel sheet looks like it's processing but the vlookups don't update. If I double click in the cell formula for the vlookup, and press enter, I get the refreshed data. Why is this and is there any workaround? I will post a sample worksheet if needed.

Thanks!

-H

[harry1013]

The following is the Vlookup used. Thanks!

=IF($C$74="Y",IF(ISBLANK(VLOOKUP("North Pac3 - Zone 12", 'D:\Analytics[Optimize Refresh Interface.xlsx]HOME'!$F:$I,4,0)),"",VLOOKUP("North Pac3 - Zone 12", 'D:\Analytics[Optimize Refresh Interface.xlsx]HOME'!$F:$I,4,0)),"")

[singerj6]

Try refreshing the data connections before recalculating

[harry1013]

my code now looks like this

ActiveWorkbook.UpdateLink Name:= _
"c:\Analytics\sample.xlsm", Type:=xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"c:\Analytics\sample1.xlsm.xlsx", Type:= _
xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"c:\Analytics\sample2.xlsm", Type:=xlExcelLinks
ActiveSheet.Calculate

Range("E1").Select
ActiveCell.FormulaR1C1 = "=NOW()"

Would this work? It's tough to tell when it is and isn't working since I have no idea when the sheet that's constantly pinging and updating data is going.

Thanks in advance,

-H