thewesties
Board Regular
- Joined
- Apr 18, 2002
- Messages
- 75
I need to create a link to an excel workbook on my network whose name is changing weekly.
The naming convention of the file is based on the previous week ending date (ie last Friday was 4/12/02 so the name of the file is WE041202.xls.)
The physical path to the file is constant on the network. I have concatenated several cells to create the formula as follows:
Cell I12: ='Spreadsheet Manager'!$H$1 (this is a worksheet in this workbook)
Cell I13: =IF(MONTH(I12)<10,CONCATENATE(0,MONTH(I12)),MONTH(I12))
Cell I14: =IF(DAY(I12)<10,CONCATENATE(0,DAY(I12)),DAY(I12))
Cell I15: ==RIGHT(YEAR(I12),2)
Cell I16: =CONCATENATE("='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE",I13,I14,I15,".xls]Prod Weekly'!$I$6")
Based on the above formulas, this is how the data is displayed:
04/12/02 (<--Cell I12)
04 (<--Cell I13)
12 (<--Cell I14)
02 (<--Cell I15)
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6 (<--Cell I16)
I would like CELL I17 to use the value of Cell I16 as the basis for the formula.
The source for I12 (in the current Worksheet 'ASAP #'!I12) is changed weekly.
The reason I don't just manually change the link is the 'Spreadsheet Manager' worksheet links to ASAP # as well as 71 other worksheets in this workbook.
My ultimate goal is to take my result (which is shown in Cell I16) and convert it from text to the actual formula.
As it is right now, if I Copy and Paste Value of I16 in to I17 I get the following:
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6
I have tried creating macros to edit the cell, but then the data would be hard coded in the macro to always type WE041202.
This message was edited by thewesties on 2002-04-19 17:46
This message was edited by thewesties on 2002-04-22 09:04
This message was edited by thewesties on 2002-04-22 12:43
The naming convention of the file is based on the previous week ending date (ie last Friday was 4/12/02 so the name of the file is WE041202.xls.)
The physical path to the file is constant on the network. I have concatenated several cells to create the formula as follows:
Cell I12: ='Spreadsheet Manager'!$H$1 (this is a worksheet in this workbook)
Cell I13: =IF(MONTH(I12)<10,CONCATENATE(0,MONTH(I12)),MONTH(I12))
Cell I14: =IF(DAY(I12)<10,CONCATENATE(0,DAY(I12)),DAY(I12))
Cell I15: ==RIGHT(YEAR(I12),2)
Cell I16: =CONCATENATE("='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE",I13,I14,I15,".xls]Prod Weekly'!$I$6")
Based on the above formulas, this is how the data is displayed:
04/12/02 (<--Cell I12)
04 (<--Cell I13)
12 (<--Cell I14)
02 (<--Cell I15)
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6 (<--Cell I16)
I would like CELL I17 to use the value of Cell I16 as the basis for the formula.
The source for I12 (in the current Worksheet 'ASAP #'!I12) is changed weekly.
The reason I don't just manually change the link is the 'Spreadsheet Manager' worksheet links to ASAP # as well as 71 other worksheets in this workbook.
My ultimate goal is to take my result (which is shown in Cell I16) and convert it from text to the actual formula.
As it is right now, if I Copy and Paste Value of I16 in to I17 I get the following:
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6
I have tried creating macros to edit the cell, but then the data would be hard coded in the macro to always type WE041202.
This message was edited by thewesties on 2002-04-19 17:46
This message was edited by thewesties on 2002-04-22 09:04
This message was edited by thewesties on 2002-04-22 12:43