Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add/Revise data in separate spreadsheets all at once
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining data from multiple worksheets and separate spreadsheets | Excel Discussion (Misc queries) | |||
how do I merge data from separate spreadsheets | Excel Discussion (Misc queries) | |||
updating separate spreadsheets | Excel Worksheet Functions | |||
how do i combine two separate spreadsheets? | Excel Discussion (Misc queries) | |||
Separate Spreadsheets | Excel Worksheet Functions |