How to synchronize rows between worksheets

[Guest]

I have a workbook with 2 worksheets in it. I would like to synchronize the
rows between those worksheets, so that when a row is inserted or deleted in
the first worksheet, a row is inserted or deleted in the second worksheet. I
would like to do this without using a macro or VB.

Is this possible?

 

[Bernie Deitrick]

Howard,

No, it isn't possible without VB. Use one sheet and then it isn't an issue.

HTH,
Bernie
MS Excel MVP

 

[Earl Kiosterud]

Howard,

If it's the same row (same row number), select both sheets (use the Ctrl key as you click
the tab of the second sheet). Now insert the row. Be sure to change the selection to only
one sheet, or you can end up saying very bad words.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome.

 

[Guest]

Thanks Earl. That's exactly what I was looking for. I didn't know Excel
could do that.

The downside to that, however, is that editing a cell while both sheets are
selected results in the same edit across both sheets -- something I want to
avoid.

Thanks.

 

[Earl Kiosterud]

Howard,

Yes, if a cell is changed while both sheets are selected, that cell is definitely changed in
both sheets. That was my reference to saying bad words. It might be better to use a macro
that does the row insert in the sheets.

I'm a little raised-eyebrowish about inserting a row in two different sheets. I wonder if
these shouldn't be in the same sheet, perhaps side by side.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome.

-----------------------------------------------------------------------

 

[Guest]

Got it, and now I understand your first reply!

I think I'll abandon this approach and return to having everything in 1
worksheet.
Thanks.

 

[Guest]

One formulas play if it's between 2 sheets in the same book ..

In Sheet2,

Instead of using simple link formulas in A1:
=Sheet1!A1
with A1 then copied across / down

Use instead in A1:
=IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1))

Then just copy A1 across / down as required. This returns the same kind of
links as the former but with the added flexibility that any new row/column
insertions in Sheet1 (within the linked area) will now be reflected in
Sheet2. Test it out and see for yourself.

---