Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Reference The Previous Or Next Workbook s
Greetings,
I have a series of workbooks which are named with year-month (eg. 2000-01.xls for the January, 2000 workbook). Because of the need to show complete weeks in a Weekly sheet, both before the 1st and after the end of the month. The formula I need to modify is: =IF(N4<0,"",OFFSET(Daily!$P$41,(42*N4)+2-2*ROW($A$2),0)) Nx is the cell with the number of days before or after the current month. Daily!P41 is the reference cell that has to be expanded to get the data from the previous and next workbooks (eg. current workbook is 2000-01.xlt, the previous workbook is 1999-12.xlt and the next workbook is 2000-02.xlt) Any help would be most appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Reference The Previous Or Next Workbook s
Do you want a formula that is self aware? In otherwords, it works whether
the name of the workbook containing the formula is 2000-01.xlt or it is 2000-02.xlt. Chip Pearson shows the general approach for returning the file name http://www.cpearson.com/excel/excelF.htm#FileName =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) You can then use this to extract information yr = Left(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),4) I think you would construct a formula like =if(mid(formula,6,2)=01,go back a year to dec,add 1 to the name) Similar if you need to go forward. You can probably lessen the complexity by creating defined names with these formulas Insert =Name=Define Name: bkName RefersTo: =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) Name: Yr Refersto: =Left(bkName,4) Name: Mon Refersto: =Mid(bkName,6,2) Name Prev Refersto: =If(Mon="01",Yr-1&"-12.xlt",Yr&"-"&text(mon-1,"00")&".xlt") Name: Next Refersto: =if(Mon="12",Yr+1&"-01.xlt",Yr&"-"&text(mon+1,"00")&".xlt") then in your formulas you can do =Offset(Indirect("["&Prev&"]Daily!$P$1"), . . . -- Regards, Tom Ogilvy "Minitman" wrote in message ... Greetings, I have a series of workbooks which are named with year-month (eg. 2000-01.xls for the January, 2000 workbook). Because of the need to show complete weeks in a Weekly sheet, both before the 1st and after the end of the month. The formula I need to modify is: =IF(N4<0,"",OFFSET(Daily!$P$41,(42*N4)+2-2*ROW($A$2),0)) Nx is the cell with the number of days before or after the current month. Daily!P41 is the reference cell that has to be expanded to get the data from the previous and next workbooks (eg. current workbook is 2000-01.xlt, the previous workbook is 1999-12.xlt and the next workbook is 2000-02.xlt) Any help would be most appreciated. TIA -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference to previous cell (always) | Excel Discussion (Misc queries) | |||
Reference previous page | Excel Worksheet Functions | |||
Reference Previous Sheet | Excel Worksheet Functions | |||
How to reference to the previous worksheet regardless of title? | Excel Worksheet Functions | |||
reference to previous worksheet | Excel Programming |