Cell reference that updates all sheets?

users
#1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I have 2 sheets in the same file. Sheet 1 has the primary information
in it that must be reflected in Sheet 2. There are a varying amount of
lines in Sheet 1.

When the cell contents of Sheet 1 change I want the cell contents of
Sheet 2 to change.....even to reflect the inserting of new rows in Sheet
1. That is; if Sheet 1 has 10 rows then Sheet 2 will have 10 rows. If
I insert a row in Sheet 1 and put some data into it then Sheet 2 must
immediately update and reflect what is in Sheet 1 including the new row
and its data.

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

#2

. wrote

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

Absolute references won't work because they are updated as you insert rows.

One solution is to use INDIRECT to always point at the same cells.

http://db.tt/mdp3mBMz

Probably there are more elegant ways of doing this :slight_smile:

#3

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

If you insert a row in for example Sheet1.a8 then the array in Sheet2 is expanded to retain the same size, even better if you have formulae in the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use Ctrl+/ for this.

The help in the wiki about arrays:
http://help.libreoffice.org/Calc/Array_Functions

Miguel Ángel

  * Inglés - detectado
  * Inglés
  * Español

  * Inglés
  * Español

  <javascript:void(0);>

#4

. wrote

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

Absolute references won't work because they are updated as you insert rows.

One solution is to use INDIRECT to always point at the same cells.

http://db.tt/mdp3mBMz

Probably there are more elegant ways of doing this :slight_smile:

--
View this message in context: http://nabble.documentfoundation.org/Cell-reference-that-updates-all-sheets-tp3589103p3589460.html
Sent from the Users mailing list archive at Nabble.com.

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

If you insert a row in for example Sheet1.a8 then the array in Sheet2 is expanded to retain the same size, even better if you have formulae in the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use Ctrl+/ for this.

The help in the wiki about arrays:
http://help.libreoffice.org/Calc/Array_Functions

Thanks Miguel for the tip!

#5

MiguelAngel wrote

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

If you insert a row in for example Sheet1.a8 then the array in Sheet2 is
expanded to retain the same size, even better if you have formulae in
the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use
Ctrl+/ for this.

The help in the wiki about arrays:
http://help.libreoffice.org/Calc/Array_Functions

Yep. Definitely much more elegant (and efficient) :wink:

Thank you, Miguel Angel :slight_smile:

#6

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

. wrote

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

Absolute references won't work because they are updated as you insert

rows.

One solution is to use INDIRECT to always point at the same cells.

http://db.tt/mdp3mBMz

Probably there are more elegant ways of doing this :slight_smile:

--
View this message in context:

http://nabble.documentfoundation.org/Cell-reference-that-updates-all-sheets-tp3589103p3589460.html

Sent from the Users mailing list archive at Nabble.com.

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

What is "Mays"?

If you insert a row in for example Sheet1.a8 then the array in Sheet2

is expanded to retain the same size, even better if you have formulae in
the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use

Ctrl+/ for this.

#7

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

. wrote

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

Absolute references won't work because they are updated as you insert

rows.

One solution is to use INDIRECT to always point at the same cells.

http://db.tt/mdp3mBMz

Probably there are more elegant ways of doing this :slight_smile:

--
View this message in context:

http://nabble.documentfoundation.org/Cell-reference-that-updates-all-sheets-tp3589103p3589460.html

Sent from the Users mailing list archive at Nabble.com.

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

What is "Mays"?

If you insert a row in for example Sheet1.a8 then the array in Sheet2

is expanded to retain the same size, even better if you have formulae in
the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use

Ctrl+/ for this.

#8

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

What is "Mays"?

It's a typo :slight_smile:

Miguel Angel meant "use keys Shift+Ctrl+Enter" :wink:

HTH,
Pedro

#9

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

. wrote

How do I do it?

I've tried the Absolute reference method ($A$1) but that won't allow for
the addition of new rows in an additional sheet.

Absolute references won't work because they are updated as you insert

rows.

One solution is to use INDIRECT to always point at the same cells.

http://db.tt/mdp3mBMz

Probably there are more elegant ways of doing this :slight_smile:

--
View this message in context:

http://nabble.documentfoundation.org/Cell-reference-that-updates-all-sheets-tp3589103p3589460.html

Sent from the Users mailing list archive at Nabble.com.

A very good option is use an array.

In Sheet2.a1 =Sheet1.a1:a15 and use Mays+Crtl+Enter to get an array.

If you insert a row in for example Sheet1.a8 then the array in Sheet2

is expanded to retain the same size, even better if you have formulae in
the array rows in Sheet2, they are also copied in the new row.

To edit an array you must select the array not a cell in the array, use

Ctrl+/ for this.

The help in the wiki about arrays:
http://help.libreoffice.org/Calc/Array_Functions

Miguel Ángel

Miguel, thank you SO much for the help. It was a little confusing and
the HELP wiki made it even worse! Then I studied your example more and
figured it out. You've really saved me hours and hours of work.

#10

Easy:
o Amend all references to Sheet 2 everywhere in your spreadsheet to refer to Sheet 1 instead.
o Delete Sheet 2.
o Bingo!

I trust this helps.

Brian Barker

#11

Hi :slight_smile:
Please let us know which wiki-page needs to be re-edited.  A clickable link would make it easier.

They do go a bit wonky sometimes or get thrown together hastily but generally they improve as more people edit the rough spots out of them.  You can edit it yourself if you register and you are probably one of the few people that really understand this topic now so you are probably the best person to do the editing now.

Generally the best documentation is on this page
http://wiki.documentfoundation.org/Documentation/Publications
and is then copied to this page
http://www.libreoffice.org/get-help/documentation/

Good luck and regards from
Tom :slight_smile:

#12

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I have 2 sheets in the same file. Sheet 1 has the primary information

in it that must be reflected in Sheet 2. There are a varying amount of
lines in Sheet 1.

When the cell contents of Sheet 1 change I want the cell contents of

Sheet 2 to change.....even to reflect the inserting of new rows in Sheet
1. That is; if Sheet 1 has 10 rows then Sheet 2 will have 10 rows. If I
insert a row in Sheet 1 and put some data into it then Sheet 2 must
immediately update and reflect what is in Sheet 1 including the new row

and its data.

How do I do it?

Easy:
o Amend all references to Sheet 2 everywhere in your spreadsheet to

refer to Sheet 1 instead.

o Delete Sheet 2.
o Bingo!

I trust this helps.

Brian Barker

Both Sheet 1 and Sheet 2 are needed. Sheet 1 is the input form for all
the data that will show up on other Sheets. Perhaps I misunderstand
what you mean?

#13

I have 2 sheets in the same file. Sheet 1 has the primary information in it that must be reflected in Sheet 2. There are a varying amount of lines in Sheet 1.

When the cell contents of Sheet 1 change I want the cell contents of Sheet 2 to change.....even to reflect the inserting of new rows in Sheet 1. That is; if Sheet 1 has 10 rows then Sheet 2 will have 10 rows. If I insert a row in Sheet 1 and put some data into it then Sheet 2 must immediately update and reflect what is in Sheet 1 including the new row and its data.

How do I do it?

Easy:
o Amend all references to Sheet 2 everywhere in your spreadsheet to refer to Sheet 1 instead.
o Delete Sheet 2.
o Bingo!

Both Sheet 1 and Sheet 2 are needed.

That's debatable. As many people do, you have told us only a small part of your problem. You have designed a solution to your overall need which then requires something that you acknowledge to be problematic. The solution may be to design your solution differently in the first place.

Sheet 1 is the input form for all the data that will show up on other Sheets.

No-one can yet know why (or even if) you need those extra sheets. No-one can know why the act of inserting a row in your input sheet needs to cause a new row to appear in other sheets. It may well be that you could inherit the data in some other way - perhaps using VLOOKUP() or similar. Or it may be (as I hinted) that you can derive the results you need directly from the first sheet, without creating all those copies. Anyone trying to help you needs to know what those results need to be before they can help.

Perhaps I misunderstand what you mean?

I think the problem is the other way about: although readers will understand what you want from what you said, they cannot understand what you *need*.

I trust this helps

Brian Barker

#14

I have 2 sheets in the same file. Sheet 1 has the primary
information in it that must be reflected in Sheet 2. There are a
varying amount of lines in Sheet 1.

When the cell contents of Sheet 1 change I want the cell contents
of Sheet 2 to change.....even to reflect the inserting of new rows
in Sheet 1. That is; if Sheet 1 has 10 rows then Sheet 2 will have
10 rows. If I insert a row in Sheet 1 and put some data into it
then Sheet 2 must immediately update and reflect what is in Sheet 1
including the new row and its data.

How do I do it?

Easy:
o Amend all references to Sheet 2 everywhere in your spreadsheet to
refer to Sheet 1 instead.
o Delete Sheet 2.
o Bingo!

Both Sheet 1 and Sheet 2 are needed.

That's debatable. As many people do, you have told us only a small
part of your problem. You have designed a solution to your overall
need which then requires something that you acknowledge to be
problematic. The solution may be to design your solution differently
in the first place.

Sheet 1 is the input form for all the data that will show up on other
Sheets.

No-one can yet know why (or even if) you need those extra sheets.
No-one can know why the act of inserting a row in your input sheet
needs to cause a new row to appear in other sheets. It may well be
that you could inherit the data in some other way - perhaps using
VLOOKUP() or similar. Or it may be (as I hinted) that you can derive
the results you need directly from the first sheet, without creating
all those copies. Anyone trying to help you needs to know what those
results need to be before they can help.

Perhaps I misunderstand what you mean?

I think the problem is the other way about: although readers will
understand what you want from what you said, they cannot understand
what you *need*.

I trust this helps

Brian Barker

Perhaps. The Array function works perfect for what I need. 'Problem is
I've looked at the "help" online that one is forced to review when you
hit the F1 key and see that a simple example that idiots like me could
understand is very much needed and missing. So, when someone says, "you
need to include the variables in the quadrated-matrix before the
registers will translate the data to a venue". (I'm making the fancy
talk up) I have NO idea what they're talking about.

Still, I'm happy with what I've got and grateful to all of you that have
helped.