NamedRange from a different Workbook

[ptmuldoon]

Is there a way to get cell value from a named range in a different workbook?

I am trying to avoid storing a username and password when connecting to an API directly in either Power Query or within the excel workbook itself.

Currently I am getting the named range for each value from a worksheet in the same workbook that is 1) wingdings font masking the characters, 2) protected the sheet hiding the formulas/values, 3) made that sheet 'Very Hidden', and 4) password protected the VBA.  And while that would stop the common user from finding it, any power user could ultimately find a way to get at it.

So I'm hoping to keep the UserName and Pass in a separate file either in OneDrive or Sharepoint, and then reference the named range in that file.  That would also give me a central place to update the user/pass should it need to be changed vs having to change it in multipe workbooks.

In creating a link to a file in one drive, I get somethign like this:

='https://mysharepointsite/Documents/LinkTest.xlsx'!UserName

Is it possibly to modify the below or a new separate function to reference another workbook?

let GetNamedRange=(NamedRange) =>
 
let
	name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
	value = name{0}[Column1]
in
	value

in GetNamedRange

[lbendlin]

cute. welcome to the formula firewall club.

You could store the credentials in a file in a sharepoint folder that only you have access to (assuming your login owns the dataset) and then treat it as just another data source that you include in the refresh but not the report load (to prevent leakage into the Power BI data model).