Is it possible to link cells on 2 different spreasheets?

I do not have time to watch this entire thing again. But I am fairly certain it is covered in this video. And I would suggest watching it if you use Excel on a regular basis.

https://www.youtube.com/watch?v=0nbkaYsR94c

This is the bio of the guy giving the talk… So he kinda knows his shit.

Avram Joel Spolsky (born 1965) is a software engineer and writer. He is the author of Joel on Software, a blog on software development, and the creator of the project management software Trello.[2] He was a Program Manager on the Microsoft Excel team between 1991 and 1994. He later founded Fog Creek Software in 2000 and launched the Joel on Software blog. In 2008, he launched the Stack Overflow programmer Q&A site in collaboration with Jeff Atwood. Using the Stack Exchange software product which powers Stack Overflow, the Stack Exchange Network now hosts over 100 Q&A sites.

You can write formulae in XLS which can refer to cells across tabs ( spreadsheet) of the same book if that’s what you’re asking for. Should be available in basic xls help also. If you’re talking about 2 different files then I’m not sure if that’s even possible / how easy it is.

Yes you can link two spreadsheets. You do it in exactly the same way as you describe. The spreadsheets must stay in the same location on your computer.

It really depends on what you mean by “linking”. The word is used in different ways by different people.

A formula in one spreadsheet can refer to a cell (or range) in an entirely separate workbook. (“Worksheet” is the term to describe the “tabs” of a workbook, and “workbook” is the term used to refer to separate Excel files.)

The format for a cell to refer to another workbook is:

=’[Workbook Name.xls]Sheet Name’!$C$3, where $C$3 (it doesn’t have to be an ‘absolute’ value like that) is an arbitrary cell name used for this example.

And it’s also possible to obtain the value of a cell in one workbook / worksheet by means of a macro (VBA) to pull the value from another workbook – in whatever location it is. (And it is possible, though problematic, to handle the case where a file might be located in differing locations.)

It’s also possible, and common, to create a hyperlink in one workbook that will open another workbook (or other file).

So it kind of depends on what it is you’re aiming at, and how you intend to “link” your files or cells.

GA, @CWOTUS. That’s along the lines of what I was looking for. I guess what I meant by “link” is “cell reference” in spreadsheet-speak.
Now, if the two spreadsheets are in different folders, can a cell in one spreadsheet still refer to a cell in the other if I use the full pathname of the file (or a relative pathname like ../)?

Yes, you “can do” that, but it also gets problematic in real life, because opening a spreadsheet that has a link (or many links) to an external sheet – any external sheet – causes Excel to flash a warning upon opening the sheet that you HAVE external links, and how do you want to handle those? That is, do you want to activate the external sheets (assuming that you still have access to them) to pull the stored values, or do you want to ignore the link and just retain whatever value was already stored in the current file? (That is, the file being opened, which has the external link that prompts the warning.)

So that costs some time and convenience, and can be a bother and distraction. And when you send the file to someone else – and the linked file is, for example, on your own computer’s drive, or on a network drive that you have access to but your colleague doesn’t – then that option to “get the stored data, please” fails when the file can’t be reached from your colleague’s machine. And the attempt takes time, and upon failure (which is frequent, in those cases), then the user has uncertainty about the data, which results in more lost time because of phone calls to check “is this accurate” or “can you send me the other file too”, etc.

And files with a lot of external links are, at least in my experience, very soon notoriously unreliable. They have a short shelf life in the real world. So I don’t recommend it, as a rule, but it is possible. (And I do it all the time, anyway, for files that are intended to have a short shelf life, such as quarterly reports for our open projects that I have to send to Finance, and which they use one-time-only to validate and check their other figures. They know that the file they get from me today is good for the short time that they need it to close the quarter, and we all know the figures are going to change next week, next month, etc.)

But don’t just take my word for all this. Try it and see for yourself. It’s how I’ve learned nearly everything that I know about Excel.

And there is another way around the “external link” limitation when you get interested in macros.

As another example, my boss, the Director of Project Operations wants to maintain a calendar of the comings and goings (that is, travel days and days out of the office) for his staff of twelve project directors, assistant directors and other project management personnel and admins. And while they could all share their Outlook calendars with him – and he could probably demand that – no one wants that kind of intrusiveness.

So what I did instead was to create a single “calendar file” listing every day of the year in Column A, and people’s names across the top of the sheet in Row 1, so every day can be marked with a short text note to show “travel”, “at site”, “vacation”, “holiday”, “personal day” or whatever applies. No one has to share as much information as they might have on a personal Outlook calendar.

But with thirteen or fourteen busy professionals, in and out of the office at all times and all working their own schedules, it would not make sense to make that a shared file. It would be opened at inconvenient times when someone else already had it open, etc. And that causes problems.

So what I did instead was to make a separate calendar file for each person. (to correspond with the names on the collective file) so that each person is only responsible to update his or her own calendar file, at whatever time suits them to do that. They have sole ownership of “their file” (or they could ask an admin or colleague to update it; but it’s still “their own” file).

Then the master spreadsheet has a macro (a pretty simple one, too, I had no idea how easy it would be to work out) and a button to activate it that has one task: Poll each person’s individual calendar, in whatever state it is saved in “right now” and plug those entries into the corresponding dates for the master file. To simplify the macro operation, I have directed that all of the files be named exactly as I named them and maintained in a common directory that we all have access to – but it doesn’t have to be that way; that was just for convenience and common access.

It takes about fifteen seconds to run the macro, and it doesn’t matter whether anyone else has their calendar open or not, and doesn’t interfere with that; it just gets “what is saved in the file now”.

@CWOTUS Thank you for such a detailed answer and thank you for the macro idea!

But let me ask you, in your example, if every colleague’s individual (calendar) spreadsheet, plus the master spreadsheet, were in the same directory, and the master spreadsheet’s cells directly refer to cells in the individual spreadsheet, with the master spreadsheets’ cells automatically updated upon launch, why would there be a need for a macro? Wouldn’t the update happen automatically? Isn’t that the purpose of using a cell reference?

I do understand what you say about the fragility of the setup. If people start mucking around with their individual spreadsheets, moving the cells around, changing their names, deleting rows, etc., the master would be rendered useless pretty quickly. Of course, you could always protect certain cells from update, I suppose.

Yes, it probably would work, but when the file was opened the user (who is sometimes the Director’s admin, and sometimes the Director himself – or it could be anyone else in the department who wants to review the group’s comings and goings to coordinate his own plans, etc.) would get the message that “this file has external links”, etc. which leads to confusion about “should I update or not?”

This way, no one has to wonder about that. They can hit the button to “Update Now” when they want to and save or not-save changes – it won’t matter, because the next user can do the update. (I’ve thought about making the macro auto-run when the file is opened, and I might do that eventually anyway. Partly this was an experiment for myself as I thought about the problem of creating a group schedule file that wouldn’t have to be updated by each individual. Like anything that I do, it can be improved; part of the question is, “Since it’s working, and every change takes time and cuts into other opportunity costs, is it worth doing?”)

The way it works now, the contents of the Master Calendar is “just text”. No formula references, no links, just text that has been entered into an entirely separate file. (And every cell in the Master file is protected except when the macro calls the child spreadsheets to make the updates. So there’s no capability for users to muck about with that Master file, either. Unless they learn how to crack Excel passwords, which is pretty darned easy.)

@CWOTUS And on all the “child” spreadsheets, I suppose you have protected every Column A and Row 1, right?

Hmm… I don’t recall. But since the users know the general functionality of the files, they realize that their dates had better correspond with the master file dates, or the whole thing is useless. And there’s only one name on each of the child spreadsheets.