How To Reference The Previous Or Next Workbook s

[Minitman]

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

 

[Tom Ogilvy]

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"), . . .