dynamic table

[Kathy]

Let me begin by apologizing for posting the same question twice. For some
reason, I am unable to locate the original and the notification sent to me by
Microsoft about a response was linked to a blank page.

I am using Office 2007 and I need to be able to have a dynamic table based
on fiscal months of the year. I have created a file that sums sales and open
orders based on date by fiscal month. The first sheet shows a 5 rolling
months with subtotals by week within each fiscal month. This information is
pulling from two additional sheets that are linked to an outside source.
This was working great until we came upon the new fiscal year. Can someone
help? I am using sumproduct to gather my totals because there are multiple
criteria to be met, as well as I was using if statements with the sumproduct
to determine the months. I have a worksheet in the file that is dedicated to
fiscal months showing dates for each. I have a column that indicates which
month number it is of the fiscal year (ie July is 1, August is 2 and so on).

 

[Roger Govier]

Hi Kathy
Can you post a sample of your data, and, the formulae that you are currently
using.
I think I understand what you are asking, but without seeing what it is that
you have, then it is difficult to post a satisfactory solution.

 

[Kathy]

Roger,

My first sheet is sales which is pulling from outside of Excel. Column A is
the date, B is the department/group, C is the total $. I have another sheet
that is basically the same except it shows open orders. My Calendar sheet
shows the beginning and ending date of each period next to the number for
that period.

An abbreviated version of the formula is:=IF($D$46='FY
Calendar'!$A$2,SUMPRODUCT(('Open Orders'!$F$3:$F$654>='FY
Calendar'!$F$2)*('Open Orders'!$F$3:$F$654<=$C$47)*('Open
Orders'!$C$3:$C$654='VS Combined Backlog'!D$28)*('Open
Orders'!$O$3:$O$654)),...IF($D$46='FY Calendar'!$A$13,SUMPRODUCT(('Open
Orders'!$F$3:$F$654>='FY Calendar'!$Q$2)*('Open
Orders'!$F$3:$F$654<=$C$47)*('Open Orders'!$C$3:$C$654='VS Combined
Backlog'!D$28)*('Open Orders'!$O$3:$O$654)),""))))))))))))

Hope this is enough info. Thanks for your help.

 

[Roger Govier]

Hi Kathy

Is it possible to send a an example of the workbook direct to me.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address