INDEX(MATCH()) between Excel Table and Duplicate of Excel Table

When performing a look up using INDEX(MATCH()) between an Excel table and a duplicate of the Excel table (created using "Existing Connections"), formula columns added to the duplicate table work perfectly because they recalculate on refresh. However, data entry columns in the duplicate Excel table appear to associate with a different record upon each refresh.

Question: Is there a way to use a duplicate Excel table for data entry columns and return selected columns from the duplicate table back to the original Excel table?

Anticipating I will asked to attach a file - doing so is possible but not easy because the data is proprietary and I would need to recreate the issue in a sample file, which I will do if needed. But, the concept is pretty straight forward and likely something others have tried, Thank you for the help!

Two connections? Currently, I have one connection created when the duplicate table was created selected the table from existing connections. What is the other connection and what type of connection?

I replicated the problem in a new spreadsheet with sample data. The issues is less about the INDEX(MATCH()) functions and more about Excel tables and a duplicate excel table. Consequently, I did not replicate the INDEX(MATCH()), which I suspect with work if the duplicate table associates with the same record consistently.

Open the file and look at the order and production tabs. Note on the production tab the record with "days to production" of 15 days. Now go to the order file and insert a new record and enter data. Although I do not think it matters where you insert the new record, insert the record above the record associate with the 15 days on the production schedule. Doing so will likely make it easier to see what happens. Refresh the production tab and the 15 days will now associate with a new record.

Please upload the file after adding the new record. But, add a record to line two and look again please. When you say nothing changed, does that mean the 15 stayed on the same line where it started or move with the record associated with initially.

Or, do you mean the table did not refresh as in did not change at all. If so, try Refresh All.

Here is another way to test the problem. Re-Sort the Production Table by Product so the 15 moves up or down. Then, refresh all.

The original file only had four rows of data. Please open the original file and repeat adding a new record to order row 3. I have the duplicate refreshing and the 15 bouncing all over the column. Maybe the link was lost in upload/download. Can you recreate the report...the refresh certainly should be working.

As I said it's probably best to upload the workbook.

When you get a chance, please send along the workbook that will not refresh. Is it unusual for such a table to not refresh?

Sometimes I get an "Enbale" button at the top of the spreadsheet, which prevents refreshing a spreadsheet. Have you clicked the button to enable the connections.

hi all, kindly allow me to join this thread. thank you in advance

Welcome! I have noted the following by progressively testing a duplicate excel table created from an existing connection:

1) Entering data into a new column on the duplicate table appears to work fine until you add new records to the original table or sort the duplicate table.
2) When an new record is added to the original table the data previously added to the duplicate table does not move down with the prior record.
3) After you sort a column in the duplicate field and refresh the duplicate table, the previously entered data bounces around to different record with every repeated refresh, even if no additional records are added or additional sorts are performed. The data in the new column for data entry moves up a few rows, then down, then back up, etc with each refresh.