Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletions?
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#2
|
|||
|
|||
One way to try ..
Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#3
|
|||
|
|||
LeeC & Max -
The INIDRECT() function is necessary only if there's a chance that the first row might be deleted. Also, ROWS($A$1:A1)-1 can be shortened to ROW()-1 So, if LeeC knows that the first row will never be deleted, this shorter formula works, too: =OFFSET(Sheet1!$A$1,ROW()-1,) "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#4
|
|||
|
|||
Thank you so very much! We (my associate and I) were searching everywhere in
"Help", certain that this could be done. Again - Thanks! "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#5
|
|||
|
|||
Thanks so much - - This shortened approach will work in some of our
scenarios. Thanks for your help, and your time! "Duke Carey" wrote: LeeC & Max - The INIDRECT() function is necessary only if there's a chance that the first row might be deleted. Also, ROWS($A$1:A1)-1 can be shortened to ROW()-1 So, if LeeC knows that the first row will never be deleted, this shorter formula works, too: =OFFSET(Sheet1!$A$1,ROW()-1,) "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#6
|
|||
|
|||
You're welcome !
Glad it helped .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... Thank you so very much! We (my associate and I) were searching everywhere in "Help", certain that this could be done. Again - Thanks! |
#7
|
|||
|
|||
Thanks, Duke. Agreed there's shorter versions. But for increased robustness'
sake, thought the extra INDIRECT wrap, and the use of the slightly longer "ROWS($A$1:A1)-1" over "ROW()-1" was worth it in this instance .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Duke Carey" wrote in message ... LeeC & Max - The INIDRECT() function is necessary only if there's a chance that the first row might be deleted. Also, ROWS($A$1:A1)-1 can be shortened to ROW()-1 So, if LeeC knows that the first row will never be deleted, this shorter formula works, too: =OFFSET(Sheet1!$A$1,ROW()-1,) |
#8
|
|||
|
|||
Max wrote...
.... In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) .... Very baroque, almost rococo. Since your formula is meant to give Sheet1!A1 in Sheet2!A1, Sheet1!A99 in Sheet2!A99, etc., better to simplify. =INDIRECT("Sheet1!RC",0) Note that this doesn't need any changes to be used in other columns. |
#9
|
|||
|
|||
"Harlan Grove" wrote
.... Since your formula is meant to give Sheet1!A1 in Sheet2!A1, Sheet1!A99 in Sheet2!A99, etc.. Just a clarification. No, not necessarily. Perhaps it might have been more illustrative to have described the example starting cell in Sheet2 as say, B2, rather than the coincident "A1" =INDIRECT("Sheet1!RC",0) Interestingly concise! Thanks. But how could the above be modified / adapted so that the formula can be placed in any starting cell in Sheet2, say in B2 and copied down to link to Sheet1's col A, say? -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#10
|
|||
|
|||
Max wrote...
.... Interestingly concise! Thanks. But how could the above be modified / adapted so that the formula can be placed in any starting cell in Sheet2, say in B2 and copied down to link to Sheet1's col A, say? With hardcoding, so Sheet2!B2 returns the value of Sheet1!A1, B2: =INDIRECT("Sheet1!R[-1]C[-1]",0) Hardcoding only the source worksheet name and top-left result cell address, B2: =INDIRECT("Sheet1!R"&ROWS($B$2:B2)&"C"&COLUMNS($B$ 2:B2),0) |
#11
|
|||
|
|||
Thanks, Harlan !
Good alternatives to learn .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Sounds like just what I need, too: but being a newbie, your instructions have
me stumped. How/where do I enter the "=OFFSET..." formula? When I click the formula bar icon, all I'm allowed to do is select a pre-existing formula from a list I've already got words in all the cells of the column where I want to put the formula. Will the formual wipe out the words? "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Yes, the formula will wipe out all the words if you put them in the same
cell, I believe the point with Max's formulas was to use another sheet for the formulas thus link the from that sheet. You enter a formula by selecting a cell and start by typing equal sign then type the formula, or you can select a cell and click in the formula bar and enter the formula there -- Regards, Peo Sjoblom "BobW" wrote in message ... Sounds like just what I need, too: but being a newbie, your instructions have me stumped. How/where do I enter the "=OFFSET..." formula? When I click the formula bar icon, all I'm allowed to do is select a pre-existing formula from a list I've already got words in all the cells of the column where I want to put the formula. Will the formual wipe out the words? "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
What I have is this kind of table:
Evaluation Criteria Rating Easy to Use yes Intuitive Design yes Windows Compatible no etc. I need to have a second sheet with the same Column A items, but different Column B content....and they need to be linked so that if I insert another Evaluation criterion (or delete one) on sheet A, the corresponding row will also appear or disappear on sheet B.. But, entering the suggested formula in each column A cell, will wipe out the data in that column. Hopefully someone will know how to link the Column As on both sheets without requiring a formula to occupy each cell of column A. Not sure how your suggestion about using another sheet for the formulas, would solve my problem, but, me being a newbie, it's not surprising! :-) "Peo Sjoblom" wrote: Yes, the formula will wipe out all the words if you put them in the same cell, I believe the point with Max's formulas was to use another sheet for the formulas thus link the from that sheet. You enter a formula by selecting a cell and start by typing equal sign then type the formula, or you can select a cell and click in the formula bar and enter the formula there -- Regards, Peo Sjoblom "BobW" wrote in message ... Sounds like just what I need, too: but being a newbie, your instructions have me stumped. How/where do I enter the "=OFFSET..." formula? When I click the formula bar icon, all I'm allowed to do is select a pre-existing formula from a list I've already got words in all the cells of the column where I want to put the formula. Will the formual wipe out the words? "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Here's a sample construct:
http://cjoint.com/?lrxU7szxxX BobW_misc.xls Assuming the source table is in Sheet1, cols A to C, with Evaluation in col A In Sheet2, Put in A2, copy down: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) Sheet2 will reflect the row deletions / insertions made in Sheet1 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Max, YOU THE MAN! Thank you for the example file...just what i needed!
"Max" wrote: Here's a sample construct: http://cjoint.com/?lrxU7szxxX BobW_misc.xls Assuming the source table is in Sheet1, cols A to C, with Evaluation in col A In Sheet2, Put in A2, copy down: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) Sheet2 will reflect the row deletions / insertions made in Sheet1 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Glad it helped, BobW !
Thanks for the feedback -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "BobW" wrote in message ... Max, YOU THE MAN! Thank you for the example file...just what i needed! |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Max, I love how it works, but...still don't understand where/how you inserted
the formula. Remember, I'm a newbie "Max" wrote: Glad it helped, BobW ! Thanks for the feedback -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "BobW" wrote in message ... Max, YOU THE MAN! Thank you for the example file...just what i needed! |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
"BobW" wrote
.. where/how you inserted the formula. In Sheet2, Put in A2, copy down: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) From the above lines, the formula is placed in Sheet2's A2, and then copied down I'd select the tab: Sheet2, then select the cell A2, click inside the formula bar, then either type-out the formula, or paste it in (if I had copied it earlier). And then I'd press ENTER to confirm the formula. To copy the formula in A2 down, I'd re-select A2 and point the cursor at its fill handle, i.e. the little black solid box at the bottom right corner of A2 (the cursor will also turn into a "black cross"), and drag it down as far as I want. Hope the above helps .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Max,
I notice that in sheet 2, row 1 of your example, there's this formula: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Whereas the formula for row 2 is more complicated: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) What's the purpose of the Row 1 formula...and what if I need three or four additional header rows instead of just one row...do I just change the "A2"s throughout, to "A5" or "A6", etc? Also, "Max" wrote: "BobW" wrote .. where/how you inserted the formula. In Sheet2, Put in A2, copy down: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) From the above lines, the formula is placed in Sheet2's A2, and then copied down I'd select the tab: Sheet2, then select the cell A2, click inside the formula bar, then either type-out the formula, or paste it in (if I had copied it earlier). And then I'd press ENTER to confirm the formula. To copy the formula in A2 down, I'd re-select A2 and point the cursor at its fill handle, i.e. the little black solid box at the bottom right corner of A2 (the cursor will also turn into a "black cross"), and drag it down as far as I want. Hope the above helps .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
In Sheet2
What's the purpose of the Row 1 formula The formula in A1 is actually not required (I forgot to delete it) as the col headers in A1:C1 could just be pasted over from Sheet1 Whereas the formula for row 2 is more complicated: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"", OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)) The above isn't really that complicated <g. It just includes an IF condition to return blanks: "" should the result evaluated by the OFFSET formula be equal to zero, i.e. basically: =IF(OFFSET(...)=0,"",OFFSET(...)) The inclusion of the IF here is just one way to produce a cleaner look in Sheet2 ...and what if I need three or four additional header rows instead of just one row... do I just change the "A2"s throughout, to "A5" or "A6", etc? No, simply put the same formula above (in A2) in the new starting cell, then copy down from there. The formula* will auto-increment correctly when you copy down from the starting cell. For example, if we wanted to link to Sheet1's A2:A10 in say, Sheet2's A10:A19, i.e. start the link in Sheet2's A10 (instead of A2), then we would put the same formula into Sheet2's A10: =IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"",OFFSET(INDIRECT("She et1!A1"),ROWS($A$1:A2)-1,)) and then copy A10 down to A19 *this part in the formula will auto-increment: ROWS($A$1:A2) when copied down to become: ROWS($A$1:A3), ROWS($A$1:A4), etc Trust the above clarifies it a little better .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Sorry, "A19" should read as "A18" in the lines
... link to Sheet1's A2:A10 in say, Sheet2's A10:A19 ... and then copy A10 down to A19 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Max, the formula you gave, to "copy down", works great except that when I add
or delete a row to worksheet 1, column A, it only adds or deletes one CELL in the Column A's of all the other worksheets...resulting in data misalignment with the rest of the columns on those sheets. How can I make the row change in worksheet 1, column A, replicate as a row change in all the other worksheets? (Note: Worksheet 1's columns b through ?? have different data layout content and requirements than columns b through ?? on the other worksheets.) I also forwarded this to you via email with examples. "Max" wrote: Sorry, "A19" should read as "A18" in the lines ... link to Sheet1's A2:A10 in say, Sheet2's A10:A19 ... and then copy A10 down to A19 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#24
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
.. How can I make the row change
in worksheet 1, column A, replicate as a (corresponding) row change in all the other worksheets? Formulas cannot achieve the essence above, Bob. We would need a subroutine (vba) to do that. (I did a brief trawl through google's archives but was unable to find something suitable to suggest) Hang around awhile for possible responses from others versed in vba who might have something to offer you. (I've cross posted this response to .programming) Good luck ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "BobW" wrote in message ... Max, the formula you gave, to "copy down", works great except that when I add or delete a row to worksheet 1, column A, it only adds or deletes one CELL in the Column A's of all the other worksheets... resulting in data misalignment with the rest of the columns on those sheets. How can I make the row change in worksheet 1, column A, replicate as a row change in all the other worksheets? (Note: Worksheet 1's columns b through ?? have different data layout content and requirements than columns b through ?? on the other worksheets.) I also forwarded this to you via email with examples. |
#25
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Bob,
In the interim, perhaps you would like to try the 2 "bare-bones" subs below on a **spare** copy of your book (First, kill all previous formulas in col A in all "slave" sheets with an in-place: Copy Paste special check "Values" OK) Press Alt+F11 to go to VBE Click Insert Module Copy and paste the 2 subs below into the code window Press Alt+Q to exit VBE and go back to Excel In Excel, in your master sheet: ToolEvaluation Click View Toolbars Forms Click on the button icon and draw a button somewhere on the sheet The Assign Macro dialog will pop up Look for "InsertRow" in the dialog box, select it OK (or just double-click on "InsertRow") The above assigns the Sub InsertRow() to this button. Right-click on the button Edit Text [to rename the button] Repeat to draw another button, assign "DeleteRow" Right-click the button to select, re-position the 2 buttons somewhere near the top, say, within A2 Test it out .. In ToolEvaluation, Say, we want to insert a new row 7 Select say, A7, then click the button "InsertRow" This will insert a new row 7 in "ToolEvaluation", and correspondingly insert a new row 7 as well in the slave sheets: "Documentum" & "Hummingbird" Re-select the "new" A7, click DeleteRow This will delete the entire row 7 in "ToolEvaluation", and correspondingly deletes row 7 in sheets: "Documentum" & "Hummingbird" as well '-------- Sub InsertRow() 'ToolEvaluation is the master sheet Sheets(Array("ToolEvaluation", "Documentum", "Hummingbird")).Select Sheets("ToolEvaluation").Activate ActiveCell.EntireRow.Insert Sheets("ToolEvaluation").Select End Sub Sub DeleteRow() 'ToolEvaluation is the master sheet Sheets(Array("ToolEvaluation", "Documentum", "Hummingbird")).Select Sheets("ToolEvaluation").Activate ActiveCell.EntireRow.Delete Sheets("ToolEvaluation").Select End Sub '------ -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#26
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Max, you genius, i owe you a virtual beer!!!
By the way, just for fun I tried it without first killing all the previous formulas in the slave sheets....guess what? It wasn't necessary; when I add a new row using your buttion, it's reflected on all slave sheets AND the new row on the slave sheet inherits the 'copied down' formula automatically so not only does the new row go in fine, but its new name replicates through all the slave sheets, too. Just what I wanted! Incidentally, the actual workbook has about 22 slave sheets, so I had to add all their names into your array string function. This is just wonderful; thank you for the lesson. You are a good teacher! "Max" wrote: Bob, In the interim, perhaps you would like to try the 2 "bare-bones" subs below on a **spare** copy of your book (First, kill all previous formulas in col A in all "slave" sheets with an in-place: Copy Paste special check "Values" OK) Press Alt+F11 to go to VBE Click Insert Module Copy and paste the 2 subs below into the code window Press Alt+Q to exit VBE and go back to Excel In Excel, in your master sheet: ToolEvaluation Click View Toolbars Forms Click on the button icon and draw a button somewhere on the sheet The Assign Macro dialog will pop up Look for "InsertRow" in the dialog box, select it OK (or just double-click on "InsertRow") The above assigns the Sub InsertRow() to this button. Right-click on the button Edit Text [to rename the button] Repeat to draw another button, assign "DeleteRow" Right-click the button to select, re-position the 2 buttons somewhere near the top, say, within A2 Test it out .. In ToolEvaluation, Say, we want to insert a new row 7 Select say, A7, then click the button "InsertRow" This will insert a new row 7 in "ToolEvaluation", and correspondingly insert a new row 7 as well in the slave sheets: "Documentum" & "Hummingbird" Re-select the "new" A7, click DeleteRow This will delete the entire row 7 in "ToolEvaluation", and correspondingly deletes row 7 in sheets: "Documentum" & "Hummingbird" as well '-------- Sub InsertRow() 'ToolEvaluation is the master sheet Sheets(Array("ToolEvaluation", "Documentum", "Hummingbird")).Select Sheets("ToolEvaluation").Activate ActiveCell.EntireRow.Insert Sheets("ToolEvaluation").Select End Sub Sub DeleteRow() 'ToolEvaluation is the master sheet Sheets(Array("ToolEvaluation", "Documentum", "Hummingbird")).Select Sheets("ToolEvaluation").Activate ActiveCell.EntireRow.Delete Sheets("ToolEvaluation").Select End Sub '------ -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#27
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Glad it worked out well for you, Bob
and we had fun, too, along the way ! <g Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "BobW" wrote in message ... Max, you genius, i owe you a virtual beer!!! By the way, just for fun I tried it without first killing all the previous formulas in the slave sheets....guess what? It wasn't necessary; when I add a new row using your buttion, it's reflected on all slave sheets AND the new row on the slave sheet inherits the 'copied down' formula automatically so not only does the new row go in fine, but its new name replicates through all the slave sheets, too. Just what I wanted! Incidentally, the actual workbook has about 22 slave sheets, so I had to add all their names into your array string function. This is just wonderful; thank you for the lesson. You are a good teacher! |
#28
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion?
Max, I wonder if you can suggest how I can link column 1, sheet A in workbook
A, to all the Sheet A's in workbooks 2-25, so that if I change any column A cell in Workbook 1, and then open any of the other workbooks, each of their Sheet A cell contents will be updated to echo the change, AND also so that if rows are inserted in Workbook A sheet A, those rows will also become inserted in all the Sheet A's in the other workbooks...upon opening them... I've tried Insert Link, and it works for the cell contents, but it doesn't seem to work for automatically picking up added rows. "Max" wrote: Glad it worked out well for you, Bob and we had fun, too, along the way ! <g Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "BobW" wrote in message ... Max, you genius, i owe you a virtual beer!!! By the way, just for fun I tried it without first killing all the previous formulas in the slave sheets....guess what? It wasn't necessary; when I add a new row using your buttion, it's reflected on all slave sheets AND the new row on the slave sheet inherits the 'copied down' formula automatically so not only does the new row go in fine, but its new name replicates through all the slave sheets, too. Just what I wanted! Incidentally, the actual workbook has about 22 slave sheets, so I had to add all their names into your array string function. This is just wonderful; thank you for the lesson. You are a good teacher! |
#29
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion?
"BobW" wrote:
.. also so that if rows are inserted in Workbook A sheet A, ... those rows will also become inserted in all the Sheet A's in the other workbooks...upon opening them... Afraid I'm out of my depth on this. Perhaps others better versed in vba would step in here to offer you something. Hang around awhile. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#30
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Is it possible to do modify this for multiple columns at a time or would I
have to do this for each column? So, I want to be able to do the same thing, but for a whole range instead of just one column. "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
#31
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I link cells, sheet to sheet, to recognize row deletion
Oh and also to make it so that blank cells in the source are also blank in
the destination (instead of a 0) Thanks! "Max" wrote: One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,) Copy A1 down as many rows as the linking is required to col A in Sheet1 This should now "recognize" any subsequent row* insertions / deletions in Sheet1 (*or even column deletions in Sheet1) viz. col A in Sheet2 will always point to what's in col A in Sheet1 Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead, change: INDIRECT("Sheet1!A1") to INDIRECT("Sheet3!B1") in the formula -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LeeC" wrote in message ... In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with link cells between 2 spreadsheets | Excel Discussion (Misc queries) | |||
How to link cells and keep number format altogether | Excel Discussion (Misc queries) | |||
How do I link two cells as to allow me to change the value in eit. | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
how can i select all the cells with same color on a sheet if there are multipale colors by vba code | Charts and Charting in Excel |