1

I have two sheets on a Google Spreadsheet. One of which is the copy of the other. Now what I want is, when I edit one sheet, the same edit must reflect on the other. How can I do that ?

1

You can use the following formula's in sheet2 (add them in A1 of course).

Formula

  1. QUERY(Sheet1!A:D, "SELECT *")
  2. SORT(Sheet1!A:D)
  3. ARRAYFORMULA(Sheet1!A:D)

Explained

The last formula is the most straight forward, because nothing can be done in its current state. The other two allow you to do some tweaking, of which the first formula is the best equipped.

Example

I've created an example file for you: range reference

0

Do you want to do this to every single cell in the 2nd sheet, or just for some of them?

If it's just for some of them, then put a formula in every single cell, saying to set it equal to the same cell in Sheet1,

eg in cell B3 in Sheet2, use the formula = 'Sheet1'!B3

(assuming that your sheets are called Sheet1 and sheet2).

You can put the same relative formula into each cell by just copying and pasting: by default, spreadsheet forumulas are relative, so they are smart enough to change the row and column references depending on where you paste them.

This means that if you copy that formula from Sheet2, cell B2, and past it into Sheet2, cell D4, then it will appear as ='Sheet1'!D4 without you having to do anything.

  • If you start editing newly added rows in Sheet1, then you need to manually add the reference formula each time? – Jacob Jan Tuinstra Apr 15 '14 at 11:02
  • Yes, that is correct. If you want the soluiton to by dynamic, and to work no matter how many rows are added, then you need to use a script which is run each time that someone updates Sheet1. But in many common spreadsheet uses, this degree of complexity is not necessary. – MaryC.fromNZ Apr 15 '14 at 11:14
  • Well, then perhaps you can upvote the answer I gave: webapps.stackexchange.com/a/59478/29140? – Jacob Jan Tuinstra Apr 15 '14 at 11:17
  • I'm not really keen to do that: I while your answer is technically correct, it only covers some columns (which you haven't explaned) and the case wehre every single cell in the 2nd sheet is to be the same as the first - and it does this by putting a formula (which you haven't explained) into the other cells in sheet2. I just don't see any real-world applications for this approach: if every single cell is to be the same, then why not just reference the first sheet. – MaryC.fromNZ Apr 16 '14 at 9:38
  • Why don't you join me in this chat room: chat.stackexchange.com/rooms/13056/spreadsheet-nerds – Jacob Jan Tuinstra Apr 16 '14 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.