Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
How to get a row consecutive from a sheet to another | Excel Worksheet Functions | |||
Refer new sheet to previous sheet | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
referring to previous sheet | Excel Discussion (Misc queries) |