I like to use numbers as opposed to typing in the name of the month (i.e.
1=Jan, 2=Feb, 3=Mar, etc). This allows you to use v/hlookup, sumproduct, or
index/match. I use this for the company sales reports where A1 is the month
cell, and the entire report updates to show the current month and YTD v.
prior year... based on the month number in A1 and the year in A2. I also
have one that displays the trailing 12 months sales trend based on the month
in A1. I just find that you are less likely to have an input error using
numbers.
something to consider,
ryanb.
"Debra Dalgleish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Or the shorter formula:
>
> =SUM(OFFSET(A2,0,0,1,MATCH(A5,A1:L1,0)))
>
> Debra Dalgleish wrote:
> > With "March" in cell A5:
> >
> > =SUM(INDIRECT("A2:" & ADDRESS(2,MATCH(A5,A1:L1,0))))
> >
> > dksoreal wrote:
> >
> >> Probably a simple solution, but I am struggling.
> >>
> >> Here is what I am trying to do. I have months Jan through Dec in a row
> >> with corresponding data below it for a few variables. I want to be
> >> able to input a date and have the formula calculate the total for a
> >> variable up to that date. For example: If I wanted the YTD for March,
> >> I would input "March" in a cell and the formula would know to sum Jan -
> >> Mar in the data table and return the YTD value.
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
Similar Threads
