Running excel data from another program. Then have to manually click 28 employees out of 130+ by name through the filter. Any way to batch different employees?

Question

I run a work hours program called Costpoint. I either have to filter my 28emps in the Costpoint program or when the spreadsheet opens up, I have to manually click each name through the filter process. Is there any way to batch this Costpoint driven spreadsheet information into my own personal filters such as "weld", "machining"? Or am I at the mercy of Costpoint that doesn't have those parameters/filters in its own program?

I want to run this spreadsheet for my dept which has both machining & welding. I would rather not have to manually click each employee individually through the filter button, or have the separate depts already filtered into weld and machining employees.

I'm choosing not to screenshot due to being a federal workplace.

Answer 1

Andreas,

 This ended up being worked out on the Costpoint side by an admin. Thank you for your help and quick reply.

Jacob

Answer 2

I run a work hours program called Costpoint. I either have to filter my 28emps in the Costpoint program or when the spreadsheet opens up

Jacob,

That sounds as if the Costpoint application generates a (new) spreadsheet every time you export data. Is that correct?

If the answer is yes, my next question is if the data in the spreadsheet is well organized, e.g. in a table?

Are the any other data formats you can export?

The idea behind my questions is this: If you want to do recurring analyses, you can use Excel to import and process the exported data in a separate file using Power Query.

Once you have the data you can filter it with slicers, create pivot tables/charts. Or you can create a sheet for every single employee using Power Query.

After you've done all that, save the file as "Costpoint analysis.xlsx" and if you export new data next time all you have to do is to open your analysis file and click Data \ Refresh All

All that is one way. If that is not possible, there is another way:

Use VBA (macros), we can store them in an AddIn or your PEROSNAL.XLSB (or just another file). With VBA you can automate a lot of steps, but not everything. Unfortunately, you need a lot of experience for this.

If you work in a federal office it will be difficult to help you as we need to see the structure of the file and the data. The data itself is not important, I also have a macro that can be used to make data anonymous. However, it is questionable whether you are allowed to use this macro.

How would you like to proceed?

Andreas.