Source Path from a Cell in the Active Worksheet Vs a named cell

petraidm58

New member
Joined
Dec 12, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
365
[FONT=&quot]I am a power query novice so please provide solutions (if there any) at a level I can understand. Here is my problem: [/FONT]
[FONT=&quot]My workbook will contain a large number of worksheets. The sheet name will be a stock ticker symbol and a cell on that worksheet contains a formula that retrieves stock ticker from that cell which I have made a named cell. All of the power query code is written and it retrieves 5 years of data from the intranet without issue. I want to be able to just copy this worksheet and rename it ( a different stock symbol) and viola be done. My problem is the power query on this new worksheet refers to original sheet's named cell or named range.[/FONT]
[FONT=&quot]Is there anyway to pass a parameter from the Active Sheet such that I don't have to go into the query code on each copied worksheet and change the named cell or hard code the source? In other words, I want to use the same power query code on multiple worksheets in the same workbook and be able to retrieve the source from Cell A1 of each worksheet without having to go into the code for that worksheet and editing the code.[/FONT]
[FONT=&quot]Thanks[/FONT]
 
Yes, it is simple to get the value from a named range

Code:
let
    name = Excel.CurrentWorkbook(){[Name=RangeName]}[Content],
    value = name{0}[Column1]
in
    value
 
Bob, I know how to get a value from a named range. Let me try to be more specific with an example.
  • I have a worksheet named VZ
  • The ticker symbol VZ is in cell A1; Cell A1 is named Symbol and the value in that cell comes from the worksheet name.
  • My power query for a worksheet begins with the following:

let
Ticker = Excel.CurrentWorkbook(){[Name="Symbol"]}[Content]{0}[Column1],
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/"&Ticker&"/history?range=10y&interval=div%7Csplit&filter=div&frequency=1d")),
Data2 = Source{2}[Data],
this is then followed by a number of lines to formulate the retrieved data the way I want it.

The query works fine.

I want to duplicate this worksheet several times (which also duplicates the queries) and just change the name of the sheet which then changes the value in Cell A1 then hit refresh all. The problem is the named range in each of the new worksheets and power query refers back to the original worksheet cell A1 because that is where the named range points to.

What I want to do is create a master worksheet that I can duplicate over and over again where the named range points to cell A1 of each duplicate (without having to edit each query) not the original worksheet.
 
You are asking PQ to focus on the currently open worksheet within the same workbook and use the same query over and over again and dump it's output to the currently open worksheet. As far as I know, it simply is not designed to work that way. You'll have to have a separate query for every stock symbol, using a separate unique range name, for them to land on the separate individual workbooks for each stock symbol. You might be able to hack this out using VB, but not using PQ, IMHO.
 
You could use a worksheet-scoped range name. Then at least you could use the same name on each sheet, and you'd only need to edit the sheet name. For example, change "Sheet1" to "Sheet2" in the following code:

Code:
Source = Excel.CurrentWorkbook(){[Name="Sheet1!MyCell"]}[Content]{0}[Column1],

I think you could write a VBA macro to modify the PQ code automatically.
 
Back
Top