Insert column into Excel table. Then have data in that column remain with it's original row.

Question

I have a table in Excel connected to a database that will be refreshed regularly. In other words, more rows will be added each time it is refreshed. It's also possible for a row to disappear. I have added a new column to the Excel table. The new column is labeled as "Included?" and can have one of 3 values. YES, NO, or blank. When the table is refreshed, the YES, NO, or blank does not stay with the row. How can I correct this?

Answer 1

I think I know what I need to do. The Excel file connection is to an Access database file. I can have a temporary table in the database that stores the current value in the "Included" column in the Excel file. I can then have an update query in the database that updates the temporary table prior to refreshing in the Excel file. Your thoughts?

Answer 2

Hi @Dale Logan, 

Thank you for posting your question in the Microsoft Q&A forum. 

The reason the "Included?" values disappear is when the database connection refreshes, excel rebuilds the entire table based on the input data from the database. This means all of the existing rows are replaced with new ones from the source, since the "Included?" values are not from the original data source, it will be removed when the table is rebuilt. 

To preserve your “Included?” values, you can store them in a separate helper table and use the XLOOKUP function to bring the values back into your main table after each refresh. 

For example, if you have a helper table named InclusionList with columns ID and Included?, you can use a formula like this in your main table: 

=XLOOKUP([@ID], InclusionList[ID], InclusionList[Included?], "") 

This way, when the database data is refreshed and Excel rebuilds the table, the “Included?” values are automatically matched back to the correct rows based on the unique ID. 

To confirm the use of the "Included?" Values, are they filled automatically or manually based on any certain conditions or criteria? 

If you’re comfortable with it, please upload your Excel file to OneDrive and share it with edit permission, then send me the link. That will make it easier to review your setup and provide more accurate troubleshooting steps. 

 

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.  

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.  

I'm looking forward to your reply.  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.