Need suggestion - best workflow for getting external data, other .xls

[astrbac]

Hello guys,

I have a simple problem to which I do know a quite crude solution. I am wondering if there is a better (or rather a more elegant) one. I regularly download report data as either a .csv or .xls (I prefer the latter) from a Google Analytics website. I then format it visually, filter and sort the way I need it. However, I don't delete enything, I just hide the data I don't need to see.

What would be the best way to refresh the data in this formatted workbook with the new data that I export and download from my Analytics site?

I was thinking that I could:

1. 1. duplicate the 4 working sheets that I have in the formatted workbook
2. 2. un-format, un-filter and unhide everything in these duplicated sheets
3. 3. simply paste the data from the downloaded workbook into these accessory sheets
4. 4. From there on it's just a matter of formulas in the working sheets ("=...") to get the data where it should be.

I believe there must be a more elegant solution though?

Thanks!

[gak67]

I can suggest 2 things:

1. Create a VBA code to automate the formatting, filtering and sorting
2. Have the formulas refer to entire rows or columns (as applicable) so when you add data you don't need to adjust them

[astrbac]

gak,

thanks for your reply! While I was waiting for someone to reply to my initial Q, I played around a bit in Excel and figures this out:

- I actually don't need VBA because I sort and filter as I am in a meeting with my boss or a client (filters on top of columns and we play with them)
- I decided that I will wither use Tabelau software if I get the budget or Excel plugin that extends Excel and allows it to connect directly to the Google API

If none of this proves feasible, I think this might be ok:

1. create one master Workbook where the data is pulled into
2. log into Google, download raw data as .xls (4 separate, single sheet workbooks)
3. create connections to external data from Master workbook to these 4 separate workbooks, into 4 different sheets
4. daily download raw data and paste over "4 separate workbooks" from which data pulled into the master one
5. play around with the data in the Master workbook

The only problem I need to sort is the cell formatting. In the sheets I download from Google, everything is stored as "General" and currency displayed as 1,111.11 (I need it as 1.111,00)

Any simple tips on how to solve this one?