• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Link Excel Files|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Link Excel Files|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Link Excel Files
sp_PrintTopic sp_TopicIcon
Link Excel Files
Avatar
Hava Yassin
Member
Members
Level 0
Forum Posts: 41
Member Since:
December 29, 2016
sp_UserOfflineSmall Offline
1
January 26, 2017 - 8:46 am
sp_Permalink sp_Print

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

 

xgtJD2.jpgImage Enlarger

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
2
January 27, 2017 - 7:31 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
3
January 27, 2017 - 10:58 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Hava Yassin
Member
Members
Level 0
Forum Posts: 41
Member Since:
December 29, 2016
sp_UserOfflineSmall Offline
4
February 13, 2017 - 10:21 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
February 14, 2017 - 12:05 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Hava Yassin
Member
Members
Level 0
Forum Posts: 41
Member Since:
December 29, 2016
sp_UserOfflineSmall Offline
6
February 15, 2017 - 2:01 am
sp_Permalink sp_Print

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.

Avatar
Hava Yassin
Member
Members
Level 0
Forum Posts: 41
Member Since:
December 29, 2016
sp_UserOfflineSmall Offline
7
February 16, 2017 - 8:09 am
sp_Permalink sp_Print

Can anyone belo me please ? 

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
8
February 16, 2017 - 8:03 pm
sp_Permalink sp_Print

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!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
February 17, 2017 - 4:25 am
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: David Jernigan, Louis Muti
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
Forum Stats:
Groups: 3
Forums: 24
Topics: 6359
Posts: 27806

 

Member Stats:
Guest Posters: 49
Members: 32336
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.