• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

Make data from a cell in one sheet appear in another sheet

I am using Google Drive and want data in one spreadsheet file to appear automatically in another document all together.

If column C of FileA, TabA contains the number 1 anyhere in the cell, then place the contents of that rows column F into column C of FileB, TabA.

Assistance is greatly appreciated.
2 Solutions
we need more info from you

1) Is fileA and FileB preset and always the same positively this can be hardcoded in the macro and avoid more routines. Pls advise and positively provide full path of both.

2) Also TabA of fileA and TabA of fileB same and known provide name.

3) Presume FileA is the file that has the macro ?

Pls advise and based which will construct the macro
frugalmuleAuthor Commented:
Google docs Excel Files are named fileA and fileB and tabs are named respectively tabA and tabB and are online.
frugalmuleAuthor Commented:
It is a google drive "online" spreadsheet.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

don't know much about google drive but if you build a macro on fileA can you save it there ? I mean when you run the file next time will it have the macro in it ?

The file name can't be FileA it should be something like http:.... how do you open the file ?
frugalmuleAuthor Commented:
The question was about how to do it in Google Drive specifically.
Sorry my knowledge is limited in Google Drive !!!
Bob LearnedCommented:
You can reference another sheet in the workbook this way:



For another workbook, it is a little trickier, using the IMPORTRANGE function:

From the Google spreadsheets function list:

Google Spreadsheets lets you reference another workbook in the spreadsheet that you're currently editing by using the ImportRange function. ImportRange lets you pull one or more cell values from one spreadsheet into another. To create your own ImportRange formulas, enter =importRange(spreadsheet-key, range). For languages where comma is used for decimal separation, use a semicolon instead of a comma to separate arguments in your formula.

Spreadsheet-key is a STRING which is the key value from the spreadsheet URL.

Range is a STRING representing the range of cells you want to import, optionally including the sheet name (defaults to first sheet). You can also use a range name if you prefer.

Given that the two arguments are STRINGs, you need to enclose them in quotes or refer to cells which have string values in them.

For example:

=importrange("abcd123abcd123", "sheet1!A1:C10") "abcd123abcd123" is the value in the "key=" attribute on the URL of the target spreadsheet and "sheet1!A1:C10" is the range which is desired to be imported.

=importrange(A1,B1) Cell A1 contains the string ABCD123ABCD123 and cell B1 contains sheet1!A1:C10

Note: In order to use ImportRange, you need to have been added as a viewer or collaborator to the spreadsheet from which ImportRange is pulling the data. Otherwise, you'll get this error: "#REF! error: The requested spreadsheet key, sheet title, or cell range was not found."

"key" is of course the string in the URL for the spreadsheet that matches to the key= parameter.

I just tested it by creating two spreadsheets. In cell A1 of the first I put a string. In cell A1 of the second I put =importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and it displayed the string from the first spreadsheet. (Your key will obviously be different.)

(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you'll need to be replace it with a semi-colon: =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))

NOTE: Google currently sets a hard limit of 50 "cross-workbook reference formulas" per spreadsheet.

<<The information in italics was taken from a StackOverflow post that was originally linked in this question. I deleted the StackOverflow link in accordance with site policy, and don't reference the author because he just copied content from Google.
byundt--Microsoft Excel Topic Advisor>>
OK, to do this you will need to determine the 'key' for FileA, this is the alphanumeric string from the URL as shown here:
 Google Sheets URL KeyIn the example above the key we need is "0Ao89xYBBD6WndGJ3TXZSMUVhSjFrVmlHdW5LcFNhd2c"

We'll call your key for FileA "fileAkeyabcdef12345" for now, just for the hell of it.

Now in FileB, Column C, Row 1 enter the following formula:

=if((regexextract((importrange("fileAkeyabcdef12345", "C1:C1")), "1+"))="1",(importrange("fileAkeyabcdef12345", "F1:F1")),)

Open in new window

Replace "fileAkeyabcdef12345" with the actual sheet key and you're good to go.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now