+ Reply to Thread
Results 1 to 14 of 14

Replacment for indirect function; external workbook is closed

  1. 11-06-2015, 02:47 PM #1
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Replacment for indirect function; external workbook is closed

    Hi,

    my story: i have 11 workbook for users and one general template, i use vlookup & inderect formula to bring the data to my master file. But this is only works when workbooks are open. I really want to use inderect because i have some conditions behaind (users, position...) when i drag the formula down take this conditions is realy easy to define which work book take to make vlookup. Is there any other strategy to do this with some other formula. I know that you could download add in but this only work for me not is some other users open the master file on his computer.

    Thank you very much for help!
    Reply With QuoteRegister To Reply
  2. 11-06-2015, 02:58 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    I you are unable to use the INDIRECT.EXT add-in (google it); then there is a UDF that you could also use. i don't have time to explain it now. If you're familair with UDFs - you should be able to figure it out. If not, ask again and I'll explain in the UK morning.

    Download both files to your desktop and open the LOOKUP file. Follow the notes... It MUST be opened with macros enabled. You may also need to adjust the path to suit the path used on your own PC.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 11-07-2015, 03:49 AM #3
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    Woow

    You are quick! No i'm not so familiar with UDFs, but ok. I try to use it and it works on desktop.
    But when I want to use internet path, formula stop working. Mybe you know why?
    I have uploaded my excel files on SharePoint.

    Is here any restriction?

    Example: =VLOOKUP($L3;pull("'https://test.net/KZ/["&Y3&".xlsx]Test'!$D:$R");15;FALSE)

    Thank you!
    Last edited by zeko90; 11-07-2015 at 03:55 AM.
    Reply With QuoteRegister To Reply
  4. 11-07-2015, 04:05 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    Does it STOP working, or is it just running VERY slowly? I don't think that there is any reason why it shouldn't work OK. One first check - change the lookup ranges - don't have it searching down 1,048,576 rows!!!! Most of those will be empty and it will slow up your PC. Use realistic ranges instead of D:R
    Reply With QuoteRegister To Reply
  5. 11-07-2015, 04:07 AM #5
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    Quote Originally Posted by Glenn Kennedy View Post
    Does it STOP working, or is it just running VERY slowly? I don't think that there is any reason why it shouldn't work OK. One first check - change the lookup ranges - don't have it searching down 1,048,576 rows!!!! Most of those will be empty and it will slow up your PC. Use realistic ranges instead of D:R
    Turn #REF!
    Reply With QuoteRegister To Reply
  6. 11-07-2015, 04:10 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    Is it possible to post the sheet here (or by email to me if that is better)?
    Reply With QuoteRegister To Reply
  7. 11-07-2015, 04:35 AM #7
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    I would like to, but you will not have authorization to SharePoint.
    I try to do it one more time on desktop and works great

    one more example:
    =VLOOKUP(L4;'https://test.net/[TEST.xlsx]TEST'!$D1:$R100;15;0) - Works great

    =VLOOKUP(L$;pull("https://test.net/["&Y4&".xlsx]E2E Overview'!$D1:$R100");15;0) -"REF! (works only if path is on desktop)
    Reply With QuoteRegister To Reply
  8. 11-07-2015, 04:43 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    I "borrowed" the UDF from a forum, some time ago -- and can't remember where I got it. I'm not good with VBA so don't really follow the code properly. I may be at the end of the road, here...

    However was this an omission::

    =VLOOKUP(L$;pull("'https://test.net/["&Y4&".xlsx]E2E Overview'!$D1:$R100");15;0)
    Reply With QuoteRegister To Reply
  9. 11-07-2015, 04:47 AM #9
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    Quote Originally Posted by Glenn Kennedy View Post
    I "borrowed" the UDF from a forum, some time ago -- and can't remember where I got it. I'm not good with VBA so don't really follow the code properly. I may be at the end of the road, here...

    However was this an omission::

    =VLOOKUP(L$;pull("'https://test.net/["&Y4&".xlsx]E2E Overview'!$D1:$R100");15;0)
    No i have it, just mistype here.
    Reply With QuoteRegister To Reply
  10. 11-07-2015, 04:55 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    I've bumped this thread on another section of the forum, hoping that someone else who has used this UDF will spot what's wrong....
    Reply With QuoteRegister To Reply
  11. 11-07-2015, 05:08 AM #11
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Replacment for indirect function; external workbook is closed

    @ zeko Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  12. 11-07-2015, 07:03 AM #12
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    Understand! Thank you! i remebmer
    Reply With QuoteRegister To Reply
  13. 11-07-2015, 07:04 AM #13
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Replacment for indirect function; external workbook is closed

    Thank very much for your help! could you post me a link to that new one which you are talking about?

    Tnx again!
    Reply With QuoteRegister To Reply
  14. 11-07-2015, 07:28 AM #14
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Replacment for indirect function; external workbook is closed

    Any additional help will be posted on THIS thread.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can indirect() work using closed external files??
    By Hall in forum Excel General
    Replies: 12
    Last Post: 12-28-2015, 03:46 AM
  2. [SOLVED] Automatically open and close external workbook for indirect function
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-03-2013, 01:49 PM
  3. INDIRECT function for closed workbook
    By BogdanH in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-14-2013, 02:50 PM
  4. Indirect and Closed Workbook
    By Bulvers in forum Excel General
    Replies: 3
    Last Post: 10-18-2011, 10:13 AM
  5. Updating Indirect with Closed Workbook
    By robgardner15 in forum Excel General
    Replies: 4
    Last Post: 07-08-2011, 07:26 AM
  6. INDIRECT references to closed external workbooks in 64-bit Excel 2010
    By Hambone70 in forum Excel General
    Replies: 3
    Last Post: 03-20-2011, 03:22 PM
  7. Indirect formula - Closed workbook
    By RichardBerry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2007, 07:32 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1