+ Reply to Thread
Results 1 to 4 of 4

Get value from 'TEXT STRING' of external file's link in a cell

  1. #1
    Registered User
    Join Date
    04-08-2020
    Location
    Dhaka Bangladesh
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    4

    Get value from 'TEXT STRING' of external file's link in a cell

    Dear Sir

    I have a separate Excel Workbook for each of our Sales Center. I have to prepare a summary report for Cash Sales. Cash Sales value is derived from D36 of each worksheet.

    I don't want to manually D36's value in my Cash Sales report by linking external link! Rather I want to create the link text and want to get value from link text. Is is possible to get the value from Text of external link files?

    For your kind consideration I have attached the demo file.

    Will you please reply if you have time?


    Farid
    Dhaka Bangladesh
    Attached Files Attached Files
    Last edited by farid.pq; 06-27-2020 at 08:24 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,258

    Re: Get value from 'TEXT STRING' of external file's link in a cell

    If I understand what you are trying to do, you are describing what the INDIRECT() function does: https://support.microsoft.com/en-us/...1-92b6306fa261 Unfortunately, your example shows that you are using full path filenames, which implies that you intend for these files to be closed, and INDIRECT() can only work on open files. If you have all of your files opened, then INDIRECT() will work just fine for this, but if you insist on having the files closed, then it won't.

    I am not sure exactly how this particular task fits into your larger project, but this seems to be to be something perhaps better accomplished by bringing all of the data into a good database, then using other database tools in Excel to query, pivot, etc. the data.

    Are you committed to the approach you have outlined in the OP, or are you willing to consider other ways to compile and query your data files?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    04-08-2020
    Location
    Dhaka Bangladesh
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    4

    Re: Get value from 'TEXT STRING' of external file's link in a cell

    I am willing to get values having the files closed under my intended approach.

    Thank you

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,258

    Re: Get value from 'TEXT STRING' of external file's link in a cell

    Then the INDIRECT() function will not work for you.

    Power Query/Get and Transform seems like the next best possibility, but I am not familiar with how it works. This tutorial may be useful: https://www.excel-university.com/ret...any-workbooks/

    I know VBA can 1) read text/filename in a cell, 2) Open the file indicated by text, 3) Retrieve data from the file, 4) Close the file, but that still involves opening the file. I suspect that a PQ based solution will be better than a VBA based solution.

+ 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. Formula cell needed -> link to variable external link based on a cell content
    By milea_sorin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2018, 10:00 AM
  2. Cell variable in external external link (hlookup fuction)
    By DorianGrim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2016, 03:11 AM
  3. [SOLVED] Search through cells, if I find a string (external Link), replace it with a formula
    By Vaslo in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-17-2012, 04:41 PM
  4. Use text from cell in formula that contains external link
    By gsb013 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2012, 11:51 PM
  5. How can I link external excel file cell formats?
    By Peter in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-27-2006, 12:25 PM
  6. [SOLVED] How can I link external file cell formats?
    By Peter in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-27-2006, 11:55 AM
  7. External file link
    By superkopite in forum Excel General
    Replies: 6
    Last Post: 04-24-2006, 08:20 PM

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