bmwbuyer2007
Board Regular
- Joined
- Jul 19, 2007
- Messages
- 82
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.
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.