Help with linking to external files, with a twist

[bmwbuyer2007]

Hello all - This is my first post, so you'll have to excuse me if I dont quite get what I'm trying to say across very well!

I have my own small business and create 2 different spreadsheets each month. 1 for my monthly figures, and 1 for my teams individual figures.

I want to link the teams individual figures spreadsheet to the monthly figures spreadsheet for whatever month or year it is without manually changing all of the links, here is what the link is for this month:

'H:\Primary\2007\[07 - July 07 Primary.xls]'!Flows$C$4

i have included a cell that i link to manually in the above formula

The first spreadsheet always stays within the 'primary' folder, the teams individual figures is moved around, e mailed and holds little relevance where it is contained as long as the links are intact. to break the monthly figures filename down:

2007 = folder for a years worth of spreasheets (12 maximum)
07 = month number (july) - just so the spreadsheets appear in order
july = current month
07 = year (2007)

I have made a formula within the teams individual figures spreadsheet so that if the year or month was different it would auto calculate where the new monthly figures is contained and update accordingly.

for: 20/03/2009

it will change the text in a cell within the second spreadsheet to:

'H:\Primary\2009\[03 - March 09 Primary.xls]

But after I have worked out where the spreadsheet is, how do i link to cells in that spreadsheet, is there such a thing as a dynamic external link?

Here's how it is working:

Primary Location Calculator:

Today: 20/03/2009 - this would be the formula =today()

Month 1: 03
Month 2: March
Year 1: 09
Year 2: 2009
Location: 'H:\Primary\2009\[03 - March 09 Primary.xls]

I add a little extra to the formula to target the cell i am looking for:

'H:\Primary\2009\[03 - March 09 Primary.xls]Flow'!$C$4

but how would i display what is in the cell of the formula I have created above? I'm working it out correctly but have no idea how to retrieve that information!

Please Help!!!

Thank you all,

Cordy.

 

[ravishankar]

Hi
The syntax for retrieving data from closed files is
='path[filename.xls]sheetname'!cellreference
your formula fits the requirement. insert it with an = in a cell.
ensure the drive specified is accessable
ravi

 

[bmwbuyer2007]

Hi Ravi,

Unfortunately this doesn't work. as I am calculating where the new file will be in a cell itself this is simply being output as plain text. I can get it to say:

='H:\Primary\2009\[03 - March 09 Primary.xls]Flow'!$C$4

but that is what the text says in the cell, whereas i want it to give the information that is in the cell...

to explain a bit further:

Primary Location Calculator:
Today: 20/03/2009 =today()
Month 1: 03 =CHOOSE(MONTH(N28), "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")
Month 2: March =CHOOSE(MONTH(N28), "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Year 1: 09 =TEXT(N28, "yy")
Year 2: 2009 =TEXT(N28, "yyyy")
Location: 'H:\Primary\2009\[03 - March 09 Primary.xls] ="'H:\Primary\"&N32&"\["&N29&" - " & N30&" " &N31& " Primary.xls]"

I know how to link to an external file, but i dont know how to use the above calculation to link dynamically to a different file for whichever month or year it is without manually updating the links...

Thanks,

Cordy