#VALUE error on linked cells that have OFFSET and VLOOKUP formulas

[rob_sheeds]

Hi,

Read about the Dfunctions and SUMIFS/COUNTIFS not working between linked objects and think my error is the same.
If not can someone please let me know why.
SYMPTOMS: Formulas wont work unless linked workbooks are open. Once open they work and as soon as the sheet is altered after they are closed, they links break.

My formula =OFFSET(('Linked Workbook'!$A$1),0,VLOOKUP(Range,RangeData,2,0)-1) or go to workbook and bring back a ceratin amount of cells to the right of A$1$ based on a lookup formula in the Main open workbook.

Cant see why this should be an issue. Anyone know another formula that does the same thing so I can avoid the error?

Cheers

 

[venkat1926]

On reading msg immediate doubt is
You have written' 'linked waorkbook"
what about sheet name.

when you write the formula it would be better to use the mouse to delineate various ranges in a formula.

 

[rob_sheeds]

Hi, That was for ease of diagnosis......
Real formula....
=OFFSET(('V:\Corporate\Finance\5. Monthly reporting\Management Reporting\1. FY13\12. July\1. Site Cost Reports\1. Original\[SCR July - CRA.xlsx]CRA - Budget'!$G$67),0,VLOOKUP(KMMonth,INDEXVLK,2,0)-1)/1000
Hope that helps

 

[T. Valko]

Hi, That was for ease of diagnosis......
Real formula....
=OFFSET(('V:\Corporate\Finance\5. Monthly reporting\Management Reporting\1. FY13\12. July\1. Site Cost Reports\1. Original\[SCR July - CRA.xlsx]CRA - Budget'!$G$67),0,VLOOKUP(KMMonth,INDEXVLK,2,0)-1)/1000
Hope that helps

The OFFSET function doesn't work on a closed file.

You might be able to use INDEX but it depends on what the VLOOKUP is doing.