Change Source of formulas to own workbook?

[akedm]

Hi,

Excel offers to change the source of formulas to other external workbooks, but now I want my formula to point to a sheet of the workbook it's in. There's no option for that. All I get with "change source" is a bunch of other external workbooks I can point to. I dont need that. I need it to point to a sheet in THIS workbook. I need to change the source to THIS workbook.

How?

Thanks.

[TMS]

Just go through that process and navigate to your own workbook. Select it and ok out.

[akedm]

Yeah - you'd think. I'm familiar with that process.

But it's not there. My workbook is not showing up as an option. This 'change source' process is anemic, frankly. Maybe it's a SharePoint thing or a OneDrive thing. But whatever it is, it's not there. Changing the source to your own workbook isn't an option, and it shouldn't be as wanderingly confusing as it is anyway. 'Point to yourself' - there, done. At the moment Excel forces you to hunt for the workbook you're actually working in like a dog chasing its own tail.

I can even save my workbook, close down Excel (I'm using the desktop version, not the lesser web-based version), restart it (the desktop version), and my workbook isnt on the list of recently saved workbooks, nor can I navigate to it in the file explorer (cause it's not really in File Explorer's realm - it's on SharePoint).

There's got to be a better way.

Seriously, there's got to be a better way.

I want to change this kind of formula that point to the PATIENTS sheet in another workbook:

Please Login or Register  to view this content.

to this cause the PATIENTS sheet is now in the same workbook as the formula:

Please Login or Register  to view this content.

Thanks.

[akedm]

OMG I just solved it.

First off - what a stupid system Excel has for 'change source' (DATA > WORKBOOK LINKS or EDIT LINKS > ... change source) when re-directing a formula to the workbook you're currently in. So, if you're frustrated, like me - I get it. I'm with you. It's literally like a dog chasing its tail - the thing you're after is already there / already attached, but you got to chase it down. And having the spreadsheet in SharePoint makes it worse.

This was my situation. I wanted to go from a formula referencing another workbook to a formula that pointed to a sheet in its own workbook:

sp change source.png


This will be a bit, but there's a lot to go through.

I'm exclusively working with Excel desktop (not the web version), Windows 11, and I have my files on SharePoint. Starting at the start, when you left-click on the Excel icon, Excel starts. When you right-click on the Excel icon you get a list of the most-recently opened Excel files - like 10 or so (spreadsheets, macro-enabled spreadsheets, etc). The point is if you shut down Excel, and then right-click on the Excel icon, you're going to see the spreadsheet you were just in on that list. This is important because even if your spreadsheet is saved to SharePoint it'll still be on that list.

I say this is important because later on when you're in the 'change source' process, Excel pulls up a list of recently opened files, but it's a different list. This list, unlike the other recently-opened files list, WILL NOT CONTAIN spreadsheets in SharePoint. If you're like me (graphic above) and the spreadsheet is sourced from SharePoint, it's as though the spreadsheet (the one you're in, by the way, at this point of the process) does not exist. How frustrating is that?

So if you're lucky and your spreadsheet is NOT in SharePoint, then you still have to chase your own tail, but you will be able to find your file - it should be on the list. If not, then you may have to hunt for it in File Exlplorer (and maybe someone else has some tips for that).

If your spreadsheet, like mine, is in SharePoint, then you'll need to add a shortcut from the SharePoint into File Explorer because Excel only offers you a recently-opened file list or File Explorer to find the file you're currently in (and yes, "to find the file you're currently in" it's as stupid as it sounds). And for some reason File Explorer doesn't natively connect to SharePoint like it does OneDrive.

This is how I did it. Hopefully there's an easier way:

To get access to SharePoint from File Explorer you need to use OneDrive. Again, if you're like me, SharePoint sorta comes with OneDrive, so in File Explorer along the left side where the folders and directories and drives are listed you should see OneDrive. That'll be your way to make this work.

Go into a browser and open your OneDrive. It's going to look really weird, but a section of the screen (likely the left side) should look familiar. Like the left side of the familar File Explorer, here, along the left side of the OneDrive screen, icons for My Files, Favorites, Quick Access, and so on should appear. And appear somewhat familar. Hopefully you see these. Again, ignore the rest of the weirdness on the OneDrive page, just focus on the part of screen where this stuff like Quick Access and My Files and Favorites are. Since you're reading this far, you have SharePoint and obviously already accessed SharePoint. The next step is to access SharePoint from OneDrive. Under the Quick Access part of the screen you will see your SharePoint drives. They're there because they've recently been accessed. So, in brief, in OneDrive you can access your SharePoint drives - they'll be listed in the Quick Access section.

Click on one of them. Well, actually, click on the drive that has the spreadsheet you're currently in. In my case the SharePoint drive is called 'Intranet - Billing', so I click on 'Intranet - Billing'. Once clicked, you should see your SharePoint folders in that drive.

Along the top will be the link we're looking for: Add Shortcut to My Files. Click it.

Now, it may take a bit to update in File Explorer (mine took a few minutes), but what you just did was add a shortcut in OneDrive to access your SharePoint drive, and more importantly shortcuts such as this will appear in File Explorer. This is why we were in OneDrive - we needed to create a shortcut to SharePoint that will show up in File Explorer, and OneDrive allows this. I know - it's a journey.

Now you have access to SharePoint in File Explorer. The shortcut we just created in OneDrive will appear as a folder when you click on your OneDrive icon in File Explorer (again, my FE took a few minutes to update).

And now you can use the File Explorer option (ie, 'browse' option) in 'change source' process to point to the spreadsheet YOU'RE ALREADY IN (serenity now...). Choose DATA > WORKBOOK LINKS or EDIT LINKS > ... 'change source' > browse > navigate to OneDrive > 'shortcut to whatever SharePoint drive your spreadsheet is saved to'.

Good luck

[TMS]

Thanks for sharing your solution.

[TMS]

Thanks for the rep.