Please bear with me as I'm an excel/power query/VBA noob. I'm setting up a project that requires "relative" links because there will be different layers of users and copies in different locations and the references that I use will have to work no matter where the workbook is located.
I set up this formula in a worksheet that fetches the current workbook's folder location. The location of the workbooks will vary, but the other workbooks that this workbook references will always be in the same folder as this workbook.
This "Location Fetch" formula fetches the folder location of the workbooks. It's found in Sheet2 B2:
I then need to Get Data from the other workbooks by fetching the results of the above and appending the file name of the workbook that I want.
In Power Query Advanced Editor, I have the following:
What I need to figure out is what to put in place of "O:\New Timesheet & Daily\Manager Name\Employee John Doe\Approved Hours\Monday.xlsx" in the above. I need to concatenate the location and the workbook name Something like this, but I'm unsure of correct syntax:
The LocationFetch formula is located in worksheet on the workbook that I'm fetching data to. So I'm unsure how to reference it in Power Query. "Monday" is text I'm trying to append.
tldr; I need to put the "folder location" results from a worksheet in my workbook into power query and append additional text for a file name.
Thank you for any and all help!
Bookmarks