Best tool to use?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hoping you can point me in the right direction.

Every month we download the credit card charges of our staff from the bank, then we pivot the info. send it to each cardholder and ask them to put in a description of the charge and the account and project coding for it.  They send the tables back with the added information and it is pasted back into the data worksheet and then reformatted to be imported into the accounting software.  The accounting software credit card module is unwieldy for our staff to go in every day or once a month and add in the required information.  The system requires them to pick a vendor and that leads to other issues.  Such as, creating single use vendors, picking the wrong vendor and impacting the 1099 reporting. That could certainly create havoc.

We thought that putting the file with the pivot tables on SharePoint and sending everyone the link would make things a bit easier but weren't sure how to restrict them to their own information and not inadvertently messing with someone else's. I don't even know if multiple restrictions are possible. Allowing them to see what others are doing is not the problem. Plus, currently when we do this we copy and paste the individual cardholder's table as values into a separate file, so that they can supply the needed information inside the table in the space provided.  

So, if I haven't bored you to tears by now, how can we automate this process so that there is one file that others can update and it automatically compiles the information into one data set and formats it for importing?  I know we can do a macro to do parts of this process given that we knew how to write such a macro. In addition I would like to add the list of monthly charges to a master list for later analysis on spending (charging) habits.  (Totally different set of macros for that.)

Any suggestions as to which tool would be the best route to take?  Power Query, Power Pivot, Power BI?  Any venture is a learning process but the issue is time.

Thanks