Vlookup error #Name?

Navop

New member
Joined
Apr 12, 2011
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Test.xlsx
    15.2 KB · Views: 45

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Navop

New member
Joined
Apr 12, 2011
Messages
3
Reaction score
0
Points
0
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
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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.
 

Navop

New member
Joined
Apr 12, 2011
Messages
3
Reaction score
0
Points
0
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... :)
 
Top