There seems to be something wrong when Excel looks at the location of your file. I tried here and when I open both files, change the formula in the Forecast sheet with a direct link to the other open file it works good.

But I must say I don't understand exactly what you are doing between those two files and with the data in it.

Try to avoid linking to files I think, but open them both and relate them this way to each other.

Hi Hava,

Your formula is using empty cells for the height argument of the OFFSET function. An empty cell will mean zero cells height range, and that will break all formulas. Make sure that you send a value other than 0 to height argument in ALL offset functions and it will work.

Hi all,

Thanks for you help, but at the moment I couldn´t do my task.

Frans Visser, yes when I open both files at the same time, the values appear. But I want to not open the files, because if I have 5 links to other files I have to open all files. I don´t like to do links, but same times it is extremely necessary. For example, when forecasts changes month by month, it is necessary to link to correspondent month to keep correct phasing.

Catalin Bombea, in height in offset I add 1, but when I close the file Actuals values dissapear.

Can help me please?

Thanks

Hi Hava,

You have formulas in Forecast.xlsx file, in Orange sheet, range O2:O10. There formulas are pointing to cells from column P for the Height argument of the OFFSET function. At this moment, in column P you have values only in P1, but the rest of the formulas are pointing to cells P2 to P9, which are empty, but these cells must have a value if you want the OFFSET function to work.

The formula from cell O2 of the same sheet indicated above, with an OFFSET formula referring to Actuals, will not work if the source file is closed.

If the Actuals will always be offset by 1 row and  column, why using OFFSET? You can simply use =[Actuals.xlsx]Orange'!B2 instead of =SUM(OFFSET([Actuals.xlsx]Orange'!A1,1,1,$P$1)), this will work even if the source is closed.

Hi Catalin.

Many thanks for your help. I use offset for YTD values. For example I want YTD 4; changing in P1 =2; I obtain for SKU #1 75.... with sum I couldn´t do this .

Thanks.

Can anyone belo me please ? 

Sorry Hava, I can't follow the discussion which is going on here, don't understand what you're asking. But I am sure Catalin wil come back when he found the time!

You have to bring the data from the external file into a new sheet, then point your offset formulas to this new sheet, instead of pointing to the external sheet.

You can bring data with simple cell references to the new sheet from Forecast, or with Power Query, this way it will work even if the source is closed.