cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ptmuldoon
Frequent Visitor

NamedRange from a different Workbook

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

 

 

 

 

1 REPLY 1
lbendlin
Super User III
Super User III

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).

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors