Need to keep formulas in spreadsheet

[csimpson24]

Hi all,

Here's my scenario. We have sales people who travel everywhere and sometimes VPN connectivity is limited. In our business system, we have a dashboard of their sales data that they want to copy into Excel to use while they travel. The spreadsheet needs to have formulas to calculate things such as profit, mark up, margins, etc. I have a couple of ideas to accomplish this. Dump the data from the dashboard to a CSV file, have a prebuilt spreadsheet containing the necessary formulas in the correct cells that is always linked to that CSV file. Possible?
Or
Have the prebuilt spreadsheet. They copy the data from the dashboard, paste into the spreadsheet while retaining the formulas. Possible?

If either is possible, I may need some help in doing so. Possibly.

Thanks in advance,
Chris

 

[Joe4]

I think I would export the data to a CSV file.
Then, I would create an Excel file with VBA code that will import the CSV file, and apply whatever formulas you need.
So if you distribute this Excel file with the VBA code in it to all the users, all the need to do is open that file, and then run the Macros against the CSV file.

A lot of the VBA code that you need can be obtained using the Macro Recorder, to record yourself performing the steps manually. That will give you a lot of what you need, though you will probably need some assistance to make it more dynamic (as the amount of data probably will differ each time).