none
Getting details from Excel RRS feed

  • Question

  • Sorry, guys, you have been very helpful before, and I need help again!

    I have a series of worksheets in an Excel workbook, let us all them SheetA, SheetB, SheetC etc.

    Each has the same basic query on it but pulling in detail from different sources as specified by a parameter on the worksheet.  The parameter is in a table with a local range name, so is accessed in the query by code like this:

    Parameter = Excel.CurrentWorkbook(){[Name="SheetA!Parameter"]}[Content]

    Easy enough so far but:

    1.  This breaks if the user changes the sheet name 

    2.  Whilst the query gets copied across to a new worksheet, if the worksheet is copied, the sheet name in the code does not change but continues to refer to the old sheet

    What I need is something similar to:

    Parameter = Excel.CurrentWorksheet(){[Name="Parameter"]}[Content]

    but I  can't see how to get that effect. 

    M

    Wednesday, December 5, 2018 11:09 AM

All replies

  • M, have you made any progress on this issue?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, April 5, 2019 2:34 PM
    Owner
  • How about some more info on the sheets?

    Are they all in the same book? Is that something you have control over?

    If so I could see having a special page with a list of the sheet names in the workbook. The use that as the parameter list to redirect to the item of interest. Then you need only a formula to list all the sheet names in a workbook as a named table.

    Without any control over the format or even naming conventions, it will be hard to universally figure out what you'll find.

    Thursday, May 16, 2019 5:25 PM