Results 1 to 5 of 5

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

  1. #1
    Neophyte petraidm58's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am a power query novice so please provide solutions (if there any) at a level I can understand. Here is my problem:
    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.
    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.
    Thanks

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,706
    Articles
    0
    Excel Version
    O365
    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

  3. #3
    Neophyte petraidm58's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    365
    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.

  4. #4
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    51
    Articles
    0
    Excel Version
    Excel 2016
    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.

  5. #5
    Seeker Richard Wein's Avatar
    Join Date
    Oct 2017
    Location
    UK
    Posts
    5
    Articles
    0
    Excel Version
    365
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •