Link from another file in Google Sheets in 2 mins
HTML-код
- Опубликовано: 20 авг 2024
- In Google Sheets, you cannot link two files (two separate spreadsheet documents) as easily as Excel but you do it through a function which imports a range of cells in a more secure way than Excel and it updates live. Use the function =IMPORTRANGE("file link","sheet name!Start cell:End cell"). Where you must physically type the special characters exactly as shown "!: but if something is written in the cell you don’t need to use speech marks " around it. I show what sort of things can go wrong as well and how to fix it.
I have been searching for how to enter a file location into a cell in google sheets and every video and blog post has absolutely nothing to do with my request. Is it always this difficult to get answers out of google? It use to be so great and now it is a POS!!!! Your video was a the top of my last search results. I like how quickly you get to the point but unfortunately it is the wrong point.
Sorry to hear. If you have a file url (for something stored on Google drive), copy the file path from the address bar & then paste it into a cell or use smart chips. See here: www.geeksforgeeks.org/how-to-use-smart-chips-in-google-docs-and-sheets/
I get it, why would you know that thing is called a smart chips hope that helps
@@learnspreadsheets Thanks. :)
So quick to the subject. The best presenter of the stuff. without wasting any time. thumbs up
Wow! Thanks!
Liked! Very straight to the point. I love it! thank you!
Yup! Glad it helped!
it really help me sir!@@learnspreadsheets
=IMPORTRANGE("file link","sheet name!Start cell:End cell")
Yes well said. Thanks!
You're a legend, thank you!
Glad it’s helpful!
Very useful! Please do more videos about Google Sheets!
Thanks! I am, launched one yesterday and another coming later this week
Thank you! It helped a lot!!
Thanks a lot for this information, David! You are awesome!
Thank you!
This is the best video I have seen on this subject. Thank you.
Thanks! Glad you like it!
You saved my hour of works. Thanks man!
Hehe! No worries! Glad you liked it
Thanks much David. Very useful.
Glad you like it!
Hey thanks for this but in my sheet it's only show loading
Sorry to hear, refresh may help, or hover over it... It may ask you to click to give permissions
This was awesome. Thank you so much for providing a concise answer to our question.
No worries, glad it helped
okay what if the other spreadsheet only allows you to view it, how to give it access
You must ask the owner of the other file for edit access
Very helpful in monitoring progress from multiple sheets. Thank you!
Yay! Glad you liked it
do u know why it a like still loading to have access, after pressing allow access, but i feel like they are not accessible, it stay 'adding permission' loading,
Thanks for the question but I’m not sure sorry
Hi, wanna ask if from 2 different GS and want to link it to each other, e.g GS file A link to GS B. If there is additional data add from time to time, how to ensure GS B will auto added as well?
pls help
Good question. Use my example but just give it some extra blank rows & columns in the cell reference mentioned so that it automatically grows. Eg file B has a link to range A1:H500 in file A. Today we only have data in cells A1 to F200 in file A, but because we made the link to the range A1:H500, when new rows or columns are added then the data will automatically populate in File B
How do you get it to also copy the formatting from the master spreadsheet to the other spreadsheet?
Sadly formatting cannot come through with a formula sorry
good tutorial, however "link" would be a misnomer, the files are not linked (as would be done with the fileID), only shows how to get the data automatically imported from a range.
Thanks for the feedback
very very helpful, god bless you
but if you may help with the ref message that I don't have access, i can't find the (allow access) button.. any help regarding this
That’s strange, seems like a bug, all I could advise is refresh the page or try another web browser
This is helpful. Thanks David!
Glad you like it!
i got the first point i want thank you ! , but how can this data show in blue and if i click it the link will be shown ?
Hi, this brings in the data but not any formatting sadly, you have to reformat it in your current sheet although conditional formatting which I cover in a more recent video may help!
thx for very clear explanation
Glad it was helpful!
Getting this error even though both files are under my personal email. Is this a bug?
"You don't have permissions to access that spreadsheet."
That is common… you have access to both but one spreadsheet doesn’t have access to the other one. This happens because spreadsheet A might be shared with someone who shouldn’t see data from spreadsheet B, even though you may have access to both. Go to the formula hover over it & click grant access.
@@learnspreadsheets Interesting. I only see an option to Learn More, not Grant Access. Anyone with a link has General Access to both files too.
Thanks for the response as well.
That works perfect!
Glad you like it!
Thank you so much for this tutorial.
Glad you like it!
Hi, this didn't work completely for me. Maybe it's changed in the last two years. When I exchanged the Comma for a semikolon it worked tho
In certain countries ; should be used instead of , in spreadsheet formulas. Its not new but a region based thing. Thank you for pointing that out!
What happens if I'm not the owner of the target document but have Editor privileges? I tried but it doesn't give me the option of "allow access"
Sorry I’m not entirely sure, this differs if you use a g suite or personal account
you need to have sufficient authority/access to the source document.
Thank you so much
First cell and linking works but after I try to connect another sheet right below the cell that is working, the sheet that is working fails and it says. "Array result was not expanded because it would overwrite data"
That message comes up when you have existing data in cells below or to the right, and the cell range you are asking for will overwrite that. e.g. You have data in cell B6 and you want to bring in data into cell B1 but the data you want to bring in is over 6 rows high. Try in a blank worksheet and you won't get that issue
Hm.. I found the easiest option which works is I leave one blank row between each importrange and the error doesn’t occur.
If you have 2 different google sheets, and want to link single cells from one to the other - import range, does not work - instead of eg. H1:J5 just link H1 - I can’t see how to do that.
Two worksheets you would link with = sign, 2 workbooks you would say A1:A1, that should work
Isn't there a faster way to do this? For example, when you link values between different sheets in the same file, you can just mark the desired range which goes a lot faster. You can't do something like this when dealing with seperate files?
Good question. In excel you can do that but in google sheets you cannot as each spreadsheet is a url with separate permissions
hello, do you know how to find duplicates in two seperate google sheets files? And highlight them?
Best using functions: COUNTIFS or VLOOKUP
How do you address an issue where a row/column gets inserted in the source spreadsheet that changes the cell(s) location referenced?
Good question, the cell range isn’t dynamic in the source, but that’s the same with excel I believe, just get data from cell A1 & use the QUERY function to filter, I cover QUERY in another video
I tried but it said "You don't have the permission". I went back to open up the 1st file and retry. Still it failed. All I want is link 1 cell content from file A to file B. Could you please show me? Thanks a lot.
Sorry sounds like an access issue and I’m not an expert on that
Hi. Faced same problem for hours. The solution to this to convert your files to google sheets. This problem occurs when you do this in files created in excel and then uploaded to google sheets.
I tried this process and this is what I've got - "You don't have permissions to access that sheet". May I know how to resolve this. Thank you
hover over the cell and you can allow access
Yes allow access! Thanks for the reply 😃
Hi David, may I know why "You don't have permissions to access that spreadsheet." appear after i do the importrange.. btw Im own the spreadsheet
Great question! Even if you you own both spreadsheets the security settings need to be enabled to allow one to access another, because although you can get to both, “harry” may have access to one spreadsheet but not the other other
This does not seem to work if the file is .XLXS - is there a way for this to work with Excel sheets
Yes sure, for Excel, just open both files and click on the the cell in the other workbook to link. support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f#:~:text=the%20source%20workbook).-,Select%20the%20cell%20or%20cells%20where%20you%20want%20to%20create,link%20to%20and%20press%20Enter.
@@learnspreadsheets I mean a .xlxs file uploaded to Google Drive and shared google drive. This method does not work when the files are uploaded to google drive.
No it doesn’t work as they are excel files, which react differently to links than g sheets files, sorry
Amazing, thank you!
No worries!
Thanks u so much
what if the spreadsheet rows you want to link move from sheet to sheet, is there something I can put in those sections to link to a group of cells regardless of the vertical position on the spreadsheet that I am linking?
You need the exact coordinates. File link, sheet name, cell reference
Thanks for this. I have an issue I can't solve. After I put in the link, and I am only looking at one cell, I get the #REF! error. Followed by "You don't have permissions" Bellow that is "learn more". I am the owner of both sheets and I am logged in with the same account. Any idea how I can fix this?
Hi, just because you have access to both files doesn’t mean they have access to eachother, when you click the top left cell with ref, there should be a pop up that gives you the option to link them or share access
@@learnspreadsheets Yes. I did that. But it still will not share data between the two workbooks. I have copied the link from address bar and copied the shared link into my formula. The one aspect i am not sure of is my cell range. I am only looking at one cell, B3 for an example. I am not looking at B3:B10. Could that affect anything?
did not work for me =importrange("Link","Calculator!Q3")
Sorry to hear, sometimes i have to delete and rewrite it to make sure
If I wanted to import specific columns, how would I do that? Ex. I would like columns A-K and P-T.
The video shows it, you just specify the start & end range if they’re adjacent. If you want to import non adjacent ranges or reorder the columns you need to combine with a QUERY function. Check out my video on QUERY for more info. You can alternatively use a CHOOSECOLS function instead of QUERY.
@@learnspreadsheets appreciate the helpful videos David!
what about syncing a single cell sheet to another file?
It would be the same method, even for just one cell
does the destination file automatically "insert new sheet" when the source file "insert new sheets"?
Good question but the link is to a range so inserting a new sheet in the source file doesn’t affect the destination file
Thank you very much!!!!
Glad you like it!
Yes, working but the design is not working on another sheet?
No sadly the formatting doesn’t come through, just the data
This is helpful! Thanks boss
Glad you find it useful!
How do i do this while adding all the functions like drop downs or formatting ?
That is not possible unless you copy & past formats but it’s not live then
Hello! What if I don't need to import a range, just one cell? TYIA!
Hi it would still be the same process, jus mention one cell in the formula. There isn’t a special way to do one cell
So this mostly worked well. The new document had none of the formatting of the original, but it did reflect updates to the original. But: it did not go both ways: when I typed something in the new document, all the data disappeared. Is this expected? Can I have one sheet actually part of 2 separate spreadsheets?
The format doesn’t come across no, it is a one way only link as you suggest. If you type in the document in a cell that would be overwritten by importtrange you do get an error and the data disappears as is the case with all dynamic arrays in g sheets
@@learnspreadsheets Is there a way to override this, so that I can also edit my new document, and changes auto-appear in the original sheet?
This works well but not with images. Is there any way you can import date AND images?
Depends how the images are stored. If the images are done using a url and the =image() function it should work well
THANK YOU!
If the tab name has slashes and parentheses how does that work?
Any allowed character for a URL should work in the same way. Sometimes characters cannot be used in a url though
Great, Thanks
No worries!
Hey David,
I am getting an error "you dont have access to the sheet"
I checked and I am the owner for both of them!
Is it because the sheet I am pulling info from is a XLSX file?
Hi, it could be for a number of reasons with permission structure, I'm not sure sorry
@learnspreadsheets ok thanks David!
I just used a Google sheet and it worked 🙂
If i do this, can i see the update from that sheet i linked??
It should update automatically
I've clicked allow access but am getting a continual loading message "Adding Permissions", with #REF! still showing in the cell. Is the problem maybe that I need to be the owner of both sheets (or just the sheet being referenced)?
Soln: Permission changed to "Anyone with the Link" and it worked. IDK if there is a way to get around that.
Hi this is possibly as the space needs to expand into cells that already have something in them obstructing it. If that’s not it I would need more information before assisting
if you dont want a range and just a single cell what do you do?
If you select a single cell and right click you can get a link to a single cell
HI, CAN I ASK WHY I ALWAYS GET " THE YOU DONT HAVE PERMISSION TO ACCESS THAT SHEET" ERROR?
It happens by default every time, & only takes one click to “allow access”. But if you don’t have permissions to the linked file then it wouldn’t work for you sadly, you need a colleague to do iy
Hi, is it possible to make a "master" sheet that contains other sheets? And they update each other if some user inputs new data...
Hi you can make a master sheet that is output only but you can’t have the output sheet be editable. To stack ranges as output only use ={range1;range2}. More info on my dynamic arrays in Google sheets video
Solution in 1 min 43 Seconds
Ahaha yeah! Tried to be efficient for that one
Is there an option to drag the formula down or right and the cell will sequentially increase like Excel?
No, but you refer to the full range so its easier and safer than Excel I think. You end up with 1 link for all the cells, rather than one link per cell
Hi David, thanks for your sharing.
Kindly advise if you have any ideas to address the requirement below by using the IMPORTRANGE("file link","sheet name!Start cell:End cell")
1) rename sheetname to othername
2) duplicate the original sheet and name as sheetname with all the latest amendment of the data
3) maintain the formula without re-link the file link (* currently each sheet in Googlesheet has specific edit#gid)
Thanks in advance.
Hi sorry I don’t fully understand the question, I would need to see it to make sense of it
@@learnspreadsheets , is fine. You can ignore my question, problem resolved by just excluded the edit#gid= in the file link. Your video click help a lot.
Ok no worries
How to link a GoogleDoc inside a GoogleSheet?
Hi you can do this with a hyperlink, click on a cell & click ctrl k then paste in the link of the google doc
@@learnspreadsheets Ty so much man (thumbup)
No problem!
i have fomulaion error, do not work. NEVER
Hi thanks for your comment, there is probably just a small issue with symbols like “,: but it does work. Sorry you’re having problems
No idea what you are talking about..
Thank you for this!!
Glad you find it useful!
What if the adding permission doesn't work? any way to fix this?
That seems like a bug, Im not sure sorry
could it be something linked to the language? i'm dutch using dutch version and i tried to function in ENG@@learnspreadsheets