linking data from network users folder

[jermsalerms]

I have 500 company members that will save a copy of the spreadsheet "Quoter" on their desktop.

The spreadsheet pulls data from the spreadsheet "RetailEZ-Price" also found on their desktop.

I need to find a way to get the Quoter spreadsheet to pull data from RetailEZ-Price.

Right now I have the following is cell A1 of "Quoter"

=IF('C:\Documents and Settings\jbecker\Desktop\[RetailEZ-Price.xls]Drop Down Boxes'!D7="","",'C:\Documents and Settings\jbecker\Desktop\[RetailEZ-Price.xls]Drop Down Boxes'!D7)

However this only works when the user "jbecker" is logged in. If "jsmith" is the user it needs to pull data from:

=IF('C:\Documents and Settings\jsmith\Desktop\[RetailEZ-Price.xls]Drop Down Boxes'!D7="","",'C:\Documents and Settings\jsmith\Desktop\[RetailEZ-Price.xls]Drop Down Boxes'!D7)

Any suggestion on how to do this so that the system changes the folder that it is looking in based on the user that is logged into the windows OS.

[mrice]

Maybe you could make use of

Application.UserName

in a user defined function.....

[T-J]

Any suggestion on how to do this so that the system changes the folder that it is looking in based on the user that is logged into the windows OS.

You shouldn't need to do this if both excel workbooks are in the same folder. In this case the path to the linked workbook will be relative, meaning you can move the workbooks to another user's folder and the link will still work.

Have both workbooks open and create the links. You should see only ='[RetailEZ-Price.xls]Drop Down Boxes'!D7 in the formula and not the full path.
Save the workbooks in "C:\Documents and Settings\jbecker\Desktop". Then copy to the other user's desktop as before.

Also see Description of link management and storage in Excel for a better explanation.

Relative vs. absolute links
Links to external workbooks are created in a relative manner whenever possible. This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook. With this method, you can move the workbooks without breaking the links between them. The links remain intact, however, only if the workbooks remain in the same location relative to each other. For example, if the linked file is C:\Mydir\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, you can move the files to the D drive as long as the source file is still located in a subfolder called "files".