Hi Navop and welcome to the board.
The named range is local scope to the Months sheet, thus;
=VLOOKUP(O5,Months!Mois,2,TRUE)
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
Hi Navop and welcome to the board.
The named range is local scope to the Months sheet, thus;
=VLOOKUP(O5,Months!Mois,2,TRUE)
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
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
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
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
This would make your task much simpler.Code:cell C28 =DATE(P5,O5,N5) Cell C29 ( and copied down ) =C28+1 Cell J24 =MAX(C28:C46)
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...![]()
Bookmarks