+ Reply to Thread
Results 1 to 5 of 5

External Link Shows #VALUE When Source File is Closed

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    149

    Question External Link Shows #VALUE When Source File is Closed

    Hi, all

    I have this specific formula which links the data from external Excel files. But I noticed whenever I close the source files, the result will show #VALUE and data will only be shown if I open the source files.

    How can I set my files in order for the main file to work without opening the source files? Does it relate to path of files?

    My formula is as below for example:
    Please Login or Register  to view this content.

    Thanks in advance. Appreciate your assistance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,804

    Re: External Link Shows #VALUE When Source File is Closed

    Short answer - with that function, you cant - the source needs to be open.

    What exactly are you trying to do?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    149

    Re: External Link Shows #VALUE When Source File is Closed

    Hi, FDibbins

    This formula is actually similar to the action of transpose. My source file have data in horizontal manner but my main file must be in vertical manner. Since I need the number to be pasted as link as well as transpose so I came out with this formula. Hereby I attached file for example.

    Thanks for the help.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,804

    Re: External Link Shows #VALUE When Source File is Closed

    uplad sample excel file, not zip files, plz. If the files are so big you have to zip them, then you are providing way too much data. We only need enough data to show what you are working with, and what you want.

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: External Link Shows #VALUE When Source File is Closed

    In cases like this I use Power Query to create a connection to the closed workbook, then formulas are written with respect to the PQ data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] External link update error: require the source file to open
    By BNCOXUK in forum Excel General
    Replies: 7
    Last Post: 01-23-2018, 11:47 AM
  2. Accessing data in an external link when you dont have the source file
    By JFNORDEN in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2017, 08:09 PM
  3. Replies: 1
    Last Post: 05-15-2015, 04:31 PM
  4. Refresh Pivots from closed external source
    By ckk403 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-14-2013, 02:27 PM
  5. External Link Shows Formula Not Info
    By nia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2005, 10:35 PM
  6. [SOLVED] SUMIF Returns a #VALUE error when external source is closed
    By Julieeeee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  7. [SOLVED] SUMIF Returns a #VALUE error when external source is closed
    By ghynes in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1