Forum Discussion
Links to Query Table Sheet not Updating
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
<-- Import Sheet
<-- Trim Sheet
After Updating Query
<-- Import Sheet
<-- Trim Sheet
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.
2 Replies
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.
- Thank you very much, that fixed it!
Not that it matters, but I have to trim the information as the data I'm importing has different analytics included in columns that move to a new column every week and mess up my summation equations. It's easier to hardcode a filter than to update the PQ references each week. I'm sure there's a better way to do this, but with my level of understanding, this is the easiest way for me to get what I need done.
