software

I've working with two different sheets in the same workbook. The first
sheet is called quot;main flashquot; and the second is called quot;actualsquot;. The
quot;main flash sheet is basically just a cover page the has totals for a
day, week, month, a year. The quot;actualsquot; sheet has all of the numbers.
My quot;actualsquot; sheet looks like this:Code:
--------------------

Column A

Date Sales
3/11 10
3/12 15
3/13 10
3/14 20
3/15 30
3/16 20
3/17 30
Total 135
3/18 20
3/19 10
3/20 25
3/21 20
3/22 15
3/23 20
3/24 30
Total 140

--------------------The Total's listed are for each week. In my quot;main flashquot; sheet I've got
the date I want information from as well as the last day of that week.
So for example today I have 3/22/06 listed in cell A1 and 3/24/06
listed in cell B1.

What I'm hoping to do is have a cell in my quot;main flashquot; sheet display
the total sales for the week from the start of the week up until the
date listed in A1. So for example since I have 3/22/06 listed in the
daily date and 3/24/06 listed in the weekly end date the formula would
need to display the value of 3/18 (the start of the week) threw
3/22/06. So that would be 20 10 25 20 15 for a value of 90. Another
example would be if I changed the daily date to 3/13/06 and the weekly
end date to 3/17/06 the value would be 35 (10 15 10).

If anyone can help me out with this I'd greatly appreciate it.--
Weasel
------------------------------------------------------------------------
Weasel's Profile: www.excelforum.com/member.php...oamp;userid=27206
View this thread: www.excelforum.com/showthread...hreadid=525297In Main Flash, enter:
=SUMPRODUCT(--(actuals!A2:A16lt;=A1),--(actuals!A2:A16gt;A2-7),actuals!B2:B16)

The weekly totals in quot;actualsquot; are skipped because of the quot;Totalquot; text.

HTH
--
AP

quot;Weaselquot; gt; a écrit dans
le message de ...
gt;
gt; I've working with two different sheets in the same workbook. The first
gt; sheet is called quot;main flashquot; and the second is called quot;actualsquot;. The
gt; quot;main flash sheet is basically just a cover page the has totals for a
gt; day, week, month, a year. The quot;actualsquot; sheet has all of the numbers.
gt; My quot;actualsquot; sheet looks like this:
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; Column A
gt;
gt; Date Sales
gt; 3/11 10
gt; 3/12 15
gt; 3/13 10
gt; 3/14 20
gt; 3/15 30
gt; 3/16 20
gt; 3/17 30
gt; Total 135
gt; 3/18 20
gt; 3/19 10
gt; 3/20 25
gt; 3/21 20
gt; 3/22 15
gt; 3/23 20
gt; 3/24 30
gt; Total 140
gt;
gt; --------------------
gt;
gt;
gt; The Total's listed are for each week. In my quot;main flashquot; sheet I've got
gt; the date I want information from as well as the last day of that week.
gt; So for example today I have 3/22/06 listed in cell A1 and 3/24/06
gt; listed in cell B1.
gt;
gt; What I'm hoping to do is have a cell in my quot;main flashquot; sheet display
gt; the total sales for the week from the start of the week up until the
gt; date listed in A1. So for example since I have 3/22/06 listed in the
gt; daily date and 3/24/06 listed in the weekly end date the formula would
gt; need to display the value of 3/18 (the start of the week) threw
gt; 3/22/06. So that would be 20 10 25 20 15 for a value of 90. Another
gt; example would be if I changed the daily date to 3/13/06 and the weekly
gt; end date to 3/17/06 the value would be 35 (10 15 10).
gt;
gt; If anyone can help me out with this I'd greatly appreciate it.
gt;
gt;
gt; --
gt; Weasel
gt; ------------------------------------------------------------------------
gt; Weasel's Profile:
www.excelforum.com/member.php...oamp;userid=27206
gt; View this thread: www.excelforum.com/showthread...hreadid=525297
gt;
Hi,

Try the following formula:

=SUM(INDIRECT(quot;Actuals!Bquot;amp;MATCH(B1,Actuals!A1:A16, 0)-6):INDIRECT(quot;Actuals!Bquot;amp;MATCH(A1,Actuals!A1:A16,0) ))

Regards,
B. R. Ramachandran

quot;Weaselquot; wrote:

gt;
gt; I've working with two different sheets in the same workbook. The first
gt; sheet is called quot;main flashquot; and the second is called quot;actualsquot;. The
gt; quot;main flash sheet is basically just a cover page the has totals for a
gt; day, week, month, a year. The quot;actualsquot; sheet has all of the numbers.
gt; My quot;actualsquot; sheet looks like this:
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; Column A
gt;
gt; Date Sales
gt; 3/11 10
gt; 3/12 15
gt; 3/13 10
gt; 3/14 20
gt; 3/15 30
gt; 3/16 20
gt; 3/17 30
gt; Total 135
gt; 3/18 20
gt; 3/19 10
gt; 3/20 25
gt; 3/21 20
gt; 3/22 15
gt; 3/23 20
gt; 3/24 30
gt; Total 140
gt;
gt; --------------------
gt;
gt;
gt; The Total's listed are for each week. In my quot;main flashquot; sheet I've got
gt; the date I want information from as well as the last day of that week.
gt; So for example today I have 3/22/06 listed in cell A1 and 3/24/06
gt; listed in cell B1.
gt;
gt; What I'm hoping to do is have a cell in my quot;main flashquot; sheet display
gt; the total sales for the week from the start of the week up until the
gt; date listed in A1. So for example since I have 3/22/06 listed in the
gt; daily date and 3/24/06 listed in the weekly end date the formula would
gt; need to display the value of 3/18 (the start of the week) threw
gt; 3/22/06. So that would be 20 10 25 20 15 for a value of 90. Another
gt; example would be if I changed the daily date to 3/13/06 and the weekly
gt; end date to 3/17/06 the value would be 35 (10 15 10).
gt;
gt; If anyone can help me out with this I'd greatly appreciate it.
gt;
gt;
gt; --
gt; Weasel
gt; ------------------------------------------------------------------------
gt; Weasel's Profile: www.excelforum.com/member.php...oamp;userid=27206
gt; View this thread: www.excelforum.com/showthread...hreadid=525297
gt;
gt;

In your quot;main flashquot; sheet:

=SUMPRODUCT(actuals!B1:B100,actuals!A1:A100gt;=B1-6,actuals!A1:A100lt;=A1)

HTH
Kostis Vezerides
Thanks for all of the help. It worked great!--
Weasel
------------------------------------------------------------------------
Weasel's Profile: www.excelforum.com/member.php...oamp;userid=27206
View this thread: www.excelforum.com/showthread...hreadid=525297