Am I asking tooooo much?

[TripleXhell]

This is a question in general, before I raise this question in the relevant forum I want to make sure it's realistic, so please do not move this mistaking it for an Excel 'problem'. It's rather in enquiry.

I'm not sure what i need to be able to do is for Excel or Access. I'm trying to do it in Excel, since I've all ready setup the foundations. But i feel i may be askign excel to do "too much". Hence this topics title.

What I need to do:

Every month I recieve a Access database that includes the assets of the whole compnay. This company is huge, split into 4 businesses.

I only need to know 1 of these businesses, which has around 45 departments.

So, simple enough, I've setup a master spreadsheet that runs a query grabbing all the information I need for this business. Easy. A pivot tabel also breaks things down for the whole business... but thats not enough.

Then, I go through the painful task of breaking it down for each deaprtment. So each department can access them.

I did this manually at first, editting the query for each department and saving them seperately.

BUT - this has to eb done every month.

I want to be able to refresh every sheet. The master to refresh off the monthly database and the departments to refresh too.....This is like 45 + spreadsheets with associated pivot tables.

Am I expecting too much of Excel?

 

[Norie]

If you are receiving the data in Access surely it's logical to work with it in Accesss.

Access has far more powerful reporting features.

What are you actually doing with the data?

 

[TripleXhell]

The data is filtered by department, saved seperately and then uploaded to the intranet so they can be accessed by the individual departments.

I've had a re think, and now i'm still using Excell, but trying to record a macro that can auto filter and save as, every month. This means i've only got to do it once - and then every month - 'click a button'.

What do u think? Would access be easier?

 

[Norie]

Yes it would be.

You would probably still need to use code though, to for example create and run queries dynamically.

 

[TripleXhell]

Here's what I've done - and it works to a treat, so i'm satisified..

I kept the master spreadsheet which run th query to the database. i need did a HUGE macro that did further queries and save'as for me. I recored me doing it twice, and then just repated and modded the code - and tested it before i did all 45.

A few copy and pastes later and Boom - i have the functions i wanted.

So next week when i get the monthly update, i smply run the master query - and then run my macro - Walla!

I'd still be interested in how it would have worked in Access?

 

[Norie]

Without knowing what data you have and how you are manipulating it, it's hard to say how it would be done in Access.

If you were just filtering out the data for each department and sending it to them, or outputting it elsewhere you could probably do it with 1 short macro.

The macro would dynamically create queries to return the required data and then do whatever with it.

If there was more data manipulation then obviously it would be more complicated.

 

[TripleXhell]

A short macro? Sounds interesting.

As it stands.. The monthly access database comes in.
I run an Excel query that filters out my company section. This is the master.
Then from the master I edit the query and filter out the indivdual depots, and save it seperately under it's depot name.

I've created a big macro that does it for me now. It filters the depot, and saves it. It was a lomg process of copy pasting and modifying the macro to do it for all depots.

What would be the quick way?

 

[Norie]

Like I said it could be done in Access by creating queries dynamically to do the filtering.

By the way it could probably be done in Excel in one macro rather than repeating the same macro and changing some values.

Again it's hard to give specific information because I don't know what data you have or how it's structured.

 

[gwkenny]

.

Agree with Norie. If you get the data in Access, it's easier to do in Access.

Unfortunately, for someone who doesn't know Access, then it's a whole different world.

It's worth the time and effort to learn though.

The quick and dirty way I'd do it in Access is to use Make Table queries and put them into separate databases to send to their respective departments.

If the stuff is originally submitted in Excel, then pivot tables and filters, maybe with some macros, would be the way to go.

But again, as Norie said, without a better description from you it's a bit like shooting blind.

Just an aside, for manipulation of large amounts of data I use Access. Extremely helpful in analyzing operations, purchases and expenditures.

Forecasting and scenario analysis (which includes cashflows and risk analysis/exposure stuff) I use Excel.

 

[TripleXhell]

It is one macro Norie. But it's a repeated process.

I recorded myself doing the procedure twice. Tested, then copy pasted the code in itself, changed the filter name and save as name (find, replace) and tested it again. This successfully filtered and saved four depots. So I went and repeated it again and again and again until I had the whole of the depots in the macro. I then delted the ones I had tested. Opened up a new work book and copied over the macro.

So now, I open up the macro spreadsheet. Then open up the Master. I refresh the master, and it's pivot table. then run the macro in the Master from the macro spreadsheet. I then sit back and watch it do the work for me...

Another macro is written to the pivot sheet to refresh when opened... so each sheet will refresh the pivot table for the particular depot.

It works quite nicely now....

The Acess way seems like even more work.. but I'm likely wrong.