xluser2007
01-23-2008, 06:35 PM
Hi All,
I have a bit of challenge that I'm trying to solve, need expert guidance.
Basically here is the scenario (simplified).
We have 7 spreadhseets for companies:
ABC.xls, XYZ.xls, GFD.xls (names are randomly chosen for explanation).
They are all structurally identical (i.e. each is taken from the same template, only values are different).
There is Total_[month].xls spreadsheet which is taken from the same template as the agents, but is simply a sum of all the agents for certain tabs e.g. Total_200710.xls is the Total for October 2007 total spreadsheet.
Now say the "Data" tab, cell A1 in Total_200710.xls is a direct one-to-one (as they come from the same structured templates) sum from the Agent spreadsheets i.e:
Total.xls_200710 Data A1 = (ABC.xls "Data" _200710 A1) +(ABC.xls "Data" _200710 A1) + ... (GFD.xls "Data" _200710 A1)
Where the formula is named ABC_Data (=ABC.xls "Data" _200710 A1).
The Agent names are really long and the formulas break down when you do a direct sum like this (goes over character limit). So we named the formulas (to preserve one-to-one range refs between the templates and make the formulas shorter and thus work!).
Basic issue is: When we copy over 200710 files into 200711 (Nov-07) folder, the Total.xls still references ABC.xls "Data" _200710 A1 etc, not ABC.xls "Data" _200711 A1.
There are a lot of names (about 50) and changing them one by one every month would be really time consuming.
is there a way to open up Total_[month].xls and if the named formulas contain the previous month, then change it to the current [month]? i.e. if current month is Nov-07 and the formuals 200710, then just change the 200711 in teh folder path reference and everything will be good.
The names stay the same, just need to change the date reference in the formulas if they contain the previous one.
Any help on this would be very greatly appreciated (the above logic is all that it should take, but is hard for a VBnewbie to code)
I have a bit of challenge that I'm trying to solve, need expert guidance.
Basically here is the scenario (simplified).
We have 7 spreadhseets for companies:
ABC.xls, XYZ.xls, GFD.xls (names are randomly chosen for explanation).
They are all structurally identical (i.e. each is taken from the same template, only values are different).
There is Total_[month].xls spreadsheet which is taken from the same template as the agents, but is simply a sum of all the agents for certain tabs e.g. Total_200710.xls is the Total for October 2007 total spreadsheet.
Now say the "Data" tab, cell A1 in Total_200710.xls is a direct one-to-one (as they come from the same structured templates) sum from the Agent spreadsheets i.e:
Total.xls_200710 Data A1 = (ABC.xls "Data" _200710 A1) +(ABC.xls "Data" _200710 A1) + ... (GFD.xls "Data" _200710 A1)
Where the formula is named ABC_Data (=ABC.xls "Data" _200710 A1).
The Agent names are really long and the formulas break down when you do a direct sum like this (goes over character limit). So we named the formulas (to preserve one-to-one range refs between the templates and make the formulas shorter and thus work!).
Basic issue is: When we copy over 200710 files into 200711 (Nov-07) folder, the Total.xls still references ABC.xls "Data" _200710 A1 etc, not ABC.xls "Data" _200711 A1.
There are a lot of names (about 50) and changing them one by one every month would be really time consuming.
is there a way to open up Total_[month].xls and if the named formulas contain the previous month, then change it to the current [month]? i.e. if current month is Nov-07 and the formuals 200710, then just change the 200711 in teh folder path reference and everything will be good.
The names stay the same, just need to change the date reference in the formulas if they contain the previous one.
Any help on this would be very greatly appreciated (the above logic is all that it should take, but is hard for a VBnewbie to code)