I have 2 spreadsheets, a.xls and b.xls. B.xls has a LOT (624 to be precise) of cells with formulas that pull info from a.xls. In an ideal world, the formula references for the location of the files would be the same, but I dont have enough control over the location of the files as they go out to the network.
Is there a macro that will make the end user put in the location of the files (eg, "c:\excel"), and then update that info in every formula? I really dont want to have to edit each of the formulas, by hand, for about 30 different PC setups.
The formulas in b.xls are in the format
=C:\[a.xls]'sheet1'!$B$4+[a.xls]'sheet1'!$B$5
if it makes any difference. If it were only 1 or 2, I wouldnt mind so much.
ALL help appreciated.
Dyfal.
Is there a macro that will make the end user put in the location of the files (eg, "c:\excel"), and then update that info in every formula? I really dont want to have to edit each of the formulas, by hand, for about 30 different PC setups.
The formulas in b.xls are in the format
=C:\[a.xls]'sheet1'!$B$4+[a.xls]'sheet1'!$B$5
if it makes any difference. If it were only 1 or 2, I wouldnt mind so much.
ALL help appreciated.
Dyfal.
