Hello,
I've been using queries to import bulk data that is update weekly with new rows and columns. This data is imported into a workbook under an individual sheet. I then trim this data in another sheet in the same workbook, by referencing the cells in the imported data sheet.
The issue with this is that once I get new rows added, I have to drag down the cells on the "trim" sheet again, as it changes the cell equations after adding a new row or column.
As an Example I made the following sheets
Before Updating Query
After Updating Query
As you can see, the new items in the columns after J are not showing up in the trim sheet. This is because once the new columns are added after the query update, the existing cells have their equations updated to move out past the new data columns in the query as though the new cells have been inserted into the import sheet.
Is there any way around this so that new rows and columns added from the query update are not considered newly inserted columns within sperate sheets of the workbook?
@TryggM Can't really imagine why you would do the 'trimming' (whatever that is) outside PQ, but what you describe is default behavior of PQ generated tables. They insert rows and columns when they expand so that they don't accidentally overwrite existing data. But you can change that under Properties on the Data ribbon in the Queries & Connections group. Select the bottom radio button as shown in the picture below.
Accepted Solutions
@TryggM Can't really imagine why you would do the 'trimming' (whatever that is) outside PQ, but what you describe is default behavior of PQ generated tables. They insert rows and columns when they expand so that they don't accidentally overwrite existing data. But you can change that under Properties on the Data ribbon in the Queries & Connections group. Select the bottom radio button as shown in the picture below.