TEXT(MONTH(TODAY()),"mmm") returns Jan

[almagg]

this formula is returning 'Jan'
should be 'Jul', right?

?

 

[T. Valko]

that works.

maybe you can take a look here:
http://www.mrexcel.com/forum/showthread.php?t=566959

this is what i am working on.
and using the 'Flash' report:

=IF(ISNA(VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$4:$HZ$500,151,FALSE)),"NA",IF(VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$ 4:$HZ$500,151,FALSE)=0,L2,VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$4:$HZ$500,151,FALSE)))

of course the value '151' needs to be replaced with something but that is 13 columns over from the start of the June data. and the final VLOOKUP would subtract June from May which is 4 columns to the left of June.

much obliged.

Are you using the MATCH function to generate the column index number for the VLOOKUP?

 

[almagg]

excuse me for not getting back sooner.
i am cleaning up an admin manager's work to make this report easier to deal with.
too many unnecessary links, files etc...
just been staring at these links etc. for the past two weeks slowly tweaking it.

The columns are relative to the range starting at F4

Col1...Col2.............Col?....................................Col151...etc
Oct....Nov.............June................
...........................SubHeader1.....SubHeader2.....Hrs.......etc(17 Subheaders in each month)

so i need to subtract the June Hrs from the May hrs and the May Hrs are 4 columns to the left of the June main header. so it is the value '151', which i put in manually which has to be adjusted. AND the very last VLOOKUP in the formula will be a subtraction: VLOOKUP(June value) - VLOOKUP(May value).

 

[almagg]

oh wait

i guess i can use a combination of MATCH and OFFSET
find the column MATCH for june then OFFSET 1row, 13columns then
subtract the value 1row, -4columns OFFSET from the MATCH.