Re: [libreoffice-users] DUM dynamic range (based on current date/month)

On Sun, 20 Jul 2014 13:47:35 -0400
Tanstaafl <tanstaafl@libertytrek.org> wrote:

> Hi all,
>
> Ok, we keep sales for Sales by month, with the Sales Rep in a Row and 
> each month in a column...

Sounds simple, but...

> So something like this:

This didn't come through properly (my email client does text by
default, and didn't get an html part, not sure if that is why I'm not
seeing what you intended), and doesn't make any sense. However,
assuming it's as simple as you state above...

I'm picturing each row for a different sales rep, and columns B
through M are for months of the year (with A being for the name). In
which case I assume that each cell has a sales amount if we are past
that month, otherwise has zero?

> What I'm trying to do is come up with a formula that can go in the
> YTD column that will SUM only Jan thru Jul for each Rep - and
> automatically change to SUM Jan thru Aug once we hit 8/1, etc...

Uh, I don't get this, maybe because I'm not a financial type, but do
you mean it should always sum Jan to Jul, unless we are in or after
Aug, in which case it should sum Jan to Aug, but it will never sum Jan
to Mar or Jan to Nov or anything else? Or do you simply mean it should
sum Jan to whatever month we've just passed?

> Anyone have a clue? I've tried so many different permutations of 
> SUMIF(), SUM(INDIRECT()), etc, and just can't figure this one out...

Assuming the latter, and that months have a zero if no sales figures
yet, then the formula should simply be "SUM(B1:M1)". The zeros just
won't affect the total.

But that's too simple for you not to have worked out, so I'll go ahead
and assume you don't mean that. Unfortunately I'm not quite sure where I
lost your explanation, so can you clarify where I've missed the point?

Paul

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted