+ Reply to Thread
Results 1 to 3 of 3

Vlook up and #N/A issue

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    herts
    MS-Off Ver
    Excel 2010
    Posts
    17

    Vlook up and #N/A issue

    I have to collate information from advisors within the call center i work. who call on various projects

    I have set up 1 main page with separate tabs with each project, id like to download project call data and paste it into the relevant tab and the information auto fill on the main page

    Currently i have

    =SUM(VLOOKUP($C31,'Belgium Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Canada Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'France Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Greece Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Ireland Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Italy data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Portugal data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'Spain Data'!$A$3:$M$75,4,FALSE)+VLOOKUP($C31,'UK Data'!$A$3:$M$75,4,FALSE))



    i have a feeling i am getting #N/A because the specific advisor may bot be calling on a particular project so it is unable to find that person . as calling on a project may vary day to day i cant set formula to read specific tabs hence including them all in the formula .

    I have a feeling its something very simple im missing . havent attached a spreadsheet as yet, but can if one is needed

    and many thanks for any help

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Vlook up and #N/A issue

    You could put each VLOOKUP function inside an IFERROR like this;

    =SUM(IFERROR(VLOOKUP($C31,'Belgium Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(...
    You might want to ask on the Excel Programming board for a VBA solution.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-23-2012
    Location
    herts
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Vlook up and #N/A issue

    Quote Originally Posted by ChemistB View Post
    You could put each VLOOKUP function inside an IFERROR like this;

    =SUM(IFERROR(VLOOKUP($C31,'Belgium Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(...
    You might want to ask on the Excel Programming board for a VBA solution.
    Ah yes that works a treat i had tried IFError but had my formula slightly wrong changed to

    =SUM(IFERROR(VLOOKUP($C100,'Belgium Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'France Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Greece Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Ireland Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Italy data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Portugal data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Spain Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'UK Data'!$A$3:$M$75,4,FALSE),0)+IFERROR(VLOOKUP($C100,'Canada Data'!$A$3:$M$75,4,FALSE),0))

    thanks very much ChemistB

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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