Update user info in formulas

Dyfal

New Member
Joined
Jan 22, 2005
Messages
3
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. :LOL:

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. :cool:

ALL help appreciated.

Dyfal.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
You're looking for the full path of a worksheet from a formula? Have a look here. Hope that helps!
 

Forum statistics

Threads
1,148,528
Messages
5,747,221
Members
424,069
Latest member
kamkwok1hh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top