refering to previous sheet
 

Hi all,

Have trolled the net for the last 3 hours and found nothing that
matches my requirements closely enough and hoping someone out there has
a solution.

Application: Excel 2000
Purpose: Monthly management reports
Background:
I am trying to get a formula that refers to the previous months
spreadsheet to calculate the current "year to date" result of RAW data.
i.e. in January I need the formula to reference December, February to
reference January etc etc etc.

My problem is that it is not just a reference it is a reference plus
other cells in the active spreadsheet i.e. =+November!H129+D129

I know that I can "hard" code the answer and in fact that's exactly
what I have done, but there must be a more elegant answer. Also it is a
real pain to search and replace formulas if I modify the spreadsheets
and have to recreate them as new ones.


I have worked out a way to "automatically" create a concatenated string
of the formula I need but I cannot convert the string to the correct
formula;

=((+VLOOKUP(+IF((+MONTH(+G125)-1)=0,12,(+MONTH(+G125)-1)),Summary!A20:B32,2)))&"!H129"+D129

----the vlookup just looks up a calendar table on the summary
spreadsheet to return the month value-------

and if that's not enough the reference to the previous months cell
and the current months cell needs to be relative.

hopefully I haven't confused everyone.

TIA

Raj

refering to previous sheet
 

For example if you have a balance sheet file for November and a balance
sheet file for December and you find out what your ytd expense is:

You just open both spreadsheets and them in December you enter a
formula like this:

=A1 "where A1 is Decembers expense" + "and then open the November sheet
and click on the cell with the YTD totals".

Hope I'm on the right track


--
Skeep
------------------------------------------------------------------------
Skeep's Profile: http://www.excelforum.com/member.php...o&userid=30822
View this thread: http://www.excelforum.com/showthread...hreadid=504868

refering to previous sheet
 

Income Statement


--
Skeep
------------------------------------------------------------------------
Skeep's Profile: http://www.excelforum.com/member.php...o&userid=30822
View this thread: http://www.excelforum.com/showthread...hreadid=504868

refering to previous sheet
 

I have kept looking and I have founf the most brilliant answer,
originally posted by Tom Ogilvy and the link he provided to get the
code is http://www.j-walk.com/ss/excel then go to developers tips.

Thanks to any that may have given this some thought.

Raj