Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community

Create a template that updates when a unique list is changed/updated in excel

Copper Contributor

Hi,

 

I am creating a custom report that references multiple data sheets in excel. I am trying to work on a project, and found out that the best way to get what I want is to extract the necessary data from different reports into a customer report template. 

 

I have a list of campuses (i.e. Orange County, SD, New Orleans, etc.). The data I am working with is a student data. Say I have a reference sheet that I store all unique campuses in. The campuses list may change overtime. And say I have two degrees (that is not the case, as I am working with over 12+ degrees) to keep it simple- like the campuses list, the unique list of degrees would be stored in another sheet within the same report I am working on. Say I have a third list of terms, which also may change overtime depending on the year. My final template is as follows, and my goal is to add to the template whenever a new campus or degree is created. The xlookup becomes so easy once I am able to create this template. However, the list of campuses may change as well as the list of degrees.. In the example below, I am working with OC and New Orleans campus, as well as two degrees (Mathematics & English). In my template, I need to create a combination of every campus & Degree in my list and command excel to create the 12 terms (Jan-Dec) format for all possible campus/degree combinations. Is this something possible in excel? Say I add to my list SD campus, then I'd like excel to insert 24 lines (12 lines for the 12 terms for Mathematics and 12 for English). Also, say I add another degree, Engineering, then I'd like excel to insert an additional 12 lines for each campus that will show (campus|Degree|Term as Orange County|Engineering|Jan-Dec terms; New Orleans|Engineering|Jan-Dec terms, and so on and so forth. I understand my question may be complicated and there may not be a solution within excel, but worth a try. Thank you in advance for assisting me with this! I sincerely appreciate your time. 

aexcel23_0-1704467565560.png

 

 

 

2 Replies

Hi @aexcel23 

 

With Get & Transform aka Power Query. In attached workbook 3 tables:

- TableCampus

- TableDegree

- TableTerm

 

#1 Update the above tables as needed (add, remove... items)

#2 Switch to sheet 'ExpectedTable'

#3 Right-click somewhere in the 'QueryResult' table > Refresh