Linking cells between worksheets

[adam]

Hello, I would like to know how to link cells between worksheets. For
example, I have 5 sheets within a workbook. I would like to link cell
"b10" on sheet 3 with cell "b16" on sheet 5 so that whenever I change
the data in one cell, the other automatically changes. Even if I erase
the data in one cell, I would also like it to be erased in the other
cell. Is there a way for this to be done? Thank you.

Adam

 

[CyberTaz]

Afraid you can't [easily] have it *both ways* Adam

One cell has to be the source that another looks to... the cell that does
the 'looking' would contain a simple formula link to the source.In your
example, B16 on Sheet 5 would contain =Sheet3!B10 which you wouldn't want to
delete from Sheet 5. If you change/delete the content from Sheet3!B10, the
linked cell on Sheet 5 would update accordingly. However, if you delete the
content of Sheet5!B16 you're deleting the link to the other sheet. If this
is what you are actually wanting to do you can either type the formula or
just type the = then click the sheet tab & cell, press return.

Otherwise, depending on what you're actually looking to accomplish, you may
be able to get it done most easily with Edit>Replace.

 

[adam]

Thanks for the reply. Part of my problem has been solved very well.
But, how do I link a cell in Sheet 3 that is blank to a cell in Sheet 5
without a "0" showing up in the cell in Sheet 5? Can this even be
done? Thanks.

Adam

Afraid you can't [easily] have it *both ways* Adam

One cell has to be the source that another looks to... the cell that does
the 'looking' would contain a simple formula link to the source.In your
example, B16 on Sheet 5 would contain =Sheet3!B10 which you wouldn't want to
delete from Sheet 5. If you change/delete the content from Sheet3!B10, the
linked cell on Sheet 5 would update accordingly. However, if you delete the
content of Sheet5!B16 you're deleting the link to the other sheet. If this
is what you are actually wanting to do you can either type the formula or
just type the = then click the sheet tab & cell, press return.

Otherwise, depending on what you're actually looking to accomplish, you may
be able to get it done most easily with Edit>Replace.
--
HTH |:>)
Bob Jones
[MVP] Office:Mac

Hello, I would like to know how to link cells between worksheets. For
example, I have 5 sheets within a workbook. I would like to link cell
"b10" on sheet 3 with cell "b16" on sheet 5 so that whenever I change
the data in one cell, the other automatically changes. Even if I erase
the data in one cell, I would also like it to be erased in the other
cell. Is there a way for this to be done? Thank you.

Adam

 

[CyberTaz]

One option if you're not concerned about the source cell containing a
negative value or zero, instead of the previous formula use:

=IF(Sheet1!B1>0,Sheet1!B1,"")

or if you need to have the formula return *any* value contained in the
source cell but remain blank if that cell is empty you could use:

=IF(ISBLANK(Sheet1!B1),"",Sheet1!B1)

Regards |:>)
Bob Jones
[MVP] Office:Mac

Thanks for the reply. Part of my problem has been solved very well.
But, how do I link a cell in Sheet 3 that is blank to a cell in Sheet 5
without a "0" showing up in the cell in Sheet 5? Can this even be
done? Thanks.

Adam

Afraid you can't [easily] have it *both ways* Adam

One cell has to be the source that another looks to... the cell that does
the 'looking' would contain a simple formula link to the source.In your
example, B16 on Sheet 5 would contain =Sheet3!B10 which you wouldn't want to
delete from Sheet 5. If you change/delete the content from Sheet3!B10, the
linked cell on Sheet 5 would update accordingly. However, if you delete the
content of Sheet5!B16 you're deleting the link to the other sheet. If this
is what you are actually wanting to do you can either type the formula or
just type the = then click the sheet tab & cell, press return.

Otherwise, depending on what you're actually looking to accomplish, you may
be able to get it done most easily with Edit>Replace.
--
HTH |:>)
Bob Jones
[MVP] Office:Mac

Hello, I would like to know how to link cells between worksheets. For
example, I have 5 sheets within a workbook. I would like to link cell
"b10" on sheet 3 with cell "b16" on sheet 5 so that whenever I change
the data in one cell, the other automatically changes. Even if I erase
the data in one cell, I would also like it to be erased in the other
cell. Is there a way for this to be done? Thank you.

Adam