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 ?
Jacob Jan Tuinstra
22.8k16 gold badges101 silver badges191 bronze badges
asked Apr 14, 2014 at 12:23
2 Answers
You can use the following formula's in sheet2 (add them in A1 of course).
Formula
QUERY(Sheet1!A:D, "SELECT *")SORT(Sheet1!A:D)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
answered Apr 14, 2014 at 13:12
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.
answered Apr 15, 2014 at 10:16
-
If you start editing newly added rows in Sheet1, then you need to manually add the reference formula each time? Apr 15, 2014 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. Apr 15, 2014 at 11:14
-
Well, then perhaps you can upvote the answer I gave: webapps.stackexchange.com/a/59478/29140? Apr 15, 2014 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. Apr 16, 2014 at 9:38
-
Why don't you join me in this chat room: chat.stackexchange.com/rooms/13056/spreadsheet-nerds Apr 16, 2014 at 11:19
IMPORTRANGE(). Here's another similar question.