nested ifs 2003

May 12, 2011 at 12:15:17
Specs: Windows XP
 i am trying to do a YTD column which looks up to the actual date and basically says if(a1="jan",(a3),if(a1="feb",sum(a3:b3) etc... through november and the otherwise being december.Unfortunately not everyone here has 2007 so i need a way to do this in 2003 any ideas???

See More: nested ifs 2003

#1
May 12, 2011 at 17:12:22
 There are multiple ways it can be done. Try this:Create a table of your months and the corresponding "end cell" for your SUM function.e.g.``` A B 11 JAN A3 12 FEB B3 13 MAR C3 14 APR D3 15 MAY E3 16 JUN F3 17 JUL G3 18 AUG H3 19 SEP I3 20 OCT J3 21 NOV K3 22 DEC L3 ```Then use this formula:=SUM(INDIRECT("A3:"&VLOOKUP(A1,\$A\$11:\$B\$22,2,0)))The VLOOKUP will find the month in the table, return the corresponding "cell notation" which the INDIRECT function will use to build the range for the SUM function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
May 13, 2011 at 05:45:20
 i will let you know on this one..

Report •

#3
May 13, 2011 at 06:09:15
 i dont think this will work.. i have six sets with months as column headers and about 100+ rows of data that needs to be summed.. and 10 tabs of this..

Report •

Related Solutions

#4
May 13, 2011 at 12:08:49
 What I offered will do exactly what you asked for in your first post:SUM a variable range of data, using the value in A1 to determine the last cell in that range.If what you really need to do is not what you asked for, you'll need to be more specific.Not only can't we see your spreadsheet from where we're sitting, we can't read minds.Please click on my signature line to read up on how to post data in this forum and then post some example data that shows what you are trying to do.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#5
May 13, 2011 at 12:48:42
 i figured out my own way thanks anyway

Report •