Re: 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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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
---

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
> ---
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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, 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

"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
---