VALUE Error When Workbook Updates on Startup

[raymaster98]

Using Excel 2010.

I have a formula in a workbook that links to another workbook and returns a #VALUE! error. The formula is:

=OFFSET(INDEX('\\srvrdata\kenny cantrell$\Kenny''s Folder\Payroll\[DI Staff List.xls]Sheet1'!$D$6:$D$60,MATCH(TRUE,'\\srvrdata\kenny cantrell$\Kenny''s Folder\Payroll\[DI Staff List.xls]Sheet1'!$D$6:$D$60=B3,0)),0,2)

However, when I open the source workbook, the cell updates correctly. How can I get the cell's formula to update when the source data workbook is closed?

Any help would be appreciated.

Thanks, Kenny

 

[Jerry Sullivan]

Hi Kenny,

There are two parts of that formula that don't work with references to a closed workbook:
OFFSET and the Array Formula reference to Sheet1'!$D$6:$D$60=B3

Try this revised version that uses some alternatives to replace those parts...

=INDEX('\\srvrdata\kenny cantrell$\Kenny''s Folder\Payroll\[DI Staff List.xls]Sheet1'!$D$6:$F$60,MATCH(B3,'\\srvrdata\kenny cantrell$\Kenny''s Folder\Payroll\[DI Staff List.xls]Sheet1'!$D$6:$D$60,0),3)

 

[raymaster98]

Thanks JS411. It worked like a charm. I appreciate your help. Kenny

 

[Jerry Sullivan]

You're very welcome Kenny.