Template Query Copy Issue

[xMIKExSMITHx]

I am creating a template for my company. In my workbook, Sheet1 is the template along with Query 1, and the name range "Client_Input" = the value in cell A1. I use this to drive the query.

When I right click on the Sheet1 tab and create a copy, a new sheet is created, Sheet1 (2), Query 1 is duplicated but references the output to Sheet1 (2) as I want, however, the query hard codes the "Client_Input" which is still referenced on Sheet1.

This is an issue because say I copy the template 20 times, every copy references the original "Client_Input" instead of the cell A1 on the respective sheet's copies.


Is there a way to fix this without having to go into name manager and change each sheets A1 reference and each sheets query?

 

[ExcelGzh]

xMIKExSMITHx

What you need is a named range that has a sheet scope rather than a workbook scope. The scope of a named range can't be changed after it's been created so you will need to delete the name "Client_Input" and re-create it setting the scope to be Sheet1 instead of Workbook. When you make copies of Sheet1 the named range "Client_Input" will come across with the copied sheet and point locally at the new sheet. If you go into Name Manager you will see that there will be two named ranges called "Client_Input", one scoped to Sheet1 and the other scoped to Sheet1 (2). The Query should reference the named range of whichever is the Active sheet at the time unless you specify different with a sheet reference in your code.

 

[xMIKExSMITHx]

Thanks for the reply. I tried to do what you suggested. and created a mock up sample file, linked below.

The steps I took are listed below:

• The data tab is set as a table and loaded to PQ as named range "DataTable", scope - workbook
• Cell B2 on the "template" tab is set as named range "CustomerFilter", scope - Template (the worksheet name)
• M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer", "Customer Name"}, {{"Total Purchases", each List.Sum([Amount]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer Name] = CustomerFilter))
in
    #"Filtered Rows"

Sample File for Template Copies with PQ

Now if I want to create a tab for each person, I would right click on the "Template" tab, move to end, and check create copy. However, when I change the Customer name in cell B2 on the new worksheet, it is still pointing back to the original B2 range from the original template sheet. I was hoping this would dynamically change when copying the template to that sheets cell B2. Is there a workaround or did is my code wrong?

 

[ExcelGzh]

I'm afraid PowerQuery is outside my area of expertise, but in fishing around with my limited knowledge it seems to me that the CustomerFilter referred to in the PowerQuery is not the same as the CustomerFilter named range. The CustomerFilter in PowerQuery is a query within PowerQuery that just happens to have the same name as the named range. See screenshot. It refers to the named range but is tied to the sheet the named range came from when the query was first defined. You will need to find a way to redefine the query CustomerFilter for each sheet (and perhaps rename it to avoid confusion).