Currently, I have a workbook with multiple worksheets. I am trying to
provide a return of the prior 3 months. Each month, when I add the next
month data, I have to update these formulas to refer to the data in the prior
two months worksheets i.e. this month I had to update last months formula
from pulling data from the Mar, Apr, & May worksheet to the Apr, May, and Jun
worksheets. How can I get a formula to automatically update and pull the
prior two months data to complete the return?

Use '3D-Sums' with a couple of 'dummy worksheets.' Ex: insert two
worksheets, one with the name Last3Start and one with the name Last3End.
Position them before and after the most recent three months' sheets,
respectively. Then the totals would be =sum(Last3Start:Last3End!A1), for
instance, to add the values from A1 on those five sheets (the two dummies
which would have no data plus the three valid monthly worksheets).
All you do each month then is move those two dummy worksheets to the
appropriate positions in the workbook.


"sisko101" wrote:

Currently, I have a workbook with multiple worksheets. I am trying to
provide a return of the prior 3 months. Each month, when I add the next
month data, I have to update these formulas to refer to the data in the prior
two months worksheets i.e. this month I had to update last months formula
from pulling data from the Mar, Apr, & May worksheet to the Apr, May, and Jun
worksheets. How can I get a formula to automatically update and pull the
prior two months data to complete the return?

You can use an array formula.

For example if you wanted to sum all the numbers before a specified
date you would enter

The row B has the profit and row A has the dates......

=SUM(($b$2:$b$200)*($a$2:$a$200<=x1))

x1 would have your date in it

remember to hit control shift enter after you put this in.


--
macroll
------------------------------------------------------------------------
macroll's Profile: http://www.excelforum.com/member.php...o&userid=23211
View this thread: http://www.excelforum.com/showthread...hreadid=559483

Sorry, read a little more into your question

Have one X1 = the date of your worksheet
Have x2 =EOMONTH(F273,-3)+1 (this is 3 mths prior period start)

Then enter the array as this

=SUM(($b$2:$T$200)*($a$2:$a$200<=x1)*($a$2:$a$200 =x2))

a2:a200 are the dates
b2:b200 are the values


--
macroll
------------------------------------------------------------------------
macroll's Profile: http://www.excelforum.com/member.php...o&userid=23211
View this thread: http://www.excelforum.com/showthread...hreadid=559483