Linking To Files From Numbers Spreadsheets

Hi, this is Gary with MacMost.com. Let me show you how you can add links to files in your Numbers spreadsheet.

So, for instance, you have a spreadsheet that lists expenses. Each expense ties to a PDF receipt that you have in a file somewhere. Is there a way to have a link so you can click on it and then open up the PDF file? Well, there is no easy way to have a link to a file. You can link to a webpage. You can link to another sheet in your document. But you can't link to a file. However, you can very easily insert the path to that file in there.

Using Shortcuts you can make it very simple to quickly jump to that file if you need it. 

So, for instance, here I've got an expenses spreadsheet. I can enter in the date, which business it was, and the amount. I've got a space here for a link to the receipt. The idea is the link would be a PDF file. So if I move this over I can see some PDF files here. If I were to drag one of these into this it actually copies the PDF file but as a background to this cell, which isn't really what I want. Not to mention it's not going to work with multipage PDF's and it's going to make the file huge. So I want to get rid of that and look for a better way. 

A simple thing I tell people to do is to copy the name of the file here and just paste it in. Now if you've got a folder with all your receipts in it, if you need to find the receipt you can see the name here, you can copy it and you can easily find it in the list or search for it. That works really well if you just occasionally need to refer to the original receipt. If it is something you need to do all the time though you probably want a quicker method. 

By the way, if you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

So one way to do it is instead of copying just the name you can select the file here, go to Edit and look for Copy. Now this will copy the file and if I paste it in I get the same problem as before. It uses it as a cell background. But if I hold the Option Key down then it says Copy as Pathname. Notice there's a keyboard shortcut for this so I don't have to go to the Menu Bar all the time. It's Options Command C. So if I use that and then I go back to the spreadsheet and I paste in you see I get the path to the file which will help me find the file anywhere. As a matter of fact I can Copy this cell here, go to the Finder, and then use Go and Go To Folder, or Shift Command G a quick paste for putting the entire path there and it will jump to that file. So that is a bunch of steps but if you don't need to do it too often it's not that bad. Just put the path to the file in this column here and you have a fairly simple way to get to the file. But what would be nice is if we could automate this. Which is something that the Shortcut's App does very well. 

This is just a series of steps that we should be able to repeat. So, let's go to the Shortcut's App and I'm going to create a new shortcut. I'm going to call this shortcut simply Open File From Path. I'm going to go to the information here for it and go to Details and select Use As A Quick Action. So I can select some content and then use that content as Input for this shortcut. Here you can see it says Received Images and 18 More. I'm going to click on that and Select All and then Deselect All. So it deselects everything and only select Text as the only valid input for this. So now I should be able to Select Text and then choose Open File From Path from the Services Menu. You can  see the Services Menu was checked over here. I'm going to make it even easier and add a Keyboard Shortcut. So I'm going to use Control Option Command O for this. So you can use whatever you want as long as it doesn't conflict with something else and now I should be able to run this shortcut using this keyboard command. Right now it is just going to have the text that is the path to the file. 

So what I want to do is find the actual file from that path. I'm going to click here to go back to the different Actions here. I'm going to look for Get File. Here you can see you have Get File from Folder. I'm going to drag that over here and it's going to ask for a folder to start with and a path. I'm going to click here and clear this and click here again and I'm going to choose, as my path, the hard drive. The main level of the hard drive. So you need to get up to it. Like, for instance, if you are in your Documents Folder here you can go up and you may just see iCloud Drive here. So you want to find some other location, like say your Downloads Folder, that won't be in iCloud Drive. You can go all the way up to Macintosh Hard Drive or whatever your hard drive is called and select Open. So it is going to start looking at Mackintosh HD. Then the path is going to be whatever comes in from here. So I'm going to click here and I'm actually going to Control Click, two-finger click, or right click here and select Insert Variable and then Shortcut Input. So whatever the text input is use that path. 

Now I've got the file. Now what I want to do is I want to Open the file. So I'll type that here and there is an Action for that. So Open and then it is going to Link to this. Open the File using the default app. So this should work to open a file given the text path. Let's go and try it out. Now notice one thing. You've got these single quotes here that are put around the file name. You've got to get rid of those. That's not part of the path. So you want to just have it without those. Now I can Control Click, two-finger click, or right click to bring up the Context Menu. Go to Services and then choose Open File From Path. Now it should open up that file based on the path. There it is. It opened up in the default app Preview. So I have a quick way to get to a file if I have the path there in Numbers. 

It even works if you just select the cell. So I selected the text in the cell. Let's just select the cell itself. This time instead of using the Context Menu I'm going to use that Keyboard Shortcut we set. So Control Option Command O and it will do the same thing and it will open up Preview and use that path there to get to the file. 

Now the one thing you might see that is different than what I've shown here is it might be asking you for permission the first time you do this. Like the first time you open one in iCloud it is going to ask for permissions to access iCloud and it may also ask for permissions if you try to access it in another location like in your Home folder as well. This actually works in any app because I haven't restricted it to just Numbers. So if you happen to have a Pages document and you have some text like the full path like this in there to a file you could use the same Keyboard Shortcut to open it. 

Let's go to iCloud Drive here and let see I've got a file here, a PDF file. I'm going to use Option Command C to copy the path. I'm going to go here and use Command V to paste it in. Notice it's got those annoying little single quotes around it like that. I notice here that this path here is a little complex because it is showing multiple documents and then Cloud docs. In other words it is in iCloud Drive here. But if I select this and use the Control Option Command O keyboard shortcut it's still going to find that path and it's still going to open the file. 

Now a variation on this that I like a little bit better is instead of just being able to open any file anywhere is select here and choose instead of the entire hard drive, just the Receipts Folder. So I've got a folder called Receipts. It is just a custom folder that I created there. But let's say it holds all the receipts that I'm going to link to in this spreadsheet. So now I've got Get File From Receipts and then it is going to use the shortcut input here. I'm going to rename this, Open Receipt, like that. So I know that it is just for this one folder for this one purpose. Notice it still has the same keyboard shortcut. Maybe I would change that to say Option Command R to open up a receipt. So now instead of including the entire path I can just get the file name here, like this, and paste it in.  Notice how that I'm just pressing Return to start the rename but I'm doing Command A to select the entire file name and Command C to copy. Just a quick little shortcut for getting the entire file name, like that. I paste it in. So now it's going to only look for this file in that Receipts Folder. I'll do Option Command R, the new keyboard shortcut that I set, and it will find that file in that specific folder. I like this. It is a little more concise. It is a little neater and also it allows me to move this folder or rename it. I can simply rename this folder or put it somewhere else. I just have file names here so all I need to do is go back to shortcuts and update the path to get the receipts and it would still work. Whereas if I had the full file path then I need to keep those files at those specific location forever. 

So while it would be useful to have the ability to link to files in Numbers at least you have these alternative methods now. Hope you found this useful. Thanks for watching.