Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same Column on Two Sheets (Auto-update / match)?
Hi. I have two sheets in the same workbook. I need a column on the 2nd
sheet to match a column on the 1st sheet. So that when data is entered into this column on sheet1 it is also entered automatically on sheet2. Is this possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I delete/add rows to sheet1 it does not update sheet2. Any advice would be greatly appreciated! Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same Column on Two Sheets (Auto-update / match)?
Your formula is fine but when you need to insert a row do the following so
that it occurs in both worksheets: 1. While holding down the CTRL key, click on the tabs for sheets 1 & 2 2. Select the row where you want to insert the new row 3. Click INSERT on the menu and select ROWS 4. Right click on either of the selected worksheet tabs 5. Select Ungroup sheets You'll have to copy the formula above the newly inserted row in sheet 2 Hope this helps -- Kevin Backmann "sarah" wrote: Hi. I have two sheets in the same workbook. I need a column on the 2nd sheet to match a column on the 1st sheet. So that when data is entered into this column on sheet1 it is also entered automatically on sheet2. Is this possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I delete/add rows to sheet1 it does not update sheet2. Any advice would be greatly appreciated! Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same Column on Two Sheets (Auto-update / match)?
Is there a simpler way to do it? A few different people will be working with
the document, and I can see it easily getting messed-up. If there's a way to ensure that both columns match, it would be preferred. Thanks again. "Kevin B" wrote: Your formula is fine but when you need to insert a row do the following so that it occurs in both worksheets: 1. While holding down the CTRL key, click on the tabs for sheets 1 & 2 2. Select the row where you want to insert the new row 3. Click INSERT on the menu and select ROWS 4. Right click on either of the selected worksheet tabs 5. Select Ungroup sheets You'll have to copy the formula above the newly inserted row in sheet 2 Hope this helps -- Kevin Backmann "sarah" wrote: Hi. I have two sheets in the same workbook. I need a column on the 2nd sheet to match a column on the 1st sheet. So that when data is entered into this column on sheet1 it is also entered automatically on sheet2. Is this possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I delete/add rows to sheet1 it does not update sheet2. Any advice would be greatly appreciated! Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same Column on Two Sheets (Auto-update / match)?
Aside from writing a macro to perform this operation, I don't know of any
other way of automating the process. It's either manual or macro driven. -- Kevin Backmann "sarah" wrote: Is there a simpler way to do it? A few different people will be working with the document, and I can see it easily getting messed-up. If there's a way to ensure that both columns match, it would be preferred. Thanks again. "Kevin B" wrote: Your formula is fine but when you need to insert a row do the following so that it occurs in both worksheets: 1. While holding down the CTRL key, click on the tabs for sheets 1 & 2 2. Select the row where you want to insert the new row 3. Click INSERT on the menu and select ROWS 4. Right click on either of the selected worksheet tabs 5. Select Ungroup sheets You'll have to copy the formula above the newly inserted row in sheet 2 Hope this helps -- Kevin Backmann "sarah" wrote: Hi. I have two sheets in the same workbook. I need a column on the 2nd sheet to match a column on the 1st sheet. So that when data is entered into this column on sheet1 it is also entered automatically on sheet2. Is this possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I delete/add rows to sheet1 it does not update sheet2. Any advice would be greatly appreciated! Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same Column on Two Sheets (Auto-update / match)?
Sarah, here's some code that would keep them sync'd although you may now get
comments about the workbook containing macros (code). Put this routine into the second worksheet's code segment and change the name of the source sheet in it. Then anytime the second sheet is chosen, the code will automatically set up column C (you can change that as required also) to be a mirror image of the designated column on the 'source' sheet. However, until this is actually done, then they may be out of sync which could affect formulas on other sheets that reference the information on this second/copy sheet in this column. To put the code in place, choose the sheet that needs to match the source sheet and right-click on the sheet's name tab and select [View Code]. Copy the code below and paste it into the code module; make changes to the sheet name and column reference as needed. Close the VB Editor. Try it out - make some changes on the source sheet, select the copy sheet and see that they appear on it properly. Private Sub Worksheet_Activate() 'assumes you want column C on this sheet 'to mirror the contents of column C on 'sheet named "Sheet1" in this workbook 'any time this sheet is selected, the 'contents of C on Sheet1 will be copied 'into column C of this sheet Dim sourceSheet As Worksheet Dim copyRange As Range Dim whereAmI As Range Set whereAmI = Selection 'change sheet name as required Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") 'change "C1:" and "C" as required Set copyRange = sourceSheet.Range("C1:" & _ sourceSheet.Range("C" & Rows.Count).End(xlUp).Address) copyRange.Copy Range("C1").PasteSpecial ' change "C1" as required. whereAmI.Activate Set whereAmI = Nothing Set copyRange = Nothing Set sourceSheet = Nothing Application.CutCopyMode = False End Sub "sarah" wrote: Is there a simpler way to do it? A few different people will be working with the document, and I can see it easily getting messed-up. If there's a way to ensure that both columns match, it would be preferred. Thanks again. "Kevin B" wrote: Your formula is fine but when you need to insert a row do the following so that it occurs in both worksheets: 1. While holding down the CTRL key, click on the tabs for sheets 1 & 2 2. Select the row where you want to insert the new row 3. Click INSERT on the menu and select ROWS 4. Right click on either of the selected worksheet tabs 5. Select Ungroup sheets You'll have to copy the formula above the newly inserted row in sheet 2 Hope this helps -- Kevin Backmann "sarah" wrote: Hi. I have two sheets in the same workbook. I need a column on the 2nd sheet to match a column on the 1st sheet. So that when data is entered into this column on sheet1 it is also entered automatically on sheet2. Is this possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I delete/add rows to sheet1 it does not update sheet2. Any advice would be greatly appreciated! Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto update helper column | Excel Discussion (Misc queries) | |||
Auto update the chart range for new sheets | Charts and Charting in Excel | |||
HOW DO I GET COLUMN TOTALS TO AUTO UPDATE? | New Users to Excel | |||
Auto Update Info from Different Sheets? | Excel Discussion (Misc queries) | |||
Vlookup? to match column in two sheets | Excel Discussion (Misc queries) |