My request is a bit simplier. I have two lists in Excel that has email
addresses in them. I have been asked to delete email addresses from one,
using the other as the master list to do so.

I tried the "Compare worksheets side by side" feature, but, it deletes from
both, not what I want. Basically, I should end up with the master list in
tact and the second list updated with the new edit.
--
Thanks

Flo


"Otto Moehrbach" wrote:

Barbara
Here is a macro for you to try. I made up a small dummy file with some
data in it in order to develop the code I would need. This code works on my
file. I say this because it may not work on your file for a number of
reasons. For instance, this code searches each of the other 2 sheets for
the book title. The code is very dumb and it searches for the EXACT book
title that exists in Column B of the row and sheet in which the change was
made. A spelling difference or an extra space will cause a search failure.
For this reason I included an error trap in case the book title could not be
found in one of the other 2 sheets. In this instance, a message box will
pop up and tell you that title such-and-such could not be found in sheet
<sheet name. The code will stop at that point. This may present a problem
for you because the code is looping through the other 2 sheets and may have
found the title in the first sheet in the loop and will have made the needed
changes in that first sheet, and then failed to find it in the second sheet,
and stopped then and not have made the changes in that second sheet. Think
about that.
If you think this may be a real problem with your data, I can write some
code for you to check that every title in every sheet can be found in every
other sheet. This would be some code that you would run just once or maybe
once in a while if you continuously add titles as I think you might.
Note that this macro is a workbook event macro and, as such, must be
placed in the workbook module of your file. In all versions of Excel short
of 2007 you can access that module by right-clicking the Excel icon that is
located immediately to the left of the word "File" in the menu that runs
across the top of your screen, and select View Code. Paste this macro into
that module. "X" out of the module to return to your sheet.
The code in this macro has some long lines that the newsgroup post may
wrap. This is a no-no because the code is not forgiving of line wrapping.
If you wish, send me an email and I'll send you the file I used for this and
it will have the code properly placed. My email address is
. Remove the "extra" from this address. HTH
Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rColBSht As Range, Sht As Worksheet, TheRow As Long
Dim TheCol As Long, OldTitle As String, NewTitle As String
Dim rAllRng As Range
Set rAllRng = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rAllRng = rAllRng.Offset(, -1).Resize(, 5)
If Not Intersect(Target, rAllRng) Is Nothing Then
TheCol = Target.Column
If TheCol = 2 Then
NewTitle = Target.Value
Application.EnableEvents = False
Application.Undo
OldTitle = Target.Value
Target.Value = NewTitle
Application.EnableEvents = True
Else
OldTitle = Cells(Target.Row, 2).Value
End If
For Each Sht In ThisWorkbook.Worksheets
If Sht.Name < Sh.Name Then
With Sht
Set rColBSht = .Range("B2", .Range("B" &
Rows.Count).End(xlUp))
If rColBSht.Find(What:=OldTitle,
LookAt:=xlWhole) Is Nothing Then
MsgBox "Book title '" & OldTitle & "'
cannot be found in sheet " & Sht.Name & "." & Chr(13) & _
"Note that some of the sheets may
have been updated.", 16, "Title Error"
Exit Sub
Else
TheRow = rColBSht.Find(What:=OldTitle,
LookAt:=xlWhole).Row
Application.EnableEvents = False
.Cells(TheRow, TheCol).Value =
Target.Value
Application.EnableEvents = True
End If
End With
End If
Next Sht
End If
End Sub


"Military Mom Too" wrote in
message ...
Otto --

The headings a Title; Author Last Name; Author First Name; Comments
and
Column A is where I place an "x" if book has been bought. Most of the
book
titles are unique, but you know every once in a while, you run across two
with the same name.

Thanks again,
Barbara


"Otto Moehrbach" wrote:

Mom
That does except for the last question I had. Excel needs something that
it
can use to find the rows in the other sheets when you make a change in
one
sheet. For example, let's say that you have ID numbers in one column.
ID
numbers are unique in that only one row in each sheet will have that ID
number. Excel can then look in the row of the cell that was changed and
get
that ID number, even if the ID number is the thing that was changed, and
search for that ID number in the other 2 sheets and duplicate the change
that was made.
You say that you have a list of books. Can you say that the names of the
books are unique? Or is there a code number that goes with each book
that
is unique? Anything that is unique?
It would help if you gave me the column headers so that I would know what
column to search for this unique entity as well as which column to
change.
HTH Otto
"Military Mom Too" wrote in
message ...
I have one workbook with three spreadsheets -- all are identical except
for
the way they are sorted. When I edit an entry on one sheet, I would
like
for
it to edit that same entry on the other two sheets. For instance, if I
want
to delete an entry on sheet 1, I would like to delete it on sheets 2
and
3.
But the entry to be deleted/edited will be a different row on each
sheet.
Does that answer any of your questions? Thanks again.


"Otto Moehrbach" wrote:

Mom
Do you mean you have 3 sheets in one workbook or one sheet in each
of
3
workbooks?
Do you mean that what's in one row in one sheet is identical to what
is
in
some other rows in the other 2 sheets?
Do you want to make a change in one row of one sheet and automatically
have
that change made in the proper rows of the other 2 sheets?
How many columns in each sheet?
What do you want to use as the trigger to make Excel act? What I mean
by
this is: Excel needs to know that you have finished making the
changes
in
that one row so that it can then act to revise the other sheets. If
you
have only one column in each sheet, then Excel can act when an entry
in
that
column is changed. But if you have multiple columns, how can Excel
know
when you are through editing that row? Think about it. Or perhaps
you
want
Excel to update the other 2 sheets when a change is made in any of the
columns? Also, if you have multiple columns, can you say that one
column
has unique entries (no entry repeats in that column in the same
sheet)?
HTH
Otto
"Military Mom Too" wrote in
message ...
I would like to be able to revise the data in several spreadsheets at
once.
I have a list of books in three different spreadsheets, each sorted
differently. I would like to add, delete or edit data in all three
without
sorting them all alike first. I know that I can enter new data by
holding
down CTRL and clicking each spreadsheet, but it doesn't work for
editing
the
data. Thanks for the help.