Hi Spencer,

Thanks a lot for your reply. I did try both the formula and it is giving me a #NAME? error. I am using excel 2003 and do not have analysis tool pack


To give u a bigger picture of the problem

I have two data validation lists one for 2011 (Jan-11, Feb-11, Mar-12, Apr-11)
and second data validation list 2012 (Jan-12, Feb-12, Mar-12, Apr-12)

I do not intend to use both data validation lists . I prefer to use only one data validation list, that is, 2011 . Corresponding month for 2012 should automatically come up when i select the month feom 2011 data validation drop down.

Hope this will further define the problem.

Thanks Spencer. Awaiting ur reply.

Without the analysis toolpak you can use this version of the formula:

=DATE(YEAR(A1)+1,MONTH(A1)+1,DAY(A1)-1)

But as you can see from =EOMONTH(A1,12) things are far easier with it, so if you can install it I would highly recommend it.

Hello Spencer,

had a further look at the problem and managed to find out what went wrong. THe formula u mentioned worked well. I had to align the date format on the data validation list and the date format on the look up range which solved the problem. Jan-12 in the validation list was coming up as - 1/12/2012, Feb-12 as 2/12/2012 and in the look up range, Jan 12- was coming up a 1/1/2012 and Feb-12 as 1/2/2012. If i use the former format , it will give me values from Jan-Jul , and if i use the later format it gives me values from Jan- Jun.

Thanks a lot for your help. This site is wonderful

Glad to be of help. :)

On Mon, 27 Aug 2012 09:17:23 +0000, Excel Dumbo wrote:


Hi Spencer,

Thanks a lot for your repliy. I tried your formula and it worked, it
gives me the result. But, it leads to another problem . I have explained
this below

For Eg Example- Result "Jul-12" is in Worksheet1 cell B1

On Worksheet2 , cell DG25 is linked to the B1 of Worksheet 1

I have a formula below

=SUMPRODUCT(($H$20:$BE$20=$DG$26)*($H$21:$BE$21<= $DG$25)*($H28:$BE28))

which pulls out the cumulative values from Jan to Jul
(H$21:$BE$21<=$DG$25) When I apply your formula mentioned in the earlier
post, my formula in Worksheet 2 gives me values only till June. The big
formula is supposed to give me values from Jan to July and not just Jan
to June.

Hope you understood the problem, as I am working on a big file, I am not
able to attach it here due to size restrictions.

Kindly advise.

Without knowing the contents of your row 26, my guess is that your formula is returning everything up to and including 7/1/2011. If you want to include the first seven months of the year (Jan-Jul inclusive), then you must change the formula in Sheet1!B1 to compute to the end of the month:

=EOMONTH(A2,12)

or

=DATE(YEAR(A2)+1,MONTH(A2)+1,0)