Referencing cells across sheets

[Karl]

Hi,

I've created a workbook with three sheets. The first three
columns of sheet 2 and sheet 3 are referenced to the first
three columns of sheet 1.

What I'd like to do, is set the workbook up so that if I
insert a row before, between or after referenced rows in
sheet one, a row will also be inserted, and referenced, in
the corresponding place on sheets 2 and 3. Does anyone
know how to do this?

Also, if no data is entered into a cell on sheet one, the
value shown in the corresponding cells of sheets '2'
and '3' is '0'. I need those cells to remain blank until a
value is entered into sheet 1. Can anyone help with that
too?

Thanks

Karl

 

[Guest]

Karl

for your 2nd issue, only you need to go to tool options
menu, view tab and uncheck Zero values

I hope this solve your problem

 

[Max]

I've created a workbook with three sheets. The first three
columns of sheet 2 and sheet 3 are referenced to the first
three columns of sheet 1.

What I'd like to do, is set the workbook up so that if I
insert a row before, between or after referenced rows in
sheet one, a row will also be inserted, and referenced, in
the corresponding place on sheets 2 and 3. Does anyone
know how to do this?

One way .. :

Click on A1 in Sheet2

Hold down Shift key and click on Sheet3's tab
(this groups the sheets: " [Group] "will appear in the titlebar)

Put in A1:

=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy A1 across to C1,
then fill down by a "safe" number of rows
to cover the max expected likely number of rows
(say, down to C500?)

Now to remove the grouping of the Sheets 2 and 3:
Right-click on either sheet's tab > Ungroup sheets
(or, click on Sheet1's tab)

Also, if no data is entered into a cell on sheet one, the
value shown in the corresponding cells of sheets '2'
and '3' is '0'. I need those cells to remain blank until a
value is entered into sheet 1. Can anyone help with that
too?

For a neat look, suppress extraneous zeros
from showing in Sheets 2 and 3 via:
Tools > Options > View tab > Uncheck "Zero values" > OK
(this has to be done on each sheet in turn)

The above should provide what's desired

 

[David]

For your first question, if you want each sheet to have
exactly the same rows, then an easier way is to select
all three sheets (as Max suggested) and do an insert row -
it will appear in all three.

 

[Karl]

Thanks all, that's brilliant!

 

[Max]

Thanks all, that's brilliant!

You're welcome !
Thanks for the feedback

 

[Guest]

Will this work in a spreadsheet where multiple pages of data have been sorted
and linked into separate worksheets within the workbook and outside the
workbook? Whenever I add a row or delete a row within my master schedule, it
messes up all of my individual worksheets. Thank you.

I've created a workbook with three sheets. The first three
columns of sheet 2 and sheet 3 are referenced to the first
three columns of sheet 1.

What I'd like to do, is set the workbook up so that if I
insert a row before, between or after referenced rows in
sheet one, a row will also be inserted, and referenced, in
the corresponding place on sheets 2 and 3. Does anyone
know how to do this?

One way .. :

Click on A1 in Sheet2

Hold down Shift key and click on Sheet3's tab
(this groups the sheets: " [Group] "will appear in the titlebar)

Put in A1:

=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy A1 across to C1,
then fill down by a "safe" number of rows
to cover the max expected likely number of rows
(say, down to C500?)

Now to remove the grouping of the Sheets 2 and 3:
Right-click on either sheet's tab > Ungroup sheets
(or, click on Sheet1's tab)

Also, if no data is entered into a cell on sheet one, the
value shown in the corresponding cells of sheets '2'
and '3' is '0'. I need those cells to remain blank until a
value is entered into sheet 1. Can anyone help with that
too?

For a neat look, suppress extraneous zeros
from showing in Sheets 2 and 3 via:
Tools > Options > View tab > Uncheck "Zero values" > OK
(this has to be done on each sheet in turn)

The above should provide what's desired

 

[Max]

Will this work in a spreadsheet where multiple
pages of data have been sorted and linked into
separate worksheets within the workbook and
outside the workbook? Whenever I add a row
or delete a row within my master schedule, it
messes up all of my individual worksheets. Thank you.

Not really sure whether it fits your situation ..
but perhaps you might want to adapt and play around with
these in *spare* copies of your workbooks ..

[INDIRECT() is used for the reference in OFFSET
to cater for possible deletion of cell A1]

Assuming Sheet1 in Book1.xls is your "master sheet"

In the "slave" sheets in Book1.xls (sheets in the same book),

Put in A1:
=OFFSET(INDIRECT("'"&"Sheet1"&"'!$A$1"),ROW(A1)-1,COLUMN(A1)-1)

Copy A1 across and down to cover a safe max expected range

In the "slave" sheets in Book2.xls (sheets in *another* book)

Put in A1:

=OFFSET(INDIRECT("'["&"Book1.xls"&"]"&"Sheet1"&"'!$A$1"),ROW(A1)-1,COLUMN(A1
)-1)

Copy A1 across and down to cover a safe max expected range

Note: Book1.xls has to be *open* for the formulas in Book2.xls
to work, otherwise you'll get #REF!

 

[Max]

[INDIRECT() is used for the reference in OFFSET

to cater for possible deletion of cell A1]

should read as:

[INDIRECT() is used for the reference in OFFSET
to cater for possible deletion of cell A1 in the "master sheet"
which is assumed to be Sheet1 in Book1.xls]