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

Thanks for the reply Paul,

I was working on a 'wtf?' reply as to why the list converted my HTML 
email (had the values in a simple table) to plain text.

Also, I have no clue what happened with the 'DUM' in the subject - I 
didn't type that... weirdness...

Blindly converting HTML to plain text AND disallowing small simple 
attachments really makes it difficult to paint an accurate picture to 
get help.

Anyway...

On 7/20/2014 2:12 PM, Paul <paulsteyn1@afrihost.co.za> wrote:
> On Sun, 20 Jul 2014 13:47:35 -0400 Tanstaafl wrote:
> > Ok, we keep sales for Sales by month, with the Sales Rep in a Row and
> > each month in a column...

> 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...

It is -

> 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.

Almost... there is also a YTD column at the end, and this is the column 
with the formula I'm working on.

But, your confusion is my fault, and stems from one important detail I 
neglected to mention.

I have one sheet for the current year, and other sheets for previous years.

The formula for the YTD column for the current year sheet is just as 
simple as you described below and works fine.

The problem is the same column for the prior year sheets. I need the YTD 
column for those sheets to sum up each Sales Reps totals for that past 
year, but only to the *current* month. Subsequent months are not zero, 
so I can't just SUM the entire range.

This is so the boss can easily see how each Rep is doing compared to 
each prior year (I pull this value in on a Master Sheet used to show 
these comparisons).

Currently I do a Find/Replace at the beginning of each month to change 
the formula to sum the correct ranges, but I'd really like this to 
happen dynamically.

> > 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?

Actually I want it to sum the range from Jan to MONTH(TODAY())-1.

So, through the end of the prior month.

Hope this better defines the problem...

Thanks again 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