How to auto populate a table based on name and date?

%3CLINGO-SUB%20id%3D%22lingo-sub-2365943%22%20slang%3D%22en-US%22%3EHow%20to%20auto%20populate%20a%20table%20based%20on%20name%20and%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365943%22%20slang%3D%22en-US%22%3E%3CP%3EFairly%20frequently%20I%20run%20a%20report%20for%20our%20store%20managers%20showing%20them%20a%2030%20day%2C%2060%20day%2C%20or%20yearly%20performance%20report%20from%20our%20employees.%20I%20need%20to%20show%20the%20date%20and%20performance%20%25%20for%20each%20employee%20in%20these%20reports.%20I%20am%20new%20to%20this%20job%20and%20the%20previous%20guy%20had%20an%20automated%20way%20to%20pull%20these%20reports%20using%20excel%2C%20but%20I%20don't%20know%20how%20it%20was%20done.%20I%20have%20a%20template%20built%20out%2C%20but%20I%20am%20essentially%20wanting%20to%20be%20able%20to%20select%20a%20name%2C%20go%20through%20a%20list%20of%20employees%2C%20and%20display%20in%20the%20report%20their%20performance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETemplate%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%229389e51d78d11f1218120f43f5d99a30.png%22%20style%3D%22width%3A%20669px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281620i80CDB3EEE971A33E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%229389e51d78d11f1218120f43f5d99a30.png%22%20alt%3D%229389e51d78d11f1218120f43f5d99a30.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20I%20want%20to%20pull%20the%20data%20from%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tttt.png%22%20style%3D%22width%3A%20316px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281623i217F797FEAD5C29A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22tttt.png%22%20alt%3D%22tttt.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20there%20has%20to%20be%20an%20easier%20way%20to%20do%20this%20rather%20than%20copying%20and%20pasting%20each%20day%20manually%2C%20which%20is%20insanely%20time%20consuming.%20Any%20help%20would%20be%20MUCH%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2365943%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2366893%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20auto%20populate%20a%20table%20based%20on%20name%20and%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1057163%22%20target%3D%22_blank%22%3E%40brochelle%3C%2FA%3E%26nbsp%3BHow%20important%20is%20the%20report%20template%3F%20If%20you%20have%20free%20hands%20to%20create%20something%20better%2C%20consider%20using%20a%20Pivot%20Table.%20A%20powerful%2C%20built-in%2C%20tool%20that%20requires%20a%20minimum%20of%20preparation.%20And%20fairly%20easy%20to%20learn.%20Are%20you%20familiar%20with%20pivot%20tables%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20appears%20to%20be%20perfectly%20organised.%20Added%20two%20calculated%20columns%20and%20with%20a%20few%20tweaks%2C%20you%20can%20create%20a%20report%20like%20in%20the%20attached%20file.%20Add%20data%20at%20the%20bottom%20of%20the%20table.%20Press%20Refresh%20All%20on%20the%20Data%20ribbon%20and%20the%20report%20gets%20updated%20instantly.%20Not%20exactly%20the%20same%20as%20what%20you%20produce%20now%2C%20but%20similar%20and%20fully%20automatic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2367997%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20auto%20populate%20a%20table%20based%20on%20name%20and%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2367997%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20template%20is%20not%20super%20important%2C%20just%20what%20the%20managers%20are%20used%20to.%20This%20is%20great%20though!%20I%20just%20got%20a%20request%20from%20a%20manager%20needing%20performance%20reports%20on%20all%20of%20their%20location's%20employees%20and%20this%20pivot%20table%20will%20be%20great%20for%20showing%20a%20mass%20number%20of%20employees!%20I%20assume%20I%20could%20just%20create%20a%20slice%20for%20that%20pivot%20to%20show%20between%202%20specific%20dates%2C%20right%3F%20This%20will%20be%20super%20helpful!%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20the%20solution%20I%20found%20for%20the%20current%20template%20is%20I%20just%20use%20an%20xlookup%20using%202%20search%20params(combined%20using%20'%26amp%3B'%20I%20didn't%20even%20know%20this%20was%20possible).%20It%20gets%20the%20job%20done%2C%20just%20fairly%20slow.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Fairly frequently I run a report for our store managers showing them a 30 day, 60 day, or yearly performance report from our employees. I need to show the date and performance % for each employee in these reports. I am new to this job and the previous guy had an automated way to pull these reports using excel, but I don't know how it was done. I have a template built out, but I am essentially wanting to be able to select a name, go through a list of employees, and display in the report their performance. 

 

Template:

9389e51d78d11f1218120f43f5d99a30.png

 

Where I want to pull the data from:

tttt.png

 

I know there has to be an easier way to do this rather than copying and pasting each day manually, which is insanely time consuming. Any help would be MUCH appreciated!

2 Replies

@brochelle How important is the report template? If you have free hands to create something better, consider using a Pivot Table. A powerful, built-in, tool that requires a minimum of preparation. And fairly easy to learn. Are you familiar with pivot tables?

 

The data appears to be perfectly organised. Added two calculated columns and with a few tweaks, you can create a report like in the attached file. Add data at the bottom of the table. Press Refresh All on the Data ribbon and the report gets updated instantly. Not exactly the same as what you produce now, but similar and fully automatic.

The template is not super important, just what the managers are used to. This is great though! I just got a request from a manager needing performance reports on all of their location's employees and this pivot table will be great for showing a mass number of employees! I assume I could just create a slice for that pivot to show between 2 specific dates, right? This will be super helpful! Thank you!

 

Also, the solution I found for the current template is I just use an xlookup using 2 search params(combined using '&' I didn't even know this was possible). It gets the job done, just fairly slow.