Results 1 to 6 of 6

Thread: Vlookup error #Name?

  1. #1

    Vlookup error #Name?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Okay just learning this vlookup.

    It worked before but now its gives me the following error ##### (#Name?)

    this is the formula I used Vlookup(O$5$,Mois,2)

    The value entered in O5 is a number between 1 to 12

    I defined a table called Mois on a worksheet called Months

    Mois = range a1 to b12 which is a 2 colum table.

    a1 - a12 = 1 to 12 (number 1 to 12)
    b1 - b12 = Jan, Feb, Mar (is the name of the months)

    so 1 = Jan, 2 = Feb, 3 = Mar .......

    If I edit name I get

    Refers to = Months!$A$1:$B$12
    Scope = Months

    I have attached the workbook if needed

    Thanks
    Attached Files Attached Files

  2. #2
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    Hi Navop and welcome to the board.

    The named range is local scope to the Months sheet, thus;

    =VLOOKUP(O5,Months!Mois,2,TRUE)

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Hi Navop,

    I'm curious... when you defined the name, did you explicitly make it scoped to the one worksheet? By default names are globally scoped... and your formula would work from any sheet.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    made the scope to Months workbook

    But I think I sovled the problem

    Did a new table as above but put the scope to whole workbook

    then I noticed it was not calculating, so look up the section calculation and it was set to manual..for some reason, placed it on auto and everything works fine...thanks for all the help

  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    I posted earlier, but for some reason it didn't get througgh.

    I wondered why you were jumping through such hoops with your dates?
    You could get rid of the values in columns B and D and make the formulae as follows
    Code:
    cell C28
    =DATE(P5,O5,N5)
    
    Cell C29 ( and copied down )
    =C28+1
    
    Cell J24
    =MAX(C28:C46)
    This would make your task much simpler.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #6
    Thanks Roger for info

    Like I said newbie at some formulas, was a friend that gave me other formula

    Will change to new formula, easier to understand

    Thanks again all for helping out...

Posting Permissions

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