jakebailey
03-13-2011, 08:46 PM
Hi - I tried searching the forum for a similar post but couldn't find one. I have a couple of things I need help or direction with.
See attached example workbook.
1. I have a workbook with invoice line data that spans multiple years. I need to calculate a running total of YTD (Year To Date) sales and another running total of LYTD (Last YTD) sales by customer. At the top of the attached report I have a cell to enter a date that I need the calculations to be based on. For example if I enter 3/15/2011 then I need total sales for this Fiscal Year up until 3/15/2011 and total sales for last fiscal year up until the same day and month. If I change the date then the calculations need to change with it.
2. To complicate things further my fiscal year starts 7/1 and ends on 6/30.
I added another column called "Calendar Year" but I'm not sure if that really helps me. Instead I think I should add a column called "Period" but then again I'm not 100% sure if that will help me either. The fiscal calendar is kind of throwing me for a loop.
I appreciate any and all help or direction on what solution would be best. I'm ok with VBA, Pivot Tables, etc;
See attached example workbook.
1. I have a workbook with invoice line data that spans multiple years. I need to calculate a running total of YTD (Year To Date) sales and another running total of LYTD (Last YTD) sales by customer. At the top of the attached report I have a cell to enter a date that I need the calculations to be based on. For example if I enter 3/15/2011 then I need total sales for this Fiscal Year up until 3/15/2011 and total sales for last fiscal year up until the same day and month. If I change the date then the calculations need to change with it.
2. To complicate things further my fiscal year starts 7/1 and ends on 6/30.
I added another column called "Calendar Year" but I'm not sure if that really helps me. Instead I think I should add a column called "Period" but then again I'm not 100% sure if that will help me either. The fiscal calendar is kind of throwing me for a loop.
I appreciate any and all help or direction on what solution would be best. I'm ok with VBA, Pivot Tables, etc;