Forum Discussion
power query with relative data source not refreshing accurate data
Hi Bridgett_B
I think you could continue using your CELL formulas, it's a clever idea.
To avoid the issue you describe when opening another workbook: Just add a cell reference to the formulas:
This way, it will always refer to that cell in this workbook, and not to another workbook.
Hi Bridgett_B
thanks for the details, so obviously my assumption was wrong.
Another thing:
In your screenshot I see that you removed all data from your source table, but the empty rows still do exist. Did you try to delete the empty lines as well, so that only the header (and the first empty row) in the source table remains? And then refresh the query?
I did finally figured out what it's doing. For examples sack lets say the workbook I have my query in is workbook A. My relative data sources in workbook A are setup on a designated "settings" tab and are various (CELL,"filename") formulas that are giving the file path and file name of the current workbook. When I open another Excel workbook, let's say workbook B, those formulas in the workbook A are updating to reflect the file name and directory path of workbook B. I think I'm going to have to either hard code that file name in, which could be problematic, or make it a parameter that the user can change/update on their own.
Hi Bridgett_B
I think you could continue using your CELL formulas, it's a clever idea.
To avoid the issue you describe when opening another workbook: Just add a cell reference to the formulas:
This way, it will always refer to that cell in this workbook, and not to another workbook.
- Great, thanks for your help!