2つのスプレッドシートを同期して使う方法（その他）
- 2つのスプレッドシートを連動させようとする時に、どちらか一つをメインなものと設定して続けていく必要があります。メインとなるスプレッドシートはBadgerのアカウントから出力しましょう。あなたが一度、それを決定すると、メインなものに新しいものを付け加えていくだけでいいのです。（ショートカット：[shift]を押しながら、[F11]を押します。）
- もう一つのスプレッドシートをコピー、貼り付けをして新しく、メインのものに重ねます。（ショートカット：[ctrl]を押して、[A]を選択します。）
- Then press and hold [ctrl] and then select [C] to copy the whole sheet. And then switch to the new sheet on the main spreadsheet and then press and hold [ctrl] and [V] to past the secondary spreadsheet onto the main spreadsheet sheet. Once it is pasted, you can close the secondary spreadsheet.
- For the rest of the article, I will say [new sheet] when referring to the info pasted from the secondary spreadsheet and the [main sheet] to refer to the information from the main spreadsheet.
- To the far right on the [main sheet], put in the name of the column that you want to bring into the new sheet.
- Right underneath the name of the column,(the second row) start typing the formula: "=index( "and then select the column of information that you're pulling from the [new sheet]. So if you're pulling from Column C in the [new sheet], you would select all of Column C. So now the formula box looks like this: =INDEX(Sheet2!D:D
- After selecting the column, put in a [,] and then type "MATCH(" (ps. don’t include the quotes “”.) Now the formula box looks like this: =INDEX(Sheet2!D:D,MATCH(
- Next, select the cell in this row on the [main sheet] that you will use to determine if they match a row on the [new sheet]. This is preferably the [name] column. So if the [name] column is in column B, we would type "$B2". If that isn't available, then the [address] or [customer id] column will work. Note that the $ sign is so that we can drag across if we are bringing across multiple columns of data from the [new sheet] to the [main sheet]. So now the formula box looks like this: =INDEX(Sheet2!D:D,MATCH(Sheet1!$A2
- Then, we would type [,] and then we would select the column on the [new sheet] that corresponds (is the one we are matching) to the column we selected from the [main sheet] in Step 8. Then type $ in front of each letter in the array that we selected. So now the formula box looks like this: =INDEX(Sheet2!D:D,MATCH(Sheet1!$A2,Sheet2!$C:$C
- Finally, we would type [,] and then type the zero key [0] followed by two closed parentheses"))". So now the formula box looks like this: =INDEX(Sheet2!D:D,MATCH(Sheet1!$A2,Sheet2!$C:$C,0)).
- If the formula is working, then it should populate the cell with information in the [new sheet] that lines up with the first row in the [main sheet].
- If it looks right, then you can drag down the formula for the rest of the column. So now the formula box in the cell right underneath the original one looks like this: =INDEX(Sheet2!D:D,MATCH(Sheet1!$A3,Sheet2!$C:$C,0)).
- If there are multiple columns that are right next to it that you want to bring across you can copy and paste across or drag across. So the formula in the cell right next to the original would look like this: =INDEX(Sheet2!E:E,MATCH(Sheet1!$A2,Sheet2!$C:$C,0)).
- Note, if there is no match, it will return a result of “#N/A”.
- If you want to get rid of the formulas and/or the #N/A’s", you can copy and paste special the new row or rows and then replace the #N/A’s with nothing.