Lookup value in another "random" workbook
Results 1 to 2 of 2

Thread: Lookup value in another "random" workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member wadergirl's Avatar
    Join Date
    Jun 2016
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup value in another "random" workbook

    So, I'm not even sure if this is possible.

    I would like a formula (or maybe could do a macro if needed), but what I'm trying to do is basically this:

    In cell B1:
    1. Take a value from cell A1 in Workbook A.
    2. Search for that value on ANY tab in ANY workbook in file path "P:\shared\2019" which has multiple subfolders (i.e. 02 2019, 03 2019, 04 2019, etc) - workbooks are mostly named by date (i.e. 01.01.19.xlsx) so theres no more than approx. 31 files per folder, but obviously it varies
    3. Return the file name of the FIRST file the value is found on

    In cell C1: same as above, but return the tab name of that file (3 possible tabs on each file)

    In cell D1 & E1: same as above, but with the SECOND file the value is found on

    In cell F1 & G1: same as above, but with the THIRD file the value is found on

    There would be no more than 3 files it would be on and would repeat for all values in column A.
    If this can be done with a formula, I could probably narrow down which subfolder in the file path to search in and just change it for each row as applicable, since I know the approximate frame each value should appear in.

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,018
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value in another "random" workbook

    Hi Wadergirl,
    that sounds like a macro to me, not something you could do in a simple function. Excel will have to open all those files and search in every one of them. In that macro, step 1 would be to list all files in that folder & subfolders, e.g. like so: https://www.mrexcel.com/forum/excel-...-contents.html
    Next step: you could open every file for every search term, but that would be rather slow. Say you have 365 files and 100 search terms, that would mean opening and closing 36500 times, which takes a looooong time. So it's probably easier to open every file once and search for all the terms you want to search for and write those results in a sheet and as step 3 process them into the format you want.
    Hope that gets you started, don't hesitate to post your code if you get stuck.
    Kind regards,
    Koen
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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