Dynamically Generated Queries

[DanAspire42]

Hi all,

I have a master list with a column of staff members names.

I need a query per staff member (to load to a table/sheet in excel)

Currently I have manually referenced the master query then filtered staff member column to a given name.

These names on this list change at any time, is there a way to have a referenced query automatically generated for each unique staff memebr name automatically and filtered to that name when the names on the master query change?

Any help appreciated.

[DanAspire42]

Hi @lbendlin 

Pulling data from a database, we have a ledger of clients where each is assigned to a staff member given some variables. Management will occasionally add/remove staff members assigned to these clients.

I have to build a workbook that has a worksheet for each staff member who currently has some clients assigned.

Currently I am referencing the main query with the complete list of clients and respective staff, then filitering staff asigned column to a given staff members name manually, rinse and repeat for ~20 staff members.

Is there a way for excel to automatically generate a new query when a new staff member is added to this list and hence automatically load a new worksheet with the table for that new staff member.

Haven't been too sure how to describe this process simply for google.

Unfortunately unable to make any changes to databse structure has to be all completed with Excel.

Regards

[lbendlin]

how is that new staff member assigned to clients?

[DanAspire42]

Comes from database, is just a column of staff names, with each row being a client and then their respective staff member in the column

[lbendlin · Accepted solution]

In general you cannot have Power Query queries created on the fly, so I would say no. But I have no experience with Excel - maybe you can fudge something through Office scripts.

[DanAspire42]

Hi @lbendlin ,

Thanks for your help, I read this was the case in an older post was hoping that might have changed. Have achieved what I needed via VBA to create new worksheets.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?