Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating external references to spreadsheets with changing names
I have multiple monthly reports in excel (example: Jan08_ABC.xls) that
reference particular spreadsheets that also change monthly (Jan08_ABC_cases.xls). I've created a formula that gets the values it needs, however I'm trying to figure out how to get the formula to change every month. For instance, the formula for January is ='[Jan08_ABC_cases.xls]Sheet1'!$B6 I need it to change for each new month. I created another formula in cell A1 that will automatically update monthly with the new name of the needed/referenced file, therefore in Feb that cell reads Feb08_ABC_cases.xls. So, my question is how do I change my initial formula to incorporate the name change. These don't work, but hopefully it gives you a good idea of what I'm trying to do (A1=Feb08_ABC_cases.xls): ='[(TEXT(A1,"")]Sheet1'!$B6 ='[A1]Sheet1'!$B6 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating external references to spreadsheets with changing names
BS
I think you will need to use an INDIRECT function. Maybe something like: =INDIRECT("[feb.xls]Sheet1!$A$1") or build the month into a cell like =INDIRECT("["&($B$6&".xls]Sheet1!$A$1")) where the month is in cell B6 or build the month explicitly in the INDIRECT function like =INDIRECT("["&(TEXT(MONTH(NOW()),"mmm")&".xls]Sheet1!$A$1")) Of course you need to customize the exact name of your file, sheet, and pick the correct cell(s). Also, the brackets, quotes and explamation points can be a little tricky to get right in these formulas. At least they are for me. Good luck. Ken Norfolk, Va On Apr 25, 3:49*pm, BS LeBlanc wrote: I have multiple monthly reports in excel (example: Jan08_ABC.xls) that reference particular spreadsheets that also change monthly (Jan08_ABC_cases.xls). *I've created a formula that gets the values it needs, however I'm trying to figure out how to get the formula to change every month. * For instance, the formula for January is ='[Jan08_ABC_cases.xls]Sheet1'!$B6 I need it to change for each new month. I created another formula in cell A1 that will automatically update monthly with the new name of the needed/referenced file, therefore in Feb that cell reads Feb08_ABC_cases.xls. *So, my question is how do I change my initial formula to incorporate the name change. These don't work, but hopefully it gives you a good idea of what I'm trying to do (A1=Feb08_ABC_cases.xls): ='[(TEXT(A1,"")]Sheet1'!$B6 ='[A1]Sheet1'!$B6 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using external references as hyperlinks | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Problems with external references when creating a drop down list | Excel Discussion (Misc queries) | |||
External References | Excel Discussion (Misc queries) |