Excel 2007 - insert text from another cell into path? 1

[dingleberry]

Hey all, just wondering if I can do this...
My spreadsheet is set up like this:

A1 = Date
C1 = value from another spreadsheet

for example for A1, I'll type in 10-2-2011 (formatted m-d-yyyy) and for C1 I have the following link:

='I:\Machining Business Unit\Production Output\[Business Unit Performance Status 10-2-2011.xls]Machining Centers'!$AK$115

which makes the C1 cell pulls the value which is in cell AK115 of this other spredsheet.

Every week, the date changes so I have to manually type in 10-9-11 in both A1 and in the C1 path so I'm wondering if I can have the path in C1 pull the date string from cell A1 and if so what that would look like. For example - Instead of

='I:\Machining Business Unit\Production Output\[Business Unit Performance Status 10-2-2011.xls]Machining Centers'!$AK$115

maybe

='I:\Machining Business Unit\Production Output\[Business Unit Performance Status A1.xls]Machining Centers'!$AK$115

Is this possible and if so what might that format look like? I know is sounds trivial but I have to retype that date a zillion times.

 

[SkipVought]

Use the INDIRECT function
[tt]
=INDIRECT("'I:\Machining Business Unit\Production Output\[Business Unit Performance Status "&TEXT(A1,"d-m-yyyy")&".xls]Machining Centers'!$AK$115")
[/tt]
in other words, you are concatenting THREE strings, the first and last are literals and the third is a reference.

BTW, the FORMAT of the date in A1 is IRRELEVANT!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dingleberry]

This is most excellent Skip. Thanks.

Any way to make it so you don't have to have the linked workbook open - or maybe to make it so that when you do have it open and it links the values and you save and then re-open the values don't appear as #ref! errors (beacuse we no longer have source files open?)


Thanks,

 

[SkipVought]

I personally abhor such references. However, I reference a great deal of data from other workbooks, using MS Query. The workbook need not be open.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]