Log In
Register
Home
December 29, 2016
Offline
Hi All,
I want to report forecasts and so I have many files with first estimate, second ... I link the files but when I close open source, there is a error #VALUE. When I open the links the values appear.
So, in my example I need values in YTD and I change the month and values appeared. So, I used a offset formula (see ORANGE SHEET - FORECAST FILE) ith links for ACTUAL FILE. SO appear #VALUE when I close the other file.
Thanks
VIP
April 21, 2015
Offline
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.
November 8, 2013
Offline
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.
December 29, 2016
Offline
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
November 8, 2013
Offline
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.
December 29, 2016
Offline
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.
December 29, 2016
Offline
Can anyone belo me please ?
VIP
April 21, 2015
Offline
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!
November 8, 2013
Offline
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.
1 Guest(s)
