In , Dave Peterson
spake thusly [edited for flow]:

Taxed Mind wrote:
If I have a list on one worksheet, which is then copied to a
second worksheet, how do I ensure the second updates if I add
new rows or change the text to the first.

I would think that some of the previous suggestions were to not
do this. Keep your data in one spot. If you need to extract
the data later, you can. But do the updates only in that one
location.

If you really have to keep the data in multiple locations, then I
think the only way to insure what you want is to do it manually
and double, triple, ... check your input.

Hmm. Well, I'm doing exactly what "Taxed Mind" wanted, and I
specifically chose to do it in a way that ensures data integrity
(because I got tired of data corruption given the old, manual
way I used to do it).

Here's a synopsis:

The data that is frequently updated that I want to be sure to keep
unsullied, I import into a worksheet in the same workbook. I
have a macro for this, and except when the macro runs, the worksheet
is left protected and hidden. It isn't in my way, because I
can't even see it unless I need to for some reason and go unhide it.

That data is imported from a CSV file that I download. I have
it set up as a data source for the normally hidden sheet.

Okay, then in my main sheets that I want to use parts of the data
from, I simply point to the hidden sheet and fill down. Now I
can sort, add other columns, etc. I have a macro for refreshing
the working sheet(s) and sorting. It's actually the same macro
as the one above.

I gave myself some visual clues as to which data is "original"
and which is altered or added. The main such is, the column
headers for the relevant columns that are left as in the original,
I format in one color; the headers for "synthetic" columns I
have there that are derived from the original data, I color-
code in a different color. I also have some conditional formatting
going on to compare values to make sure nothing got corrupted when
I wasn't "looking out" with my formulas.

I actually have a third header color for "derivative" columns
that are, in turn, based on the first set of "synthetic" columns.
Anyway, I can tell at a glance from the colors and the conditional
formatting which data is original and which is mine.

The charts also auto-update. The most recent thing I figured
out was to have charts whose title[1] changes depending on which
data I have filtered. Note that I don't yet know any VBA,
so this is all done with standard macros and lots of trial
and error.

[1] I cheat here. It's not really the title that gets used,
but a dummy data set whose name shows up in the legend.

Dallman Ross