Relative Reference instead of Absolute Reference

[KillerOfGiants]

Hello,

I created a transition file that takes a QuickBooks csv export data and portrays the data in such a way to be copied into another csv file so that that data can be imported to an outside entity. (1099s for reference).

My issue is, when I created the transition file (with the original QB export file open) everything works fine. When I close the QB file, all the file names then switch to full path (myname is my user name):
'C:\Users\myname\Desktop\[1099 Detail.csv]1099 Detail'!$A$2:$A$100001

What I want is for the formula to look in the same directory, no matter what person is exporting from their QuickBooks. What I found is it should be something like this:
'\[1099 Detail.csv]1099 Detail'!$A$2:$A$100001

But that does not work (and opens a file box to update values choose where I am pulling the data.

Added note: When I close the QB file, the file path name decides it want to put a driver letter in front of my attempted relative reference. (VBA is not an option).

 

[6StringJazzer]

The way Excel shows path names is a little confusing. In your original attempt, did you actually try using that file in a different directory?

Excel will show the full path name in the formula when the target file is closed, even if it actually using a relative path. You should be able to take your original file, close it, copy it to another folder, and I will bet that when you open in there you will a full path name of the new folder.