+ Reply to Thread
Results 1 to 8 of 8

INDEX(MATCH) is retrieving information from an inaccessible location??

  1. #1
    Registered User
    Join Date
    10-19-2011
    Location
    Barkhamsted, CT
    MS-Off Ver
    Excel 2013
    Posts
    22

    INDEX(MATCH) is retrieving information from an inaccessible location??

    Good day all,
    I use the index(match) formulas on a purchasing workbook. I enter in our internal part number. The formulas then retrieve all the other information...manufacturer, part number, price...from our database workbook.
    Recently we lost the drive that had the database. I had to make a purchase. Thinking I would have to hand type everything in I was surprised when I types in the part number the cell filled in with the information.
    How is it getting this information if it does not have access to the database? I am thinking it is an "internal cache" the file used. Can I retrieve,view all of it?

    Thank you,
    Ken

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    Or is there a (hidden) sheet in your file, with data of the database?




    Use CTRL+T to see the formula's used in the workbook.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-19-2011
    Location
    Barkhamsted, CT
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    Thank you for the reply. It is not a hidden sheet. When I try to open it it gives the message "We can't update some of the links in your workbook right now.
    When I select Edit Links it states Error: Source not found. I attached a sample file. Where it says R00001, if you change it to R00022 you should se the fields to the right change.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    The below formula is in the

    K5=IFERROR(INDEX('\\ls-file01\Quality\DocCenter\[LightStat Database.xlsx]LIPN'!$F$2:$F$10068,MATCH($F5,'\\ls-file01\Quality\DocCenter\[LightStat Database.xlsx]LIPN'!$A$2:$A$10068,0)),"")

    The red text contains the directory of the master file.


    Maybe there is a back up of the data available.

  5. #5
    Registered User
    Join Date
    10-19-2011
    Location
    Barkhamsted, CT
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    I wish there was a backup. Our IT guy did not have one set up. The red text is on a drive which is off site trying to be recovered. It still gets information even when I disconnect the network cable from the computer. From searching it seems it could be a cache issue. It remembers the database somehow. I have been trying to see if I can retrieve that information.

  6. #6
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    just throwing a thought out there, maybe there is a way with VBA to somehow grab this temporarily stored data so that you can rebuild your other file?
    I wouldn't have the slightest idea where to begin with that though
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  7. #7
    Registered User
    Join Date
    10-19-2011
    Location
    Barkhamsted, CT
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    Hello dosydos,
    I was hoping if there was a hidden cache or something it would have all the fields in the database, even the ones not used by the purchase form. For now I am renaming the purchase file and using it to regenerate the basic list.

    Thanks all for the help.

    Ken

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: INDEX(MATCH) is retrieving information from an inaccessible location??

    I was hoping if there was a hidden cache or something it would have all the fields in the database
    Fraid not, it has 3935 rows of data & these are the only columns
    Excel 365 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    2
    ANT00001
    #REF!
    #REF!
    ?W24P-U? Inventek Systems RF ANT 2.4GHZ PCB TRACE CONN MT
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    1.44
    EACH
    #REF!
    #REF!
    #REF!
    3
    ANT00002
    #REF!
    #REF!
    ?HLK-RM04 ESP-07 WAVGAT Store 2.4GHz WIFI Antenna 3dbi Ufl IPX Connector Brass Inner Aerial 29cm Length 1.13 Cable
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    0.6
    EACH
    #REF!
    #REF!
    #REF!
    4
    BAG00001
    #REF!
    #REF!
    WGB1308173(F40305DT) GLOBAL INDUSTRIAL Double Track Seal Top Bag 5" x 3" 4 Mil 1,000 Pack
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    33.95
    BOX/1000
    #REF!
    #REF!
    #REF!
    Sheet: Sheet1

+ 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. Excel index match retrieving multiple columns of data
    By Rjoiner in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-05-2021, 02:42 PM
  2. Replies: 1
    Last Post: 08-22-2020, 04:47 PM
  3. Vlookup or Match index for retrieving multiple data for same id
    By dilipsny in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2018, 08:31 AM
  4. [SOLVED] Retrieving data - MATCH INDEX or multiple IF statements?
    By REV18 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2018, 04:32 AM
  5. [SOLVED] Retrieving multiple results with INDEX MATCH?
    By alinahaylie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-12-2017, 11:09 AM
  6. Index/Match or Vlookup for retrieving data from rows?
    By awcwa in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-05-2013, 01:52 PM
  7. Replies: 4
    Last Post: 07-16-2012, 06:22 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