Need advice before I build

[subxone]

I'm building an asset forecast model. It will drag information in from an MS Access table, calculate a utilsation value over N period then provide a forecast value over N period. I'm familiar wiht Excel to a point however I would like to clarify a few things before i build:

1. I plan on using power query to bring in the Access table with the asset details
2. The asset details will then be put in a table
3. I will have premade forumlas on the right of the table which will calculate a N period forecast
4. I want to add a slicer on this table so I can slice and dice the assets by the various description fields available <--- Not sure how to do this
5. I want to have the table transposed into a report layout ready for printing or saving as PDF <--- Not sure how to do this

Appreciate any input

[dflak]

This will be a lot more difficult to do since you are using Excel 2003. A lot of the things you want to do are automatic with later versions of Excel.

I don't know about power query, but if memory serves me correctly, MS-Query will bring data onto a sheet and it has an option to replicate formulas so that takes care of point #3.

Slicers are not available for Excel 2003, so you'll probably have to program a user form with a listbox that sets a filter.

For PDF, all you really have to do is record a macro and modify the code it produces.

[subxone]

Sorry dflak I have Office 2016 not 2003

[dflak]

Well, that makes life a lot easier! I am not familiar with power query. Does it bring the data into an Excel table? If it does, then columns added immediately to the right of the returned data will have their formulas "memorized" and copied down for each row.

Slicers are easy. Select a cell in the returned data. Select Insert -> Slicer. You will get a list of column headers for which you can create a slicer. Also if you select a slicer and look in slicer options you can do a lot of things to make it look better.

As for publishing as a PDF, here's the macro I recorded.

Please Login or Register  to view this content.

I would not depend on ActiveSheet. Use Sheets("Sheet Name") instead. The only other thing is to change the file name to something you want. Generally I "compute" the file name based on a date stamp either in the code or on the spreadsheet and read it in with Range().

[subxone]

dflak, would Power Pivot be a better option to bring data in from a Access database table? The reason I'm hesitant to use it, is because it can only summarise data. I need to be able to interact with the data. For example the system will run its calculations however the user will have the option to overwrite values as the see fit. - System suggests product A is classified as class 1, the user can overwrite that and say class 2 instead.

With tables, can I reference them in other sheets - eg. Sheet1 has tblFormulas, can I make a reference to tblForumlas and have it transposed/linked in Sheet2?

Slicers are working great! And thanks for the vba, I'll add it in the later stages of the build.

[dflak]

If power query is working for you, by all means, stick with it. It's just that I've not used it and am not familiar with what it does and doesn't do.

My weapon of choice is MS-Query.

[subxone]

I think its the same thing dflak, I go to Data > New Query > From Database > From MS Acces Database

[dflak]

Actually it's different. Data -> From Other Data Sources -> MS-Query. As long as you have the drivers for it, you can make a database out of any ODBC connection, Access, Other Excel Spreadsheets and text files. MS-Query also allows passing parameters at run time.

I think for Access it doesn't make that much difference. As long as the data comes out as tables, you are good to go.