+ Reply to Thread
Results 1 to 12 of 12

Vlookup to external worksheet problem

  1. 10-29-2014, 11:07 PM #1
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Vlookup to external worksheet problem

    I have a worksheet that does a vlookup against an external spreadsheet. Within the external spreadsheet, it is arranged into days of the week. For example, there is a sheet called Monday, Tuesday, Wednesday, and etc... Therefore the vlookup references sheet Monday and another cell does a vlookup for Tuesday and etc. The issue is that the external spreadsheet sheets populate as the week goes on. So on Monday, only the Monday sheet appears. When this occurs any of the vlookup data that does not contain sheet Monday changes to vlookup Monday. How do I keep the vlookup references pointing to the correct days of the week and return a 0 value if the sheet does not exist?
    Reply With QuoteRegister To Reply
  2. 10-30-2014, 12:07 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Vlookup to external worksheet problem

    Try this...

    =IF(ISERR(INDIRECT(TEXT(TODAY(),"'DDDD'!")&"A1")),"Sheet Doesn’t Exist","Your Formula Here")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 10-30-2014, 12:13 AM #3
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Vlookup to external worksheet problem

    Hi,
    Welcome to this forum,
    Try using IFERROR formula
    the syntax is
    =IFERROR(VLookup(_, _, _, _),0)
    or
    Provide a sample non sensitive workbook ..
    for attaching the workbook..
    1. click Edit Post on the right bottom corner.
    2. Click Go Advanced.
    3. click Manage Attachments.
    4. Attach file and click Save changes..


    Regards,
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  4. 10-30-2014, 10:00 PM #4
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Vlookup to external worksheet problem

    Thank you both so much for the information. I took the easier formula of the iferror. It appears to be working. I have one last questions. Is there a way to disable the warning "We can't update some of the links in your workbook right now."?
    Reply With QuoteRegister To Reply
  5. 10-30-2014, 10:12 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Vlookup to external worksheet problem

    Just a quick note, INDIRECT() will only work on open workbooks
    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
    Reply With QuoteRegister To Reply
  6. 10-30-2014, 11:03 PM #6
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Vlookup to external worksheet problem

    You can try putting whole path of the source file .. May be it will update the links..?
    like
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Example
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards,
    Reply With QuoteRegister To Reply
  7. 10-30-2014, 11:10 PM #7
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Vlookup to external worksheet problem

    Is there a way to disable the warning "We can't update some of the links in your workbook right now." when the worksheet opens. Everything is currently working on the worksheet with the iferror statement.
    Reply With QuoteRegister To Reply
  8. 10-31-2014, 12:16 AM #8
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Vlookup to external worksheet problem

    Press Alt+E+K and select Startup Prompt and select Don't Display the alert and update links
    Reply With QuoteRegister To Reply
  9. 10-31-2014, 04:11 PM #9
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Vlookup to external worksheet problem

    Awesome, thank you
    Reply With QuoteRegister To Reply
  10. 10-31-2014, 04:18 PM #10
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Vlookup to external worksheet problem

    I have another question. I have the formula "=VLOOKUP(A3,MP!$A$4:$C$101,3,FALSE)" where it is doing a vlookup on cell A3 against worksheet MP. This works, however, I have to copy the data into the sheet that cooresponds to the day of the week I am looking for. There is a external spreadsheet that is called manpower that has different sheets in it as well (Monday, Tuesday, Wednesday, and etc....). What I would like is where I select a drop down box and select the day of the week, ex. Monday and it will find the Monday sheet and return the vlookup function.
    Reply With QuoteRegister To Reply
  11. 10-31-2014, 10:18 PM #11
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Vlookup to external worksheet problem

    Say Drop Down is in Cell H1 then Respective formula would be..
    =VLOOKUP(A3,INDIRECT("'[manpower.xlsx]"&H1&"'!$A$4:$C$101"),3,FALSE)
    Note:-
    The manpower.xlsx should be open simultaneously
    Reply With QuoteRegister To Reply
  12. 11-01-2014, 11:37 AM #12
    okcjeremy
    okcjeremy is offline
    Registered User
    Join Date
    10-29-2014
    Location
    Okc
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Vlookup to external worksheet problem

    ok, awesome. I will run a macro to open and update the content of the sheet. Thank you so much for the help
    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. [SOLVED] Text to Column, Vlookup, External Data Problem
    By ft9a in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-14-2014, 03:31 PM
  2. worksheet function vlookup . i have problem with datatype mismatch
    By sakvel79 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2013, 11:28 AM
  3. Problem when trying to update a cell in an external worksheet that is already open
    By BazzaBit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2013, 06:55 PM
  4. [SOLVED] Indirect Vlookup into external file, specifying a tab contained in a cell in the worksheet
    By debbi56 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2012, 06:47 PM
  5. vlookup table in external worksheet
    By KarenF in forum Excel General
    Replies: 5
    Last Post: 08-08-2006, 05:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1