Keeping Appended Data In Sync After Data Connection Refresh

schnide

New Member
Joined
Feb 14, 2008
Messages
24
I have a table with two columns in Excel:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]United Kingdom[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United States of America[/TD]
[TD]Washington DC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]France[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Spain[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Japan[/TD]
[TD]Tokyo[/TD]
[/TR]
</tbody>[/TABLE]

Column A is populated by an external data connection from a CSV file. Column B is data that I then manually append with metadata, directly in Excel.

This is all fine until additional data is added to the CSV file. When I refresh the data in Excel, it pushes down column A only and so column B becomes out of sync:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]United Kingdom[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United States of America[/TD]
[TD]Washington DC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Italy[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]France[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Spain[/TD]
[TD]Tokyo[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Japan[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

..and of course the metadata is wrong.

Can anyone suggest a way to setup the sheet so that column B will dynamically push down when new rows are added, like so?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]United Kingdom[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United States of America[/TD]
[TD]Washington DC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Italy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]France[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Spain[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Japan[/TD]
[TD]Tokyo[/TD]
[/TR]
</tbody>[/TABLE]

..so that I can continue to add the metadata and keep it synced up? My guess is that there probably isn't a way since Excel wouldn't know how to handle a row being removed if an entry present in the CSV file was no longer there on a subsequent refresh.

However, all thoughts welcome (and appreciated as always)!
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top