How to synchronize rows between worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Howard Schisler - 30 Mar 2007 15:32 GMT

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?

Reply to this Message

Bernie Deitrick - 30 Mar 2007 15:58 GMT

Howard,

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

HTH,
Bernie
MS Excel MVP

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

Reply to this Message

Earl Kiosterud - 30 Mar 2007 16:07 GMT

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.

Signature

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.

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

Reply to this Message

Howard Schisler - 30 Mar 2007 16:44 GMT

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.

> Howard,
>
[quoted text clipped - 8 lines]
> >
> > Is this possible?

Reply to this Message

Earl Kiosterud - 30 Mar 2007 18:42 GMT

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.

Signature

> Thanks Earl. That's exactly what I was looking for. I didn't know Excel
> could do that.
[quoted text clipped - 18 lines]
>> >
>> > Is this possible?

Reply to this Message

Howard Schisler - 30 Mar 2007 19:12 GMT

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.

> Howard,
>
[quoted text clipped - 26 lines]
> >> >
> >> > Is this possible?

Reply to this Message

Max - 31 Mar 2007 02:46 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message